这两天在聊天群中碰到一个复制中断问题，报错信息大致是GTID复制模式下主库产生了匿名事务，导致从库无法完成事务的重放。下面我一起来看看这个问题。
下面是场景复现
环境介绍
| 角色 | 数据库版本 | gtid_mode | enforce_gtid_consistency | PORT |
|---|---|---|---|---|
| master | 8.0.27 | ON | ON | 3307 |
| slave | 8.0.27 | ON | ON | 3308 |
主库状态
mysql> show master status;
+-----------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+--------------------------------------------+
| mybinlog.000025 | 1537 | | | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230 |
+-----------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%gtid%';
+----------------------------------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| group_replication_gtid_assignment_block_size | 1000000 |
| gtid_executed | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-208 |
| session_track_gtids | OFF |
+----------------------------------------------+--------------------------------------------+
9 rows in set (0.00 sec)复制
从库状态
mysql> show global variables like '%gtid%';
+----------------------------------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | WARN |
| group_replication_gtid_assignment_block_size | 1000000 |
| gtid_executed | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON_PERMISSIVE |
| gtid_owned | |
| gtid_purged | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-208 |
| session_track_gtids | OFF |
+----------------------------------------------+--------------------------------------------+
9 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.24.17
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mybinlog.000025
Read_Master_Log_Pos: 1537
Relay_Log_File: VM-24-17-centos-relay-bin.000002
Relay_Log_Pos: 415
Relay_Master_Log_File: mybinlog.000025
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
Exec_Master_Log_Pos: 1537
Relay_Log_Space: 634
Until_Condition: None
Until_Log_File:
.......
Master_Server_Id: 313307
Master_UUID: d5a6a450-bebd-11ec-b524-5254006c5c4e
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:
.....
Executed_Gtid_Set: d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230
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)复制
制造匿名事务
匿名事务即非GTID事务正常产生的方法：
在不开启GTID模式下产生事务 在GTID模式下设置开启允许产生匿名事务参数，手动制造。
我这次使用的是第二种方法
主库操作
mysql> set global gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.04 sec)
mysql> show global variables like '%gtid%';
+----------------------------------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| group_replication_gtid_assignment_block_size | 1000000 |
| gtid_executed | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230 |
| gtid_executed_compression_period | 0 |
| gtid_mode | OFF_PERMISSIVE |
| gtid_owned | |
| gtid_purged | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-208 |
| session_track_gtids | OFF |
+----------------------------------------------+--------------------------------------------+
9 rows in set (0.00 sec)
### 产生匿名事务==========
mysql> set @@session.GTID_NEXT='anonymous';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_gtid;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+--------------------------------------------+
| mybinlog.000027 | 196 | | | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230 |
+-----------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t_gtid select 4;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show master status;
+-----------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+--------------------------------------------+
| mybinlog.000027 | 522 | | | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230 |
+-----------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)复制
可以看到 主在insert之后未生成新的GTID。
master 再创建一个非匿名事务
## 重新登录会话
mysql> insert into t_gtid select 5;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show master status;
+-----------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+--------------------------------------------+
| mybinlog.000029 | 522 | | | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-231 |
+-----------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)复制
查看此时slave的状态
salve操作
mysql> show global variables like '%gtid%';
+----------------------------------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| group_replication_gtid_assignment_block_size | 1000000 |
| gtid_executed | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | d5a6a450-bebd-11ec-b524-5254006c5c4e:1-208 |
| session_track_gtids | OFF |
+----------------------------------------------+--------------------------------------------+
9 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.24.17
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mybinlog.000027
Read_Master_Log_Pos: 196
Relay_Log_File: VM-24-17-centos-relay-bin.000006
Relay_Log_Pos: 369
Relay_Master_Log_File: mybinlog.000027
Slave_IO_Running: No
Slave_SQL_Running: Yes
....
Exec_Master_Log_Pos: 196
Relay_Log_Space: 634
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
....
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1, at file /data/mysql_mgr/3307/binlog/mybinlog.000027, position 196.; the first event '' at 4, the last event read from '/data/mysql_mgr/3307/binlog/mybinlog.000027' at 275, the last byte read from '/data/mysql_mgr/3307/binlog/mybinlog.000027' at 275.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 313307
Master_UUID: d5a6a450-bebd-11ec-b524-5254006c5c4e
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: 220615 11:29:59
.....
Executed_Gtid_Set: d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230
Auto_Position: 1
....
1 row in set, 1 warning (0.01 sec)复制
可以发现此时已经报错Cannot replicate anonymous transaction when AUTO_POSITION = 1,
我们看看binlog
里怎么写
解析master的binlog :mybinlog.000027 pos:196 和pos：275
# at 125
#220615 11:24:43 server id 313307 end_log_pos 196 CRC32 0xae845568 Previous-GTIDs
# d5a6a450-bebd-11ec-b524-5254006c5c4e:1-230
# at 196
#220615 11:29:59 server id 313307 end_log_pos 275 CRC32 0x8ccf2f95 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1655263799838051 immediate_commit_timestamp=1655263799838051 transaction_length=326
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1655263799838051 (2022-06-15 11:29:59.838051 CST)
# immediate_commit_timestamp=1655263799838051 (2022-06-15 11:29:59.838051 CST)
/*!80001 SET @@session.original_commit_timestamp=1655263799838051*//*!*/;
/*!80014 SET @@session.original_server_version=80027*//*!*/;
/*!80014 SET @@session.immediate_server_version=80027*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 275
#220615 11:29:59 server id 313307 end_log_pos 349 CRC32 0xfa15f7b4 Query thread_id=3043 exec_time=0 error_code=0复制
日志中可以看到做了一个操作SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
如果去执行这个SQL将会报错
mysql> SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
ERROR 1782 (HY000): @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.复制
而带有GTID事务日志如下
# at 196
#220615 10:53:51 server id 313307 end_log_pos 275 CRC32 0x21eabf32 GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1655261631789825 immediate_commit_timestamp=1655261631789825 transaction_length=326
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1655261631789825 (2022-06-15 10:53:51.789825 CST)
# immediate_commit_timestamp=1655261631789825 (2022-06-15 10:53:51.789825 CST)
/*!80001 SET @@session.original_commit_timestamp=1655261631789825*//*!*/;
/*!80014 SET @@session.original_server_version=80027*//*!*/;
/*!80014 SET @@session.immediate_server_version=80027*//*!*/;
SET @@SESSION.GTID_NEXT= 'd5a6a450-bebd-11ec-b524-5254006c5c4e:228'/*!*/;
# at 275
#220615 10:53:51 server id 313307 end_log_pos 349 CRC32 0x09312173 Query thread_id=2965 exec_time=0 error_code=0
SET TIMESTAMP=1655261631/*!*/;
SET @@session.pseudo_thread_id=2965/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN复制
可以看到非匿名事务会指定下一个GTID号SET @@SESSION.GTID_NEXT= 'd5a6a450-bebd-11ec-b524-5254006c5c4e:228'/*!*/;
解决思路
想办法跳过这个事务 执行这个事务
先介绍下面两个参数gtid_moe
和 enforce_gtid_consitency
gtid_mode
OFF :新产生的事物和复制中的事物都不能是gtid模式
OFF_PERMISSIVE:产生的事务是匿名事务（即非gtid），复制中的事务可以是匿名事物和gtid事物
ON_PERMISSIVE ：产生的事物是gtid事物，复制中的事务可以是匿名和gtid事务
ON :新产生的事务只能gtid事物，复制中只能是gtid事务
这里解释下
复制中的事务
这个概念: 以ON
为例， 实例为master
时，不允许将匿名事务传到从，当为slave
时，不允许接收到匿名事务enforce_gtid_consitency
设置mysql检查 是否有非gtid格式的事物/语句
off: 不检查是否有非gtid格式的事物/语句
warn: 当发现非gtid格式的事物/语句，返回警告，并记录在日志中
on: 当发现非gtid格式的事物/语句，返回报错
可以得出想让slave 执行需要让这两个参数设置成
gtid_mode=ON_PERMISSIVE
enforce_gtid_consistency=warn复制
目前状态是
gtid_mode=on
enforce_gtid_consistency=on复制
注意以下几点
GTID模式的主从复制中的 master_auto_position=1
选项也需要修改为0从库设置为 ON_PERMISSIVE
时，主库需要gtid_mode=ON_PERMISSIVE
，如果为ON
复制会报错'Cannot replicate anonymous transaction when @@GLOBAL.GTID_MODE = ON
,主会检测从库的配置只能是ON
第一种：跳过该事物办法
master
mysql> set global gtid_mode=ON_PERMISSIVE;复制
slave
mysql> set global gtid_mode = ON_PERMISSIVE;
Query OK, 0 rows affected (0.02 sec)
mysql> set global enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to master_auto_position=0;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)复制
恢复配置
slave
mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.04 sec)复制
master
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.03 sec)复制
slave
mysql> stop slave;
mysql> change master to master_auto_position=1;
mysql> start slave;复制
第二种：不跳过该事物办法
操作如下
master
mysql> set global gtid_mode=ON_PERMISSIVE;复制
slave
mysql> set global gtid_mode = ON_PERMISSIVE;
mysql> set global enforce_gtid_consistency=warn;
mysql> stop slave;
mysql> change master to master_auto_position=0;
mysql> start slave;复制
恢复配置
slave
mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.04 sec)复制
master
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.03 sec)复制
slave
mysql> stop slave;
mysql> change master to master_auto_position=1;
mysql> start slave;复制
总结
当主
gtid_mode =on
时，enforce_gtid_consistency
必须为on
。也要求从同样要求gtid_mode =on,enforce_gtid_consistency=on
；反之，slave也会要求主同样配置如果需要将
enforce_gtid_consistency
降级，则需要先将gtid_mode
降级。master_auto_position=1
时需要求GTID_MODE=NO,enforce_gtid_consistency=ON在进行处理这个问题进行
change master
时不要使用指定binlog,position
方式。
文章转载自天天李拜天DBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
308次阅读
2025-04-17 10:41:41
MySQL 8.0 OCP 1Z0-908 考试题解析指南
青年数据库学习互助会
284次阅读
2025-04-30 12:17:54
MySQL数据库常用的41个脚本，速来下载！
小周的数据库进阶之路
59次阅读
2025-04-14 09:40:54
从定时到实时：用 inotify+rsync 实现文件高效同步
小周的数据库进阶之路
52次阅读
2025-04-28 11:01:22
MySQL 9.3 正式发行（GA）
MySQL解决方案工程师
50次阅读
2025-04-16 10:33:45
实现一个 MySQL 配置对比脚本需要考虑哪些细节？
爱可生开源社区
48次阅读
2025-04-23 11:19:35
MySQL中怎么找到对行记录上锁的SQL？
bisal的个人杂货铺
37次阅读
2025-04-22 10:12:16
在 Oracle 数据库中，关于 Undo 段的描述，下列哪一项是正确的？
小伙
37次阅读
2025-04-14 20:33:43
Doris FE 常见问题与处理指南
数据极客圈
35次阅读
2025-04-18 17:28:33
事务持续执行之谜：怎样找出对行记录上锁的 SQL？
爱可生开源社区
33次阅读
2025-04-17 10:41:44
