mysql5.7 gtid多源_MySQL5.7 基于GTID的多源复制实践

环境说明：主机IPMySQL版本端口复制帐号复制密码

Master1192.168.1.2255.7.253306repl123456

Master2192.168.1.1005.7.253306repl123456

Slave192.168.1.2405.7.253306

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。