MySQL 5.7 enable GTID replication

mysql 5.7.6及以后版本支持在線啟用GTID replication，所以你可以使用傳統(tǒng)的方法或者在線方法 enable GTID replication

一：傳統(tǒng)方法enable GTID replication

參考 mysql 5.6 enable GTID replication

二：online enable GTID replication

NOTE:使用在線開啟關(guān)閉GTID需要滿足如下幾個條件1)復(fù)制拓?fù)渲兴衜ysql版本必須是5.7.6或者更高版本2)復(fù)制拓?fù)渲兴衜ysql實(shí)例gtid_mode必須設(shè)置為off

1. On each server, execute:(在每個mysql實(shí)例執(zhí)行如下命令)

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;##5.6版本該參數(shù)只有on/off兩個值，5.7.6版本之后多了WARN。設(shè)置該參數(shù)如果有不符合GTID限制的語句，能執(zhí)行但會在error中記錄warning，在我們真正開始enable GTID之前應(yīng)先開啟該參數(shù)讓業(yè)務(wù)正常跑一段時間，如果有相關(guān)警告，應(yīng)先處理程序中這些語句NOTE：確認(rèn)上一步執(zhí)行成功，再繼續(xù)一下步
2. On each server, execute:(處理完業(yè)務(wù)中違反GTID限制的程序后，在所有數(shù)據(jù)庫實(shí)例執(zhí)行如下語句)
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;##該參數(shù)設(shè)置為ON后，所有違反GTID限制的程序在數(shù)據(jù)庫上執(zhí)行都會報(bào)錯
3. On each server, execute:(上一步執(zhí)行成功后，在所有的數(shù)據(jù)庫實(shí)例上執(zhí)行如下語句,無所謂哪個實(shí)例先執(zhí)行)
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;##5.7.6之后GTID_MODE參數(shù)可選值如下：全局系統(tǒng)變量GTID_MODEOFF 不產(chǎn)生GTID, Slave只接受不帶GTID的事務(wù)OFF_PERMISSIVE 不產(chǎn)生GTID, Slave即接受不帶GTID的事務(wù)也接受帶GTID的事務(wù)ON_PERMISSIVE 產(chǎn)生GTID, Slave即接受不帶GTID的事務(wù)也接受帶GTID的事務(wù)ON 產(chǎn)生GTID, Slave只接受帶GTID的事務(wù)
4.  On each server, execute:(無所謂哪個實(shí)例先執(zhí)行)
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';檢查所有實(shí)例的ONGOING_ANONYMOUS_TRANSACTION_COUNT值為0后再繼續(xù)后面步驟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，那么再執(zhí)行完第五步以后，在備份腳本所在服務(wù)器上執(zhí)行flush logs;，然后執(zhí)行一次全備
7. On each server, execute:
SET @@GLOBAL.GTID_MODE = ON;8. On each server, add gtid-mode=ON to my.cnf.
在每個實(shí)例的配置文件中添加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'];##如果你使用了多源復(fù)制，那么為每個channel執(zhí)行如上操作
參考鏈接：
http://blog.csdn.net/shaochenshuo/article/details/54862603http://blog.csdn.net/shaochenshuo/article/details/54138317http://blog.csdn.net/shaochenshuo/article/details/54138317http://dev.mysql.com/doc/mysql-replication-excerpt/5.6/en/replication-gtids-concepts.htmlhttp://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-gtids-concepts.html?ff=nopfplshttps://dev.mysql.com/worklog/task/?spm=5176.100239.blogcont41200.6.xtY06T&id=7083http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html