1、导出数据
导出 4 个工厂的数据到 192.168.100.5,在该服务器上运行。
cd /data/backup
mysqldump -uroot -p -h192.168.100.1 --master-data=2 --single-transaction db01 >db01.sql
mysqldump -uroot -p -h192.168.100.2 --master-data=2 --single-transaction db02 >db02.sql
mysqldump -uroot -p -h192.168.100.3 --master-data=2 --single-transaction db03 >db03.sql
mysqldump -uroot -p -h192.168.100.4 --master-data=2 --single-transaction db04 >db04.sql
2.设置 GTID 值
2.1导入数据
use db01;
source /data/backup/db01.sql;
use db03;
source /data/backup/db03.sql;
use db04;
source /data/backup/db04.sql;
stop slave ;
reset slave all;
reset master;
# 这个命令会把本地的 binlog 给清理掉,慎用
show master status \G
File: mysql-bin.000001
Position: 155
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: # GTID 为空
如果报错:
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
需要执行:
mysql>reset master;
注意同时会把binlog清除。
2.2 设置gtid
# 获取导出SQL文件中的GTID_PURGED的值
# grep -w "GTID_PURGED" /tmp/db01.sql
SET @@GLOBAL.GTID_PURGED='39e42c4d-876f-11ea-8229-286ed488e793:1-31';
# grep -w "GTID_PURGED" /tmp/db02.sql
SET @@GLOBAL.GTID_PURGED='5d7ef438-f249-11ea-a518-0894ef181fcf:1-5980';# grep -w "GTID_PURGED" /tmp/db04.sql
SET @@GLOBAL.GTID_PURGED='8a426026-b5e7-11ea-8816-0050568399c4:1-56548';
SET @@GLOBAL.GTID_PURGED='39e42c4d-876f-11ea-8229-286ed488e793:1-31,
5d7ef438-f249-11ea-a518-0894ef181fcf:1-5980,
8a426026-b5e7-11ea-8816-0050568399c4:1-56548';
2.3 设置主从关系
change master to master_host='192.168.100.1',master_user='repl',master_password='123456',master_auto_Position=1 for channel 'ims_guangzhou';
change master to master_host='192.168.100.3',master_user='repl',master_password='123456',master_auto_Position=1 for channel 'ims_tianjin';
change master to master_host='192.168.100.4',master_user='repl',master_password='123456',master_auto_Position=1 for channel 'ims_kunshan';
start slave;
show slave status\G
# 在 100.1 广州工厂建一个测试表试下,能不能同步
use db01;
create table tb1(id int primary key);
3 完成配置