故障分析 | DDL 导致的 Xtrabackup 备份失败

作者：赵黎明

爱可生 MySQL DBA 团队成员，熟悉 Oracle、MySQL 等数据库，擅长数据库性能问题诊断、事务与锁问题的分析等，负责处理客户 MySQL 及我司自研 DMP 平台日常运维中的问题，对开源数据库相关技术非常感兴趣。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

---

背景

近日，客户反馈某生产业务系统凌晨的物理备份都失败了（一主二从的集群，仅在两个从库上做 Xtrabackup 全备，主库不参与备份），需排查备份失败的原因。

案例分析

由于客户使用的是我司爱可生的 DMP 数据库管理平台，当备份失败时，在备份目录中会写入一个 FAIL 的标志文件，然后回滚掉残留文件，此时 Xtrabackup 自身的日志已无法查看，不过可以通过 urman-agent 组件（负责备份恢复）日志来获取备份失败的信息，以下是当时两个从库上的报错信息

• 从库1日志

• 从库2日志

两个从库虽然报错的时间不同，但报错的内容一致，都指向了“不记录 redo 日志的 DDL 操作”：

[FATAL] InnoDB: An optimized(without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
PXB will not be able take a consistent backup. Retry the backup operation

经确认，客户的确是在凌晨执行了 DDL 业务变更，变更的内容为创建一张新表，并给现存的两张表添加字段，加字段的表大约有几百万行记录，这一信息与日志给出的内容吻合，看来问题大概率是出在加字段的 DDL 操作上

那什么是不记录 redo 的 DDL 的操作呢？为何会存在不记录 redo 的 DDL ？

首先，我们知道，在 MySQL 5.7 中给表加字段属于 ONLINE DLL ，会重建表，但允许并发 DML（PS：MySQL 8.0 加字段不需要重建表）

由于 MySQL 采用的是索引组织表（IOT），表上的索引当然也需要重建，由于采用了 ALGORITHM=INPLACE 的方式，允许并发 DML

在 MySQL 5.7 中，对索引有一个优化，即 Sorted Index Builds ，会在创建或重建索引的时候通过 bulk load 、bottom-up 的方式来填充索引记录

采用 Sorted Index Build 方式创建索引时，不会记录到 redo 日志中，而这就是之前 Xtrabackup 报错中所描述的“An optimized(without redo logging) DDL operation”的场景

Percona 称这是 Xtrabackup 2.4.x 的一个 bug ，主要是为了避免得到一个错误的备份集，一旦检测到有不记录 redo 的 DDL 操作，就会将备份进程终止，而客户生产环境中的 PXB 版本正是2.4.5

针对这一问题，Percona在Xtrabackup 2.4.8 及之后的版本中，新增了–lock-ddl，–lock-ddl-timeout，–lock-ddl-per-table这几个参数，使其可以在备份时加上备份锁，或给表加上 MDL 锁来阻塞其他的 DDL 操作，使备份顺利完成

原因明确了，就知道如何解决问题了，最后分别在2个从库上执行手动备份，每个实例（500G左右）大约耗时2小时40分钟完成备份

Percona 以增加参数的方式提供了解决备份失败的方法，那如果暂时无法升级 PXB 版本，仅在 MySQL 层面，有没有解决方法呢？其实也是可以的。

对于以上提到的几种场景，我们都来测试一下吧

场景测试

环境准备

创建测试表

/usr/local/sysbench/share/sysbench# sysbench oltp_insert.lua --db-driver=mysql --threads=256 --time=300 --mysql-host=10.186.60.68 --mysql-port=3332 --mysql-user=zlm --mysql-password=zlm --tables=2 --table-size=2000000 --db-ps-mode=disable --report-interval=10 prepare
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest2'...
Inserting 2000000 records into 'sbtest2'
Creating table 'sbtest1'...
Inserting 2000000 records into 'sbtest1'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...

准备脚本（batch_ddl.sh）

-- 该脚本的作用是对测试表进行持续的DDL操作（增加/删除字段，模拟客户的业务变更）
dmp2 (master) ~/script# echo > batch_ddl.sh
dmp2 (master) ~/script# cat << EOF > batch_ddl.sh
> #!/bin/bash
> echo "alter table sbtest1 add sid varchar(32);"|/data/mysql/base/5.7.36/bin/mysql -h10.186.60.68 -P3332 -uzlm -pzlm sbtest
> sleep 1
> echo "alter table sbtest2 add sid varchar(32);"|/data/mysql/base/5.7.36/bin/mysql -h10.186.60.68 -P3332 -uzlm -pzlm sbtest
> sleep 10
> echo "alter table sbtest1 drop sid;"|/data/mysql/base/5.7.36/bin/mysql -h10.186.60.68 -P3332 -uzlm -pzlm sbtest
> sleep 1
> echo "alter table sbtest2 drop sid;"|/data/mysql/base/5.7.36/bin/mysql -h10.186.60.68 -P3332 -uzlm -pzlm sbtest
> EOF

场景1：备份时并发执行 DDL（无额外参数）

执行脚本

dmp2 (master) ~/script# while true; do bash batch_ddl.sh; done
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
... 略

验证脚本是否生效（观察测试表的 sid 字段，时而有，时而无）