MySQL M-S GTID
基於GTIDs的MySQL Replication
什麼是GTIDs以及有什麼特定？
1、GTIDs（Global transaction identifiers）全域性事務識別符號，是mysql 5.6新加入的一項技術
2、當使用GTIDs時，每一個事務都可以被識別並且跟蹤
3、新增新的slave或者當發生故障需要將master身份或者角色遷移到slave上時，都無需考慮是哪一個二進位制日誌以及哪個position值，極大簡化了相關操作
4、GTIDs是完全基於事務的，因此不支援MYISAM儲存引擎
5、GTID由source_id和transaction_id組成：
1>source_id來自於server_uuid,可以在auto.cnf中看到
2>transation_id是一個序列數字，自動生成
使用GTIDs的限制條件有哪些？
1、不支援非事務引擎（MYisam），因為可能會導致多個gtid分配給同一個事務
2、create table … select 語句不支援（主庫語法報錯）
3、create/drop temporary table 語句不支援
4、必須使用enforce-gtid-consistency引數
5、sql-slave-skip-counter不支援(傳統的跳過錯誤方式)
6、GTID複製環境中必須要求統一開啟和GTID或者關閉GTID
7、在mysql 5.6.7之前，使用mysql_upgrade命令會出現問題
GTID的生命週期包含以下部分：
1. A transaction is executed and committed on the master.
This transaction is assigned a GTID using the master`s UUID and the smallest nonzero transaction sequence number not yet used on this server; the GTID is written to the master`s binary log (immediately preceding the transaction itself in the log).
2. After the binary log data is transmitted to the slave and stored in the slave`s relay log, the slave reads the GTID and sets the value of its gtid_next system variable as this GTID. This tells the slave that the next transaction must be logged using this GTID.It is important to note that the slave sets gtid_next in a session context.
3. The slave verifies that this GTID has not already been used to log a transaction in its own binary log. If this GTID has not been used, the slave then writes the GTID, applies the transaction, and writes the transaction to its binary log. By reading and checking the transaction`s GTID first, before processing the transaction itself, the slave guarantees not only that no previous transaction having this GTID has been applied on the slave, but also that no other session has already read this GTID but has not yet committed the associated transaction. In other words, multiple clients are not permitted to apply the same transaction concurrently.
4. Because gtid_next is not empty, the slave does not attempt to generate a GTID for this transaction but instead writes the GTID stored in this variable—that is, the GTID obtained from the master—immediately preceding the transaction in its binary log.
總結：有了GTID大大的簡化了複製的過程，降低了維護的難度
配置基於GTIDs的Replication
在生產環境中，大多數情況下使用的MySQL5.6基本上都是從5.5或者更低的版本升級而來，這就意味著之前的mysql replication方案是基於傳統的方式部署，並且已經在執行，因此，接下來我們就利用已有的環境升級至基於GITDs的Replication
傳統的方案部署參考：https://www.cnblogs.com/yanjieli/p/9831084.html
注意：
1、開啟GITDs需要在master和slave上都配置gtid-mode，log-bin，log-slave-updates，enforce-gtid-consistency（該引數在5.6.9之前是–disable-gtid-unsafe-statement）
2、其次，slave還需要增加skip-slave-start引數,目的是啟動的時候，先不要把slave起來，需要做一些配置
詳細操作步驟：
當前環境是傳統的AB複製轉換成GTID模式
master：192.168.1.166
slave：192.168.1.114
1、將master和slave伺服器都設定為read-only
mysql>set @@global.read_only=ON;
2、停止兩臺伺服器的mysql服務
3、配置master
master： [root@master ~]# vim /etc/my.cnf log-bin=mysql-bin gtid-mode=on log-slave-updates enforce-gtid-consistency [root@master ~]# service mysqld restart
4、配置slave
slave： [root@slave1 ~]# vim /etc/my.cnf gtid-mode=on log-bin log-slave-updates enforce-gtid-consistency skip-slave-start [root@slave1 ~]# service mysqld restart mysql> change master to master_host=`192.168.1.166`,master_port=3306,master_user=`slave`,master_password=`123`,master_auto_position=1; mysql> start slave; mysql> show slave status G; Auto_Position: 1
5、關閉read-only模式
mysql> set @@global.read_only=OFF;
6、測試
master檢視: mysql> select * from db01.table03; +------+------+ | id | name | +------+------+ | 1 | haha | | 2 | wowo | | 4 | yoyo | | 1 | haha | | 2 | wowo | | 4 | yoyo | +------+------+ 6 rows in set (0.07 sec) slave檢視： mysql> select * from db01.table03; +------+------+ | id | name | +------+------+ | 1 | haha | | 2 | wowo | | 4 | yoyo | | 1 | haha | | 2 | wowo | | 4 | yoyo | +------+------+ 6 rows in set (0.07 sec) master插入資料並檢視： mysql> insert into db01.table03 values(5,`ouou`); Query OK, 1 row affected (0.04 sec) mysql> select * from db01.table03; +------+------+ | id | name | +------+------+ | 1 | haha | | 2 | wowo | | 4 | yoyo | | 1 | haha | | 2 | wowo | | 4 | yoyo | | 5 | ouou | +------+------+ 7 rows in set (0.00 sec) slave檢視： mysql> select * from db01.table03; +------+------+ | id | name | +------+------+ | 1 | haha | | 2 | wowo | | 4 | yoyo | | 1 | haha | | 2 | wowo | | 4 | yoyo | | 5 | ouou | +------+------+ 7 rows in set (0.00 sec) 並且master上面操作後檢視slave的狀態，下面就會有事務產生 mysql> show slave statusG; Retrieved_Gtid_Set: 5624c184-5b55-11e8-b117-000c293dfd08:1 Executed_Gtid_Set: 5624c184-5b55-11e8-b117-000c293dfd08:1 Auto_Position: 1