備注:測(cè)試數(shù)據(jù)庫(kù)版本為MySQL 8.0
這個(gè)blog我們來(lái)聊聊MySQL 的主從GTID復(fù)制
概述
GTID復(fù)制又叫全局事物ID（global transaction ID）箱锐，是一個(gè)已提交事物的編號(hào)畔裕，并且是一個(gè)全局唯一的編號(hào)瓢宦，MYSQL5.6版本之后在主從復(fù)制類型上新增了GTID復(fù)制。
GTID是由server_uuid和事物id組成的垦垂，即GTID=servier_uuid:transacton_id。Server_uuid是在數(shù)據(jù)庫(kù)啟動(dòng)過(guò)程中自動(dòng)生成的，每臺(tái)機(jī)器的server-uuid不一樣攻谁。UUID存放在數(shù)據(jù)目錄的auto.cnf文件下。而trasaciton_id就是事物提交時(shí)由系統(tǒng)順序分配的一個(gè)不會(huì)重復(fù)的序列號(hào)弯予。
GTID存在的價(jià)值:
1戚宦、GTID使用master_auto_position=1 代替了基于binlog和position號(hào)的主從復(fù)制搭建的方式，更便于主從復(fù)制的搭建锈嫩。
2受楼、GTID可以知道事務(wù)在最開始是在哪個(gè)實(shí)例上提交的。
3呼寸、GTID方便實(shí)現(xiàn)主從之間的failover艳汽，再也不用不斷的去找position和binlog。
GTID搭建模式：
GTID不需要傳統(tǒng)的binlog和position號(hào)了对雪，而是在從庫(kù)”change master to”時(shí)使用”master_auto_position=1”的方式搭建河狐，這就讓操作變得更加方便和可靠了。
GTID使用限制條件
GTID復(fù)制是針對(duì)事物慌植，一個(gè)gtid對(duì)于一個(gè)事務(wù)甚牲。
1、 不能使用create table table_name select * from table_name蝶柿。
2丈钙、 在一個(gè)事務(wù)中即包含事務(wù)表的操作，又包含非事物表交汤。
3雏赦、 不支持create temporary table or drop temporary table語(yǔ)句操作劫笙。
4、 使用GTID復(fù)制從庫(kù)調(diào)過(guò)錯(cuò)誤星岗，不支持執(zhí)行slave_skip_errors填大。
一.傳統(tǒng)復(fù)制切換成GTID過(guò)程
主從數(shù)據(jù)庫(kù)服務(wù)器同時(shí)修改以下參數(shù)：
-- error log 不會(huì)出現(xiàn)警告信息，如果有俏橘，需要先修復(fù)允华，才能繼續(xù)后面操作。
set global enforce_gtid_consistency=warn;
set global enforce_gtid_consistency=on;
set global gtid_mode=off_permissive;
set global gtid_mode=on_permissive;
mysql> set global enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)
mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)
mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)
確認(rèn)從庫(kù)沒(méi)等待的事務(wù):
0代表沒(méi)有等待的事務(wù)寥掐。
mysql> show global status like '%ongoing%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0 |
+-------------------------------------+-------+
1 row in set (0.00 sec)
主從庫(kù)上同時(shí)設(shè)置gtid_mode=on;
set global gtid_mode=OFF_PERMISSIVE;
set global gtid_mode=ON_PERMISSIVE;
set global enforce_gtid_consistency=on;
set global gtid_mode=on;
show variables like '%gtid%';
mysql> set global gtid_mode=OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)
mysql> set global gtid_mode=ON;
ERROR 3111 (HY000): SET @@GLOBAL.GTID_MODE = ON is not allowed because ENFORCE_GTID_CONSISTENCY is not ON.
mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.01 sec)
mysql> set global gtid_mode=ON;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
9 rows in set (0.02 sec)
把傳統(tǒng)復(fù)制模式改為（GTID）復(fù)制
stop slave;
change master to master_auto_position=1;
start slave;
show slave status \G
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_auto_position=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.31.1.112
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000017
Read_Master_Log_Pos: 156
Relay_Log_File: ipctest-relay-bin.000003
Relay_Log_Pos: 365
Relay_Master_Log_File: binlog.000017
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 576
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 08260f93-cbe5-11ea-bd0d-000c293fa60d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 16186d18-cbe5-11ea-8c79-000c297ccd64:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
mysql>
二.GTID復(fù)制與傳統(tǒng)復(fù)制的切換
主庫(kù)
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000017 | 648 | | | 08260f93-cbe5-11ea-bd0d-000c293fa60d:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
主從同時(shí)執(zhí)行
把gtid_mode=off和enforce_gtid_consistency=off寫入主從庫(kù)配置文件中靴寂。重啟后可以繼續(xù)生效，并進(jìn)行測(cè)試召耘。
set global gtid_mode='on_permissive';
set global gtid_mode='off_permissive';
set global enforce_gtid_consistency=off;
set global gtid_mode=off;
從庫(kù)切換操作：
stop slave;
change master to master_auto_position=0;
change master to
master_host='10.31.1.112',
master_user='repl',
master_password='test',
MASTER_LOG_FILE='binlog.000017',
MASTER_LOG_POS=648 ,
get_master_public_key=1;
start slave;
show slave status \G
