笙亿网络策划

MySQL 主从架构下如何安全执行 DDL 操作避免延迟

约 6 分钟读完 4 阅
文章导读
在 MySQL 主从架构下，安全执行 DDL 避免延迟的最推荐方案是使用在线 schema 变更工具（如 gh-ost 或 pt-online-schema-change），避免直接在主库执行阻塞性 ALTER TABLE。适用场景为大表结构变更，风险边界在于从库回放速度跟不上主库 binlog 生成速度。
📋 目录
  1. 壹 命令速用版
  2. 贰 为什么会这样
  3. 叁 分步处理
  4. 肆 怎么验证是否生效
  5. 伍 常见坑
  6. 陆 常见问题
  7. 柒 参考来源
A A

在 MySQL 主从架构下，安全执行 DDL 避免延迟的最推荐方案是使用在线 schema 变更工具（如 gh-ost 或 pt-online-schema-change），避免直接在主库执行阻塞性 ALTER TABLE。适用场景为大表结构变更，风险边界在于从库回放速度跟不上主库 binlog 生成速度。

先说结论：大表 DDL 必须使用在线变更工具，严禁业务高峰期直接执行。

  • 适合：百万行以上大表或高写入负载场景
  • 先看：从库复制延迟阈值和剩余磁盘空间
  • 建议：优先选用 gh-ost 或 pt-online-schema-change

命令速用版

以下命令需在具备主库写入权限的客户端执行，执行前请确保已备份。

\# gh-ost 示例，`--max-lag-millis` 控制允许的最大复制延迟
gh-ost `--user`=root `--password`=yourpassword `--host`=127.0.0.1 `--database`=test_db `--table`=test_table `--alter`="ADD COLUMN new_col INT" `--max-lag-millis`=3000 `--execute`

\# pt-online-schema-change 示例，`--recursion-method` 指定检测从库延迟方式
pt-online-schema-change `--user`=root `--password`=yourpassword `--host`=127.0.0.1 `--alter`="ADD COLUMN new_col INT" D=test_db,t=test_table `--recursion-method`=processlist `--max-lag`=5s `--execute`

为什么会这样

直接执行 DDL 会获取元数据锁（MDL），导致从库回放线程阻塞，进而引发复制延迟。

在 MySQL 5.6 之前，DDL 操作会锁表，主库写入暂停，从库回放该语句时也会阻塞后续事务。即使 MySQL 5.6+ 引入了 Online DDL，对于大表而言，元数据锁等待和从库单线程回放（5.7 之前）仍是瓶颈。在线变更工具通过创建新表、触发器同步数据、最后重命名表的方式，将锁表时间缩短到秒级，减少从库积压。

分步处理

步骤 1：评估环境与备份

检查主从版本是否一致，确认从库磁盘空间足够容纳新表（至少需要原表大小的额外空间）。执行全量备份或确认最近备份可用。

步骤 2：选择工具与参数

若表上有触发器，pt-online-schema-change 可能冲突，建议选用 gh-ost。设置 `--max-lag` 或 `--max-lag-millis` 参数，当从库延迟超过阈值时工具会自动暂停数据拷贝，防止延迟扩大。

步骤 3：灰度执行

MySQL 主从架构下如何安全执行 DDL 操作避免延迟

先在测试环境或负载较低的从库执行变更验证。生产环境执行时，避开业务高峰期，并在低峰期启动任务。

步骤 4：切换与清理

工具完成数据拷贝后，会在低负载时刻自动重命名表（Cut-over）。完成后检查主从状态，确认无异常后删除旧表（工具通常会自动清理，但需确认）。

怎么验证是否生效

执行变更过程中和完成后，需在从库执行以下命令监控延迟：

SHOW SLAVE STATUS\G

重点关注 Seconds_Behind_Master 字段。若该值在执行期间始终维持在阈值范围内（如 5 秒以内），且完成后归零，说明操作未造成严重延迟。同时检查主库 SHOW PROCESSLIST 确认无长时间锁等待。

常见坑

磁盘空间不足：在线变更工具需要创建影子表，若磁盘空间不足会导致任务失败甚至主库只读。

外键约束冲突：若表存在外键关系，gh-ost 和 pt-osc 处理方式不同，部分场景不支持直接变更，需先移除外键。

触发器干扰：pt-online-schema-change 依赖触发器同步数据，若原表已有触发器，需手动处理或换用 gh-ost。

主键缺失：若表没有主键，在线变更工具可能无法高效行级拷贝，导致性能下降。

MySQL 主从架构下如何安全执行 DDL 操作避免延迟

常见问题

MySQL 5.6+ 的 Online DDL 可以直接用吗？

小表可以直接用，大表建议用工具。

MySQL 5.6+ 的 Online DDL 虽然不锁数据读写，但仍需获取元数据锁，且从库回放时仍可能阻塞。对于百万行以上大表，在线变更工具更安全。

执行过程中从库延迟飙升怎么办？

工具会自动暂停，无需人工干预。

配置了 `--max-lag` 参数后，当检测到从库延迟超过设定值，工具会停止数据拷贝直到延迟恢复。若持续飙升，需检查从库性能或网络。

变更失败会影响原表数据吗？

不会影响，原表保持不变。

在线变更工具是在新表上操作，最后一步才重命名。若中途失败，只需删除新表和清理触发器，原表数据不受影响。

参考来源

1. MySQL Official Documentation, "ALTER TABLE Statement", https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

2. Percona Toolkit Documentation, "pt-online-schema-change", https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

3. GitHub, "github/gh-ost", https://github.com/github/gh-ost