gtid复制和传统复制的在线切换
注意事项：
1、主库和从库要么都用gtid，要么都不用gtid，否则会报错 The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON.
前提： All servers in your topology must use MySQL 5.7.6 or later. You cannot disable GTID transactions online on any single server unless all servers which are in the topology are using this version. All servers have gtid_mode set to ON.
步骤： 1、Execute the following on each slave, and if you using multi-source replication, do it for each channel and include the FOR CHANNEL channel clause:
STOP SLAVE [FOR CHANNEL 'channel'];
CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file, \
MASTER_LOG_POS = position [FOR CHANNEL 'channel'];
START SLAVE [FOR CHANNEL 'channel'];
2、On each server, execute:
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
3、On each server, execute:
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
4、On each server, wait until the variable @@GLOBAL.GTID_OWNED is equal to the empty string. This can be checked using:
SELECT @@GLOBAL.GTID_OWNED;
5、mysql> SELECT MASTER_POS_WAIT('mysql-bin.000011','194');
在slave上执行select master_pos_wait(file, pos)，返回后判断一下返回值>=0 则认为主从同步完成。 其他返回值：若当前slave为启动或在等待期间被终止，返回NULL； 若指定的值已经在之前达到，返回0
6、FLUSH LOGS
7、SET @@GLOBAL.GTID_MODE = OFF;
8、On each server, set gtid-mode=OFF in my.cnf gtid-mode=OFF enforce_gtid_consistency=OFF
参考：http://blog.csdn.net/zhengwei125/article/details/52588348 官方参考：https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-disable-gtids.html
传统的复制改gtid
1、On each server, execute: SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
2、On each server, execute:
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
3、On each server, execute:
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
4、On each server, execute:
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
5、On each server, wait until the status variable ONGOING_ANONYMOUS_TRANSACTION_COUNT is zero. This can be checked using:
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
7、If you use binary logs for anything other than replication, for example point in time backup and restore, wait until you do not need the old binary logs having transactions without GTIDs. FLUSH LOGS 所有的节点也可以执行一下: flush logs; 用于切换一下日志。
8、On each server, execute:
SET @@GLOBAL.GTID_MODE = ON;
On each server, add gtid-mode=ON to my.cnf. enforce_gtid_consistency=on gtid-mode=ON
mysql>STOP SLAVE [FOR CHANNEL 'channel'];
mysql>CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel'];
mysql>START SLAVE [FOR CHANNEL 'channel'];
参考：http://www.linuxidc.com/Linux/2016-12/138173.htm 官方：https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html
gtid的限制： https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html
