MySQL GTID全解(概念、参数、相关表、在线切换、故障修复)shijie97博客-

1.1 GTID基本概念

  GTID(全局事务ID)是MySQL在5.6加入的特性，MySQL会为每一个DDL/DML操作增加一个唯一标记，叫做GTID。主库可以直接通过GTID定位要发送的binlog位置，不再需要像传统复制一样指定文件和位点。

  GTID由UUID和NUMBER组成，UUID存储在auto.cnf文件中，也可以通过select @@server_uuid查到，NUMBER为GTID序号。

[root@zijie ~]# cat /data/mysql57/data/auto.cnf [auto] server-uuid=2a4aaa93-98e4-11ea-b9b2-00163e0c8a51 

GTID事务：由全局事务标识符（GTID）标识，格式为UUID：NUMBER。在binlog日志中的每个GTID的事务总是在BEGIN标记之前（Query log event 的之前）加上一个Gtid_log_event。GTID事务可以使用GTID来进行自动查找事务，也可以使用binlog file和pos进行寻址。 匿名事务：没有分配GTID，MySQL 5.7.6及更高版本在binlog日志中的每个匿名事务总是在begin标记之前（Query log event的之前）加上一个 Anonymous_gtid_log_event，在这个Event中使用了SET @@SESSION.GTID_NEXT= 'ANONYMOUS'语句。在以前的版本中，匿名事务没有这个Anonymous_gtid_log_event。只能使用binlog file和pos来解决。 

1.2 参数介绍

binlog_gtid_simple_recovery #此变量控制在MySQL启动或重新启动时，在搜索二进制日志中的GTID期间如何迭代扫描二进制日志文件 当binlog_gtid_simple_recovery = FALSE时，迭代扫描二进制日志文件的方法是： 初始化gtid_executed系统变量：从新的二进制文件倒推迭代扫描，直到匹配到二进制日志中第一个具有Previous_gtids_log_event事件且该事件中包含非空的GTID集合时停止迭代扫描。然后使用来自该二进制日志文件中的所有的Previous_gtids_log_event和Gtid_log_events事件中的GTID生成一 个GTID集合，该集合被用于设置 gtid_executed系统变量的值以及更新mysql.gtid_executed表中的GTID结合。如果倒推迭代扫描的路径上的二进制日志中有大量不具备 GTID的事务（匿名事务），那么这个迭代过程可能需要很长时间，例如在 GTID复制转换到传统复制模式之后。注意：如果所有的binlog中都没有找到 具有GTID的事务的事件，则该变量被设置为空串     初始化gtid_purged系统变量：从旧的二进制日志到新的二进制日志顺序迭代扫描，直到匹配到二进制日志中第一个具有 Previous_gtids_log_event事件且该事件中包含非空的GTID集合时或者至少有一个Gtid_log_event事件时停止迭代扫描。然后从这个二进制日志读取 Previous_gtids_log_event生成GTID集合。再使用内部变量gtids_in_binlog中保存的GTID集合减去该GTID集合，把计算结果存储在内部变量 gtids_in_binlog_not_purged中。gtid_purged的值被初始化为gtid_executed的值减去gtids_in_binlog_not_purged。注意：如果所有的binlog中都没有找 到具有GTID的事务的事件，则该变量被设置为空串   当binlog_gtid_simple_recovery = TRUE时，mysql仅迭代扫描旧的和新的二进制日志文件，而 gtid_purged和gtid_executed的值仅基于这些文件中的 Previous_gtids_log_event或Gtid_log_event计算。这使得在mysqld重新启 动期间或清除二进制日志时，只会迭代扫描两个二进制日志文件。加快了扫描速度。  * 新的二进制日志是由MySQL 5.7.5或更早版本生成的。对于某些二进制日志开启了GTID，但新的二进制日志又关闭了GTID * 在5.7.7之前的MySQL版本上使用SET GTID_PURGED语句且在执行SET GTID_PURGED时指定的需要清理的二进制日志并没有完全清除成功   * 如果在任一情况下导致计算出不正确的GTID集合，即使后续重新启动服务 器，也会保持不正确，无论该系统参数设置为何值，需要先手工清理那些可 能导致计算不正确值的binlog或者更新数据库版本到5.7.7及其之后的版本  enforce_gtid_consistency #是否启用强制GTID一致性 设置为true时，服务器通过只执行可以以事务安全方式记录的那些语句来强制执行GTID一致性。在使用gtid_mode=ON时，需要把该参数一并设置 为ON。 由于只有当enforce_gtid_consistency为true时才会记录事务安全语句，因此，使用GTID复制时不支持以下操作： * 不支持CREATE TABLE ... SELECT语句 * 不支持在事务内使用CREATE TEMPORARY TABLE语句(5.7开始支持临时表) * 不支持在一个事务或语句内同时更新事务表和非事务表的语句   gtid_executed_compression_period #表示mysql server在执行了该变量指定的事务数量之后，执行一次压缩mysql.gtid_executed表，如果设置为0则表示不压缩mysql.gtid_executed表 由于在使用二进制日志功能时，mysql.gtid_executed表不会实时记录从库同步的主库事务的GTID，所以也就不会发生表的压缩，即在开启binlog时该变量不起作用，除非禁用二进制日志记录时，该表中才会实时记录从库同步的主库事务的 GTID，所以此时压缩功能才生效。  gtid_mode #是否开启gtid复制模式，该参数有如下几个有效值 在MySQL 5.7.6以前，gtid_mode是只读参数，修改值只能通过重启实例生效。 在MySQL 5.7.6开始，gtid_mode支持在线动态修改。 在MySQL里事务分为两种，一种是匿名事务（anonymous），一种是GTID事务。GTID事务即该事务带有GTID信息，而匿名事务即使没有GTID信息的事务，在主从复制中只能指定binlog文件和位点的方式来搭建。 从MySQL 5.7.6开始，gtid_mode有以下四个值： OFF，主库所有新启的事务以及从库的事务都要求是匿名事务；例如主库发起事务A，那么这个事务没有GTID信息，相关binlog传到从库，由从库复制该事务时，从库开启的这个事务也必须是匿名的，从库的gtid_mode也应该是OFF或OFF_PERMISSIVE； OFF_PERMISSIVE，主库新启的事务是匿名事务，但从库事务允许是匿名的或者是GTID事务；即从库的gtid_mode需要是ON_PERMISSIVE或OFF_PERMISSIVE或OFF； ON_PERMISSIVE，主库新启的事务是GTID事务，从库事务允许是匿名的或者是GTID事务；即从库的gtid_mode需要是ON或ON_PERMISSIVE或OFF_PERMISSIVE； ON，主库新启的事务是GTID事务，从库的事务也要求是GTID事务，即若主为ON，那么从库的gtid_mode应该要ON_PERMISSIVE或ON。  各值兼容规律： ON,ON_PERMISSIVE,OFF_PERMISSIVE,OFF 按以上顺序，主库确定某个取值，那么从库只能取相同的值，或者该值临近两侧的值。 例如主库是ON_PERMISSIVE，那么从库可以是ON,ON_PERMISSIVE,OFF_PERMISSIVE，但不能是OFF；主库是ON，那么从库可以是ON,ON_PERMISSIVE，但不能是OFF_PERMISSIVE,OFF。   gtid_next #在GTID复制架构中，用于指定如何获取GTID号，该变量要生效需要依赖于gtid_mode=on，如果 gtid_mode为OFF，则设置此变量不起作用 * AUTOMATIC：使用下一个自动生成的全局事务ID。    * ANONYMOUS：事务不具有全局标识符，仅由binlog file和binlog pos标识 (在MySQL 5.7版本中，该类型事务在binlog中多了一个 Anonymous_gtid_log_event来标记匿名事务，在这个Event中使用了SET @@SESSION.GTID_NEXT= 'ANONYMOUS'语句)    * UUID中的全局事务ID：NUMBER格式。即，手工指定一个GTID号，手工 指定GTID常用语在复制发生错误时，手工跳过发生错误的事务GTID（手工 指定后，通过执行begin;commit;语句来提交一个空事务使其GTID号增长到 指定的GTID位置），手工指定的事务号提交或回滚之后，需要把该变量的 值再次修改回AUTOMATIC，并重新启动复制 会话变量，动态变量，枚举类型，有效值为：AUTOMATIC、 ANONYMOUS、手工指定一个GTID，默认值为AUTOMATIC  注意：在MySQL 5.7.5 之前的版本中(5.6.x 在 MySQL 5.6.20 之前的版本中)，当该变量值不为AUTOMATIC时，drop temporary table或者drop table与drop temporary table的组合使用场景会失败，这目前是一个BUG(#17620053) * 在5.7.1版本或5.6.20版本中，在该变量不为AUTOMATIC时，无法执行 CHANGE MASTER TO, START SLAVE, STOP SLAVE, REPAIR TABLE, OPTIMIZE TABLE, ANALYZE TABLE, CHECK TABLE, CREATE SERVER, ALTER SERVER, DROP SERVER, CACHE INDEX, LOAD INDEX INTO CACHE, FLUSH, or RESET这些语句，这是一个BUG (#16062608, #16715809, #69045) (#16062608)  gtid_owned #此变量保存的GTID集合值取决于其作用范围，当使用会话级别查看时，该变量显示包含该客户端的所有GTID集合; 当使用全局级别查看时，该变量显示server中 所有的GTID集合  gtid_purged #该变量的值是记录所有已经从二进制日志中清除的事务集合。 该GTID集合是gtid_executed表中记录的所有事务集合的一个子集。当服务器启动时，gtid_purged的全局值被初始化为一组GTID（但如果是一 个从未清理过binlog的实例，这个变量可能一直为空）。 在MySQL 5.7.6之前，此变量 只有在gtid_mode = ON时才可设置。 在MySQL 5.7.6及更高版本中，无论 gtid_mode的值如何，此变量都是可设置的 在MySQL 5.7.6及其之前的版本产生的binlog，有可能在mysqld重启之后重 新计算gtid_purged系统变量时计算错误，可以使用系统变量 binlog_gtid_simple_recovery=false来避免计算错误，在MySQL 5.7.7及其之后的版本无此问题，可以直接设置binlog_gtid_simple_recovery = TRUE  RESET MASTER： 删除binlog索引文件中列出的所有binlog文件 清空binlog索引文件 创建一个新的binlog文件 清空系统变量gtid_purged和gtid_executed 在MySQL 5.7.5 及后续版本中, RESET MASTER还会会清空 mysql.gtid_executed数据表  RESET SLAVE： 清除slave 复制时的master binlog的位置 清空master info, relay log info 删除所有的relay log文件，并创建一个新的relay log文件。 重置复制延迟(CHANGE MASTER TO 的 MASTER_DELAY参数指定的)为0  session_track_gtids 控制服务器是否在当前会话中跟踪gtid并将其返回给客户端。根据变量值，在执行每个事务结束时，服务器gtid由跟踪器捕获并返回给客户机。允许使用这些session_track_gtids值： OFF：追踪器不收集GTID。这是默认设置。 OWN_GTID：跟踪器收集成功提交的读/写事务生成的GTID。 ALL_GTIDS：跟踪器在当前事务提交时收集gtid执行的系统变量中的所有gtid，无论事务是读/写还是只读。 无法在事务上下文中设置会话跟踪ID。 

1.3 相关表

#该表提供查询与当前实例中的数据一致的GTID集合 mysql> select * from gtid_executed; Empty set (0.00 sec)  mysql> flush logs; Query OK, 0 rows affected (0.02 sec)  mysql> select * from gtid_executed; *************************** 1. row *************************** #代表数据来源的GTID集合     source_uuid: 2a4aaa93-98e4-11ea-b9b2-00163e0c8a51 #每个UUID集合的小事务号  interval_start: 1 #每个UUID集合的大事务号   interval_end: 8 *************************** 2. row ***************************    source_uuid: 7e95a013-a65a-11ea-9e81-00163e0c8a51 interval_start: 1   interval_end: 2 2 rows in set (0.04 sec)  #该表提供查询IO线程读取主库的位置信息，以及从库连接主库的IP、账号、端 口、密码等信息  mysql> select * from slave_master_infoG *************************** 1. row *************************** #表示master.info中的信息行数或者slave_master_info表中的信息字段数        Number_of_lines: 25 #表示从库IO线程当前读取主库 新的binlog file名称，对应show slave status的Master_Log_File        Master_log_name: mybinlog.000003 #表示从库IO线程当前读取主库 新的binlog position，对应show slave status的Read_Master_Log_Pos         Master_log_pos: 194 #表示从库IO线程当前正连接的主库IO或者主机名，对应show slave status的Master_Host                   Host: 172.16.8.167 #表示从库IO线程用于连接主库用户名，对应show slave status的User_name              User_name: repl #表示从库IO线程用于连接主库的用户密码，对应show slave status的User_password          User_password: repl #表示从库IO线程所连接主库的网络端口，对应show slave status的Master_Port                   Port: 3306 #表示从库IO线程断线重连主库的间隔时间，单位为秒，默认值为60，对应show slave status的Connect_Retry          Connect_retry: 60 #表示主从之间的连接是否支持SSL，对应show slave status的Master_SSL_Allowed            Enabled_ssl: 0 #表示CA(Certificate Authority)认证文件名，对应show slave status的Master_SSL_CA_File                 Ssl_ca: #表示CA(Certificate Authority)认证文件路径，对应show slave status的Master_SSL_CA_Path             Ssl_capath: #表示SSL认证证书文件名，对应show slave status的Master_SSL_Cert               Ssl_cert: #表示用于SSL连接握手中可能使用到的密码列表，对应show slave statu的Master_SSL_Cipher             Ssl_cipher: #表示SSL认证的密钥文件名，对应show slave status的Master_SSL_Key                Ssl_key: #表示是否需要校验server的证书，对应show sslave status的Master_SSL_Verify_S erver_Cert Ssl_verify_server_cert: 0 #表示主从之间的复制心跳包的间隔时间，单位为秒              Heartbeat: 30 #表示从库可用于连接主库的网络接口，默认为空，对应show slave status的Master_Bind                   Bind: #表示从库复制需要忽略哪些server-id，注意：这是一个列表，第一个数字表示需要忽略的实例server-id总数，对应show slave status的Replicate_I NUMBERre_Server_Ids     INUMBERred_server_ids: 0 #表示主库的UUID，对应show slave status的Master_UUID                   Uuid: 2a4aaa93-98e4-11ea-b9b2-00163e0c8a51 #表示从库大允许重连主库的次数，对应show slave status的Master_Re try_Count            Retry_count: 86400 #SSL证书撤销列表文件的路径                Ssl_crl: #包含ssl证书吊销列表文件的目录路径            Ssl_crlpath: #表示从库是否启用在主库中自动寻找位置的功能（使用1时启动自动寻找位置，如果使用auto_position=0，则不会自动找位置），对应show slave status的Auto_position  Enabled_auto_position: 1  #表示从库复制通道名称，一个通道代表一个复制源           Channel_name:  #表示在Master上的TLS版本号            Tls_version: 1 row in set (0.00 sec) #该表提供查询SQL线程重放的二进制文件对应的主库位置和relay log当前新的位置  mysql> select * from slave_relay_log_infoG *************************** 1. row ***************************   #表示relay-log.info中的信息行数或者 slave_relay_log_info表中的信息字段数，用于版本化表定义   Number_of_lines: 7   #表示当前新的relay log文件名称，对应show slave status的Relay_Log_File    Relay_log_name: /data/mysql57slave/logs/relaylog.000004   #表示当前新的relay log文件对应的近一次完整接收的event的位置，对应show slave status的Relay_Log_Pos     Relay_log_pos: 240   #表示SQL线程当前正在重放的中继日志对应的主库binlog文件名，对应show slave status的Relay_Master_Log_File   Master_log_name: mybinlog.000003   #表示SQL线程当前正在重放的中继日志对应 主库binlog文件中的位置，对应show slave status的Exec_Master_Log_Pos    Master_log_pos: 4   #表示延迟复制指定的从库必须延迟主库多少秒，对应show slave status的SQL_Delay         Sql_delay: 0   #表示从库当前并行复制有多少个worker线程 Number_of_workers: 0 #用于内部唯一标记表中的每一行记录，目前 总是1                Id: 1 #表示从库复制通道名称，用于多源复制，一个通道对应一个主库源，对应show slave status的Channel_name      Channel_name: 

图片来自重庆八怪的《主从32讲》

1.4 在线切换

MySQL从5.7.6开始可以在线切换 gtid-mode的几种状态说明： off :不产生gtid，基于binlog+position，slave也不能接受gtid的日志; off_permissive:不产生gtid，但做为slave可以识别gtid事务也可以识别非gtid事务; on_permissive:产生gtid事务，slave可以处理gtid事务和非gtid事务; on:产生gtid事务，slave只接受gtid事务。  传统--->GTID  1.所有的Server执行 set @@global.enforce_gtid_consistency = warn; 特别注意： 这一步是关建的一步使用不能出现警告。  2.所有的server上执行 set @@global.enforce_gtid_consistency = on;  3.所有的Server上执行（要执行完） set @@global.gtid_mode = off_permissive; set @@global.gtid_mode=on_permissive; #产生gtid的日志，这个步骤号称是不关心任何节点，但从管理上推荐在slave上先执行，然后再去master上执行。  4.传统的binlog复制完成确认 #显示已标记为匿名的正在进行的匿名事务的数量。 show status like 'ongoing_anonymous_transaction_count'; 需要所有的节点都确认为0. 所有的节点也可以执行一下: flush logs; 用于切换一下日志。   5. 所有的节点启用gtid_mode set @@global.gtid_mode=on;  6.配置文件修改 gtid_mode=on enforce_gtid_consistency=on  7.启用gtid的自动查找节点复制 stop slave; CHANGE MASTER TO  MASTER_HOST='127.0.0.1',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_AUTO_POSITION=1; start slave;    GTID--->传统 就是上面的反向过程 1.停止复制 stop slave; show slave statusG;查看Exec_Master_Log_Pos的位置 change master to master_host='127.0.0.1',master_user='repl',master_password='repl',master_port=3306,master_log_file='mybinlog.000008',master_log_pos=80711; start slave;  2.主库先更改日志格式，然后从库更改 set @@global.gtid_mode=on_permissive; set @@global.gtid_mode=off_permissive;  3. select @@global.gtid_owned; 要为空才正常  4.动态修改参数 set @@global.gtid_mode=off; set @@global.enforce_gtid_consistency = off;  5.my.cnf修改 

1.5 故障修复

##传统复制## #停止复制 mysql>stop slave; #设定跳过一个事务 mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #重新开启复制 mysql>start slave; #这样就正常了，但是，当然还是要把数据修改上去 mysql>update tables set ... where; ##GTID## #停止复制 mysql>stop slave; #然后执行设置一个事务GTID来跳过，就是要跳过这个事务的意思 mysql>SET gtid_next = '2a4aaa93-98e4-11ea-b9b2-00163e0c8a51:1-8'; #注入空事务 mysql>BEGIN;COMMIT; #把GTID设置回自动模式 mysql>SET gtid_next = 'AUTOMATIC'; #重新开启复制 mysql>START SLAVE; #当然跳过了，并不代表这个数据就不修改了，还是要你手动去修改一下，这样就一切回归正常了 mysql>update tables set ...;  #停止复制 mysql>stop slave; #直接设置上面的GTID值+1 mysql>SET @@GLOBAL.GTID_PURGED='2a4aaa93-98e4-11ea-b9b2-00163e0c8a51:1-8'; #重新开启复制 mysql>START SLAVE; #当然跳过了，并不代表这个数据就不修改了，还是要你手动去修改一下，这样就一切回归正常了 mysql>update tables set ...;  ##传统模式## #停止复制 mysql>stop slave; #设定跳过一个事务 mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #重新开启复制 mysql>start slave; #这样就正常了，但是，当然还是要把数据修改上去 mysql>update tables set ... where ;