GTID模式下多源复制的搭建和部分实例的恢复
一 搭建
1 mysqldump导出
mysql -B db1 > a.sql
mysql -B db2 > b.sql
2 配置文件设置过滤规则
replicate_wild_do_table=db1.%
replicate_wild_do_table=db2.%
3 强制导入
mysql -f < a.sql
mysql -f < b.sql
4 进行change
CHANGE MASTER TO MASTER_HOST = '127.0.0.1',MASTER_PORT = 3306,MASTER_USER = 'repl',MASTER_PASSWORD = 'repl',MASTER_AUTO_POSITION = 1 FOR CHANNEL '3306';
CHANGE MASTER TO MASTER_HOST = '127.0.0.1',MASTER_PORT = 3307,MASTER_USER = 'repl',MASTER_PASSWORD = 'repl',MASTER_AUTO_POSITION = 1 FOR CHANNEL '3307';
5 设置跳过事务
1 reset master
2 过滤a.sql b.sql的set purge
3 SET @@GLOBAL.GTID_PURGED='36a6ec7d-c959-11e9-9ade-60fa9dffe49b:1-3,94ff46c0-680f-11e9-b5a9-60fa9dffe49b:1-2,36a6ec7d-c959-11e9-9ade-60fa9dffe49b:1-3'
6 启动复制
start slave
7 观察复制进程是否正常
二 修复重新导入某库
1 stop slave for channel '3307' && drop database db2
2 mysql -B db2 > b.sql 重新导出新的库
3 mysql -f < b.sql 重新导入新的库
4 stop slave 停止全部slave复制 获取这时候的gtid_purge
5 过滤b.sql 获取gtid_purge
6 reset master 执行set @@GLOBAL.GTID_PURGED=4步骤的GTID+5步骤的GTID
7 CHANGE MASTER TO MASTER_HOST = '127.0.0.1',MASTER_PORT = 3307,MASTER_USER = 'repl',MASTER_PASSWORD = 'repl',MASTER_AUTO_POSITION = 1 FOR CHANNEL '3307';
8 start slave