MySQL GTID全面總結(jié)

更新時(shí)間：2021年03月26日 14:40:35 作者：https://mp.weixin.qq.com/s/kiUrdPEMYGz0uVBMpufsvQ

這篇文章主要介紹了MySQL GTID的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下

01 GTID簡(jiǎn)介

GTID，全稱Global transaction identifiers，也稱之為全局事務(wù)ID。MySQL-5.6.2開(kāi)始支持，MySQL-5.6.10后完善，GTID 分成兩部分，一部分是服務(wù)的UUid,UUID保存在mysql數(shù)據(jù)目錄的auto.cnf文件中,
這是一個(gè)非常重要的文件，不能刪除，這一部分是不會(huì)變的。下面是一個(gè)uuid的值舉例：

[root@dev01 mysql]# cat auto.cnf 
[auto]
server-uuid=ac1ebad0-ef76-11e7-872b-080027a03bb6

另外一部分就是事務(wù)ID了，隨著事務(wù)的增加，值依次遞增。也就是說(shuō)，GTID實(shí)際上是由UUID+TID組成的。其中UUID是一個(gè)MySQL實(shí)例的唯一標(biāo)識(shí)。TID代表了該實(shí)例上已經(jīng)提交的事務(wù)數(shù)量。如下所示為一個(gè)GTID的例子：

3db33b36-0e51-409f-a61d-c99756e90155:1-14

02 GTID工作原理

1、master更新數(shù)據(jù)時(shí)，會(huì)在事務(wù)前產(chǎn)生GTID，一同記錄到binlog日志中。
2、slave端的i/o 線程將變更的binlog，寫入到本地的relay log中。
3、sql線程從relay log中獲取GTID，然后對(duì)比slave端的binlog是否有記錄。
4、如果有記錄，說(shuō)明該GTID的事務(wù)已經(jīng)執(zhí)行，slave會(huì)忽略。
5、如果沒(méi)有記錄，slave就會(huì)從relay log中執(zhí)行該GTID的事務(wù)，并記錄到binlog。
6、在解析過(guò)程中會(huì)判斷是否有主鍵，如果沒(méi)有就用二級(jí)索引，如果沒(méi)有就用全部掃描。

03 GTID的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

1.一個(gè)事務(wù)對(duì)應(yīng)一個(gè)唯一GTID，一個(gè)GTID在一個(gè)服務(wù)器上只會(huì)執(zhí)行一次
2.GTID是用來(lái)代替?zhèn)鹘y(tǒng)復(fù)制的方法，GTID復(fù)制與普通復(fù)制模式的最大不同就是不需要指定二進(jìn)制文件名和位置
3.減少手工干預(yù)和降低服務(wù)故障時(shí)間，當(dāng)主機(jī)掛了之后通過(guò)軟件從眾多的備機(jī)中提升一臺(tái)備機(jī)為主機(jī)

缺點(diǎn)：

1.不支持非事務(wù)引擎
2.不支持create table ... select 語(yǔ)句復(fù)制(主庫(kù)直接報(bào)錯(cuò))
原理：（會(huì)生成兩個(gè)sql，一個(gè)是DDL創(chuàng)建表SQL，一個(gè)是insert into 插入數(shù)據(jù)的sql。
由于DDL會(huì)導(dǎo)致自動(dòng)提交，所以這個(gè)sql至少需要兩個(gè)GTID，但是GTID模式下，只能給這個(gè)sql生成一個(gè)GTID ）
3.不允許一個(gè)SQL同時(shí)更新一個(gè)事務(wù)引擎表和非事務(wù)引擎表
4.開(kāi)啟GTID需要重啟（5.7除外）
5.對(duì)于create temporary table 和 drop temporary table語(yǔ)句不支持
6.不支持sql_slave_skip_counter

04 測(cè)試環(huán)境搭建

節(jié)點(diǎn)：
server1   192.168.197.128 3306   Master
server2   192.168.197.137 3306   Slave
server3   192.168.197.136 3306   Slave

開(kāi)啟GTID需要啟用這三個(gè)參數(shù)：

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates = 1

搭建測(cè)試環(huán)境的步驟如下：

1.在主節(jié)點(diǎn)上創(chuàng)建復(fù)制用戶，開(kāi)啟主節(jié)點(diǎn)的GTID選項(xiàng);

mysql> grant replication slave on *.* to 'repluser'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.從節(jié)點(diǎn)上進(jìn)行change master to操作，搭建主從，如下：

mysql> change master to 
    -> master_host='192.168.197.128',
    -> master_user='repluser',
    -> master_password='123456',
    -> master_port=3306,
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

3.搭建成功后，在主節(jié)點(diǎn)197.128上查看從節(jié)點(diǎn)是否加入：

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID              |
+-----------+------+------+-----------+--------------------------------------+
|     3 |   | 3306 |     | 969488f5-c486-11e8-adb7-000c29bf2c97 |
|     2 |   | 3306 |     | bb874065-c485-11e8-8b52-000c2934472e |
+-----------+------+------+-----------+--------------------------------------+
 rows in set (. sec)

查看連接：

mysql> show processlist;
+----+----------+------------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User   | Host       | db  | Command     | Time | State                             | Info       |
+----+----------+------------------+------+------------------+------+---------------------------------------------------------------+------------------+
|  | root   | localhost    | NULL | Query      |  0 | starting                           | show processlist |
| 3 | repluser | work_NAT_4:60051 | NULL | Binlog Dump GTID | | Master has sent all binlog to slave; waiting for more updates | NULL       |
|  | repluser | work_NAT_5: | NULL | Binlog Dump GTID | 5970 | Master has sent all binlog to slave; waiting for more updates | NULL       |
+----+----------+------------------+------+------------------+------+---------------------------------------------------------------+------------------+
 rows in set (. sec)

4.三臺(tái)測(cè)試環(huán)境的UUID分別是：

197.128
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid            |
+--------------------------------------+
| bd0d-8691-11e8-afd6-4c3e51db5828 |
+--------------------------------------+
 row in set (0.00 sec)

197.137
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid            |
+--------------------------------------+
| bb874065-c485-11e8-8b52-000c2934472e |
+--------------------------------------+
 row in set (0.00 sec)

197.136
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid            |
+--------------------------------------+
| f5-c486-11e8-adb7-000c29bf2c97 |
+--------------------------------------+
 row in set (0.00 sec)

05 開(kāi)始測(cè)試

測(cè)試環(huán)境主要分為以下幾個(gè)方面：

a.測(cè)試復(fù)制的故障轉(zhuǎn)移

b.復(fù)制錯(cuò)誤跳過(guò)

1 測(cè)試復(fù)制的故障轉(zhuǎn)移

先來(lái)看看測(cè)試復(fù)制的故障轉(zhuǎn)移：

（1）首先將server 3的復(fù)制過(guò)程停掉

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

（2）在server 1上創(chuàng)建一些數(shù)據(jù)

mysql> create table yyy.a(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table yyy.b(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table yyy.c(id int);
Query OK, 0 rows affected (0.02 sec)

（3）在另外兩臺(tái)上面查看數(shù)據(jù)結(jié)果：

server 
mysql> show tables from yyy;
+---------------+
| Tables_in_yyy |
+---------------+
| a       |
| b       |
| c       |
+---------------+
 rows in set (0.00 sec)

server 
mysql> show tables from yyy;
Empty set (0.00 sec)

(4)此時(shí)可以發(fā)現(xiàn)，server 2 的數(shù)據(jù)相比較server 3，它的數(shù)據(jù)比較新，此時(shí)停止server 1，模擬主服務(wù)器宕機(jī)：

[root@work_NAT_1 init.d]# service mysqld stop
Shutting down MySQL............              [ OK ]

（5）此時(shí)我們發(fā)現(xiàn)其他兩個(gè)節(jié)點(diǎn)已經(jīng)不能訪問(wèn)server 1了

mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Reconnecting after a failed master event read
         Master_Host: 192.168.197.128
         Master_User: repluser
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000006
     Read_Master_Log_Pos: 1364
        Relay_Log_File: mysql-relay-bin.000004
        Relay_Log_Pos: 1569
    Relay_Master_Log_File: mysql-bin.000006
       Slave_IO_Running: Connecting
      Slave_SQL_Running: Yes
     Exec_Master_Log_Pos: 1364
       Relay_Log_Space: 2337 
        Master_SSL_Key: 
    Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
        Last_IO_Errno: 2003
        Last_IO_Error: error reconnecting to master 'repluser@192.168.197.128:3306' - retry-time: 60 retries: 1
        Last_SQL_Errno: 0

（6）我們需要設(shè)置server 2為server 3的主庫(kù)，因?yàn)閟erver 2的數(shù)據(jù)比較新。此時(shí)如果采用以前的辦法，需要計(jì)算之前主庫(kù)的log_pos和當(dāng)前要設(shè)置成主庫(kù)的log_pos，很有可能出錯(cuò)。所以出現(xiàn)了一些高可用性的工具如MHA，MMM等解決問(wèn)題。

在MySQL5.6之后，很簡(jiǎn)單的解決了這個(gè)難題。因?yàn)橥皇聞?wù)的GTID在所有節(jié)點(diǎn)上的值一致，那么根據(jù)server3當(dāng)前停止點(diǎn)的GTID就能定位到server2上的GTID，所以直接在server3上執(zhí)行change即可：

mysql> change master to 
  -> master_host='192.168.197.137',
  -> master_user='repluser',
  -> master_password='123456',
  -> master_port=,
  -> master_auto_position=;
Query OK, rows affected, warnings (0.01 sec)

（7）此時(shí)查看server 3上的數(shù)據(jù)，可以發(fā)現(xiàn)，數(shù)據(jù)已經(jīng)同步過(guò)來(lái)了;

2 復(fù)制錯(cuò)誤跳過(guò)

上面的測(cè)試中，最終的結(jié)果是server 2是主節(jié)點(diǎn)，server 3是從節(jié)點(diǎn)，下面我們來(lái)驗(yàn)證復(fù)制錯(cuò)誤跳過(guò)的辦法。

(1)首先我們?cè)趶墓?jié)點(diǎn)上執(zhí)行一個(gè)drop的語(yǔ)句，讓兩邊的數(shù)據(jù)不一致，如下：

mysql> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| DBAs        |
| customer      |
| inc_db       |
| mysql       |
| performance_schema |
| sys        |
| testdb       |
| yeyz        |
| yyy        |
+--------------------+
 rows in set (. sec)

mysql> drop database yyy;
Query OK, rows affected (. sec)

mysql> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| DBAs        |
| customer      |
| inc_db       |
| mysql       |
| performance_schema |
| sys        |
| testdb       |
| yeyz        |
+--------------------+
 rows in set (. sec)

（2）然后我們?cè)趕erver 2上執(zhí)行drop database yyy的操作，如下：

mysql> drop database yyy;
Query OK, 3 rows affected (0.02 sec)

（3）此時(shí)我們看到server 3上已經(jīng)出現(xiàn)了主從不同步的錯(cuò)誤警告，因?yàn)樗厦娌](méi)有yyy的數(shù)據(jù)庫(kù)(前一步已經(jīng)刪除)，錯(cuò)誤情況如下;

mysql> show slave status\G
*************************** . row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 192.168.197.137
         Master_User: repluser
         Master_Port: 
        Connect_Retry: 
       Master_Log_File: mysql-bin.
     Read_Master_Log_Pos: 
        Relay_Log_File: mysql-relay-bin.
        Relay_Log_Pos: 
    Relay_Master_Log_File: mysql-bin.
       Slave_IO_Running: Yes
      Slave_SQL_Running: No
          Last_Errno: 
          Last_Error: Error 'Can't drop database 'yyy'; database doesn't exist' on query. Default database: 'yyy'. Query: 'drop database yyy'
         Skip_Counter: 
     Exec_Master_Log_Pos: 
       Relay_Log_Space: 
        Last_SQL_Error: Error 'Can't drop database 'yyy'; database doesn't exist' on query. Default database: 'yyy'. Query: 'drop database yyy'
 Replicate_Ignore_Server_Ids: 
       Master_Server_Id: 
         Master_UUID: bb874065-c485-e8-b52-c2934472e
       Master_Info_File: mysql.slave_master_info
      Retrieved_Gtid_Set: bd0d--e8-afd6-c3e51db5828:-,
bb874065-c485-e8-b52-c2934472e:
      Executed_Gtid_Set: db33b36-e51-f-a61d-c99756e90155:-,
bd0d--e8-afd6-c3e51db5828:-,
f5-c486-e8-adb7-c29bf2c97:
        Auto_Position: 
     Replicate_Rewrite_DB: 
         Channel_Name: 
      Master_TLS_Version: 
 row in set (0.00 sec)

(4)當(dāng)我們使用傳統(tǒng)的方法來(lái)跳過(guò)這個(gè)錯(cuò)誤的時(shí)候，會(huì)提示出GTID模式下不被允許，如下：

mysql> set global sql_slave_skip_counter=;
ERROR (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

那么這種方式下應(yīng)該如何跳過(guò)這個(gè)錯(cuò)誤呢？

(5)因?yàn)槲覀兪峭ㄟ^(guò)GTID來(lái)進(jìn)行復(fù)制的，也需要跳過(guò)這個(gè)事務(wù)從而繼續(xù)復(fù)制，這個(gè)事務(wù)可以到主上的binlog里面查看：因?yàn)椴恢勒夷膫€(gè)GTID上出錯(cuò)，所以也不知道如何跳過(guò)哪個(gè)GTID。但是我們可以在show slave status里的信息里找到在執(zhí)行Master里的POS:2012，也就是上述第(3)步第18行代碼?，F(xiàn)在我們拿著這個(gè)pos:2012去server 2的日志里面找，可以發(fā)現(xiàn)如下信息：

# at 2012
#190305 20:59:07 server id 2 end_log_pos 2073 GTID  last_committed=9    sequence_number=10   rbr_only=no
SET @@SESSION.GTID_NEXT= 'bb874065-c485-11e8-8b52-000c2934472e:1'/*!*/;
# at 2073
#190305 20:59:07 server id 2 end_log_pos 2158 Query  thread_id=3   exec_time=0   error_code=0
SET TIMESTAMP=/*!*/;
drop database yyy
/*!*/;

（6）我們可以看到GTID_NEXT的值是

，然后我們通過(guò)下面的方法來(lái)重新恢復(fù)主從復(fù)制：

mysql> stop slave;
Query OK, rows affected (0.00 sec)

mysql> set session gtid_next='bb874065-c485-11e8-8b52-000c2934472e:1';
Query OK, rows affected (0.00 sec)

mysql> begin;
Query OK, rows affected (0.00 sec)

mysql> commit;
Query OK, rows affected (0.01 sec)

mysql> set session gtid_next=automatic;
Query OK, rows affected (0.00 sec)

mysql> start slave;
Query OK, rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 192.168.197.137
         Master_User: repluser
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000002
     Read_Master_Log_Pos: 2158
        Relay_Log_File: mysql-relay-bin.000003
        Relay_Log_Pos: 478
    Relay_Master_Log_File: mysql-bin.000002
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
     Exec_Master_Log_Pos: 2158
       Relay_Log_Space: 1527
       Until_Condition: None
       Master_Server_Id: 2
         Master_UUID: bb874065-c485-11e8-8b52-000c2934472e
       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: 
      Retrieved_Gtid_Set: bd0d-8691-11e8-afd6-4c3e51db5828:-7,
bb874065-c485-11e8-8b52-000c2934472e:
      Executed_Gtid_Set: db33b36-0e51-409f-a61d-c99756e90155:-14,
bd0d-8691-11e8-afd6-4c3e51db5828:-7,
f5-c486-11e8-adb7-000c29bf2c97:,
bb874065-c485-11e8-8b52-000c2934472e:
        Auto_Position: 
     Replicate_Rewrite_DB: 
         Channel_Name: 
      Master_TLS_Version: 
 row in set (0.00 sec)

以上就是MySQL GTID全面總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于MySQL GTID的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: