慎用mysqldump与GTID自动定位：一次备份数据丢失的排查

慎用mysqldump与GTID自动定位：一次备份数据"丢失"的排查

本文摘要

导入mysqldump的备份数据，并通过MASTER_AUTO_POSITION=1配置从节点，启动复制后，出现1032错误（行不存在）。经排查发现，MySQL 5.7.38的mysqldump备份文件内部存在逻辑矛盾：其记录的起始复制位置与GTID集存在时间差，导致使用MASTER_AUTO_POSITION=1配置从节点时，会丢失备份期间的数据。而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    --user=greatsql --basedir=/gdb/mysqldb/mysql-5.7.38 --datadir=/gdb/mysqldb/5738/data
...#输出省略
password ! Please consider switching off the --initialize-insecure option.

创建用户

在MySQL中创建用户：

mysql>CREATE USER admin@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'admin' ;
mysql>GRANT ALL PRIVILEGES ON . TO admin@'%'  WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;

创建测试库及测试表

在MySQL中创建测试库及测试表：

mysql>CREATE DATABASE sbtest;
Query OK, 1 row affected (0.01 sec)
mysql>USE 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
mysql>CREATE 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-db=sbtest --mysql-host=192.168.18.2 --mysql-port=5738 --mysql-user=admin  --mysql-password=admin --tables=3 --table_size=3000000 --report-interval=2 --threads=4 --db-driver=mysql --skip-trx=off --db-ps-mode=disable --create-secondary=off --time=0 --simple-ranges=0 --sum-ranges=0 --order-ranges=0 --distinct-ranges=0 --mysql-ignore-errors=9001,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
USER="admin"
PASSWORD="admin"
HOST="192.168.18.2"
PORT=5738
DBNAME="sbtest"
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);"
done

GTID信息

sysbench 插入数据后：mysql>SHOW 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)备份完成：mysql>SHOW 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-purged=ON --master-data=2 --routines --events --triggers --databases sbtest    --force > /tmp/sbtest.sql

停止数据写入

备份完成后，kill 掉写入数据的t_keep.sh 进程

GreatSQL导入备份文件

$ du -sh sbtest.sql 
1.7G    sbtest.sql登录GreatSQL数据库，将备份文件导入到GreatSQL数据库
GreatSQL>SOURCE /tmp/sbtest.sql;

基于GTID模式配置从库

将GreatSQL配置为基于GTID模式的MySQL从库

GreatSQL>CHANGE MASTER TO MASTER_HOST='192.168.18.2',MASTER_USER="ADMIN",MASTER_PASSWORD='ADMIN',MASTER_PORT=5738,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)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>START SLAVE;
Query OK, 0 rows affected (0.00 sec)

主从数据对比

gtid信息对比：对比主库和从库的gtid信息，两个库是一致的，都是1-3537

主库:
mysql>SHOW 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)
从库:
GreatSQL>SHOW 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表数据不一致，主库比从库多

主库:
mysql>SELECT count(*) FROM t_keep_alive;
+----------+
| count(*) |
+----------+
|      171 |
+----------+
1 row in set (0.01 sec)从库:
GreatSQL>SELECT 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_FILE+LOG_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_HOST='192.168.18.2',MASTER_USER="ADMIN",MASTER_PASSWORD='ADMIN',MASTER_PORT=5738,MASTER_LOG_FILE='BINLOG.000004', MASTER_LOG_POS=643606664;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_FILE='binlog.000004', MASTER_LOG_POS=643606664;
--
-- 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_PURGED='e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3466';
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2026-01-12 11:11:36
SET @@GLOBAL.GTID_PURGED='e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3466';

备份文件认为1-3466这些事务的数据已经写入到数据库。

实际情况情况呢？

解析备份文件LOG_FILE、LOG_POS相邻的数据。

解析binlog.000004 ，MASTER_LOG_POS=643606664后的下一个事务是'e9ceeaef-ed3d-11f0-893c-00163ed468c9:3416'

SET @@SESSION.GTID_NEXT= 'e9ceeaef-ed3d-11f0-893c-00163ed468c9:3416'/*!*/;
# at 643606729
#260112 11:10:37 server id 425738  end_log_pos 643606803 CRC32 0x7f5398a1

LOG_FILE、LOG_POS 对应的下一个事务是'e9ceeaef-ed3d-11f0-893c-00163ed468c9:3416'，这种同步方式是从3416开始同步数据。

而通过MASTER_AUTO_POSITION=1的方式同步数据，由于备份文件最后是

SET @@GLOBAL.GTID_PURGED='e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3466'; (MySQL 5.7.38的mysqldump在备份完成时才获取gtid信息) ，这条语句写入数据库后，数据库认为1-3466的数据已经写入到数据库了，这些数据不需要进行同步。

配置MASTER_AUTO_POSITION=1后，从3467开始同步新的数据。两种同步方式差异就是备份期间产生的51条数据(3467-3416=51 )，在主库UPDATE备份期间产生的数据，从库就会出现1032错误，导致主从复制链路失败。

问题总结

1、使用MySQL 5.7.38的mysqldump进行数据备份，备份数据导入从库，通过MASTER_AUTO_POSITION=1配置主从后，主从数据库数据不一致，从库开始从备份完成时生成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_PURGED='e9ceeaef-ed3d-11f0-893c-00163ed468c9:1-3537';
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=643641556;

4、经过测试验证，mysqldump5.7.36之前和mysqldump8.0.31之后的版本不存在此问题。

参考文章

https://cloud.tencent.com/developer/article/1915277?shareByChannel=link

https://bugs.mysql.com/bug.php?id=105761