环境说明:
DB:MySQL 8.0.xx
问题现象:
检查备份日志:备份失败
cat /mysqldata/dblogs/back/mysqldump_bak_cjc_192.0.0.101_202601062145.log
结果如下：
[2026-01-06 21:45:01] #####################
[2026-01-06 21:45:01] # MySQL 备份开始 #
[2026-01-06 21:45:01] #####################
[2026-01-06 21:45:01] mysqldump process count is 0
[2026-01-06 21:45:01] ==== MySQL Backup Begin ====
[2026-01-06 22:34:18] backup file is not integrity
[2026-01-06 22:34:18] ###task aborted!###
检查失败日志:
mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `t1` at row: 0
详细日志如下:
mysql@cjc-db-01:/mysqldata/dbscripts$cat /mysqldata/dblogs/back/mysqldump_err_cjc_192.0.0.101_202601062145.log
......
mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `t1` at row: 0
[2026-01-06 22:34:18] DUMP_ERROR: backup file is not integrity
分析问题原因:
分析备份期间的binlog日志:
mysqlbinlog --no-defaults --start-datetime="2026-01-06 21:44:00" --stop-datetime="2026-01-06 22:35:00" --base64-output=decode-rows --verbose -vvv mysql-bin.011643 > /mysqldata/dbtmpfile/0107binlog.log
发现有13张表在mysqldump期间执行了truncate:
cat 0107binlog.log |grep "TRUNCATE"
结果如下:
TRUNCATE TABLE t13
TRUNCATE TABLE t12
TRUNCATE TABLE t11
TRUNCATE TABLE t10
TRUNCATE TABLE t9
TRUNCATE TABLE t8
TRUNCATE TABLE t7
TRUNCATE TABLE t6
TRUNCATE TABLE t5
TRUNCATE TABLE t4
TRUNCATE TABLE t3
TRUNCATE TABLE t2
TRUNCATE TABLE t1
查看执行时间
cat 0107binlog.log |grep -C 10 "TRUNCATE"|grep "#260106"|awk -F " " '{print $2}'
22:00:09
22:00:10
22:00:10
......
22:00:12
22:00:12
22:00:13
22:01:24
22:01:24
继续分析其他时间段，TRUNCAT等DDL操作时间:
mysqlbinlog --no-defaults --start-datetime="2026-01-06 13:00:00" --stop-datetime="2026-01-06 23:05:59" --base64-output=decode-rows --verbose -vvv mysql-bin.011643 > /mysqldata/dbtmpfile/0107binlog_E.log
发现这些DDL操作执行时间是有规律的,应该是从00:00开始，每2个小时执行一次:
mysql@cjc-db-01:/mysqldata/dbtmpfile$cat 0107binlog_E.log |grep -i -C 10 truncate|grep "#2601"|awk -F " " '{print $2}'
14:00:10
14:00:10
14:00:10
......
16:00:09
16:00:10
......
16:00:11
16:00:12
16:01:09
16:01:09
18:00:10
18:00:10
18:00:10
......
18:00:12
18:01:19
18:01:19
20:00:10
20:00:10
20:00:10
......
20:01:16
22:00:09
22:00:10
22:00:10
22:00:10
......
22:01:24
疑问:
mysqldump备份期间，对表执行truncate会有哪些影响，备份失败?truncate被锁阻塞?还是没影响？
通过如下实验进行验证:
mysqldump + DDL 测试
创建测试数据:
新增测试库、表
create database chen;
use chen;
create table t1(id int AUTO_INCREMENT PRIMARY KEY,name varchar(10),time datetime default CURRENT_TIMESTAMP);
#INSERT INTO T1(NAME,TIME) VALUES(CONCAT('user',FLOOR(RAND()*10000)),NOW() - INTERVAL FLOOR(RAND() * 365) DAY);
插入1百万行数据
DELIMITER $$
CREATE PROCEDURE p_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=1000000 DO
INSERT INTO T1(NAME,TIME) VALUES(CONCAT('user',FLOOR(RAND()*10000)),NOW() - INTERVAL FLOOR(RAND() * 365) DAY);
SET i = i+1;
END WHILE;
END$$
DELIMITER ;
执行:
mysql> call p_insert();
Query OK, 1 row affected (52 min 42.69 sec)
mysql> select * from t1 limit 5;
+----+----------+---------------------+
| id | name | time |
+----+----------+---------------------+
| 1 | user9757 | 2025-06-15 10:31:18 |
| 3 | user9172 | 2025-02-22 10:31:18 |
| 5 | user6454 | 2025-06-08 10:31:18 |
| 7 | user82 | 2025-09-30 10:31:18 |
| 9 | user3479 | 2025-02-08 10:31:18 |
+----+----------+---------------------+
5 rows in set (0.00 sec)
继续新增数据:
mysql> insert into t2(name,time) select name,time from t1;
Query OK, 1000000 rows affected (7.93 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into t1(name,time) select name,time from t1;
Query OK, 1000000 rows affected (8.77 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into t1(name,time) select name,time from t1;
Query OK, 2000000 rows affected (17.57 sec)
Records: 2000000 Duplicates: 0 Warnings: 0
mysql> insert into t1(name,time) select name,time from t1;
Query OK, 4000000 rows affected (35.17 sec)
Records: 4000000 Duplicates: 0 Warnings: 0
mysql> insert into t1(name,time) select name,time from t1;
Query OK, 8000000 rows affected (1 min 11.85 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
场景一:
mysqldump备份，还没备份到t2时，执行t2的truncate操作;
会话1:
rm -f /cjc/mysqldata/dbtmpfile/chen_01.sql
mysqldump -udumper -p****** --databases chen --hex-blob --extended-insert=FALSE --source-data=2 \
--single-transaction --routines --triggers --events --set-gtid-purged=OFF \
--flush-logs > /cjc/mysqldata/dbtmpfile/chen_01.sql
会话2:
mysql> use chen;
Database changed
mysql> truncate table t2;
Query OK, 0 rows affected (0.12 sec)
会话3:
mysql> show processlist;
+-----+-----------------+----------------------+------+------------------+---------+-----------------------------------------------------------------+---------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+----------------------+------+------------------+---------+-----------------------------------------------------------------+---------------------------------------------+
| 11 | event_scheduler | localhost | NULL | Daemon | 9144681 | Waiting on empty queue
......
| 232 | dumper | localhost | chen | Query | 14 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` |
+-----+-----------------+----------------------+------+------------------+---------+-----------------------------------------------------------------+---------------------------------------------+
11 rows in set (0.00 sec)
会话1:前台备份:
mysql@SATEST-DB-004:/home/mysql$mysqldump -udumper -p****** --databases chen --hex-blob --extended-insert=FALSE --source-data=2 \
> --single-transaction --routines --triggers --events --set-gtid-purged=OFF \
> --flush-logs > /cjc/mysqldata/dbtmpfile/chen_01.sql
报错如下:
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `t2` at row: 0
检查备份文件:
导出了t2的表结构，但是无法导出t2表数据:
tail -30 /cjc/mysqldata/dbtmpfile/chen_01.sql
......
INSERT INTO `t1` VALUES (32286625,'user7884','2025-12-05 11:24:01');
INSERT INTO `t1` VALUES (32286627,'user1066','2025-10-09 11:24:01');
INSERT INTO `t1` VALUES (32286629,'user9300','2025-02-14 11:24:01');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `t2`
--
DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t2` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t2`
--
LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
场景二:
mysqldump备份，正在导出t1数据时，执行t1的truncate操作;
会话1:
rm -f /cjc/mysqldata/dbtmpfile/chen_01.sql
mysqldump -udumper -p****** --databases chen --hex-blob --extended-insert=FALSE --source-data=2 \
--single-transaction --routines --triggers --events --set-gtid-purged=OFF \
--flush-logs > /cjc/mysqldata/dbtmpfile/chen_01.sql
mysqldump执行成功，没有报错:
mysqldump: [Warning] Using a password on the command line interface can be insecure.
会话2:
mysql> use chen;
mysql> truncate table t1;
Query OK, 0 rows affected (11.88 sec)
会话3:可以看到会话2的truncate被阻塞，在等待 Waiting for table metadata lock。
mysql> show processlist;
+-----+-----------------+----------------------+------+------------------+---------+-----------------------------------------------------------------+---------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+----------------------+------+------------------+---------+-----------------------------------------------------------------+---------------------------------------------+
......
| 230 | root | localhost | chen | Query | 4 | Waiting for table metadata lock | truncate table t1 |
| 231 | root | localhost | NULL | Query | 0 | init | show processlist |
| 233 | dumper | localhost | chen | Query | 5 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` |
+-----+-----------------+----------------------+------+------------------+---------+-----------------------------------------------------------------+---------------------------------------------+
场景三:
mysqldump备份，已经导出完t1数据，正在导出t2数据，执行t1的truncate操作;
重新插入数据，创建小表t1，和大表t2。
t1表:5行数据
insert into chen.t1(name,time) values('aaa',sysdate());
insert into chen.t1(name,time) values('bbb',sysdate());
insert into chen.t1(name,time) values('ccc',sysdate());
insert into chen.t1(name,time) values('ddd',sysdate());
insert into chen.t1(name,time) values('eee',sysdate());
select count(*) from t1;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.02 sec)
t2表:20971520行数据
insert into t2(name,time) select name,time from t1;
insert into t2(name,time) select name,time from t2;
insert into t2(name,time) select name,time from t2;
......
insert into t2(name,time) select name,time from t2;
Query OK, 10485760 rows affected (1 min 30.69 sec)
Records: 10485760 Duplicates: 0 Warnings: 0
select count(*) from t2;
+----------+
| count(*) |
+----------+
| 20971520 |
+----------+
1 row in set (6.02 sec)
会话1:
rm -f /cjc/mysqldata/dbtmpfile/chen_01.sql
mysqldump -udumper -p****** --databases chen --hex-blob --extended-insert=FALSE --source-data=2 \
--single-transaction --routines --triggers --events --set-gtid-purged=OFF \
--flush-logs > /cjc/mysqldata/dbtmpfile/chen_01.sql
mysqldump执行成功，没有报错:
mysqldump: [Warning] Using a password on the command line interface can be insecure.
会话2:
mysql> use chen;
mysql> truncate table t1;
Query OK, 0 rows affected (0.10 sec)
会话3:
mysql> show processlist;
+-----+-----------------+----------------------+------+------------------+---------+-----------------------------------------------------------------+---------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+----------------------+------+------------------+---------+-----------------------------------------------------------------+---------------------------------------------+
......
| 237 | dumper | localhost | chen | Query | 6 | executing | SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2` |
+-----+-----------------+----------------------+------+------------------+---------+-----------------------------------------------------------------+---------------------------------------------+
11 rows in set (0.00 sec)
结论:
mysqldump备份期间，对表执行truncate会有哪些影响，备份失败?truncate被锁阻塞?还是没影响？都有可能，不同场景，结果不同:
场景一:
mysqldump备份，当前正在备份和导出t1表数据，还没备份到t2时，其他会话执行t2的truncate操作:
1.其他会话可以正常执行t2的truncate操作；
2.mysqldump当备份到t2表时，可以正常导出t2表结构，但是无法导出t2表数据，并出现如下报错，自动中断mysqldump操作:
mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table t2 at row: 0
mysqldump最开始备份时，会检查所有表的表结构版本，在导出表数据时会用之前检查的表结构版本和当前表的表结构版本进行对比，如果不同，就会报错。
场景二:
mysqldump备份，当前正在导出t1数据时，其他执行t1的truncate操作;
1.其他会话的t1的truncate操作会被阻塞，Waiting for table metadata lock，直到mysqldump完成t1表的备份。
mysqldump导出表数据时，会先加锁:
LOCK TABLES t1 WRITE;
导出数据完成后，才会释放锁:
UNLOCK TABLES;
2.mysqldump备份可以正常完成，不会异常中断。
场景三:
mysqldump备份，已经导出完t1数据，当前正在导出t2数据，其他会话执行t1的truncate操作;
1.其他会话可以正常执行t1的truncate操作；
2.mysqldump备份可以正常完成，不会异常中断。
解决方案:
调整备份开始时间，错开truncate操作，第二天查看，备份恢复正常。
root@cjc-db-01:/root#cat /mysqldata/dblogs/back/mysqldump_bak_cjc_192.0.0.101_202601072005.log
[2026-01-07 20:05:02] #####################
[2026-01-07 20:05:02] # MySQL 备份开始 #
[2026-01-07 20:05:02] #####################
[2026-01-07 20:05:02] mysqldump process count is 0
[2026-01-07 20:05:02] 待删除的文件:/mysqldata/back/mysqldump_eberm_202601062145.sql.gz
[2026-01-07 20:05:02] delete backup status: sucess
[2026-01-07 20:05:02] ==== MySQL Backup Begin ====
[2026-01-07 22:22:03] backup status: sucess 2026-01-07-22:22:03
[2026-01-07 22:22:03] 备份文件名称:/mysqldata/back/mysqldump_eberm_202601072005.sql.gz
[2026-01-07 22:22:03] 备份文件大小:31G
[2026-01-07 22:22:03] ==== MySQL Backup End ====
[2026-01-07 22:22:03] 待删除的文件:
[2026-01-07 22:22:03] delete backup status: sucess
[2026-01-07 22:22:03] #####################
[2026-01-07 22:22:04] # MySQL 备份结束 #
[2026-01-07 22:22:04] #####################
root@cjc-db-01:/root#
欢迎关注我的公众号《IT小Chen》
最后修改时间：2026-01-12 09:38:23
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
