MySQL / 系统课时

使用 pt-online-schema-change

使用 pt-online-schema-change

pt-online-schema-change 把影子表变更流程封装成可执行工具:创建新表、应用 DDL、建立同步触发器、分块复制、按负载节流,并在最后切换表名。本文用“给订单表新增可空列”串起前置检查、dry-run、节流、外键处理、正式执行和失败清理。

学习目标

  • 用 --dry-run、--print 和 --execute 分阶段推进在线结构变更
  • 理解 --chunk-time、--max-load、--critical-load 与 --max-lag 的作用边界
  • 判断主键、触发器、外键、资源容量和失败残留对变更的影响

执行前先证明这张表适合迁移

工具依赖可唯一定位行的键来分块复制,并通过触发器同步源表变化,因此生产表应先确认主键或唯一索引。同时检查业务触发器、入向外键、可用磁盘、复制拓扑以及备份恢复能力,避免运行到切换阶段才发现前置条件不满足。

下面的查询一次检查唯一键、现有触发器和入向外键。将 <DB> 与 <TABLE> 替换为实际库表名。

sql
SELECT
  EXISTS (
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = '<DB>'
      AND TABLE_NAME = '<TABLE>'
      AND NON_UNIQUE = 0
  ) AS has_unique_key,
  (
    SELECT COUNT(*)
    FROM information_schema.TRIGGERS
    WHERE TRIGGER_SCHEMA = '<DB>'
      AND EVENT_OBJECT_TABLE = '<TABLE>'
  ) AS trigger_count,
  (
    SELECT COUNT(*)
    FROM information_schema.KEY_COLUMN_USAGE
    WHERE REFERENCED_TABLE_SCHEMA = '<DB>'
      AND REFERENCED_TABLE_NAME = '<TABLE>'
  ) AS incoming_fk_columns;

预期结果不是固定数字,而是帮助你判断是否具备安全执行条件。下面示例表示存在唯一键、没有现有触发器,也没有其他表通过外键引用目标表。

text
has_unique_key  trigger_count  incoming_fk_columns
1               0              0

若已有业务触发器,不要直接忽略。工具自身也需要触发器维护影子表,应结合当前 Percona Toolkit 版本、已有触发器语义和工具对触发器的限制确认是否可执行。若存在入向外键,则必须先明确 --alter-foreign-keys-method 策略。

先做 dry-run,再看工具准备执行什么

下面演示给 <TABLE> 新增 archived_at。阈值只是示例,不是通用生产默认值;应根据实例的 Threads_running、复制延迟和业务峰值重新定标。

bash
pt-online-schema-change \
  --alter "ADD COLUMN archived_at DATETIME NULL" \
  D=<DB>,t=<TABLE>,h=<HOST>,P=<PORT>,u=<USER> \
  --ask-pass \
  --chunk-time=0.5 \
  --max-load Threads_running=50 \
  --critical-load Threads_running=100 \
  --max-lag=2 \
  --dry-run \
  --print

预期结果是工具完成安全检查,并执行 dry-run 阶段的测试建表、修改影子表等检查动作;不会进入创建同步触发器、分块复制数据和最终 swap 阶段。--print 会打印将执行的 SQL。不要把 exit_code=0 当成工具输出;若需要记录退出码,应由外层 Shell 显式读取 $?。临时表名及打印出的 SQL 会随表结构和工具版本变化。

text
<SAFETY_CHECK_OUTPUT>
<CREATE_AND_ALTER_SHADOW_TABLE_SQL>

pt-online-schema-change 从前置检查到最终切换的执行流程

节流参数决定迁移是不是在线

--chunk-time=0.5 会根据最近批次执行时间动态调整 chunk 大小,使单批复制尽量接近目标时长,因此它不是固定每批复制多少行。较短的 chunk 通常更容易让业务请求穿插执行,但也可能延长迁移总时长。

--max-load 用于负载超过阈值时暂停复制,--critical-load 用于达到危险阈值时终止,--max-lag 则在副本延迟超过限制时等待追平。三者分别对应业务负载、实例保护和复制链路保护。上线前应从监控基线反推阈值,而不是直接复制示例数字。

还要保留最终切换的 MDL 风险。即使所有 chunk 都很小,最后的 RENAME TABLE 仍可能等待表上的元数据锁,因此变更窗口内应同时关注长事务、performance_schema.metadata_locks 和应用错误率。

外键处理必须单独设计

当其他表通过外键引用目标表时,旧表和新表交换名称并不能自动消除约束引用问题。存在入向外键时,需要明确 --alter-foreign-keys-method 策略;前面的演示以 incoming_fk_columns=0 为前提,若结果非零,不应原样执行。

生产环境通常重点评估 rebuild_constraints:切换后通过 ALTER TABLE 重建子表约束,使其指向新表,但代价是子表也会执行 DDL。drop_swap 则可能减少部分重建成本,却会引入原表短暂不存在以及失败后回退困难的风险,因此不能当作普通加速开关。

真正需要估算的是“更新外键需要多久、会锁什么、能否接受”。外键定义本身只是元数据,但约束重建的成本可能落在子表数据上。

正式执行、验证和失败清理

确认 dry-run、磁盘空间、复制延迟和外键方案都通过后,再将 --dry-run 换成 --execute。下面仍使用同一组示例阈值。

bash
pt-online-schema-change \
  --alter "ADD COLUMN archived_at DATETIME NULL" \
  D=<DB>,t=<TABLE>,h=<HOST>,P=<PORT>,u=<USER> \
  --ask-pass \
  --chunk-time=0.5 \
  --max-load Threads_running=50 \
  --critical-load Threads_running=100 \
  --max-lag=2 \
  --execute

预期结果是工具完成创建新表、建立同步机制、分块复制和最终切换;时间戳、行数估算及临时对象名称属于动态值,不应作为固定断言。

text
Creating new table...
Creating triggers...
Copying approximately <ROWS> rows...
Copied rows OK.
Swapping tables...
Successfully altered `<DB>`.`<TABLE>`.

执行后立即验证目标列和本次变更留下的同步触发器是否已清理。将 <DB>、<TABLE> 替换为实际名称。

sql
USE `<DB>`;
SHOW COLUMNS FROM `<TABLE>` LIKE 'archived_at';
SHOW TRIGGERS LIKE '<TABLE>';

预期结果是目标列存在;成功完成且未使用特殊保留选项时,本次在线变更使用的同步触发器不应继续残留。

text
Field        Type      Null  Key  Default  Extra
archived_at  datetime  YES        NULL
<NO_PT_OSC_TRIGGER_ROWS>

若任务失败,不要看到 _new、_old 或 pt_osc_... 就立即删除。先确认失败发生在复制前、复制中还是交换后,再核对哪个表仍承载业务写入、外键当前指向谁以及触发器是否仍在同步。旧表即使还存在,也不代表可以瞬时无损回滚;切换后它如果停止接收新写入,就可能已经落后于新表,回切前仍需重新做数据追平与一致性验证。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

使用 pt-online-schema-change

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通