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

概述

先从一个监控告警的典型案例说起：凌晨时段收到MySQL数据库监控告警，提示某个实例的全量备份操作失败。登录备份节点查看日志，报错信息如下：An optimized (without redo logging) DDL operation has been performed. All modified pages may not ha ve been flushed to the disk yet.PXB will not be able to take a consistent backup. Retry the backup operation. 顺着这条报错信息，结合业务侧的逻辑排查，最终定位到了根本原因。不仅如此，后续还引申出多个值得深入探讨的问题，我们从原理层面逐一进行了拆解。

全备报错分析

与业务侧确认后，发现全备期间确实有跑批任务在运行，但并未执行创建索引的操作。为了全面排查可能导致全备失败的原因，我们专门分析了binlog。从binlog解析结果来看，DML操作包括update、delete、insert，DDL操作包括drop和optimize。但确实没有找到创建索引的语句，即没有alter table add index或create index的记录。

全备工具使用的是xtrabackup。根据报错关键字，在Percona官方博客中找到了一个完全匹配的案例。该案例描述的是MySQL 5.7全备时导致备份文件损坏或不一致，其根本原因在于某些DDL语句跳过了写redo——具体指向了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，也不是整个InnoDB不写redo、所有事务都不写redo。而是——针对“正在构建的那些新索引页”的页面修改，不走常规的redo logging记录。锁定这个目标后就好办了：禁用redo，其实就是索引构建过程中产生的新page的操作，不会记录到redo log中。

那么为什么这样设计？因为批量排序索引构建B+Tree本身就是一种可重放操作——只要原数据还在、原聚簇索引还在，二级索引就可以重新创建。它并不是只有redo才能恢复的操作。而redo对它而言，代价极高、收益很低。举个例子：在创建100G的索引时，会产生100G甚至更多的redo，这些redo包含每个页的修改、每次页分裂、每次页头部修改等。更严重的是，redo log本质上是顺序fsync日志，而建索引本来就已经是大量排序+大量page写入，再加上redo的写放大，会导致redo file被疯狂刷满，日志检查点压力骤增；checkpoint压力巨大，引起严重堵塞；DDL性能急剧下降（DDL时间变长），写redo反而成了瓶颈；DDL狂写redo时，redo是全局共享资源，业务可能出现写延迟，受到影响；另外，崩溃恢复时间也会变长，redo recovery需要回放巨量的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一样，都是用来对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触发的表重建操作是就地完成的。在准备阶段和提交阶段，只会短暂获取独占表锁。准备阶段更新元数据并创建一个中间表，提交阶段提交表元数据更改。整个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

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

总结： 无论表中是否有数据，在执行全备过程中，只要执行了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

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

以上测试结果汇总如下：

从统计结果可以得出：

• 全备期间，一旦执行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 ha ve 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变更，最好与全备时间窗口错开，以免导致全备失败。

到这里，备份失败的问题算是定位并解决了。不过整个备份恢复的闭环还没有完全走完——备份成功后，用该备份进行恢复，恢复后的实例是否符合预期？这个待后续更新。