mysql 5.7 enable GTID replication

来源：互联网发布：mac和max 编辑：程序博客网时间：2024/04/28 11:36

mysql 5.7.6及以后版本支持在线启用GTID replication，所以你可以使用传统的方法或者在线方法 enable GTID replication

一：传统方法enable GTID replication

参考 mysql 5.6 enable GTID replication

二：online enable GTID replication

NOTE:使用在线开启关闭GTID需要满足如下几个条件
1)复制拓扑中所有mysql版本必须是5.7.6或者更高版本
2)复制拓扑中所有mysql实例gtid_mode必须设置为off

1. On each server, execute:(在每个mysql实例执行如下命令)

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;##5.6版本该参数只有on/off两个值，5.7.6版本之后多了WARN。设置该参数如果有不符合GTID限制的语句，能执行但会在error中记录warning，在我们真正开始enable GTID之前应先开启该参数让业务正常跑一段时间，如果有相关警告，应先处理程序中这些语句

NOTE：确认上一步执行成功，再继续一下步

2. On each server, execute:(处理完业务中违反GTID限制的程序后，在所有数据库实例执行如下语句)

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;##该参数设置为ON后，所有违反GTID限制的程序在数据库上执行都会报错

3. On each server, execute:(上一步执行成功后，在所有的数据库实例上执行如下语句,无所谓哪个实例先执行)

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;##5.7.6之后GTID_MODE参数可选值如下：全局系统变量GTID_MODEOFF 不产生GTID, Slave只接受不带GTID的事务OFF_PERMISSIVE 不产生GTID, Slave即接受不带GTID的事务也接受带GTID的事务ON_PERMISSIVE 产生GTID, Slave即接受不带GTID的事务也接受带GTID的事务ON 产生GTID, Slave只接受带GTID的事务

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';检查所有实例的ONGOING_ANONYMOUS_TRANSACTION_COUNT值为0后再继续后面步骤NoteOn a replication slave, it is theoretically possible that this shows zero and then non-zero again. This is not a problem, it suffices that it shows zero once.

6. 备份

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.
如果你的备份策略需要binlog，那么再执行完第五步以后，在备份脚本所在服务器上执行flush logs;，然后执行一次全备

7. On each server, execute:

SET @@GLOBAL.GTID_MODE = ON;

8. On each server, add gtid-mode=ON to my.cnf.

在每个实例的配置文件中添加gtid-mode=ON

9. execute the following on each slave:

STOP SLAVE [FOR CHANNEL 'channel'];CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel'];START SLAVE [FOR CHANNEL 'channel'];##如果你使用了多源复制，那么为每个channel执行如上操作

参考链接：

http://blog.csdn.net/shaochenshuo/article/details/54862603
http://blog.csdn.net/shaochenshuo/article/details/54138317
http://blog.csdn.net/shaochenshuo/article/details/54138317
http://dev.mysql.com/doc/mysql-replication-excerpt/5.6/en/replication-gtids-concepts.html
http://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-gtids-concepts.html?ff=nopfpls
https://dev.mysql.com/worklog/task/?spm=5176.100239.blogcont41200.6.xtY06T&id=7083
http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html

0 0