GTID，你了解多少？

MySQL数据库技术栈 2020-07-06

1006

全局事务标识符（Global Transaction Identifier,GTID）是MySQL5.6版本开始在主从复制方面推出的重要特性，它是一个已提交事务的编号，并且是全局唯一编号，不仅是在主库上，在给定的复制设置中的所有数据库上，它都是唯一的。

GTID是由server_uuid和事务id组成，格式为GTID=server_uuid:transaction_id。其中server_uuid是数据库启动时自动生成的，存放在数据库目录下的auto.cnf文件中，transaction_id是事务提交时由系统顺序分配的序列号。

基于GTID的主从复制环境搭建

1. 环境准备

MySQL版本：5.7.25

IP：master: 192.168.94.128

slave : 192.168.94.129

2. 主从节点的主要参数配置如下：

server-id=6
log_bin=on
binlog-format=row
enforce_gtid_consistency=ON
gtid_mode=ON
#其中server-id主从不能一样。

3. 建立复制用户

GRANT REPLICATION SLAVE, 
REPLICATION CLIENT ON *.* 
TO 'repl'@'%' identified by 'repl'

4. 数据同步。

分两种情况:

第一种情况是两个库都是新搭建的环境，直接在从库执行change master 语句就行。

第二种情况是主库已经跑了一段时间了，主库的binlog可能已经删除了，无法获取所有的GTID信息。这时候就需要用备份先还原备库。

当采用mysqldump备份时，采用如下命令备份，针对备份的数据在备库可以通过source命令恢复。

mysqldump -uroot -pmyroot --single-transaction 
--master-data=2 --all-databases --triggers 
--routines --events -A >master.sql

生成的文件中可以看到如下信息：

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
-- GTID state at the beginning of the backup
SET @@GLOBAL.GTID_PURGED='7e6667ab-903a-11ea-ae4a-000c29d7c488:1-16';

其中SET @@SESSION.SQL_LOG_BIN= 0表示临时关闭binlog的写入，是否生成binlog意味着在导入数据的时候会不会生成新的Gtid事务，从库导入时不需要在生成Gtid,所以需要关闭binlog。

GTID_PURGED代表备份时刻主库已经执行过的Gtid事务合集，在从库SET @@GLOBAL.GTID_PURGED='7e6667ab-903a-11ea-ae4a-000c29d7c488:1-16'后从库会跳过这段范围。

如果是采用的Xtrabackup的备份方式，主库gtid_purged信息会保存在xtrabackup_info和xtrabackup_binlog_info中。恢复时可以手工执行下面命令来跳过这段范围。

SET @@GLOBAL.GTID_PURGED='7e6667ab-903a-11ea-ae4a-000c29d7c488:1-16'

出现问题：在从库执行以下命令时报错

mysql> SET @@GLOBAL.GTID_PURGED='7e6667ab-903a-11ea-ae4a-000c29d7c488:1-16';
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when
@@GLOBAL.GTID_EXECUTED is empty。

解决方案：因为GTID_EXECUTED集合中已经有值，需要先置空。在从库执行下面命令。

mysql> reset master;
Query OK, 0 rows affected (1.77 sec)

5. 执行复制命令。

mysql> change master to    
master_host='192.168.94.128',
master_port=3306,
master_user='repl',
master_password='repl',
master_auto_position=1;

出现问题：数据在恢复过来之后执行change master时报错。

mysql> change master to    
master_host='192.168.94.128',master_port=3306,
master_user='repl',master_password='repl',
master_auto_position=1;
ERROR 1794 (HY000): Slave is not configured or failed to initialize 
properly. You must at least set --server-id to enable either a 
master or a slave. Additional error messages can be found in the 
MySQL error log.

解决方案

mysql> use mysql
mysql> drop table slave_master_info;
mysql> drop table slave_relay_log_info;
mysql> drop table slave_worker_info;
mysql> drop table innodb_index_stats;
mysql> drop table innodb_table_stats;

然后重启数据库。重启后再执行change master命令。

6. 启动复制

mysql> start slave;

7. 确认从库的复制情况

mysql> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.94.128
                 Master_User: repl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: efs.000036
         Read_Master_Log_Pos: 339253
              Relay_Log_File: node2-relay-bin.000002
               Relay_Log_Pos: 339414
       Relay_Master_Log_File: efs.000036
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
                 ...............
         Exec_Master_Log_Pos: 339253
             Relay_Log_Space: 339621
                 ...............
                 Master_UUID: 7e6667ab-903a-11ea-ae4a-000c29d7c488
            Master_Info_File: /var/lib/mysql/master.info
                   SQL_Delay: 0
     Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
          Retrieved_Gtid_Set: 7e6667ab-903a-11ea-ae4a-000c29d7c488:17-1020            Executed_Gtid_Set: 7e6667ab-903a-11ea-ae4a-000c29d7c488:1-1020
               Auto_Position: 1
1 row in set (0.00 sec)

GTID模式和传统模式的在线切换

GTID模式切换为传统模式

1. 从库：停掉主从复制，寻找主库binlog的位点，重新开始复制。

mysql> stop slave;
mysql> change master to master_auto_position=0,
master_log_file='efs.000036',
master_log_pos=339253;
mysql> start slave;

2. 在主从服务器上分别将GTID模式设置为on_permissive.

mysql> set global gtid_mode=on_permissive;

3. 在主从服务器上分别将GTID模式设置为off_ permissive.

mysql> set global gtid_mode=off_permissive;

4. 等待所有服务器上的gtid_owned变为空，它表示正在由线程执行的全局GTID集合。

5. 在主从服务器上分别关闭GTID模式

mysql> set global gtid_mode=off;
mysql> set global enforce_gtid_consistency=off;

6. 最后将gtid_mode=off和enforce_gtid_consistency=off写入配置文件，下次重启时直接生效。

传统模式切换为GTID模式

1. 在主从服务器上将enforce_gtid_consistency设置为warn。

执行完后需要监控错误日志是否有告警，如果有告警就需要调整应用程序，事其只使用gtid兼容的特性。这一步非常重要，进入下一步前必须保证错误日志中没有任何警告。

mysql> set global enforce_gtid_consistency=warn;

2. 在主从服务器上分别将enforce_gtid_consistency设置为on，确保所有事物不能违反GTID的一致性。

mysql> set global enforce_gtid_consistency=on;

3. 在主从服务器上分别将GTID模式设置为off_ permissive，这一步表示新的事务是匿名的，同时允许复制的事务是GTID或者是匿名的。这时候解析主库的binlog可以看到新事务的GTID_NEXT= 'ANONYMOUS';

mysql> set global gtid_mode=off_permissive;

4. 在主从服务器上分别将GTID模式设置为on_permissive，这一步表示新事务使用GTID，同时允许复制的事务是GTID或者是匿名的。

mysql> set global gtid_mode=on_permissive;

5. 确认从库的ONGOING_ANONYMOUS_TRANSACTION_COUNT参数是否为0。为0表示已标记为匿名的正在进行的事务数量已经处理完了。

mysql> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

6. 在主从服务器上设置gtid_mode=on，开启GTID。

mysql> set global gtid_mode=on;

7. 现阶段复制还是基于位点的，执行以下命令调整为基于GTID模式的复制。

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
mysql> start slave;

8. 修改配置文件，下次数据库重启时自动生效GTID。

enforce_gtid_consistency=ON
gtid_mode=ON

GTID模式下的限制

更新事务中不能混合事务型引擎和非事务型引擎。
如果同一个事务中包含了事务引擎（InnoDB）和非事务引（MyIsam），会导致多个GTID分配给同一个事务。当主从数据库的同一张表采用不同的存储引擎时也会出现在类似情况。
不支持CREATE TABLE ... SELECT statements。
在binlog_format=row模式下，该语句会分成两个事务执行，分配两个GTID，在binlog_format= STATEMENT下会分配一个GTID，当主从之间的binlog_format不一样时就会出现问题了。因此在GTID模式下执行CREATE TABLE ... SELECT statements会报错：
```
ERROR 1786 (HY000): Statement violates GTID consistency: 
CREATE TABLE ... SELECT.
```
不支持CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE。
在GTID模式下，autocommit=1时可以创建临时表。
不支持sql_slave_skip_counter跳过错误。
GTID模式下不建议进行mysql_upgrade。

故障模拟：GTID模式下跳过一个事务

场景：假设t表，从库由于人为操作失误的往t表插入一条数据：

insert into t value(1004,1004,1004,1004,1004);

主库再次插入该条数据时从库报错：

Last_SQL_Error: Could not execute Write_rows event on table test.t; 
Duplicate entry '1004' for key 'PRIMARY', Error_code: 1062; 
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log 
efs.000042, end_log_pos 1224。

问题分析：

通过报错信息可以看到出错事务的binlog是efs.000042，end_log_pos 1224。开始位置是Exec_Master_Log_Pos: 902。

在主库解析binlog：

mysqlbinlog -vvv -decode-rows=base64 --start-position=902 
--stop-position=1224 efs.000042>42.sql

从主库解析的binlog中可以看出出错事务的GTID是：7e6667ab-903a-11ea-ae4a-000c29d7c488:1023。

事务语句是：insert into t value(1004,1004,1004,1004,1004)。

解决方案：

在从库执行：

mysql> stop slave;
mysql> set GTID_NEXT='7e6667ab-903a-11ea-ae4a-000c29d7c488:1023';
mysql> begin;
mysql> commit;
mysql> set GTID_NEXT='AUTOMATIC';
mysql> start slave;
最后通过show slave status\G查看从库的复制状态。

为了方便大家交流，我建了一个微信群，大家可以在群里聊技术、聊理想、聊生活，欢迎加入！

往期推荐

【MySQL入门】之MySQL数据库的锁机制（一）

【MySQL入门】之MySQL数据库的锁机制（二）

【MySQL性能调优】-关于索引的那些事儿（一）

【索引潜规则】-覆盖索引、ICP、MRR详解

技术

文章转载自MySQL数据库技术栈，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

GTID，你了解多少？

基于GTID的主从复制环境搭建

GTID模式和传统模式的在线切换

GTID模式下的限制

故障模拟：GTID模式下跳过一个事务

评论