MySQL redo日志禁用导致全量备份失败

概述

先分享一个监控告警案例：凌晨收到MySQL数据库监控告警，显示某个实例的全备操作失败。登录备份节点查看日志，报错信息如下：An optimized (without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet.PXB will not be able to take a consistent backup. Retry the backup operation. 根据该报错，结合业务侧逻辑排查，最终定位到根因，并引申出多个值得深挖的问题。后续从原理层面逐一拆解。

全备报错分析

与业务侧确认后，发现全备期间确有跑批任务运行，但未执行创建索引操作。为全面排查导致全备失败的可能因素，专门分析了binlog。解析结果显示，DML操作包括update、delete、insert，DDL操作包括drop和optimize，但未发现alter table add index或create index等创建索引语句。

全备工具为xtrabackup。根据报错关键字，在Percona官方博客中找到完全匹配的案例。该案例指出MySQL 5.7全备时可能因某些跳过写redo的DDL语句导致备份文件损坏或不一致，具体指向MySQL新增的“排序索引构建”功能。

但binlog解析结果与该案例不符——无创建索引操作记录。重新仔细阅读案例后，发现核心关键点：只要触发排序索引构建，全备期间就可能失败。再次分析binlog，既然排除创建索引，那么还有哪些DDL会触发排序索引构建？此时，optimize操作引起关注。optimize table用于整理表空间碎片、释放磁盘空间。联想到跑批场景常用delete+optimize逻辑，这便说得通了。那么optimize是否真的会触发排序索引构建？为何会触发？

直接在测试环境中验证。

测试环境进行验证

环境信息：

• 全备工具：xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
• MySQL server：8.0.25 MySQL Community Server - GPL
• 操作系统：CentOS Linux release 7.8.2003 (Core)

验证步骤：

（1）利用sysbench创建一张测试表

安装sysbench后，准备数据：

sysbench ./oltp_read_write.lua --threads=10 --mysql-user=ashan--mysql-password='yourpassword' --mysql-host=192.168.8.127 --mysql-port=3306 --mysql-db=sbtest --report-interval=5 --table-size=2000000 --tables=1 prepare

（2）循环执行drop、create、insert、delete、optimize操作

脚本如下：

[root@localhost backup]# cat run_createtable_insert_delete_optimize.sh
#!/bin/bash
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db1.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db1.sb1 as select id,c from sbtest.sbtest1 where id < 50000;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "delete from db1.sb1;optimize table db1.sb1;"
sleep 1
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db2.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db2.sb1 as select id,c from sbtest.sbtest1 where id < 50000;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "delete from db2.sb1;optimize table db2.sb1;"

循环执行脚本：

while true;do sh run_createtable_insert_delete_optimize.sh ;done

输出如下：

（3）循环执行物理全备脚本

全备脚本：

[root@localhost bin]# cat x.sh
./xtrabackup --defaults-file=/etc/my.cnf --user=root --password='yourpassword' --target-dir=/backup/full_20250528 --backup --host=127.0.0.1 --port=3306 --galera-info --parallel 2 --check-privileges --no-version-check --no-backup-locks --lock-ddl;echo $
sleep 1
rm -rf /backup/full_20250528
echo "======================================================================================================================================================================================="

注意： 全备脚本x.sh放在与xtrabackup二进制文件同目录中。

循环执行全备脚本：

while true;do sh x.sh ;done

提示： xtrabackup退出码：备份过程中无错误时退出码为0；发生错误时退出码为1。某些情况下由于MySQL库中包含的命令行选项代码，退出码可能不是0或1（例如未知选项时退出码为255）。所以在xtrabackup备份后，使用echo $获取上一条命令的退出状态码。

全备日志输出：

验证确认：全备过程中执行optimize操作，确实会导致全备失败。

疑问及扩展

测试验证结束，但引发出更多疑惑：

• 问题1： 索引构建过程中为什么要禁用redo？不禁用会出现什么问题？
• 问题2： optimize table为什么会触发批量排序索引构建？
• 问题3： 若将delete+optimize table换成truncate table，或换成drop table+create table+create index，是否也会触发排序索引构建？
• 问题4： 是否存在解决办法，使redo被禁用时全备仍能继续而不失败？

问题1：索引构建过程中为什么要禁用redo，不禁用会出现什么问题？

从MySQL官方文档可了解排序索引构建的概念、构建的三个阶段及其与重做日志记录的关系。核心在于：禁用redo并非关闭整个MySQL实例的redo，也不是所有事务不写redo，而是针对“正在构建的新索引页”的页面修改，不记录常规redo logging。锁定目标后，原理清晰：禁用redo意味着索引构建过程中新page的操作不写入redo log。

这样设计的原因在于：批量排序索引构建B+Tree本身是一种可重放操作——只要原数据和原聚簇索引还在，二级索引就可以重新创建，并不依赖redo来恢复。而redo对其代价极高、收益极低。例如创建100G索引时，可能产生100G甚至更多redo，包含每个页的修改、页分裂、页头部修改等。更严重的是，redo log为顺序fsync日志，建索引时大量排序+大量page写入，加上redo写放大，会导致redo file被频繁刷满、检查点压力骤增，进而引发严重堵塞。DDL性能急剧下降，写redo反成瓶颈；全局共享资源redo被大量占用，可能导致业务写延迟；崩溃恢复时间也会延长，需回放巨量redo。由于redo被禁用，系统会执行检查点操作，强制将所有脏页写入磁盘，确保索引构建能承受意外退出或故障。在排序索引构建期间，页面清理线程定期收到信号刷新脏页，使检查点操作快速完成。

问题2：optimize table为什么会触发批量排序索引构建？

optimize table等价于recreate + analyze操作，测试验证中已体现。官方文档说明：对于InnoDB表，OPTIMIZE TABLE映射为ALTER TABLE ... FORCE，重建表并更新索引统计信息，释放聚集索引中未使用的空间。即optimize table映射为ALTER TABLE FORCE语句，与ALTER TABLE tbl_name ENGINE=INNODB一样用于碎片整理。重建表的过程涉及旧数据和索引搬迁，再执行bulk load，从而触发排序索引构建。

特别注意：测试中使用了delete删除全表数据（而非部分数据），同样触发全备报错。这说明xtrabackup在全备时检测的是“是否发生了不写redo的DDL”，而不会判断该DDL最终构建了多少个数据页。换言之，一旦检测到InnoDB的DDL进入批量排序索引构建流程，就会报错。

若感兴趣，可将测试中的delete from db1.sb1改为delete from db1.sb1 limit 45000，再运行，结果同样触发全备不一致。上面直接删除全表数据主要是为了测试临界点——即最终无数据页时能否触发全备不一致。而删除部分数据，剩余数据页更多，参与排序索引构建的页面更多。

特别提醒： delete删除数据时应分批操作，切勿直接删除全表数据。数据量较大时可带上索引条件列，按分批小事务执行。

关于optimize table的补充说明：

OPTIMIZE TABLE对常规InnoDB表和分区InnoDB表使用在线DDL，可减少并发DML操作的停机时间。触发的表重建操作为就地完成，仅在准备阶段和提交阶段短暂获取独占表锁。准备阶段更新元数据并创建中间表，提交阶段提交表元数据更改。整个optimize table过程实质是将表页面重新排序整理，使所有B+Tree更紧凑、碎片更少。

还可测试：在完全无数据的情况下，optimize table是否也会造成排序索引构建？

测试脚本：

[root@localhost backup]# cat run_createtable_optimize.sh
#!/bin/bash
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db1.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db1.sb1 as select id,c from sbtest.sbtest1 where 1=0;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "optimize table db1.sb1;"
sleep 1
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db2.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db2.sb1 as select id,c from sbtest.sbtest1 where 1=0;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "optimize table db2.sb1;"

循环执行：

while true;do sh run_createtable_optimize.sh ;done

再循环执行全备脚本：

while true;do sh x.sh ;done

测试结果：全备失败，仍然报全备不一致错误。

用例场景	是否单独创建索引	是否有数据	是否导致全备失败
drop table+create table as select +delete+optimize table	否	是	是
drop table+create table+optimize table	否	否	是

总结： 无论表中是否有数据，在全备过程中只要执行optimize table语句，就会导致全备失败。

问题3：如果将delete+optimize table换成truncate table，或者换成drop table+create table+create index，是否也会出现排序索引构建？

先看truncate table操作。它类似于drop table+create table，为原子操作，要么全部成功，要么全部失败。truncate语句属于DDL。对InnoDB表执行truncate table后，该表会删除现有表空间并创建新表空间。这个重建表空间的操作可理解为drop table+create table，会写入redo log，并不会触发排序索引构建。

对于drop table+create table select+create index操作，将组合测试分为以下5个用例：

（1）drop table+create table as select+create index

测试脚本：

[root@localhost backup]# cat run_createtable_createidx.sh
#!/bin/bash
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db1.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db1.sb1 as select id,c from sbtest.sbtest1 where id < 50000;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create unique index ix on db1.sb1 (id);"
sleep 1
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db2.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db2.sb1 as select id,c from sbtest.sbtest1 where id < 50000;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create unique index ix on db2.sb1 (id);"

循环执行：

while true;do sh run_createtable_createidx.sh ;done

再循环执行全备脚本：

while true;do sh x.sh ;done

测试结果： 全备失败，出现全备不一致报错。

（2）drop table+create table

此用例验证无create index时是否还会触发全备不一致。

测试脚本：

[root@localhost backup]# cat run_createtable_droptable.sh
#!/bin/bash
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db1.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db1.sb1(id int key,c varchar(23));"
sleep 1
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db2.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db2.sb1(id int key,c varchar(23));"

循环执行：

while true;do sh run_createtable_droptable.sh ;done

再循环执行全备：

while true;do sh x.sh ;done

测试结果： 全备成功，无全备不一致报错。

（3）create table+drop table

进一步验证无create index的情况。

测试脚本：

[root@localhost backup]# cat run_createtable_droptable.sh
#!/bin/bash
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db1.sb1 like sbtest.sbtest1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db1.sb1;"
sleep 1
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db2.sb1 like sbtest.sbtest1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db2.sb1;"

循环执行：

while true;do sh run_createtable_droptable.sh ;done

再循环执行全备：

while true;do sh x.sh ;done

测试结果： 全备成功，无全备不一致报错。

（4）已有表结构，有数据，执行drop index+create index

该用例主要用于复现并印证Percona博客中的案例。

先插入数据：

mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db1.sb1 as select id,c from sbtest.sbtest1 where id < 50000;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db2.sb1 as select id,c from sbtest.sbtest1 where id < 50000;"

测试脚本：

[root@localhost backup]# cat run_dropidx_createidx.sh
#!/bin/bash
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "alter table db1.sb1 drop index ix;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create unique index ix on db1.sb1 (id);"
sleep 1
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "alter table db2.sb1 drop index ix;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create unique index ix on db2.sb1 (id);"

循环执行：

while true;do sh run_dropidx_createidx.sh ;done

再循环执行全备：

while true;do sh x.sh ;done

测试结果： 全备失败，出现全备不一致报错。

（5）已有表结构，无数据，执行create index

测试表中无数据时，创建索引是否会触发全备不一致。

测试脚本：

[root@localhost backup]# cat run_createtable_nodata_createidx.sh
#!/bin/bash
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db1.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db1.sb1 as select id,c from sbtest.sbtest1 where 1=0;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create unique index ix on db1.sb1 (id);"
sleep 1
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "drop table if exists db2.sb1;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create table db2.sb1 as select id,c from sbtest.sbtest1 where 1=0;"
mysql -uroot -P3306 -hlocalhost -pyourpassword -e "create unique index ix on db2.sb1 (id);"

循环执行：

while true;do sh run_createtable_nodata_createidx.sh ;done

再循环执行全备：

while true;do sh x.sh ;done

测试结果： 全备失败，出现全备不一致报错。

以上测试结果汇总：

测试用例（按顺序）	用例场景	是否单独创建索引	是否有数据	是否导致全备失败
1	drop table+create table as select+create index	是	是	是
2	drop table+create table	否	否	否
3	create table+drop table	否	否	否
4	drop index+create index	是	有	是
5	create index	是	否	是

统计结果显示：

• 全备期间一旦执行create index，无论表中是否有数据，都可能导致全备失败。
• 全备期间执行drop table或create table，不会导致全备失败。

问题4：是否存在解决办法，即redo被禁用时全备仍能继续而不中止？

存在。关键在于全备命令xtrabackup中的参数——no-backup-locks。该参数控制备份阶段是否使用备份锁代替FLUSH TABLES WITH READ LOCK。若服务器不支持备份锁，该选项无效。此选项默认启用，使用--no-backup-locks可禁用。即指定--no-backup-locks时，全备会使用FLUSH TABLES WITH READ LOCK全局锁获取一致性点位；默认配置（即--backup-locks，不指定也是该配置）使用更轻量级的锁。与FLUSH TABLES WITH READ LOCK不同，LOCK TABLES FOR BACKUP不会刷新表，存储引擎不会被强制关闭表，表也不会从表缓存中移除。因此，LOCK TABLES FOR BACKUP只会等待冲突语句完成（如DDL语句和对非事务表的更新），但从不等待SELECT语句或对InnoDB/MyRocks表的UPDATE语句完成。

验证测试中，使用上述测试用例，在全备时不指定--no-backup-locks，全备均成功。但会输出warning级别告警：An optimized(without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet.This offline backup may not be consistent. 即xtrabackup将全备可能不一致的情况以warning级别打印，这说明已无需担心全备期间触发排序索引构建导致redo禁用——MySQL会强制刷盘来推进checkpoint，从而解决此问题。

指定与不指定--no-backup-locks参数的备份日志结果对比：

指定了--no-backup-locks：

不指定--no-backup-locks：

指定--no-backup-locks的结果与之前测试一致，备份直接失败。

不指定时，虽然提示备份可能不一致，但仍会继续执行并完成全备。

根据xtrabackup官方Release notes，从8.0.28版本开始，错误日志采用标准化结构，统一日志头让跟踪操作进度或查看日志诊断问题更加容易。

使用8.0.28版本的xtrabackup进行全备，测试结果如下：

全备失败的日志：

全备成功的日志：

总结

通过以上测试验证可得出结论：造成全备失败的根因，是全备期间执行了optimize table语句，其原理是触发了排序索引构建。由于全备时指定了--no-backup-locks参数，使用FTWRL获取一致性位点，当备份到optimize table所涉及的数据页时，xtrabackup发现redo被禁用，无法进行一致性备份，最终导致全备失败。

调整及建议：

• 使用xtrabackup进行全备时，尽量不要指定--no-backup-locks参数，xtrabackup已有更轻量化的备份锁可替代FTWRL。
• 建议使用8.0.28及以上版本，日志输出更清晰，更易排查问题。
• 若有DDL变更，最好与全备时间窗口错开，以免导致全备失败。

至此，备份失败的问题已定位并解决。不过整个备份恢复的闭环尚未完全走完——备份成功后，使用该备份进行恢复，恢复后的实例是否符合预期？待后续更新。