MySQL GTID快速了解 原创 数据库 2022年5月30日 23:16 夏至未至 1115 当前内容 20651 字，在路上，马上到，马上到 ### 何为gtid GTID，全称 `Global transaction identifiers`，也称之为全局事务ID。是一种新的主从集群复制方式。 #### gtid组成 `GTID` 由两部分组成，一部分是服务的 `UUID`, `UUID` 保存在 mysql 数据目录的 `auto.cnf` 文件中： [root@dev01 mysql]# cat auto.cnf [auto] server-uuid=bc1cbad3-efs6-16e7-873b-050027a08bb6 另外一部分就是 **事务ID** 了，随着事务的增加，值依次递增。 **总结**：`GTID`实际上是由`UUID+TID`组成的。其中`UUID`是一个`MySQL`实例(服务)的唯一标识。`TID`代表了该实例上已经提交的事务数量。GTID举例： #### gtid格式 1.单个 GTID: bc1cbad3-efs6-16e7-873b-050027a08bb6:1 前一部分是 `server_uuid`，后面一部分是执行事务的唯一标志，通常是自增的。 2.区间 GTID： bc1cbad3-efs6-16e7-873b-050027a08bb6:1-160 前一部分是 `server_uuid`，后面一部分是执行事务的**唯一标志的集合**。 ### gtid作用过程 #### gtid在binlog中的结构 <div style="text-align: center;"><img src="http://www.codecomeon.com:80/group1/M00/00/01/rBlbH2KXTcmAYrY6AAD4ibJzmoU1002316" title="MySQL GTID快速了解"></div> <div style="text-align: center;"><img src="http://www.codecomeon.com:80/group1/M00/00/01/rBlbH2KXTdKAU5uSAAEjeU38clI5266971" title="MySQL GTID快速了解"></div> #### gtid如何工作 1.`master`节点更新数据时，会在事务前产生 GTID，一同记录到 binlog 日志中。 2.`slave` 节点的 i/o 线程，监控到主节点变更的 binlog，将其读取后写入到本地的 中继日志 relay log 中。 3.从节点自己的 SQL 线程从中继日志 relay log 中获取 GTID，然后对比 `slave` 端的 binlog 是否有记录。如果有记录，说明该 GTID 的事务已经执行，`slave` 会忽略。如果没有记录，`slave` 就会从 relay log 中执行该 GTID 标识的事务，并记录到从节点自己的 binlog 中。 ### gtid特点 #### 优点 1. 一个事务对应一个唯一GTID，一个GTID在一个服务器上只会执行一次。 2. GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。 3. 减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机。 #### 缺点 1. 不支持非事务引擎 2. 不支持create table ... select 语句复制(主库直接报错) 原理：（ 会生成两个sql，一个是DDL创建表SQL，一个是insert into 插入数据的sql。由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID ） 3. 不允许一个SQL同时更新一个事务引擎表和非事务引擎表 4. 开启GTID需要重启（5.7除外） 5. 对于create temporary table 和 drop temporary table语句不支持 6. 不支持sql_slave_skip_counter ### 体验gtid复制过程 主：192.168.78.188 3308 MySQL [(none)]> show variables like '%uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 1d9b633d-e1ae-11ec-8ece-0242ac110002 | +---------------+--------------------------------------+ 1 row in set (0.01 sec) MySQL [(none)]> 从：192.168.78.189 3306 MySQL [(none)]> show variables like '%uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | f19b117a-cf6d-11ec-9fd7-0242ac110002 | +---------------+--------------------------------------+ 1 row in set (0.01 sec) MySQL [(none)]> 集群关系建立完成后，从节点执行如下命令，查看同步情况： MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.78.188 Master_User: gtid_copy Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 2507 Relay_Log_File: 67bf0b455536-relay-bin.000002 Relay_Log_Pos: 2723 Relay_Master_Log_File: mysql-bin.000001 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: 2507 Relay_Log_Space: 2940 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: 188 Master_UUID: 1d9b633d-e1ae-11ec-8ece-0242ac110002 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: 1d9b633d-e1ae-11ec-8ece-0242ac110002:1-10 Executed_Gtid_Set: 1d9b633d-e1ae-11ec-8ece-0242ac110002:1-10 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, 1 warning (0.00 sec) ERROR: No query specified MySQL [(none)]> 之后在主库已经建好的表中，插入3条语句： MySQL [(none)]> use gtid_test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MySQL [gtid_test]> insert into test values (1); Query OK, 1 row affected (0.00 sec) MySQL [gtid_test]> insert into test values (2); Query OK, 1 row affected (0.00 sec) MySQL [gtid_test]> insert into test values (3); Query OK, 1 row affected (0.00 sec) MySQL [gtid_test]> 在 `auto_commit=1`的前提下，插入3条语句，一共执行了3个事务。再次在从节点查询： MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.78.188 Master_User: gtid_copy Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 3362 Relay_Log_File: 67bf0b455536-relay-bin.000002 Relay_Log_Pos: 3578 Relay_Master_Log_File: mysql-bin.000001 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: 3362 Relay_Log_Space: 3795 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: 188 Master_UUID: 1d9b633d-e1ae-11ec-8ece-0242ac110002 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: 1d9b633d-e1ae-11ec-8ece-0242ac110002:1-13 Executed_Gtid_Set: 1d9b633d-e1ae-11ec-8ece-0242ac110002:1-13 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, 1 warning (0.00 sec) ERROR: No query specified MySQL [(none)]> 主库执行 `show master status\G;` MySQL [gtid_test]> show master status\G; *************************** 1. row *************************** File: mysql-bin.000001 Position: 3362 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1d9b633d-e1ae-11ec-8ece-0242ac110002:1-13 1 row in set (0.00 sec) ERROR: No query specified MySQL [gtid_test]> 分析可得： 主库的 Executed_Gtid_Set 为：1d9b633d-e1ae-11ec-8ece-0242ac110002:1-13 从库的 Retrieved_Gtid_Set 为: 1d9b633d-e1ae-11ec-8ece-0242ac110002:1-13 Executed_Gtid_Set为： 1d9b633d-e1ae-11ec-8ece-0242ac110002:1-13 也就是说主库产生了3个事务，从库接收到了主库的3个事务，且都已全部执行。 Retrieved_Gtid_Set : 从库已经接收到主库的事务编号 (Retrieved：已取回) Executed_Gtid_Set : 从库自身已经执行完成的事务编号 (Executed：执行) ### binlog中查看gtid记录 MySQL [(none)]> show binlog events; +------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 188 | 126 | Server ver: 8.0.29, Binlog ver: 4 | | mysql-bin.000001 | 126 | Previous_gtids | 188 | 157 | | | mysql-bin.000001 | 157 | Gtid | 188 | 236 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:1' | | mysql-bin.000001 | 236 | Query | 188 | 475 | CREATE USER 'gtid_copy'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$GL9/L+I a9!e\rwqP9w2omzaGanI8JWC4/1qPFLDd04RC7qfB76pUib1I6Slc6' /* xid=9 */ | | mysql-bin.000001 | 475 | Gtid | 188 | 552 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:2' | | mysql-bin.000001 | 552 | Query | 188 | 692 | GRANT REPLICATION SLAVE ON *.* TO 'gtid_copy'@'%' /* xid=17 */ | | mysql-bin.000001 | 692 | Gtid | 188 | 769 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:3' | | mysql-bin.000001 | 769 | Query | 188 | 859 | FLUSH PRIVILEGES | | mysql-bin.000001 | 859 | Gtid | 188 | 938 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:4' | | mysql-bin.000001 | 938 | Query | 188 | 1198 | ALTER USER 'gtid_copy'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$q@10\'DYL#}]l HGRGcq7t8cNr9Nbsbja.6Vb4VeX7HdDPsi3XGpTFJX4' PASSWORD EXPIRE NEVER /* xid=21 */ | | mysql-bin.000001 | 1198 | Gtid | 188 | 1277 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:5' | | mysql-bin.000001 | 1277 | Query | 188 | 1485 | ALTER USER 'gtid_copy'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' /* xid=22 */ | | mysql-bin.000001 | 1485 | Gtid | 188 | 1562 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:6' | | mysql-bin.000001 | 1562 | Query | 188 | 1652 | FLUSH PRIVILEGES | | mysql-bin.000001 | 1652 | Gtid | 188 | 1731 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:7' | | mysql-bin.000001 | 1731 | Query | 188 | 1939 | ALTER USER 'gtid_copy'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' /* xid=24 */ | | mysql-bin.000001 | 1939 | Gtid | 188 | 2016 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:8' | | mysql-bin.000001 | 2016 | Query | 188 | 2106 | flush privileges | | mysql-bin.000001 | 2106 | Gtid | 188 | 2183 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:9' | | mysql-bin.000001 | 2183 | Query | 188 | 2306 | create database gtid_test /* xid=49 */ | | mysql-bin.000001 | 2306 | Gtid | 188 | 2383 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:10' | | mysql-bin.000001 | 2383 | Query | 188 | 2507 | use `gtid_test`; create table test (a int ) /* xid=58 */ | | mysql-bin.000001 | 2507 | Gtid | 188 | 2586 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:11' | | mysql-bin.000001 | 2586 | Query | 188 | 2666 | BEGIN | | mysql-bin.000001 | 2666 | Table_map | 188 | 2721 | table_id: 94 (gtid_test.test) | | mysql-bin.000001 | 2721 | Write_rows | 188 | 2761 | table_id: 94 flags: STMT_END_F | | mysql-bin.000001 | 2761 | Xid | 188 | 2792 | COMMIT /* xid=66 */ | | mysql-bin.000001 | 2792 | Gtid | 188 | 2871 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:12' | | mysql-bin.000001 | 2871 | Query | 188 | 2951 | BEGIN | | mysql-bin.000001 | 2951 | Table_map | 188 | 3006 | table_id: 94 (gtid_test.test) | | mysql-bin.000001 | 3006 | Write_rows | 188 | 3046 | table_id: 94 flags: STMT_END_F | | mysql-bin.000001 | 3046 | Xid | 188 | 3077 | COMMIT /* xid=67 */ | | mysql-bin.000001 | 3077 | Gtid | 188 | 3156 | SET @@SESSION.GTID_NEXT= '1d9b633d-e1ae-11ec-8ece-0242ac110002:13' | | mysql-bin.000001 | 3156 | Query | 188 | 3236 | BEGIN | | mysql-bin.000001 | 3236 | Table_map | 188 | 3291 | table_id: 94 (gtid_test.test) | | mysql-bin.000001 | 3291 | Write_rows | 188 | 3331 | table_id: 94 flags: STMT_END_F | | mysql-bin.000001 | 3331 | Xid | 188 | 3362 | COMMIT /* xid=68 */ | +------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 37 rows in set (0.00 sec) MySQL [(none)]> 可以看出，每一个 `GTID_NEXT`,都有与之对应的数据库写操作。另外集群搭建见：[MySQL以GTID方式搭建主备集群](https://www.codecomeon.com/posts/191/ "MySQL以GTID方式搭建主备集群") 本文标题： MySQL GTID快速了解 本文作者： 夏至未至 发布时间： 2022年5月30日 23:16 最近更新： 2022年6月1日 23:51 原文链接： 许可协议： 署名-非商业性-禁止演绎 4.0 国际(CC BY-NC-ND 4.0) 请按协议转载并保留原文链接及作者 GTID(3) 上一个 MySQL以GTID方式搭建主备集群 下一个 文本字符选择统计工具分享 当前文章评论暂未开放,请移步至留言处留言。
