传统复制切换到GTID模式
5.7.6以后参数gtid_mode可以动态修改
GTID_MODE:
OFF
彻底关闭GTID,如果关闭状态的备库接受到带GTID的事务,则复制中断
OFF_PERMISSIVE
可以认为是关闭GTID前的过渡阶段,主库在设置成该值后不再生成GTID,备库在接受到带GTID 和不带GTID的事务都可以容忍
主库在关闭GTID时,执行事务会产生一个Anonymous_Gtid事件,会在备库执行:
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’
备库在执行匿名事务时,就不会去尝试生成本地GTID了ON_PERMISSIVE
可以认为是打开GTID前的过渡阶段,主库在设置成该值后会产生GTID,同时备库依然容忍带GTID和不带GTID的事务
ON
完全打开GTID,如果打开状态的备库接受到不带GTID的事务,则复制中断
准备工作
1.拓扑中的所有服务器都必须使用MySQL 5.7.6或更高版本. 除非拓扑中的所有服务器都使用此版本,否则无法在任何单个服务器上启用GTID事务.
2.所有服务器都将gtid_mode设置为默认值OFF.
The following procedure can be paused at any time and later resumed where it was, or reversed by jumping to the corresponding step of Section 16.1.5.3, “Disabling GTID Transactions Online”, the online procedure to disable GTIDs. This makes the procedure fault-tolerant because any unrelated issues that may appear in the middle of the procedure can be handled as usual, and then the procedure continued where it was left off.
切换过程可以再任意时刻停止,并稍后继续执行
注意
在每一步执行完全完成后再执行下一步
从传统复制模式切换到GTID模式
1.在每个sever执行:
1 | SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN; |
让服务器与您的正常工作负载运行一段时间并监视日志. 如果此步骤在日志中导致任何警告,请调整应用程序,以使其仅使用兼容GTID的功能,并且不会产生任何警告.
Important
这是第一个重要步骤. 您必须确保在进入下一步骤之前不会在错误日志中生成警告.
2.在每个sever执行:
1 | SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON; |
3.在每个sever执行:
1 | SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE; |
哪个服务器首先执行此语句无关紧要,但重要的是所有服务器在任何服务器开始下一步之前完成此步骤.
4.在每个sever执行:
1 | SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE; |
哪个服务器首先执行此语句无关紧要.
5.在每个服务器上,等待状态变量ONGOING_ANONYMOUS_TRANSACTION_COUNT为零. 可以使用如下方式查询:1
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
ONGOING_ANONYMOUS_TRANSACTION_COUNT显示已标记为匿名的正在进行的事务的数量
注意:
在从库上,理论上这个值可能显示为零,之后又显示为非零值.这并不是问题,只要显示为零一次即可
6.等待生成到步骤5的所有事务复制到所有服务器. 您可以在不停止更新的情况下执行此操作：唯一重要的是所有anonymous transactions都被复制了.
有关检查所有匿名事务已复制到所有服务器的一种方法,请参见第16.1.5.4节“验证匿名事务的复制”.
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.
For instance, after step 6 has completed, you can execute FLUSH LOGS on the server where you are taking backups. Then either explicitly take a backup or wait for the next iteration of any periodic backup routine you may have set up.
Ideally, wait for the server to purge all binary logs that existed when step 6 was completed. Also wait for any backup taken before step 6 to expire.
Important
This is the second important point. It is vital to understand that binary logs containing anonymous transactions, without GTIDs cannot be used after the next step. After this step, you must be sure that transactions without GTIDs do not exist anywhere in the topology.
8.在每个sever执行:
1 | SET @@GLOBAL.GTID_MODE = ON; |
9.在每个sever的my.cnf配置文件中添加
1 | gtid-mode=ON |
现在可以保证所有事务都具有GTID（除了在步骤5或更早版本中生成的已经处理的事务之外）. 要开始使用GTID协议,以便稍后执行自动故障切换,请在每个从站上执行以下操作. 或者,如果使用多源复制,请对每个通道执行此操作,并包括FOR CHANNEL通道子句：1
2
3STOP SLAVE [FOR CHANNEL 'channel'];
CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel'];
START SLAVE [FOR CHANNEL 'channel'];
参考