MySQL5.7基于GTID的多源复制实践
环境说明：
网站建设哪家好，找创新互联公司！专注于网页设计、网站建设、微信开发、小程序开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了邗江免费建站欢迎大家使用！
主机 | IP | MySQL版本 | 端口 | 复制帐号 | 复制密码 |
Master1 | 192.168.1.225 | 5.7.25 | 3306 | repl | 123456 |
Master2 | 192.168.1.100 | 5.7.25 | 3306 | repl | 123456 |
Slave | 192.168.1.240 | 5.7.25 | 3306 |
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。
新闻标题：MySQL5.7基于GTID的多源复制实践
转载来源：http://abwzjs.com/article/jisgci.html
