投稿
  1. 极客之音首页
  2. 数据库

MySQL 基于 GTID 搭建主从复制

小半 • • 数据库 • 阅读 428

引言

前文MySQL 主从搭建介绍了基于传统的位点模式搭建主从复制的流程，本文介绍一个相对基础但却很重要的知识点，基于 GTID 模式搭建主从复制，并结合相关操作简单介绍 GTID 的原理。

原理

GTID 概念

MySQL 5.6 版本开始支持 GTID 特性，全称为 Global Transaction Identifier。

具体是为每个事务增加一个全局唯一的标记，称为 GTID，该标记在整个主从集群中保持唯一。

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (source). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup.

GTID 的优点很多，如：

  • 搭建主从时可以不需要指定 binlog 的文件名与位置，通过 GTID 可以直接定位到 binlog 的位置；
  • 根据 GTID 可以明确的看到事务执行的个数，原因是位点的单位是字节，GTID 的单位是事务；
  • 根据 GTID 可以明确的看到事务执行的源端，原因与 GTID 的格式有关。

GTID 的格式为source_id:transaction_id，其中：

  • 使用 server_uuid 作为 source_id，表示事务执行的源端；
  • 使用 gno 作为 transaction_id，表示事务提交的顺序，实际上是从全局计数器中获取到的序号，计数从 1 开始。


多个 GTID 组成 GTID set，即 GTID 集合，一个 GTID set 可以包含多个 server_uuid。

如：13351c5e-d8dd-11e7-9704-b8ca3a5c1739:1-5, 1a7d571b-d996-11e7-962d-ecf4bbc4fdf9:1-10。

不过 GTID set 不总是连续的，因此有可能出现 GTID set interval，表示 GTID 范围区间，一个 GTID set 可以包含多个 GTID set interval。

如：fea4f4b7-49ad-11e9-92e7-38bc016923c2:1-5:7-10。


注意 GTID 满足幂等性，因此可以有效保证数据的一致性。同一个 GTID 在指定实例上最多只能执行一次，事务多次尝试执行时，既不报错也不再执行。

The auto-skip function for GTIDs means that a transaction committed on the source can be applied no more than once on the replica, which helps to guarantee consistency. Once a transaction with a given GTID has been committed on a given server, any attempt to execute a subsequent transaction with the same GTID is ignored by that server. No error is raised, and no statement in the transaction is executed.

GTID 参数

常见参数主要有以下三个：

  • gtid_owned，表示正在提交中的事务 GTID，并记录对应的线程 ID。当事务提交或回滚时，该变量被释放。该参数主要用于数据库内部使用，比如用于多线程复制时判断事务是否被其他线程应用，查看时结果通常为空；

When used with global scope, gtid_owned holds a list of all the GTIDs that are currently in use on the server, with the IDs of the threads that own them. This variable is mainly useful for a multi-threaded replica to check whether a transaction is already being applied on another thread.

  • gtid_executed，表示已经提交过的事务 GTID，binlog 可能已经被清理也可能没清理；

This GTID set contains all the GTIDs that have been used (or added explicitly to gtid_purged) on the server, whether or not they are currently in a binary log file on the server. It does not include the GTIDs for transactions that are currently being processed on the server (@@GLOBAL.gtid_owned).

  • gtid_purged，表示已经提交过的事务 GTID，但是 binlog 已经被清理。因此 gtid_purged 是 gtid_executed 的子集；

The set of GTIDs in the gtid_purged system variable (@@GLOBAL.gtid_purged) contains the GTIDs of all the transactions that have been committed on the server, but do not exist in any binary log file on the server. gtid_purged is a subset of gtid_executed.

上述参数都是内存数据，实例重启后所有的内存数据都会丢失，因此 GTID 模块初始化时需要读取 GTID 持久化介质。实际上 GTID 支持两种持久化介质：

  • binlog 中的 GTID_LOG_EVENT，要求开启 log_slave_updates；
  • mysql.gtid_executed数据表，MySQL 5.7.5 中引入，因此不要求开启 log_slave_updates。

注意mysql.gtid_executed表中的数据并非实时更新，对于主从，都不包括当前 binlog 中的事务。

该表数据触发更新的条件包括：

  • binlog 文件切换
  • 数据库关机

执行reset master命令时在 gtid_executed 清空的同时也会清空mysql.gtid_executed数据表。

注意 gtid_executed 是只读变量，因此无法直接修改。


此外介绍下执行show slave status命令时显示的与 GTID 相关的两个参数：

  • Retrieved_Gtid_Set，从库扫描最后一个 relay log 文件所得的 GTID；
  • Executed_Gtid_Set，当前实例上执行过的 GTID set，主库也有该参数。


《MySQL DBA 工作笔记》中分别从变量视图、表和文件视图、 操作视图三个维度整理了 GTID 相关的各种参数，具体如下图所示。

MySQL 基于 GTID 搭建主从复制
image-20230213203540175

复制流程

位点模式下可以很明确的知道从库由主库拉取 binlog 的起始位点，那么 GTID 模式的重点就是自动判断复制起始位点的方法，即 GTID Auto-Positioning。

GTIDs replace the file-offset pairs previously required to determine points for starting, stopping, or resuming the flow of data between source and replica.

首先明确下开启 GTID 模式的条件，具体包括：

  • log-bin = mysql-bin
  • binlog_format = row
  • log-slave-updates = 1
  • gtid_mode = ON
  • enforce_gtid_consistency = 1
  • MASTER_AUTO_POSITION=1

主从复制过程中，GTID 自动定位用于在主库上找到从库缺少的 binlog，主库与从库分别拥有的 GTID 为：

  • 主库，show master status的结果中的 gtid_executed 参数
  • 从库，show slave status结果中的 Retrieved_Gtid_Set 和 Executed_Gtid_Set 的并集

因此，GTID 自动定位的流程可以简单理解为：

  • 首先判断主库的 gtid_purged 变量是否是从库 GTID 的子集，如果不是，表明从库需要的 binlog 在主库已经不存在了；
  • 如果是，比较从库与主库的 GTID，找到从库缺少的 GTID；
  • 然后根据 GTID 定位 binlog 文件，从最新的 binlog 文件开始扫描，当第一次找到 Previous_gtids_log_event 中不包括从库缺少的事务时，从该文件开始；
  • 最后主库发送从库缺少的 binlog 给从库，从库在收到后回放事务，同步数据。

如果设置参数 relay_log_recovery=ON，那么 Retrieved_Gtid Set 将不会使用。


考虑一种常见场景，通过备份恢复给运行中的主库搭建一个新的从库时，很有可能执行过的 binlog 已经被清理。那么当主库要将从库缺失的事务全部发送给从库时，就会发现 binlog 文件找不到，进而导致复制报错。

You can change the value of gtid_purged in order to record on the server that the transactions in a certain GTID set have been applied, although they do not exist in any binary log on the server. When you add GTIDs to gtid_purged, they are also added to gtid_executed.

这种情况下，可以首先通过执行reset master命令重置 GTID 的执行历史（reset the GTID execution history），具体是将 gtid_executed 清空，同样也会将 gtid_purged  清空，然后通过指定 gtid_purged 参数跳过主库的 GTID set。

SET @@GLOBAL.gtid_purged = 'gtid_set';

《深入理解MySQL主从原理》中详细对比了 POSITION MODE 和 GTID AUTO_POSITION MODE 的区别，并绘制主从复制流程图如下所示，图中仅展示部分内容。

MySQL 基于 GTID 搭建主从复制
从库复制流程 (1)

GTID VS 位点

由于 GTID 是 MySQL 5.6 版本才开始支持的特性，因此 5.5 版本中无法使用 GTID。

对比 5.5.14 与 5.7.24 版本中分别执行show slave status命令的结果，显示除了 GTID 相关的字段以外，5.5 其实比 5.7 要少很多参数，比如 Master_UUID、Slave_SQL_Running_State、Channel_Name 等。

MySQL 基于 GTID 搭建主从复制
image-20230213201147940

GTID 相比于位点模式，在主从复制过程中不需要关注各种位点字段，一定程度上可以简化处理流程。

首先需要注意的是从库在回放事务时 GTID 与主库保持一致，但位点并不一致。

从show slave status的结果中也可以看到位点相关有 6 个字段，如果将文件名与字节数（position）组成坐标（coordinates），可以将 6 个位点字段分为三组坐标，其中一组与 IO 线程有关，两组与 SQL 线程有关。原因是 relay log 中的位点与 binlog 中不一致。具体包括：

  • (Master_Log_File, Read_Master_Log_Pos) – this pair of coordinates show information about slave I/O thread state.
  • (Relay_Log_File, Relay_Log_Pos) – this pair of coordinates show information about slave SQL thread state from relay log perspective.
  • (Relay_Master_Log_File, Exec_Master_Log_Pos) – this pair of coordinates show information about slave SQL thread state from Master binlog perspective.

因此，在处理复制中断问题过程中，对于位点模式的主从复制，如果需要重新搭建复制，指定的开始位点需要是从库 SQL 线程执行到的位点，即 (Relay_Master_Log_File, Exec_Master_Log_Pos)  坐标。

由于change master之前需要执行reset slave，因此 relay log 中接收到的未执行的 binlog 被删除，重新接收不会导致 binlog 重复消费。

如果指定 IO 线程执行到的位点即 (Master_Log_File, Read_Master_Log_Pos)，如果 SQL 线程落后于 IO 线程，将导致数据丢失。

而对于 GTID 模式的主从复制，就简单多了，不需要关注是哪个位点，修复好报错事务后启动复制即可。

操作流程

位点模式

从库的数据来自主库使用 mysqldump 做的备份，起始位点来自备份文件。

[root@test dumps]# cd /export/zhangkai321/mysql/3341/dumps/
[root@test dumps]# ll
总用量 284548
-rw-r--r-- 1 root root 291374132 8月  22 18:07 full_2022-08-22-18:07:21.sql
[root@test dumps]# 
[root@test dumps]# head -35 full_2022-08-22-18:07:21.sql
--
-- GTID state at the beginning of the backup 
--

SET @@GLOBAL.GTID_PURGED='b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-10285660,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011';

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=11371;

基于位点模式搭建主从。

change master to
master_host='127.0.0.1',
master_port=3341,
master_user='replicater',
master_password='3341',
master_log_file='mysql-bin.000030',
master_log_pos=11371;

复制运行一段时间后，准备切换为 GTID 复制。

切换前分别查看主从相关参数。

主库

由于 GTID 号中包括 uuid，因此首先查看 uuid。

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7 |
+--------------------------------------+
1 row in set (0.00 sec)

查看 gtid 参数，注意是查看全局变量。

mysql> show global variables like '%gtid%';
+----------------------------------+-----------------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                                         |
+----------------------------------+-----------------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                                            |
| enforce_gtid_consistency         | ON                                                                                            |
| gtid_executed                    | b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11297435,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011 |
| gtid_executed_compression_period | 1000                                                                                          |
| gtid_mode                        | ON                                                                                            |
| gtid_owned                       |                                                                                               |
| gtid_purged                      | b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11296721,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011 |
| session_track_gtids              | OFF                                                                                           |
+----------------------------------+-----------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

其中：

  • gtid_purged 中显示有两种 uuid，分别是作为主库与从库时的写入；
  • gtid_executed 与 gtid_purged 的结果不一致，其中后者多 11297435 – 11296721 = 714 个事务。

从库

查看 uuid。

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| f248888e-2feb-11ec-a7b0-fa163e19c3b7 |
+--------------------------------------+
1 row in set (0.00 sec)

查看 gtid 参数。

mysql> show global variables like '%gtid%';
+----------------------------------+--------------------------------------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                                                              |
+----------------------------------+--------------------------------------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                                                                 |
| enforce_gtid_consistency         | ON                                                                                                                 |
| gtid_executed                    | b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:16-10269228:10285661-11297435,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1-1000016 |
| gtid_executed_compression_period | 1000                                                                                                               |
| gtid_mode                        | ON                                                                                                                 |
| gtid_owned                       |                                                                                                                    |
| gtid_purged                      | b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:16-10269228:10285661-11296727,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1-1000016 |
| session_track_gtids              | OFF                                                                                                                |
+----------------------------------+--------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

参数对比

gtid_executed

对比主从的 gtid_executed 参数。

# master
[master] b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11297435,
[slave]  f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011

# slave
[master] b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:16-10269228:10285661-11297435,
[slave]  f248888e-2feb-11ec-a7b0-fa163e19c3b7:1-1000016

参考备份文件，备份的起始 GTID 为 10285661，因此两个实例作为主从执行的 GTID 范围为 10285661-11297435。

SET @@GLOBAL.GTID_PURGED='b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-10285660,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011';

此外，从库执行SHOW Master STATUS命令，显示 Executed_Gtid_Set 与show slave status结果中的 gtid_executed 结果一致。

mysql> SHOW Master STATUS G
*************************** 1. row ***************************
             File: mysql-bin.000015
         Position: 16273
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:16-10269228:10285661-11297435,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1-1000016
1 row in set (0.00 sec)

gtid_purged

对比主从的 gtid_purged 参数，结果类似于 gtid_executed。

# master
[master] b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11296721
[slave]  f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011

# slave
[master] b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:16-10269228:10285661-11296727
[slave]  f248888e-2feb-11ec-a7b0-fa163e19c3b7:1-1000016

mysql.gtid_executed

主库，查询mysql.gtid_executed数据表，显示结果与show master status不一致。

其中前者是 1-11297157，后者是 1-11297435。

mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7 |              1 |     11297157 |
| f248888e-2feb-11ec-a7b0-fa163e19c3b7 |        1000011 |      1000011 |
+--------------------------------------+----------------+--------------+
2 rows in set (0.00 sec)

从库，查询mysql.gtid_executed数据表，显示结果与show slave status或show master status也不一致。

其中前者是 10285661-11297379，后者是 10285661-11297435。

mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7 |             16 |     10269228 |
| b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7 |       10285661 |     11297379 |
| f248888e-2feb-11ec-a7b0-fa163e19c3b7 |              1 |      1000016 |
+--------------------------------------+----------------+--------------+
3 rows in set (0.00 sec)

上面提到，mysql.gtid_executed数据表中不包括当前 binlog 中的事务，下面进行验证。

查看 binlog 文件列表，显示当前 binlog 文件名为 mysql-bin.000015。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000011 | 700520668 |
| mysql-bin.000012 |       273 |
| mysql-bin.000013 | 582851632 |
| mysql-bin.000014 | 200708520 |
| mysql-bin.000015 |     16273 |
+------------------+-----------+
5 rows in set (0.00 sec)

使用 mysqlbinlog 分析 mysql-bin.000015 文件，显示 Previous-GTIDs 等于 10285661-11297379，与数据表中的查询结果一致。

[root@test data]# /export/servers/mysql/bin/mysqlbinlog --base64-output=decode-row -vv mysql-bin.000015 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230130 19:43:24 server id 3342  end_log_pos 123 CRC32 0xcec9afba   Start: binlog v 4, server v 5.7.24-log created 230130 19:43:24
# Warning: this binlog is either in use or was not closed properly.
# at 123
#230130 19:43:24 server id 3342  end_log_pos 250 CRC32 0x43872af1   Previous-GTIDs
# b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:16-10269228:10285661-11297379,
# f248888e-2feb-11ec-a7b0-fa163e19c3b7:1-1000016
# at 250
#230202 16:34:18 server id 3341  end_log_pos 315 CRC32 0x45281da5   GTID    last_committed=0    sequence_number=1   rbr_only=no
SET @@SESSION.GTID_NEXT= 'b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:11297380'/*!*/;
# at 315

其中：

  • 每个 binlog 文件中都包括 Previous-GTIDs，对应 Previous_gtids_log_event，用于记录已经执行过的 GTID set，便于定位事务所在文件；
  • 每个事务在 binlog 文件中都包括 @@SESSION.GTID_NEXT，对应 GTID_Event，用于指定下一个事务的GTID，便于定位事务所在位置。

GTID 模式

检查

主从分别执行show master status与show slave status查看 GTID SET。

主库

mysql> SHOW Master STATUS G
*************************** 1. row ***************************
             File: mysql-bin.000037
         Position: 420880780
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11297435,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011
1 row in set (0.00 sec)

从库

mysql> SHOW SLAVE STATUS G
*************************** 1. row ***************************
           Retrieved_Gtid_Set: b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:11297272-11297435
            Executed_Gtid_Set: b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:16-10269228:10285661-11297435,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1-1000016

由于主库已经运行很久了，binlog 被 purge 过，因此主库要比从库多很多事务，如 1-11297272。

MASTER-AUTO-POSITION

在开启 MASTER_AUTO_POSITION 参数后尝试立即启动复制。

mysql> change master to
    -> master_host='127.0.0.1',
    -> master_port=3341,
    -> master_user='replicater',
    -> master_password='3341',
    -> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

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

果然复制报错 1236。

mysql> SHOW SLAVE STATUS G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 127.0.0.1
                  Master_User: replicater
                  Master_Port: 3341
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: test
           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: 0
              Relay_Log_Space: 154
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3341
                  Master_UUID: b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7
             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: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 230208 17:53:05
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:16-10269228:10285661-11297435,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1-1000016
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

具体报错如下所示，原因是主库已经删除了从库需要的 binlog。

Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

那么，从库自动找点时是从哪个 GTID 号开始的呢？

根据错误日志，从库会从主库最开始的 binlog 开始读取，因此报错文件找不到。

2023-02-08T17:52:55.171257+08:00 24 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3358, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='127.0.0.1', master_port= 3341, master_log_file='', master_log_pos= 4, master_bind=''.
2023-02-08T17:53:05.118370+08:00 25 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2023-02-08T17:53:05.118723+08:00 25 [Note] Slave I/O thread for channel '': connected to master 'replicater@127.0.0.1:3341',replication started in log 'FIRST' at position 4
2023-02-08T17:53:05.141428+08:00 26 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log.000001' position: 4
2023-02-08T17:53:05.142093+08:00 25 [ERROR] Error reading packet from server for channel '': The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)
2023-02-08T17:53:05.142114+08:00 25 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236
2023-02-08T17:53:05.142119+08:00 25 [Note] Slave I/O thread exiting for channel '', read up to log 'FIRST', position 4

gtid_purged

复制报错的原因是从库缺少的 binlog 在主库已经被 purge，因此可以通过修改 gtid_purged 参数设置已经提交但是但是 binlog 已经被清理的事务 GTID。

那么，从库的 gtid_purged 变量应该设置成什么呢？

回顾下主库的 gtid_executed 与 gtid_purged 参数。

# gtid_executed
[master] b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11297435,
[slave]  f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011

# gtid_purged
[master] b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11296721
[slave]  f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011

以下列出四种可能：

  • 主库 gtid_purged，不包括从库 server_uuid，即 1-11296721；
  • 主库 gtid_purged，包括从库 server_uuid，即 1-11296721, 1000011；
  • 主库 gtid_executed，不包括从库 server_uuid，即 1-11297435；
  • 主库 gtid_executed，包括从库 server_uuid，即 1-11297435, 1000011；

下面分别测试进行验证。

首先，清理现有的复制状态。

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

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

mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)

然后，清空 GTID。

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

清空以后，可以看到 gtid_executed、gtid_purged 与 mysql.gtid_executed 均清空。

mysql>  show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery      | ON    |
| enforce_gtid_consistency         | ON    |
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.00 sec)

mysql> select * from mysql.gtid_executed;
Empty set (0.00 sec)


下面开始测试。

1）主库 gtid_purged，不包括从库 server_uuid，即 1-11296721

指定 gtid_purged 参数后可以看到 gtid_executed 也被设置为相同的值，满足 gtid_purged 是 gtid_executed 的子集的条件。

mysql> set @@global.gtid_purged="b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11296721";
Query OK, 0 rows affected (0.00 sec)

mysql>  show global variables like '%gtid%';
+----------------------------------+-------------------------------------------------+
| Variable_name                    | Value                                           |
+----------------------------------+-------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                              |
| enforce_gtid_consistency         | ON                                              |
| gtid_executed                    | b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11296721 |
| gtid_executed_compression_period | 1000                                            |
| gtid_mode                        | ON                                              |
| gtid_owned                       |                                                 |
| gtid_purged                      | b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11296721 |
| session_track_gtids              | OFF                                             |
+----------------------------------+-------------------------------------------------+
8 rows in set (0.00 sec)

复制 IO 线程报错 1236，原因是主库清理了从库需要的 binlog。

                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

2）主库 gtid_purged，包括从库 server_uuid，即 1-11296721, 1000011

mysql> set @@global.gtid_purged="b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11296721,f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011";
Query OK, 0 rows affected (0.00 sec)

复制 SQL 线程报错 1146，原因是从库事务回放失败。

                   Last_Errno: 1146
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:11296722' at master log , end_log_pos 8957. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

查看数据表，显示该事务是一个删表操作，表明事务重复执行。

因此主库已发送 binlog 给从库，但是起始位点超前，表明指定的 gtid_purged 最大值偏小。

mysql> select * from performance_schema.replication_applier_status_by_worker limit 1 G
*************************** 1. row ***************************
         CHANNEL_NAME: 
            WORKER_ID: 1
            THREAD_ID: NULL
        SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:11296722
    LAST_ERROR_NUMBER: 1146
   LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:11296722' at master log , end_log_pos 8957; Error executing row event: 'Table 'test_zk._t3_bak_new' doesn't exist'
 LAST_ERROR_TIMESTAMP: 2023-02-12 22:00:32
1 row in set (0.00 sec)

3）主库 gtid_executed，不包括从库 server_uuid，即 1-11297435

mysql> set @@global.gtid_purged="b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11297435";
Query OK, 0 rows affected (0.00 sec)

复制 IO 线程报错 1236，原因是主库清理了从库需要的 binlog。

                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

4）主库 gtid_executed，包括从库 server_uuid，即 1-11297435, 1000011

mysql> set @@global.gtid_purged="b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11297435,f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011";
Query OK, 0 rows affected (0.00 sec)

复制正常，IO 线程与 SQL 线程均未报错。

               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-11297435,
f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011
                Auto_Position: 1

因此：

  • 复制报错可以分为两类，IO 线程报错与 SQL 线程报错，不同的报错对应不同的复制进度；
  • 要求主库的 gtid_purged 变量是从库 GTID 的子集，因此当指定 gtid_purged 等于主库 gtid_purged 或 gtid_executed，不包括从库 server_uuid 时，IO 线程报错。而当指定 gtid_purged 等于主库 gtid_purged，包括从库 server_uuid 时，SQL 线程报错；
  • 正常情况下 GTID 满足幂等性，因此不会重复执行，测试报错的原因是已清空 GTID 执行历史；
  • 在主从数据一致的条件下，需要将从库 gtid_purged 设置等于 gtid_executed。

结论

MySQL 5.6 版本开始支持 GTID 特性，为每个事务增加一个全局唯一的标记，称为 GTID。

GTID 有很多优点，比如根据 GTID 可以明确的看到事务执行的个数与源端。

主从复制过程中，GTID 模式可以替换传统的位点模式实现自动定位。

要在主库上找到从库缺少的 binlog，具体可以分为三步：

  • 找到从库缺少的事务，通过对比主从 GTID 实现；
  • 找到从库缺少的事务所在的 binlog 文件名，基于文件中的 Previous_gtids_log_event 实现；
  • 找到从库缺少的事务所在的 binlog 位置，基于文件中的 @@SESSION.GTID_NEXT 实现。

其中主从 GTID 对比的前提是明确主从分别拥有的 GTID，具体为：

  • 主库，show master status的结果中的 gtid_executed 参数
  • 从库，show slave status结果中的 Retrieved_Gtid_Set 和 Executed_Gtid_Set 的并集

因此，主从复制过程中 GTID 自动定位的流程可以简单理解为：

  • 首先判断主库的 gtid_purged 变量是否是从库 GTID 的子集，如果不是，表明从库需要的 binlog 在主库已经不存在了；
  • 如果是，比较从库与主库的 GTID，找到从库缺少的 GTID；
  • 然后根据 GTID 定位 binlog 文件，从最新的 binlog 文件开始扫描，当第一次找到 Previous_gtids_log_event 中不包括从库缺少的事务时，从该文件开始；
  • 最后主库发送从库缺少的 binlog 给从库，从库在收到后回放事务，同步数据。


最后通过将位点模式切换到 GTID 模式的案例介绍一种常见场景的处理方法。

当从库缺少的 binlog 在主库已经被 purge 时复制报错，可以通过修改 gtid_purged 参数设置已经提交但是但是 binlog 已经被清理的事务 GTID。

修复过程具体分为两步：

  • 执行reset master命令重置 GTID 的执行历史（reset the GTID execution history），具体是将 gtid_executed 清空，同样也会将 gtid_purged  清空；
  • 指定 gtid_purged 参数跳过主库的 GTID set，将从库的 gtid_purged 变量设置为主库的 gtid_executed 变量，包括其中的从库 server_uuid 部分。

待办

  • GTID lifecycle
  • MySQL read-only transaction
  • 主从复制详细流程

参考教程

  • 《深入理解MySQL主从原理》
  • 《MySQL DBA 工作笔记》
  • MySQL 5.7 Reference Manual  /  GTID Format and Storage
https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html
  • 基于GTID搭建主从MySQL
https://www.cnblogs.com/ZhuChangwu/p/13040214.html


原文始发于微信公众号（丹柿小院）：MySQL 基于 GTID 搭建主从复制

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/178603.html

赞 (0)
小半的头像小半
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！