基于GTID的MySQL5.7的多源复制
主库1：192.168.1.246:3306 主库2：192.168.1.246:3307 从库1：192.168.1.246:3308安装5.7数据库 初始化5.7数据库，与之前不一样，命令如下(--defaults-file 一定为第一个参数) ./bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize --user=mysql 新登录进去后改密码，新建用户 set password for 'root'@'localhost'=password('root'); CREATE USER 'root'@'%' IDENTIFIED BY 'root'; grant all privileges on *.* to 'root'@'%'; CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl'; grant replication slave on *.* to 'repl'@'192.168.1.%'; flush privileges; 3306端口上新建数据库db3306-新建表db_status 3307端口上新建数据库db3307-新建表db_status3307 CREATE TABLE `db_status3307` ( `id` int(11) NOT NULL AUTO_INCREMENT, `host` varchar(30) NOT NULL DEFAULT '', `port` varchar(10) NOT NULL DEFAULT '', `uptime_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=317 DEFAULT CHARSET=utf8; insert into db_status3307(host,port) values('123456','3307');搭建多源复制 把3306数据dump出，导入3308端口 /usr/local/mysql/bin/mysqldump -uroot -proot -h127.0.0.1 -P3306 --master-data=2 --single-transaction --databases --add-drop-database db3306 >3306.sql /usr/local/mysql/bin/mysql -uroot -proot -h127.0.0.1 -P3308 < 3306.sql 配置CHANNEL 3306 CHANGE MASTER TO MASTER_HOST = '192.168.1.246', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL '3306'; start slave; show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Retrieved_Gtid_Set: d023f67f-4a90-11e7-9eff-0800271d97b5:3 Executed_Gtid_Set: d023f67f-4a90-11e7-9eff-0800271d97b5:1-3 同样操作3307，数据dump出，导入3308端口 /usr/local/mysql/bin/mysqldump -uroot -proot -h127.0.0.1 -P3307 --master-data=2 --single-transaction --databases --add-drop-database db3307 >3307.sql /usr/local/mysql/bin/mysql -uroot -proot -h127.0.0.1 -P3308 < 3307.sql 导入报错 ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 此时3308 reset master;在执行导入 配置CHANNEL 3307 CHANGE MASTER TO MASTER_HOST = '192.168.1.246', MASTER_PORT = 3307, MASTER_USER = 'repl', MASTER_PASSWORD = 'repl', MASTER_AUTO_POSITION = 1 FOR CHANNEL '3307'; start slave for channel '3307'; show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Retrieved_Gtid_Set: 6e71bd59-4a97-11e7-ad35-0800271d97b5:9 Executed_Gtid_Set: 6e71bd59-4a97-11e7-ad35-0800271d97b5:1-9 多源复制处理报错与单机类似 命令 都加上 for channel '3307'; 跳过错误 stop slave; set gtid_next='6e71bd59-4a97-11e7-ad35-0800271d97b5:1'; begin;commit; set gtid_next='AUTOMATIC'; start slave; show slave status\G
