MySQL5.7 基于GTID的多源复制实践

环境说明：

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 #多源复制只能配置为table

relay_log_info_repository =TABLE #多源复制只能配置为table

在Master1,Maste2上创建复制帐号：

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上创建测试数据库test1,测试表t1

mysql> create database test1;

mysql> use test1;

mysql> create table t1 (id int);

mysql> insert into t1 values(1);

在Master2上创建测试数据库test2,测试表t2

mysql>create database test2;

mysql> use test2;

mysql> create table t2(id int);

mysql> insert into t2 values(2);

备份导出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

备份时报的警告信息可以忽略掉：

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上创建test1,test2这两个数据库

mysql>create database test1;

mysql>create database test2;

2.导入test1数据

#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上先执行reset master操作，后面等数据都导入之后再手动指定,后面会说。

mysql>reset master;

#mysql -S /tmp/mysql3306.sock test1 < test1.sql; #这个时候导入就不会报错了

3.导入test2的数据

mysql> reset master; #因为导入test1数据GTID_EXECUTED又生成了,要再次清理。

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

4.获取sql文件中gtid_purged的值，并在Slave上手动设置

#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; #因为导入test2数据GTID_EXECUTED又生成了,要再次清理。

mysql> set @@global.GTID_PURGED=’31315146-adbb-11e9-b99a-525400c3d235:1-3,e712f244-adba-11e9-abe6-525400ebcfd9:1-3′   #注意把两个库的值都要设置，以逗号分隔。

5.配置主从同步

1.设置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.配置同步过滤规则

因为Master会把所有的gtid推给Slave,如果只是部分库做同步的话，从库执行了不存在相关库的gtid时就会出错。

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

6.启动Slave

mysql> start slave for channel ‘Master1’; #对应Master1的同步

mysql> start slave for channel ‘Master2’; #对应Master2的同步

7.验证

1.查看主从同步连接是否异常,有报错先解决报错再进行第二步测试。

mysql> show slave status\G;

2.在Master1上的test1库t1表、Master2上的test2库t2表分别写入一条记录，查看是否同步到Slave。