Resolving Xtrabackup Backup Failures Caused by Non‑Redo Logging DDL Operations in MySQL 5.7

Background

A customer reported that nightly physical backups on a primary‑replica MySQL cluster (one master, two slaves) were failing; only the two replica servers performed Xtrabackup full backups.

Case Analysis

The backup directory contains a FAIL flag file, and Xtrabackup’s own logs are unavailable, so the urman‑agent component logs are examined. Both replica logs show the same error:

[FATAL] InnoDB: 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 take a consistent backup. Retry the backup operation

The failure coincides with a midnight DDL change that created a new table and added a column to two existing tables containing millions of rows.

Why Some DDL Operations Do Not Write Redo Logs

In MySQL 5.7, adding a column is an ONLINE DDL operation that rebuilds the table using ALGORITHM=INPLACE while allowing concurrent DML. Because MySQL stores tables as Index‑Organized Tables (IOT), indexes must also be rebuilt. The rebuild uses SORTED INDEX BUILDS , which bulk‑loads index records in a bottom‑up fashion and does not record redo logs. This matches the error message above.

Percona identifies this as a bug in Xtrabackup 2.4.x: when an optimized DDL without redo logging is detected, the backup is aborted. Starting with Xtrabackup 2.4.8, new parameters ( --lock-ddl , --lock-ddl-timeout , --lock-ddl-per-table ) allow the backup process to acquire locks that block conflicting DDL.

Scenario Tests

Environment Preparation

Create Test Tables

/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

DDL Script (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

Scenario 1 – Backup with Concurrent DDL (No Extra Parameters)

Running the DDL script continuously and then starting Xtrabackup reproduces the customer’s failure:

./xtrabackup ...
InnoDB: An optimized (without redo logging) DDL operation has been performed. ...
PXB will not be able to make a consistent backup. Retry the backup operation

Conclusion: Without DDL‑locking parameters, concurrent DDL causes backup failure.

Scenario 2 – Backup with --lock-ddl

Adding --lock-ddl to the Xtrabackup command on a MySQL Community server yields:

Error: LOCK TABLES FOR BACKUP is not supported.

Conclusion: The lock‑DDL option is only supported by Percona Server, not by vanilla MySQL.

Scenario 3 – Backup with --lock-ddl-per-table

Using --lock-ddl-per-table acquires an MDL lock on each table before copying its .ibd file, allowing the backup to complete successfully while DDL is blocked until the lock is released.

Conclusion: Per‑table MDL locking resolves the conflict.

Scenario 4 – Disable ONLINE DDL (set old_alter_table=1 )

Setting the global variable old_alter_table=1 forces DDL to use ALGORITHM=COPY , which always writes redo logs. After this change, Xtrabackup completes without any lock parameters.

Warning: Disabling ONLINE DDL reduces DML concurrency and is not generally recommended.

Summary

Avoid DDL changes during backup windows.

Sorted Index Builds in MySQL 5.7 conflict with Xtrabackup 2.4.x; upgrade Xtrabackup or use DDL‑locking parameters.

MySQL can bypass the issue by disabling ONLINE DDL, but this impacts write performance.

References

Sorted Index Builds

Online DDL Operations

old_alter_table Variable