慎用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
311
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
