这确实是--single-transaction备份时的典型痛点——它能实现InnoDB的无锁热备份，但一旦备份期间有ALTER/CREATE/DROP这类DDL操作，就会直接打断一致性快照，导致备份失败。下面是几个实战中好用的解决方案，按优先级排序：

1. 使用MySQL 8.0.26+新增的--lock-for-backup选项（最优解） #

MySQL在8.0.26版本专门针对这个场景推出了--lock-for-backup参数，完美平衡了备份一致性和业务可用性：

• 它会在备份初始化阶段短暂获取全局元数据锁（MDL），仅用于建立一致性快照和记录必要的备份元数据，快照建立完成后立即释放锁；
• 后续备份过程中，普通DML操作完全不受影响，而DDL操作会被自动阻塞，直到备份完成后才允许执行；
• 既避免了DDL破坏备份，又不会长时间锁表影响业务。

使用命令示例：

mysqldump --single-transaction --lock-for-backup -u your_user -p your_database > backup_$(date +%Y%m%d).sql

注意：这个选项仅适用于MySQL 8.0.26及以上版本，如果你还在使用低版本MySQL，建议优先考虑升级，或者看下边的替代方案。

2. 备份窗口临时限制DDL操作（适合低峰期可协调场景） #

如果你的业务有固定的低峰维护窗口，可以临时限制应用或运维账号的DDL权限，直到备份完成：

• 备份前，回收相关账号的DDL权限：

  REVOKE ALTER, CREATE, DROP, RENAME ON *.* FROM 'app_user'@'%';
  

• 备份完成后，立即恢复权限：

  GRANT ALTER, CREATE, DROP, RENAME ON *.* TO 'app_user'@'%';
  

这个方案的优点是简单直接，缺点是需要协调业务方暂停DDL操作，适合运维权限可控、业务有低峰期的场景。

3. 给备份脚本添加重试逻辑（兜底方案） #

如果无法避免备份期间的DDL，且无法升级MySQL版本，可以给备份脚本加上重试机制，当备份因DDL失败时自动重试：

示例Shell脚本：

#!/bin/bash
# 备份配置
DB_USER="root"
DB_PASS="your_password"
DB_NAME="your_database"
BACKUP_DIR="/path/to/backup"
MAX_RETRIES=3
RETRY_INTERVAL=5

# 创建备份目录（如果不存在）
mkdir -p $BACKUP_DIR

# 循环执行备份
for ((i=1; i<=MAX_RETRIES; i++)); do
    BACKUP_FILE="${BACKUP_DIR}/backup_$(date +%Y%m%d_%H%M%S).sql"
    echo "开始第 $i 次备份，目标文件：$BACKUP_FILE"
    
    # 执行备份
    mysqldump --single-transaction -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_FILE
    
    # 检查备份是否成功
    if [ $? -eq 0 ]; then
        echo "备份成功！文件已保存至 $BACKUP_FILE"
        exit 0
    else
        echo "第 $i 次备份失败，$RETRY_INTERVAL 秒后重试..."
        sleep $RETRY_INTERVAL
    fi
done

echo "已尝试 $MAX_RETRIES 次备份，全部失败，请检查DDL操作或手动执行备份"
exit 1

这个方案的核心是利用重试来避开DDL操作的时间窗口，适合业务无法暂停DDL、且MySQL版本较低的场景，但无法保证每次都成功，只能作为兜底。

补充：为什么DDL会导致备份失败？ #

--single-transaction的原理是开启一个只读事务，获取InnoDB的一致性快照，备份过程中基于这个快照读取数据。而DDL操作会对目标表施加排他的MDL锁，并且会隐式提交所有未完成的事务（包括mysqldump的备份事务），直接导致备份进程因快照失效或表结构变更而报错终止。

内容的提问来源于stack exchange，提问作者salman