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> 替换为实际库表名。
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;预期结果不是固定数字,而是帮助你判断是否具备安全执行条件。下面示例表示存在唯一键、没有现有触发器,也没有其他表通过外键引用目标表。
has_unique_key trigger_count incoming_fk_columns
1 0 0若已有业务触发器,不要直接忽略。工具自身也需要触发器维护影子表,应结合当前 Percona Toolkit 版本、已有触发器语义和工具对触发器的限制确认是否可执行。若存在入向外键,则必须先明确 --alter-foreign-keys-method 策略。
先做 dry-run,再看工具准备执行什么
下面演示给 <TABLE> 新增 archived_at。阈值只是示例,不是通用生产默认值;应根据实例的 Threads_running、复制延迟和业务峰值重新定标。
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 会随表结构和工具版本变化。
<SAFETY_CHECK_OUTPUT>
<CREATE_AND_ALTER_SHADOW_TABLE_SQL>
节流参数决定迁移是不是在线
--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。下面仍使用同一组示例阈值。
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预期结果是工具完成创建新表、建立同步机制、分块复制和最终切换;时间戳、行数估算及临时对象名称属于动态值,不应作为固定断言。
Creating new table...
Creating triggers...
Copying approximately <ROWS> rows...
Copied rows OK.
Swapping tables...
Successfully altered `<DB>`.`<TABLE>`.执行后立即验证目标列和本次变更留下的同步触发器是否已清理。将 <DB>、<TABLE> 替换为实际名称。
USE `<DB>`;
SHOW COLUMNS FROM `<TABLE>` LIKE 'archived_at';
SHOW TRIGGERS LIKE '<TABLE>';预期结果是目标列存在;成功完成且未使用特殊保留选项时,本次在线变更使用的同步触发器不应继续残留。
Field Type Null Key Default Extra
archived_at datetime YES NULL
<NO_PT_OSC_TRIGGER_ROWS>若任务失败,不要看到 _new、_old 或 pt_osc_... 就立即删除。先确认失败发生在复制前、复制中还是交换后,再核对哪个表仍承载业务写入、外键当前指向谁以及触发器是否仍在同步。旧表即使还存在,也不代表可以瞬时无损回滚;切换后它如果停止接收新写入,就可能已经落后于新表,回切前仍需重新做数据追平与一致性验证。