1. 问题背景

生产环境中，MySQL 数据库每天凌晨 2 点执行定时 DDL 操作（如新增索引、修改表结构），随后触发 mysqldump 逻辑备份任务，但备份经常失败。本文通过模拟该场景，分析失败根因，并给出可落地的优化建议。

2. 场景模拟

为了复现问题，我们搭建一个最小化测试环境：

• MySQL 8.0 实例，单机部署。
• 业务表orders，数据量约 500 万行。
• 定时任务 1：每天 02:00 执行TRUNCATE TABLE orders_db.orders。
• 定时任务 2：每天 02:10 执行mysqldump --single-transaction --set-gtid-purged=OFF -u backup -p*** orders_db > /backup/orders_db.sql。

模拟脚本（Shell 片段）：

#!/bin/bash # 02:00 执行截断表 0 2 * * * mysql -uroot -p*** -e "TRUNCATE TABLE orders_db.orders;" 02:10 执行备份 10 2 * * * mysqldump --single-transaction --set-gtid-purged=OFF -u backup -p*** orders_db > /backup/orders_db.sql

3. 失败现象与日志

备份任务执行后，日志中出现以下典型错误：

mysqldump: Error 1184: Aborted connection ... to db: 'orders_db' user: 'backup' host: 'localhost' (Got an error reading communication packets) mysqldump: Couldn't execute 'SHOW CREATE TABLE `orders`': Lost connection to MySQL server during query (1213)

同时，MySQL 错误日志（error.log）中可见：

[ERROR] [MY-010584] Event Scheduler: ... Aborted connection ... (Got timeout reading communication packets)

4. 根因分析

结合现象，失败根因主要有以下几类：

4.1 DDL 与备份并发导致元数据锁（MDL）阻塞

mysqldump 使用--single-transaction时，会先执行FLUSH TABLES WITH READ LOCK获取一致性快照，随后获取各表的 MDL 读锁。若此时 DDL 尚未完成，或 DDL 正在等待 MDL 写锁，备份线程会被阻塞，直至超过lock_wait_timeout（默认 31536000 秒，但受net_read_timeout/net_write_timeout影响）而中断。

4.2 大表 DDL 长时间占用资源

对 500 万行以上的表执行ALTER TABLE添加索引，即使使用ALGORITHM=INPLACE，仍会消耗大量 I/O 和 CPU。若 DDL 在 02:10 仍未结束，备份任务启动时资源竞争加剧，导致连接被 kill 或超时。

4.3 备份超时参数设置过短

mysqldump 客户端默认net_read_timeout=30s、net_write_timeout=60s。当服务器端因 DDL 负载导致响应变慢时，客户端容易触发超时断开。

4.4 备份与 DDL 的锁等待链

DDL 需要 MDL 写锁，而备份的FLUSH TABLES WITH READ LOCK需要全局读锁。若业务侧存在长事务持有相关表 MDL 读锁，DDL 会排队等待，进而拖住后续备份，形成锁等待链。

5. 排查步骤

5.1 查看 MySQL 进程列表与锁等待

SHOW FULL PROCESSLIST; -- 关注 State 列为 Waiting for table metadata lock 的会话

5.2 查询 MDL 锁等待详情

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA = 'orders_db' AND OBJECT_NAME = 'orders'\G

5.3 检查 InnoDB 事务与锁

SELECT * FROM information_schema.innodb_trx\G SELECT * FROM performance_schema.data_locks\G

5.4 确认备份超时参数

SHOW VARIABLES LIKE 'net_read_timeout'; SHOW VARIABLES LIKE 'net_write_timeout'; SHOW VARIABLES LIKE 'lock_wait_timeout';

5.5 查看 MySQL 错误日志

tail -n 200 /var/log/mysql/error.log | grep -i "aborted\|timeout\|metadata lock"

6. 优化建议

6.1 调整任务调度时间，避免 DDL 与备份重叠

将 DDL 提前至 01:00 执行，备份延后至 03:00，确保两者完全错开。这是最简单有效的方案。

6.2 使用在线 DDL 工具

对大表 DDL 使用pt-online-schema-change（Percona Toolkit）或gh-ost，通过拷贝表 + 增量同步方式完成结构变更，避免长时间持有 MDL 写锁。

6.3 优化 mysqldump 参数

mysqldump --single-transaction --quick --set-gtid-purged=OFF \ --net-read-timeout=300 --net-write-timeout=300 \ --lock-wait-timeout=60 \ -u backup -p*** orders_db > /backup/orders_db.sql

• --net-read-timeout/--net-write-timeout：调大客户端超时，避免因瞬时负载断开。
• --lock-wait-timeout：控制等待 MDL 锁的最长时间，超时后快速失败并告警，而不是无限阻塞。

6.4 改用物理备份工具

若条件允许，使用XtraBackup或 MySQL 8.0 的CLONE插件做物理备份，不依赖 MDL 读锁，对 DDL 并发更友好。

6.5 监控与告警

• 监控performance_schema.metadata_locks，发现长时间 MDL 等待立即告警。
• 为备份任务增加失败重试与通知机制，避免静默失败。
• 记录 DDL 执行时长，评估是否需要拆分或优化 DDL 语句。

7. 总结

MySQL 定时 DDL 与 mysqldump 备份冲突的根因，通常是 MDL 锁阻塞、大表 DDL 资源占用和客户端超时参数过短共同作用的结果。通过错峰调度、使用在线 DDL 工具、调优备份参数，以及引入物理备份方案，可以有效降低备份失败概率，保障数据安全。