Mysql資料庫單向同步（一主兩從）

winsonyuan發表於2012-07-05
MySql資料庫


一、mysql的主從同步配置

要求，mysql主從資料庫，一主兩從，其中：

119.161.145.209是主資料庫，119.161.145.215是從資料庫1，119.161.145.216是從資料庫2

 <wbr>

mysql伺服器IP地址規劃

 <wbr>

主資料庫-ERP

從資料庫一（shop1）

從資料庫二（shop2）

公網IP(eth0)

掩碼

閘道器

私網IP(eth1)

119.161.145.209

255.255.255.224

119.161.145.193

192.168.10.204

119.161.145.215

255.255.255.224

119.161.145.193

192.168.10.206

119.161.145.216

255.255.255.224

119.161.145.193

192.168.10.202

 <wbr>

Mysql資料庫單向同步（一主兩從）文件

 <wbr>

 <wbr>

 <wbr>

二、Mysql安裝資訊

my.cnf位置：/data0/mysql/3306/my3306.cnf

定義

socket = /data0/mysql/3306/mysql3306.sock

basedir = /usr/local/webserver/mysql

datadir = /data0/mysql/3306/data

log-error = /data0/mysql/3306/mysql_error.log

include = /usr/local/webserver/mysql/include/mysql

libs = /usr/local/webserver/mysql/lib/mysql

 <wbr>

連線本地mysql

mysql -uroot -p123456 -S /data0/mysql/3306/mysql3306.sock

連線遠端

mysql -ureplication -prepmysql -h 192.168.10.204

 <wbr>

手工啟動mysql

/usr/local/webserver/mysql/bin/mysqld_safe --defaults-file=/data0/mysql/3306/my3306.cnf 2>&1 > /dev/null &

手工關閉mysql

/usr/local/webserver/mysql/bin/mysqladmin -u root -p123456 -S /data0/mysql/3306/mysql3306.sock shutdown

 <wbr>

指令碼啟動與關閉mysql

/data0/sh/mysqlservie.sh {start;stop;restart}

 <wbr>

以root賬號連線到本地ERP資料庫

mysql -uroot -p -S /data0/mysql/3306/mysql3306.sock

遠端連線到ERP資料庫，賬號與密碼replication：repmysql

mysql -ureplication -prepmysql -h 192.168.10.204

 <wbr>

檢視ERP伺服器 mysql資料庫裡的使用者名稱

show databases;

use mysql;

 <wbr>

三、在ERP上建立商城資料庫的賬號（主資料庫）

 <wbr>

1、建立一個replication賬號，密碼repmysql，只有192.168.10.202可以訪問，此賬號為資料庫同步賬號

grant replication slave on *.* to 'replication'@'192.168.10.202' identified by 'repmysql' with grant option;

grant file on *.* to replication@'192.168.10.202' identified by 'repmysql'; <wbr>

grant all privileges on backup.* to replication@'192.168.10.202' identified by 'repmysql';

同理建立206與210的replication賬號

grant replication slave on *.* to 'replication'@'192.168.10.206' identified by 'repmysql' with grant option;

grant file on *.* to replication@'192.168.10.206' identified by 'repmysql'; <wbr>

grant all privileges on backup.* to replication@'192.168.10.206' identified by 'repmysql';

 <wbr>

grant replication slave on *.* to 'replication'@'192.168.10.210' identified by 'repmysql' with grant option;

grant file on *.* to replication@'192.168.10.210' identified by 'repmysql'; <wbr>

grant all privileges on backup.* to replication@'192.168.10.210' identified by 'repmysql';

 <wbr>

flush privileges;

 <wbr>

2、順便建立網站的讀寫分離賬號：oucampdb_write與oucampdb_read

A：建立oucampdb_write賬號，密碼123456，192.168.10網段都可以登入。此賬號為商城讀寫分離資料庫的主庫寫賬號

INSERT INTO user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv) VALUES ('192.168.10.%','oucampdb_write',PASSWORD('123456'),'Y','Y','Y','Y');

 <wbr>

B：建立oucampdb_read賬號，密碼123456，192.168.10網段都可以登入。此賬號為商城讀寫分離資料庫的主庫讀賬號

INSERT INTO user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv) VALUES ('192.168.10.%','oucampdb_read',PASSWORD('123456'),'Y','Y','Y','Y');

flush privileges;

重新整理

flush privileges;

 <wbr>

select * from mysql.user;

 <wbr>

root

localhost
127.0.0.1

replication

192.168.10.202
192.168.10.206
192.168.10.210

oucampdb_read

%

127.0.0.1
192.168.10.202
192.168.10.206
192.168.10.210
192.168.10.%

oucampdb_ write

192.168.10.%

nagios

192.168.10.210

 <wbr>

 <wbr>

命令備註

修資料庫賬號，改使用者名稱oucampdb_write為oucampdb_read

update user set User='oucampdb_read' where Host='192.168.10.%';

 <wbr>

 <wbr>

四、修改主與從資料庫的my.cnf

在ERP的mysql的配置檔案/data0/mysql/3306/my3306.cnf中的[mysqld]中增加

log-bin <wbr> = /data0/mysql/3306/binlog

server-id <wbr> = 3

 <wbr>

在shop1的mysql的配置檔案/data0/mysql/3306/my3306.cnf中的[mysqld]中增加

log-bin           <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> = /data0/mysql/3306/binlog

server-id         <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> = 4

binlog-do-db      <wbr><wbr><wbr><wbr><wbr><wbr> = oucamp_db

binlog-ignore-db  <wbr><wbr> = mysql

master-host       <wbr><wbr><wbr><wbr><wbr><wbr><wbr> = 192.168.10.204

master-user       <wbr><wbr><wbr><wbr><wbr><wbr><wbr> = replication

master-password   <wbr><wbr><wbr> = repmysql

master-port       <wbr><wbr><wbr><wbr><wbr><wbr><wbr> = 3306

replicate-do-db   <wbr><wbr><wbr>  <wbr>= oucamp_db

replicate-ignore-db <wbr> = mysql

master-connect-retry = 10

slave-skip-errors    <wbr><wbr><wbr>= 1032

 <wbr>

在shop2的mysql的配置檔案/data0/mysql/3306/my3306.cnf中的[mysqld]中增加

log-bin           <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> = /data0/mysql/3306/binlog

server-id         <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>  <wbr>= 6

binlog-do-db      <wbr><wbr><wbr><wbr><wbr><wbr> = oucamp_db

binlog-ignore-db  <wbr><wbr>  <wbr>= mysql

master-host       <wbr><wbr><wbr><wbr><wbr><wbr><wbr> = 192.168.10.204

master-user       <wbr><wbr><wbr><wbr><wbr><wbr><wbr> = replication

master-password   <wbr><wbr><wbr> = repmysql

master-port       <wbr><wbr><wbr><wbr><wbr><wbr><wbr> = 3306

replicate-do-db   <wbr><wbr><wbr>  <wbr>= oucamp_db

replicate-ignore-db <wbr> = mysql

master-connect-retry = 10

slave-skip-errors    <wbr><wbr><wbr>= 1032

 <wbr>

 <wbr>

五、mysql的同步配置

 <wbr>

1、主庫鎖庫，獲取MASTER_LOG_FILE與MASTER_LOG_POS的值，並且匯出資料庫的備份

 <wbr>

a：主庫鎖庫

flush tables with read lock;

b：使用命令show master status檢視主庫的MASTER_LOG_FILE與MASTER_LOG_POS的值，手工做從庫同步主庫在除錯，需要這2個引數：        <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>

show master status;

c：解鎖（先不要執行這個操作，2個從庫同步之後再解鎖）

unlock tables;

 <wbr>

2、在主庫上匯出要備份的資料庫並SCP到從庫1和2

cd /data0/mysql/3306/

/usr/local/webserver/mysql/bin/mysqldump -u root -p -S /data0/mysql/3306/mysql3306.sock oucamp_db > oucamp_db.sql

scp oucamp_db.sql root@192.168.10.206:/data0/mysql/3306/

scp oucamp_db.sql root@192.168.10.202:/data0/mysql/3306/

 <wbr>

3、在從庫上匯入備份的oucamp_db.sql，分別在shop1與shop2兩個從庫上操作

cd /data0/mysql/3306/

mysql -uroot -p -S /data0/mysql/3306/mysql3306.sock

輸入資料庫root密碼

 <wbr>

刪除oucamp_db資料庫

drop database oucamp_db

建立新的oucamp_db資料庫

create database oucamp_db;

匯入資料庫sql

use oucamp_db;

source oucamp_db.sql;

 <wbr>

配置mysql同步，從庫（slave database）修改完my.cnf後，需要執行的語句，注意MASTER_LOG_FILE與MASTER_LOG_POS的值要在主庫獲取（見上）   <wbr><wbr><wbr>

 <wbr>

slave stop;

 <wbr>

CHANGE MASTER TO MASTER_HOST='192.168.10.204',

MASTER_PORT=3306,

MASTER_USER='replication',

MASTER_PASSWORD='repmysql',

MASTER_LOG_FILE='binlog.000054',

MASTER_LOG_POS=2697328;

 <wbr>

slave start;

注：執行從庫的CHANGE MASTER命令，主庫必須在鎖表狀態，否則同步出1032錯誤

 <wbr>

檢視從庫狀態   <wbr><wbr><wbr>

show slave status\G;

quit

 <wbr>

六、檢視mysql的錯誤日誌，檢測同步狀態是否有錯誤

tail -30 /data0/mysql/3306/mysql_error.log

沒有錯誤的話，主庫可以執行unlock tables解鎖

 <wbr>

測試主從資料庫同步，在主庫上建立一個test表，插入一條資料，然後在從庫上看這個表以及這條資料是否存在：

主庫上操作

mysql -uroot -p -S /data0/mysql/3306/mysql3306.sock

 <wbr>

use oucamp_db;

create table test(id int,name varchar(20),company varchar(40));

insert into test values(1,'aaa','wwwwwwwww');

select * from oucamp_db.test;

從庫上操作

mysql -uroot -p -S /data0/mysql/3306/mysql3306.sock

 <wbr>

use oucamp_db;

select * from oucamp_db.test;

 <wbr>

 <wbr>

查詢mysql版本號     <wbr><wbr><wbr><wbr><wbr>

SHOW VARIABLES LIKE 'version';

 <wbr>

查詢mysql服務狀態 <wbr>

ps -ef |grep mysql

相關文章