MySQL5.7 基于GTID的多源復(fù)制實(shí)踐

環(huán)境說(shuō)明：

Master1配置文件：

[mysqld]

user = mysql

socket = /tmp/mysql3306.sock

basedir = /usr/local/mysql

datadir = /data/mysql/mysql3306/data

server-id = 2253306

log_bin = /data/mysql/mysql3306/logs/mysql-bin

log_slave_updates = 1

binlog_format = ROW

gtid_mode = on

enforce_gtid_consistency = on

Master2配置文件

[mysqld]

user = mysql

socket = /tmp/mysql3306.sock

basedir = /usr/local/mysql

datadir = /data/mysql/mysql3306/data

server-id = 1003306

log_bin = /data/mysql/mysql3306/logs/mysql-bin

log_slave_updates = 1

binlog_format = ROW

gtid_mode = on

enforce_gtid_consistency = on

Slave配置文件

[mysqld]

user = mysql

socket = /tmp/mysql3306.sock

basedir = /usr/local/mysql

datadir = /data/mysql/mysql3306/data

server-id = 2403306

log_bin = /data/mysql/mysql3306/logs/mysql-bin

log_slave_updates = 1

binlog_format = ROW

gtid_mode = on

enforce_gtid_consistency = on

master_info_repository =TABLE #多源復(fù)制只能配置為table

relay_log_info_repository =TABLE #多源復(fù)制只能配置為table

在Master1,Maste2上創(chuàng)建復(fù)制帳號(hào)：

mysql>set sql_log_bin=0;

mysql> grant replication slave on *.* to 'repl'@'192.168.1.240' identified by '123456';

mysql>set sql_log_bin=1;

在Master1上創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)test1,測(cè)試表t1

mysql> create database test1;

mysql> use test1;

mysql> create table t1 (id int);

mysql> insert into t1 values(1);

在Master2上創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)test2,測(cè)試表t2

mysql>create database test2;

mysql> use test2;

mysql> create table t2(id int);

mysql> insert into t2 values(2);

備份導(dǎo)出Master1,Master2上的test1,test2

Master1

#mysqldump -S /tmp/mysql3306.sock --single-transaction --master-data=2 test1 > test1.sql

Master2:

#mysqldump -S /tmp/mysql3306.sock --single-transaction --master-data=2 test2 > test2.sql

備份時(shí)報(bào)的警告信息可以忽略掉：

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

Slave配置

1.先在Slave上創(chuàng)建test1,test2這兩個(gè)數(shù)據(jù)庫(kù)

mysql>create database test1;

mysql>create database test2;

2.導(dǎo)入test1數(shù)據(jù)

#mysql -S /tmp/mysql3306.sock ?test1 < test1.sql

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解決辦法：在Slave上先執(zhí)行reset master操作，后面等數(shù)據(jù)都導(dǎo)入之后再手動(dòng)指定,后面會(huì)說(shuō)。

mysql>reset master;

#mysql -S /tmp/mysql3306.sock test1 < test1.sql; #這個(gè)時(shí)候?qū)刖筒粫?huì)報(bào)錯(cuò)了

3.導(dǎo)入test2的數(shù)據(jù)

mysql> reset master; #因?yàn)閷?dǎo)入test1數(shù)據(jù)GTID_EXECUTED又生成了,要再次清理。

#mysql -S /tmp/mysql3306.sock ?test2 < test2.sql;

4.獲取sql文件中g(shù)tid_purged的值，并在Slave上手動(dòng)設(shè)置

#grep -m 1 ?"GTID_PURGED" test1.sql

SET @@GLOBAL.GTID_PURGED='31315146-adbb-11e9-b99a-525400c3d235:1-3';

#grep -m 1 ?"GTID_PURGED" test2.sql

SET @@GLOBAL.GTID_PURGED='e712f244-adba-11e9-abe6-525400ebcfd9:1-3';

mysql> reset master; #因?yàn)閷?dǎo)入test2數(shù)據(jù)GTID_EXECUTED又生成了,要再次清理。

mysql> set @@global.GTID_PURGED='31315146-adbb-11e9-b99a-525400c3d235:1-3,e712f244-adba-11e9-abe6-525400ebcfd9:1-3' ? #注意把兩個(gè)庫(kù)的值都要設(shè)置，以逗號(hào)分隔。

5.配置主從同步

1.設(shè)置Master1,Master2的信息

mysql>change master to master_host='192.168.1.225',master_port=3306,master_user='repl',master_password='123456',master_auto_position=1 for channel 'Master1';

mysql>change master to master_host='192.168.1.100',master_port=3306,master_user='repl',master_password='123456',master_auto_position=1 for channel 'Master2';

2.配置同步過(guò)濾規(guī)則

因?yàn)镸aster會(huì)把所有的gtid推給Slave,如果只是部分庫(kù)做同步的話，從庫(kù)執(zhí)行了不存在相關(guān)庫(kù)的gtid時(shí)就會(huì)出錯(cuò)。

mysql> change replication filter replicate_do_db=(test1,test2);

6.啟動(dòng)Slave

mysql> start slave for channel 'Master1'; #對(duì)應(yīng)Master1的同步

mysql> start slave for channel 'Master2'; #對(duì)應(yīng)Master2的同步

7.驗(yàn)證

1.查看主從同步連接是否異常,有報(bào)錯(cuò)先解決報(bào)錯(cuò)再進(jìn)行第二步測(cè)試。

mysql> show slave status\G;

2.在Master1上的test1庫(kù)t1表、Master2上的test2庫(kù)t2表分別寫入一條記錄，查看是否同步到Slave。