汇总丨MySQL GTID技术点，看这一篇就够了！

vlambda
2020-07-21

汇总丨MySQL GTID技术点，看这一篇就够了！

墨墨导读：对于MySQL GTID，经过多年的磨炼已经很稳定了，作为position方式的延伸，在如今使用环境中带来了很多方便。本文分享GTID技术点的汇总。

MySQL复制不管用那个方式，都离不开binlog方式进行的。GTID作为position方式的延伸，在如今使用环境中带来了很多方便。下面一步一步的把gtid相关的知识点复习一下。

基础

先了解复制的基本原理：

1. MySQL复制方式

master用户写入数据，生成event记到binary log中. slave接收master上传来的binlog，然后按顺序应用，重现master上的操作。

传统的复制基于(file,pos)，当主从发生宕机，切换的时候有问题
slave保存的是原master上的(file,pos)，无法直接指向新master上的(file,pos)

2. 日志记录上position方式和GTID方式区别

直观图对比：
汇总丨MySQL GTID技术点，看这一篇就够了！

主从复制,默认是通过pos复制(postion)方式,将用户进行的每一项操作都进行编号(pos),每一个event都有一个起始编号,一个终止编号。GTID就是类似于pos的一个作用,全局通用并且日志文件里事件的GTID值是一致的.
pos与GTID在日志里是一个标识符，在slave 里已不同的方式展现。
GTID的生成受gtid_next控制。
在Master上，gtid_next是默认的AUTOMATIC,即GTID在每次事务提交时自动生成。它从当前已执行的GTID集合(即gtid_executed)中，找一个大于0的未使用的最小值作为下个事务GTID。同时将GTID写入到binlog(set gtid_next记录)，在实际的更新事务记录之前。
在Slave上，从binlog先读取到主库的GTID(即set gtid_next记录)，而后执行的事务采用该GTID。

3. GTID优势

更简单的实现failover，不像传统方式那样在需要找log_file和log_Pos。
更简单的搭建主从复制。
复制集群有一个统一的方式识别复制位置，给集群管理带来了便利。
正常情况下，GTID是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。
MySQL5.7.6版本开始可以在线升级gtid模式

冷门功能：接口

这类接口MySQL初期是为了通过开发接口，解决执行状态和复制延迟的跟踪准备的。但现在基本不适用。

1. FUNCTION
汇总丨MySQL GTID技术点，看这一篇就够了！

GTID_SUBSET(set1,set2)
给定两组全局事务标识符set1和set2，如果set1中的所有gtid也在set2中，则返回true。否则返回false。
GTID_SUBTRACT(set1,set2)
给定两组全局事务标识符set1和set2，只返回set1中不属于set2的gtid。
WAIT_FOR_EXECUTED_GTID_SET()
监视应用于服务器上的所有gtid，包括从所有复制通道和用户客户机到达的事务。
如果指定了超时，并且在GTID集中的所有事务应用之前超时时间已经过去，则函数停止等待。超时是可选的，默认超时为0秒，在这种情况下，函数总是等待，直到应用了GTID集中的所有事务。
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(gtid_set[, timeout][,channel])
等待所有事务都已应用，或者等待超时时间过完。8.0.18弃用功能，暂不介绍。

参考：https://dev.mysql.com/doc/refman/5.7/en/gtid-functions.html

2. API

跟踪gtid从库接受的情况，返回值：成功的为零。发生错误时非零。
跟踪器机制的一种用途是为MySQL连接器和客户端应用程序提供一种方法，能确保执行情况，已经有增强半同步这样的机制，基本没有，除非进行二次开发

mysql_session_track_get_first()
mysql_session_track_get_next()

网上分析案例：

DROP TABLE IF EXISTS test.t1;CREATE TABLE test.t1 (i INT, f FLOAT);--enable_session_track_infoSET @@SESSION.session_track_schema=ON;SET @@SESSION.session_track_system_variables='*';SET @@SESSION.session_track_state_change=ON;USE information_schema;SET NAMES 'utf8mb4';SET @@SESSION.session_track_transaction_info='CHARACTERISTICS';SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET TRANSACTION READ WRITE;START TRANSACTION;SELECT 1;INSERT INTO test.t1 () VALUES();INSERT INTO test.t1 () VALUES(1, RAND());COMMIT;

shell> mysqltest < testscriptDROP TABLE IF EXISTS test.t1;CREATE TABLE test.t1 (i INT, f FLOAT);SET @@SESSION.session_track_schema=ON;SET @@SESSION.session_track_system_variables='*';-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES-- session_track_system_variables-- *SET @@SESSION.session_track_state_change=ON;-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES-- session_track_state_change-- ONUSE information_schema;-- Tracker : SESSION_TRACK_SCHEMA-- information_schema -- Tracker : SESSION_TRACK_STATE_CHANGE-- 1SET NAMES 'utf8mb4';-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES-- character_set_client-- utf8mb4-- character_set_connection-- utf8mb4-- character_set_results-- utf8mb4 -- Tracker : SESSION_TRACK_STATE_CHANGE-- 1SET @@SESSION.session_track_transaction_info='CHARACTERISTICS';-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES-- session_track_transaction_info-- CHARACTERISTICS -- Tracker : SESSION_TRACK_STATE_CHANGE-- 1 -- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS-- -- Tracker : SESSION_TRACK_TRANSACTION_STATE-- ________ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION READ WRITE;-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION READ WRITE; START TRANSACTION;-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION READ WRITE; -- Tracker : SESSION_TRACK_TRANSACTION_STATE-- T_______ SELECT 1;11-- Tracker : SESSION_TRACK_TRANSACTION_STATE-- T_____S_ INSERT INTO test.t1 () VALUES();-- Tracker : SESSION_TRACK_TRANSACTION_STATE-- T___W_S_ INSERT INTO test.t1 () VALUES(1, RAND());-- Tracker : SESSION_TRACK_TRANSACTION_STATE-- T___WsS_ COMMIT;-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS---- Tracker : SESSION_TRACK_TRANSACTION_STATE-- ________ok

参考：
https://dev.mysql.com/doc/refman/5.7/en/mysql-session-track-get-first.html
https://dev.mysql.com/doc/refman/5.7/en/c-api-functions.html

判断方式

1. 如何判断复制方式GTID 还是 pos

Show slave status 查看Auto_Position字段。0是pos 方式， 1是gtid方式。
汇总丨MySQL GTID技术点，看这一篇就够了！

gtid变更为pos方式：

change master to master_auto_position=0;

2. 当前执行gtid信息

mysql> SELECT @@GLOBAL.GTID_EXECUTED;+----------------------------------------------+| @@GLOBAL.GTID_EXECUTED |+----------------------------------------------+| 39d0a7f2-702c-11ea-92a0-000c29b9a76d:1-46534 |+----------------------------------------------+
mysql> SELECT * FROM mysql.gtid_executed;

汇总丨MySQL GTID技术点，看这一篇就够了！

mysql.gtid_executed表是由MySQL服务器提供给内部使用的。它允许副本在副本上禁用二进制日志记录时使用GTIDs，并允许在二进制日志丢失时保留GTID状态。RESET MASTER命令，gtid_executed表将被清除。

服务意外停止的情况下，当前二进制日志文件中的gtid集不会保存在gtid_executed表。在恢复期间，这些gtid将从二进制日志文件添加到表中，以便可以继续复制。

3. gtid_executed

若MySQL服务器启用了二进制日志，则表mysql.gtid_executed的更新仅在二进制rotation时发生，因为发生重启等情况依旧可以通过扫描二进制日志判断得知当前运行的GTID位置。

简单来说，该表会记录当前执行的GTID
在MySQL 5.6中必须配置参数log_slave_updates的最重要原因在于当slave重启后，无法得知当前slave已经运行到的GTID位置，因为变量gtid_executed是一个内存值：
MySQL 5.7将gtid_executed这个值给持久化。采用的技巧与MySQL 5.6处理SQL thread保存位置的方式一样，即将GTID值持久化保存在一张InnoDB表中，并与用户事务一起进行提交，从而实现数据的一致性。
触发条件：

在binlog发生rotate(flush binary logs/达到max_binlog_size)或者关闭服务时，会把所有写入到binlog中的Gtid信息写入到mysql.gtid_executed表。
从库：如果没有开启log_bin或者没有开启log_slave_updates，从库在应用relay-log中的每个事务会执行一个insert mysql.gtid_executed操作。

常用命令

1. gtid设置

gtid_mode=ON #必选
enforce-gtid-consistency=true #必选
log-bin=mysql #5.6必选 5.7.5和它之后可选，为了高可用，最好设置
server-id=1 #开启log-bin的必须设置
log-slave-updates=ON # 5.6必选 5.7.5和它之后可选，为了高可用切换，最好设置ON

2. gtid跳过 gtid_next

stop slave;
set gtid_next='d74faa2d-5819-11e8-b248-ac853db70398:10603';
begin;commit;
set gtid_next='automatic';
start slave;

备注;该操作类似于sql_slave_skip_counter，只是跳过错误，不能保证数据一致性，需要人工介入，固强烈建议从机开启read_only=1

3. gtid清除gtid_pureged

命令的实际意义：因没有binlog信息（expire_logs_days），不考虑这些gtid确认和回滚。常用备份恢复，搭建从库的时候使用。
自动触发机制：flush，服务器重新启动

使用场景：

在副本上禁用二进制日志记录提交的复制事务的GTIDs。
写入二进制日志文件的事务的GTIDs，该文件现在已被清除。
通过语句set @@GLOBAL.gtid_purged显式添加到集合中的gtid。

mysqldump --set-gtid-purged=off/on 参数;
是否将GTID_PURGED’添加到输出中
汇总丨MySQL GTID技术点，看这一篇就够了！

4. gtid升级

pos升级gtid方式，条件允许建议重新搭建从库的方式。以下方式存在风险。
gtid_mode可选值

ON：完全打开GTID，如果打开状态的备库接受到不带GTID的事务，则复制中断
ON_PERMISSIV：可以认为是打开gtid前的过渡阶段，主库在设置成该值后会产生GTID，同时备库依然容忍带GTID和不带GTID的事务
OFF_PERMISSIVE：可以认为是关闭GTID前的过渡阶段，主库在设置成该值后不再生成GTID,备库在接受到带GTID和不带GTID事务都可以容忍。主库在关闭GTID时，执行事务会产生一个Anonymous_Gtid事件，会在备库执行：set @@session.gtid_next=‘anonymous’
OFF：彻底关闭GTID，如果关闭状态的备库收到带GTID的事务，则复制中断

从position模式切换到GTID模式：

1)在每个sever执行WARN模式:
这一步设置之后，使得所有事物都允许违反GTID的一致性

mysql>SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
#这是第一个重要步骤. 您必须确保在进入下一步骤之前不会在错误日志中生成警告.

2)在每个sever执行ON模式:
以确保所有的事务都不能违反GTID一致性

mysql>SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

3)在每个sever执行OFF模式:
这一步表示，新的事务是匿名的，同事允许复制的事务是GTID或是匿名的

mysql>SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
#需要确保这一步操作在所有的服务器上执行

4)在每个sever执行ON模式:
这一步表示，新的事务是GTID的，同事允许复制的事务是GTID或是匿名的

mysql>SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
#需要确保这一步操作在所有的服务器上执行

5)在每个服务器上,等待状态变量ONGOING_ANONYMOUS_TRANSACTION_COUNT为零. 可以使用如下方式查询:

mysql>SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
#在所有从库上查询该状态，必须为0 才能进行下一步。该状态宝石已标示为匿名的正在#进行的事务数量，如果状态值为0表示无事务等待被处理

等待生成到步骤5的所有事务复制到所有服务器. 可以在不停止更新的情况下执行此操作：唯一重要的是所有anonymous transactions都被复制了.

6)GTID_MODE = ON在每所有服务器上执行:

mysql>SET @@GLOBAL.GTID_MODE = ON;

7)修改每个my.cnf文件:

gtid-mode=ON
ENFORCE_GTID_CONSISTENCY = ON

8)上面复制虽然配置了GTID模式，但还是基于Binlog方式的。可通过选项MASTER_AUTO_POSITION设置为1，把复制调整为基于GTID模式的复制，具体操作如下:

mysql>STOP SLAVE [FOR CHANNEL 'channel'];
mysql>CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel'];
mysql>START SLAVE [FOR CHANNEL 'channel'];

5. gtid 压缩 gtid_executed_compression_period

启用GTID时，服务器会定期在mysql.gtid_executed表上执行此类压缩。通过设置gtid_executed_compression_period系统变量，可以控制压缩表之前允许的事务数，从而控制压缩率。该变量的默认值为1000; 这意味着，默认情况下，在每1000次事务之后执行表压缩。

将gtid_executed_compression_period设置为0可以防止执行压缩; 但是，如果执行此操作，应该为gtid_executed表可能需要的磁盘空间量的大幅增加做好准备。
使用以下语句查询：

mysql> select thread_id,thread_os_id,name, processlist_command,processlist_statefrom `performance_schema`.threads where name like '%compress%';+-----------+--------------+--------------------------------+---------------------+-------------------+| thread_id | thread_os_id | name | processlist_command | processlist_state |+-----------+--------------+--------------------------------+---------------------+-------------------+| 26 | 8024 | thread/sql/compress_gtid_table | Daemon | Suspending |+-----------+--------------+--------------------------------+---------------------+-------------------+

备注：如发现 processlist_state 值一直是: "Compressing gtid_executed table"说明进行压缩。记录锁的内存从操作系统申请，所以当表gtid_executed不断增大时，最终会导致MySQL OOM。

6. binlog_gtid_simple_recovery

MySQL启动或重启时在搜索GTIDs期间迭代二进制日志文件的方式。就是为了初始化 gtid_executed ， gtid_purged参数，扫描binlog 或则 event相关信息
MySQL 5.7.7或更老版本的二进制日志，需要在设置binlog_gtid_simple_recovery=FALSE，如果存在非gtid的binlog比较多的时候，会非常影响性能的。

限制

到目前为止已经发展完善，但存在一些场景是受限的。
1. create table xxx as select：
拆分成两部分：
create table xxxx like table;
insert into xxxx select *from table;

2. 临时表的限制
使用GTID复制模式：
1.不支持create temporary table 和 drop temporary table。
2.在autocommit=1的情况下可以创建临时表，
3.Master端创建临时表不产生GTID信息，所以不会同步到slave，但是在删除临时表的时候会产生GTID会导致，主从中断.

3.事务操作
涉及非事务性存储引擎的更新，非事务性存储引擎事务性存储引擎更新表则不能在同一条语句或同一事务中执行。

4.mysql_upgrade
GTID模式和mysql_upgrade。在启用全局事务标识符(GTIDs)的情况下运行时，不要通过mysql_upgrade(——write binlog选项)启用二进制日志记录。

5.sql_slave_skip_counter
传统模式的跳过postion方式gtid模式下不支持。

总结

1. gtid能不能做的更好。