慎用mysqldump与GTID自动定位一次备份数据丢失的排查本文摘要导入mysqldump的备份数据并通过MASTER_AUTO_POSITION1配置从节点启动复制后出现1032错误行不存在。经排查发现MySQL 5.7.38的mysqldump备份文件内部存在逻辑矛盾其记录的起始复制位置与GTID集存在时间差导致使用MASTER_AUTO_POSITION1配置从节点时会丢失备份期间的数据。而mysqldump5.7.36之前和mysqldump8.0.31之后的版本不存在此问题。本文记录完整的复现、分析和解决方案。背景某业务系统使用的是MySQL 5.7.38计划将数据库替换为国产数据库GreatSQL使用mysqldump从MySQL 5.7.38备份了一份数据导入到GreatSQL之后将GreatSQL配置为MySQL 5.7.38的从库系统运行一段时间后SHOW SLAVE STATUS检查复制状态发现复制链路报错 Last_Errno: 1032。搭建了MySQL 5.7.38和GreatSQL对该场景进行了模拟。安装MySQL 5.7.38$ cd /gdb/mysqldb $ tar -xvf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz $ mv mysql-5.7.38-linux-glibc2.12-x86_64 mysql-5.7.38 $ mkdir -p /gdb/mysqldb/5738/{data,tmp,binlog} $chown -R greatsql:greatsql /gdb/mysqldb $ ./mysqld --initialize-insecure --usergreatsql --basedir/gdb/mysqldb/mysql-5.7.38 --datadir/gdb/mysqldb/5738/data ...#输出省略 password ! Please consider switching off the --initialize-insecure option.创建用户在MySQL中创建用户mysqlCREATE USER admin% IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY admin ; mysqlGRANT ALL PRIVILEGES ON . TO admin% WITH GRANT OPTION; mysqlFLUSH PRIVILEGES;创建测试库及测试表在MySQL中创建测试库及测试表mysqlCREATE DATABASE sbtest; Query OK, 1 row affected (0.01 sec) mysqlUSE sbtest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysqlCREATE TABLE t_keep_alive( name varchar(20), create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.08 sec)使用 Sysbench 构造数据$ sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-dbsbtest --mysql-host192.168.18.2 --mysql-port5738 --mysql-useradmin --mysql-passwordadmin --tables3 --table_size3000000 --report-interval2 --threads4 --db-drivermysql --skip-trxoff --db-ps-modedisable --create-secondaryoff --time0 --simple-ranges0 --sum-ranges0 --order-ranges0 --distinct-ranges0 --mysql-ignore-errors9001,9002,9000,1062 prepare WARNING: Both event and time limits are disabled, running an endless test sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3) Initializing worker threads... Creating table sbtest2... Creating table sbtest1... Creating table sbtest3... Inserting 3000000 records into sbtest2 Inserting 3000000 records into sbtest3 Inserting 3000000 records into sbtest1插入数据脚本计划在备份期间每秒插入一条测试数据$ more t_keep.sh #!/bin/bash USERadmin PASSWORDadmin HOST192.168.18.2 PORT5738 DBNAMEsbtest while true do /gdb/mysqldb/mysql-5.7.38/bin/mysql -u$USER -p$PASSWORD -h$HOST -P$PORT $DBNAME -e INSERT INTO t_keep_alive(name) SELECT SUBSTRING(UUID(), 1, 8) ;SELECT sleep(1); doneGTID信息sysbench 插入数据后 mysqlSHOW MASTER STATUS; ---------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | -------------------------------------------------------------- | binlog.000004 | 643592650 | | | e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3366 | --------------------------------------------------------------- 1 row in set (0.00 sec) 备份完成 mysqlSHOW MASTER STATUS; --------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set| ------------------------------------------------------------------ | binlog.000004 | 643622680 | | | e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3471 | -------------------------------------------------------------------------- 1 row in set (0.00 sec)开始插入数据$ bash t_keep.sh mysql: [Warning] Using a password on the command line interface can be insecure. ---------- | sleep(1) | ---------- | 0 | ---------- mysql: [Warning] Using a password on the command line interface can be insecure. ---------- | sleep(1) | ---------- | 0 | ----------备份数据/gdb/mysqldb/mysql-5.7.38/bin/mysqldump -uadmin -h192.168.18.2 -P5738 -padmin --single-transaction --set-gtid-purgedON --master-data2 --routines --events --triggers --databases sbtest --force /tmp/sbtest.sql停止数据写入备份完成后kill 掉写入数据的t_keep.sh 进程GreatSQL导入备份文件$ du -sh sbtest.sql 1.7G sbtest.sql 登录GreatSQL数据库将备份文件导入到GreatSQL数据库 GreatSQLSOURCE /tmp/sbtest.sql;基于GTID模式配置从库将GreatSQL配置为基于GTID模式的MySQL从库GreatSQLCHANGE MASTER TO MASTER_HOST192.168.18.2,MASTER_USERADMIN,MASTER_PASSWORDADMIN,MASTER_PORT5738,MASTER_AUTO_POSITION1; Query OK, 0 rows affected, 2 warnings (0.02 sec) GreatSQLSHOW MASTER STATUS; -------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | -------------------------------------------------------------- | binlog.000001 | 154 | | | e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3466 | -------------------------------------------------------------- 1 row in set (0.00 sec) GreatSQLSTART SLAVE; Query OK, 0 rows affected (0.00 sec)主从数据对比gtid信息对比对比主库和从库的gtid信息两个库是一致的都是1-3537主库: mysqlSHOW MASTER STATUS; ------------------------------------------------------------------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------ | binlog.000004 | 643641556 | | | e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3537 | ----------------------------------------------------------------- 1 row in set (0.00 sec) 从库: GreatSQLSHOW MASTER STATUS; --------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | -------------------------------------------------------------------------------- | binlog.000001 | 154 | | | e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3537 | --------------------------------------------------------------------------------- 1 row in set (0.00 sec)表数据对比t_keep_alive表数据不一致主库比从库多主库: mysqlSELECT count(*) FROM t_keep_alive; ---------- | count(*) | ---------- | 171 | ---------- 1 row in set (0.01 sec) 从库: GreatSQLSELECT count(*) FROM t_keep_alive; ---------- | count(*) | ---------- | 120 | ---------- 1 row in set (0.01 sec) 差异数据分析从库11:10:36到11:11:37之间没有数据写入从库。在主库更新备份期间产生的数据 从库就会出现1032错误SQL线程状态变为NO | 41f01fcf | 2026-01-12 11:10:33 | 2026-01-12 11:10:33 | | 428b22d3 | 2026-01-12 11:10:34 | 2026-01-12 11:10:34 | | 43266c4c | 2026-01-12 11:10:35 | 2026-01-12 11:10:35 | | 43c105c5 | 2026-01-12 11:10:36 | 2026-01-12 11:10:36 | | 68155846 | 2026-01-12 11:11:37 | 2026-01-12 11:11:37 | | 68b0b70c | 2026-01-12 11:11:38 | 2026-01-12 11:11:38 | | 694b88d8 | 2026-01-12 11:11:39 | 2026-01-12 11:11:39 |基于LOG_FILELOG_POS搭建主从GreatSQL STOP SLAVE; Query OK, 0 rows affected (0.01 sec) GreatSQL RESET SLAVE all; Query OK, 0 rows affected (0.01 sec) GreatSQL DROP DATABASE sbtest; Query OK, 4 rows affected (0.92 sec) GreatSQL RESET MASTER; Query OK, 0 rows affected (0.01 sec) GreatSQL SOURCE /tmp/sbtest.sql; GreatSQL SHOW MASTER STATUS; ------------------------------------------------------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set| ------------------------------------------------------------------ | binlog.000001 | 154 | | | e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3466 | ------------------------------------------------------------------ 1 row in set (0.00 sec) GreatSQL SELECT * FROM t_keep_alive; ........... | 43c105c5 | 2026-01-12 11:10:36 | 2026-01-12 11:10:36 | ---------------------------------------------------- 49 rows in set (0.00 sec) GreatSQL RESET MASTER; Query OK, 0 rows affected (0.02 sec) GreatSQL SHOW MASTER STATUS; ---------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ---------------------------------------------------------------------------- | binlog.000001 | 154 | | | | ---------------------------------------------------------------------------- 1 row in set (0.00 sec) GreatSQL CHANGE MASTER TO MASTER_HOST192.168.18.2,MASTER_USERADMIN,MASTER_PASSWORDADMIN,MASTER_PORT5738,MASTER_LOG_FILEBINLOG.000004, MASTER_LOG_POS643606664; Query OK, 0 rows affected, 2 warnings (0.02 sec) GreatSQL START SLAVE; Query OK, 0 rows affected (0.00 sec) GreatSQL SELECT * FROM t_keep_alive; ........ | 92abaf1c | 2026-01-12 11:12:48 | 2026-01-12 11:12:48 | ---------------------------------------------------- 171 rows in set (0.00 sec) GreatSQL SHOW MASTER STATUS; ----------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ----------------------------------------------------------------- | binlog.000001 | 154 | | | e9ceeaef-ed3d-11f0-893c-00163ed468c9:3416-3537 | ----------------------------------------------------------------- 1 row in set (0.00 sec)通过LOG_FILE、LOG_POS配置主从同步gtid同步范围为:3416-3537,通过master_auto_position配置主从同步gtid同步范围为:3467-3537。问题分析查看备份文件-- -- Position to start replication or point-in-time recovery from -- 文件的开头部分 -- CHANGE MASTER TO MASTER_LOG_FILEbinlog.000004, MASTER_LOG_POS643606664; -- -- Current Database: sbtest -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ sbtest /*!40100 DEFAULT CHARACTER SET utf8 */; USE sbtest; -- -- Table structure for table sbtest1 ....... 略 UNLOCK TABLES; -- -- Dumping events for database sbtest -- -- -- Dumping routines for database sbtest -- -- -- GTID state at the end of the backup -- -- 文件结尾部分 SET GLOBAL.GTID_PURGEDe9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3466; /*!40103 SET TIME_ZONEOLD_TIME_ZONE */; /*!40101 SET SQL_MODEOLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTESOLD_SQL_NOTES */; -- Dump completed on 2026-01-12 11:11:36 SET GLOBAL.GTID_PURGEDe9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3466;备份文件认为1-3466这些事务的数据已经写入到数据库。实际情况情况呢解析备份文件LOG_FILE、LOG_POS相邻的数据。解析binlog.000004 MASTER_LOG_POS643606664后的下一个事务是e9ceeaef-ed3d-11f0-893c-00163ed468c9:3416SET SESSION.GTID_NEXT e9ceeaef-ed3d-11f0-893c-00163ed468c9:3416/*!*/; # at 643606729 #260112 11:10:37 server id 425738 end_log_pos 643606803 CRC32 0x7f5398a1LOG_FILE、LOG_POS对应的下一个事务是’e9ceeaef-ed3d-11f0-893c-00163ed468c9:3416’这种同步方式是从3416开始同步数据。而通过MASTER_AUTO_POSITION1的方式同步数据由于备份文件最后是SET GLOBAL.GTID_PURGEDe9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3466;(MySQL 5.7.38的mysqldump在备份完成时才获取gtid信息) 这条语句写入数据库后数据库认为1-3466的数据已经写入到数据库了这些数据不需要进行同步。配置MASTER_AUTO_POSITION1后从3467开始同步新的数据。两种同步方式差异就是备份期间产生的51条数据(3467-341651 )在主库UPDATE备份期间产生的数据从库就会出现1032错误导致主从复制链路失败。问题总结1、使用MySQL 5.7.38的mysqldump进行数据备份备份数据导入从库通过MASTER_AUTO_POSITION1配置主从后主从数据库数据不一致从库开始从备份完成时生成GTID信息开始同步主库备份期间产生的数据没有同步到从库。备份参数--single-transaction在备份开始前,先执行START TRANSACTION命令,以此来获得备份的一致性备份程序应该获取此时的GTID信息。2、通过LOG_FILE、LOG_POS方式搭建从库可以正常进行主从数据同步。3、使用MySQL 5.7.30、MySQL 8.0.32、GreatSQL8.0.32 的mysqldump程序进行备份程序获取的LOG_FILE、LOG_POS和GTID信息都在备份文件头记录这两者对应的日志文件位置是一样的使用GTID搭建主从后数据是一致的。MySQL 5.7.38的mysqldump程序MASTER_LOG_FILE和MASTER_LOG_POS在备份文件头GTID_PURGED在文件尾。用 MySQL 5.7.30、 MySQL 8.0.32、GreatSQL8.0.32 进行备份测试 备份的时候GTID和文件点位都在备份文件头 SET GLOBAL.GTID_PURGEDe9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3537; -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILEbinlog.000004, MASTER_LOG_POS643641556;4、经过测试验证mysqldump5.7.36之前和mysqldump8.0.31之后的版本不存在此问题。参考文章https://cloud.tencent.com/developer/article/1915277?shareByChannellinkhttps://bugs.mysql.com/bug.php?id105761