pt-online-schema-change
不锁表地变更 MySQL 表结构:建影子表、触发器同步增量、分块拷贝数据,最后原子改名替换。
语法
pt-online-schema-change [OPTIONS] DSN在 sakila.actor 上加一列:
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor把 sakila.actor 改成 InnoDB(因其本就是 InnoDB,相当于无阻塞地做 OPTIMIZE TABLE):
pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor破坏性操作
本工具会改名、删除原表,且通过触发器在主表上写入。使用前务必阅读文档并核对备份。默认不修改表,必须显式加 --execute 才会真正执行。
安全提示
不要在命令行用 --password 提供 MySQL 密码:命令行密码对系统上所有用户可见,且会存入 ps 采集输出。请使用 MySQL 选项文件或 --ask-pass。
用法示例
以下命令假定已通过选项文件或本机 socket 配置好 MySQL 连接。改表是破坏性操作,务必先 --dry-run --print 预览,确认无误再加 --execute 真正执行,并在非生产环境先验证。
场景:先 dry-run 预览改动
改表前先空跑一遍,看清会执行哪些 SQL、会不会触发安全检查:
pt-online-schema-change --alter "ADD COLUMN status TINYINT NOT NULL DEFAULT 0" --dry-run --print D=appdb,t=orders场景:大表在线加索引
生产大表加索引不锁表,业务无感知:
pt-online-schema-change --alter "ADD INDEX idx_create_time (create_time)" --execute D=appdb,t=orders场景:大表加字段
加一个可空字段(字段名/类型按需替换):
pt-online-schema-change --alter "ADD COLUMN remark VARCHAR(255) DEFAULT NULL" --execute D=appdb,t=orders场景:修改字段类型或长度
把字段从 VARCHAR(64) 扩到 VARCHAR(128):
pt-online-schema-change --alter "MODIFY COLUMN name VARCHAR(128)" --execute D=appdb,t=orders场景:表引擎 MyISAM 转 InnoDB
历史 MyISAM 表转 InnoDB,顺带完成碎片整理:
pt-online-schema-change --alter "ENGINE=InnoDB" --execute D=appdb,t=orders场景:在线删除索引
删除不再使用的索引:
pt-online-schema-change --alter "DROP INDEX idx_old" --execute D=appdb,t=orders场景:限流并控制从库延迟
大表变更的标准姿势:限制从库延迟、单块耗时与服务器负载,避免拖垮主从:
pt-online-schema-change --alter "ADD COLUMN remark VARCHAR(255) DEFAULT NULL" \
--max-lag 2s --chunk-time 0.5 --max-load Threads_running=30 \
--execute D=appdb,t=orders功能说明
工具模拟 MySQL 内部改表的方式,但作用在你要改的表的副本上:原表不被锁定,客户端可继续读写。
工作原理
- 创建一张空的影子表(新表),按
--alter改好结构。 - 把原表数据分小块(chunk)拷进新表;每块大小默认由
--chunk-time动态调整,让每块执行时间大致相同。 - 在原表上建触发器,原表在拷贝期间的增删改实时同步到新表(因此原表若已有触发器则默认无法工作,除非用
--preserve-triggers)。 - 拷贝完成后用原子的
RENAME TABLE同时改名新旧表,随后默认删除原表。
主键 / 唯一索引要求
绝大多数情况下表上必须有 PRIMARY KEY 或 UNIQUE INDEX,工具要靠它建 DELETE 触发器来同步新表。唯一例外是 --alter 本身就在用现有列新建主键/唯一索引。
安全保护
工具在不动表的前提下做一系列保护(除非你显式 --execute):
- 没有 PRIMARY KEY / UNIQUE INDEX 会拒绝运行。
- 检测到复制过滤(
binlog_ignore_db、replicate_do_db等)会拒绝运行。 - 发现从库延迟超过
--max-lag会暂停数据拷贝。 - 发现服务器负载过高,按
--max-load暂停、按--critical-load中止。 - 设置
innodb_lock_wait_timeout=1(及 MySQL 5.5+ 的lock_wait_timeout=60),让自己更易成为锁等待的受害者、更少干扰其他事务;可用--set-vars改。 - 有外键引用本表时拒绝改表,除非指定
--alter-foreign-keys-method。 - 无法在 Percona XtraDB Cluster 节点上改 MyISAM 表。
Percona XtraDB Cluster / Galera
pt-online-schema-change 支持 PXC 5.5.28-23.7 及以上,但有两条限制,且无法关闭:只能改 InnoDB 表;wsrep_OSU_method 必须设为 TOI(total order isolation)。若节点上的表是 MyISAM 或正被转成 MyISAM,或 wsrep_OSU_method 非 TOI,工具直接报错退出。MySQL 5.7+ 的 GENERATED 列会被忽略(其值由表达式生成)。
外键处理
外键引用被改的表时要特殊处理:原子改名后外键会"跟随"被改名的原表,必须改回指向新表。工具自动找出引用本表的"子表",支持四种方式(--alter-foreign-keys-method):
auto:自动选最优;能用rebuild_constraints就用它,否则用drop_swap。rebuild_constraints:用ALTER TABLE删除并重加指向新表的外键(优先方式)。子表不大、ALTER 时间小于--chunk-time时用。因 MySQL 限制,外键重加后名字会被加前导下划线,所需索引也可能被 MySQL 自动改名。drop_swap:关FOREIGN_KEY_CHECKS=0,先删原表再改名新表。比原子 RENAME 快且不阻塞,但风险更高:删原表到改名之间表短暂不存在,查询会报错;若改名失败已无法中止(原表已永久删除)。此方式强制--no-swap-tables与--no-drop-old-table。none:类似drop_swap但不"交换",外键会指向不存在的表,通常在SHOW ENGINE INNODB STATUS里看到外键违例。供 DBA 自行接管工具内置逻辑。
--alter 语法与限制
--alter 是不含 ALTER TABLE 关键字的变更内容,逗号分隔可写多条。以下限制若触碰会导致不可预期失败:
- 几乎都要有 PRIMARY KEY / UNIQUE INDEX(用于建 DELETE 触发器);用现有列新建主键/唯一索引时除外。
- 不能用 RENAME 子句重命名整张表。
- 不能通过"删列再用新名加回"来重命名列——工具不会把原列数据拷到新列。
- 新增无默认值且 NOT NULL 的列会失败(不会替你猜默认值)。
DROP FOREIGN KEY 约束名须用带前导下划线的名字。因 MySQL 限制,工具建新表时给外键约束名加了前导下划线。例如要删:
CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)须写:
pt-online-schema-change --alter "DROP FOREIGN KEY _fk_foo" D=db,t=tbl- MySQL 5.0 不使用
LOCK IN SHARE MODE:MyISAM 转 InnoDB 时,5.0 有约 5% 概率触发主从不同错误而破坏复制(类似 bugs.mysql.com/bug.php?id=45694,5.0 无修复)。无该锁时测试 100% 通过,故数据丢失/复制破坏风险可忽略;但用 5.0 做 MyISAM→InnoDB 务必核对新表。
复制 / 从库延迟控制
工具自动发现并连接从库,监控复制延迟:
--max-lag(默认 1s):每块拷贝后查所有从库的Seconds_Behind_Source,超限则睡--check-interval秒再查。会永远等到从库追平;从库停了也永远等其重启。--check-replica-lag:只监控指定的单个从库 DSN,覆盖"监控所有从库"的默认行为。--recursion-method(默认processlist,hosts):发现从库的方法,可选processlist/hosts/dsn=DSN/none。--recurse:发现从库时递归的层级数,默认无限。--skip-check-replica-lag:检查延迟时跳过的从库 DSN,可多次指定;须写完整 IP(如h=127.0.0.1,不能写h=127.1)。--channel:使用复制通道时指定通道名,用于SHOW REPLICA STATUS计算延迟。--max-flow-ctl:类似--max-lag但针对 PXC 集群,检查集群因 Flow Control 暂停的时间占比;0 表示一检测到 Flow Control 就暂停;默认不检查(仅 PXC 5.6+)。
用 dsn 方法只监控指定从库:建一张 dsns 表(结构 id, parent_id, dsn),插入从库 DSN 即可,运行中途改表内容会被很快读取:
CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);INSERT INTO dsns (dsn) VALUES ('h=10.10.1.16'), ('h=10.10.1.17');限流
--chunk-time(默认 0.5):动态调整每块大小,使每次拷贝查询约耗时这么久;用指数衰减均值跟踪速率,负载变化时自适应。设为 0 或显式--chunk-size可关闭动态调整。--chunk-size(默认 1000,后缀 k/M/G):每块行数;未显式设置只作起点,之后由--chunk-time调整。--chunk-size-limit(默认 4.0):chunk 不得超过期望大小的此倍数;无唯一索引时估算可能不准,用EXPLAIN估算超--chunk-size × limit则停止拷贝。最小 1,0 关闭超限检查。--sleep(默认 0):每块后睡眠秒数;--max-lag/--max-load无法限流时用亚秒值(如 0.1)。--max-load(默认Threads_running=25):每块后查SHOW GLOBAL STATUS,状态变量超阈值则暂停;不给阈值按当前值加 20%。例如Threads_connected:110或Threads_connected=110指定显式阈值。
--dry-run 与 --execute
--dry-run 与 --execute 互斥。默认两者都不给时,工具只做安全检查后退出。--dry-run 会建并改新表,但不建触发器、不拷数据、不替换原表,配合 --print 可看清将执行的语句。--execute 表示你已读文档并要真正改表。
输出
工具把活动信息打到 STDOUT,数据拷贝阶段把 --progress 进度报告打到 STDERR;--print 可看更多 SQL。指定 --statistics 时末尾打印内部事件计数,例如:
# Event Count
# ====== =====
# INSERT 1选项
| 选项 | 说明 |
|---|---|
--alter | 类型:string。表结构变更,不含 ALTER TABLE 关键字;逗号分隔可写多条。限制:几乎都要有主键/唯一索引(建 DELETE 触发器用);不能 RENAME 整表;不能"删列再加同名"重命名列;新增无默认值且 NOT NULL 的列会失败;DROP FOREIGN KEY 须用带前导下划线的约束名;MySQL 5.0 不用 LOCK IN SHARE MODE(MyISAM→InnoDB 约 5% 概率破坏复制) |
--alter-foreign-keys-method | 类型:string。外键如何改指向新表:auto(自动选)、rebuild_constraints(ALTER 重加外键,子表不大时优先)、drop_swap(关外键检查后先删原表再改名,更快不阻塞但风险高,强制 --no-swap-tables --no-drop-old-table)、none(不处理,外键将指向不存在的表) |
--[no]analyze-before-swap | 默认:yes。交换前对新表执行 ANALYZE TABLE;默认仅 MySQL 5.6+ 且 innodb_stats_persistent 开启时执行。避免繁忙大表改名后无优化器统计而全表扫描、引发中断 |
--ask-pass | 连接 MySQL 时交互式询问密码 |
--binary-index | 修改 --history 行为,使历史表上下边界列用 BLOB 类型;适用于含二进制类型键或非标准字符集的大表。见 --history 与 --resume |
--[no]buffer-stdout | 默认:yes。开启 STDOUT 缓冲;用 tee、kubectl logs 等后处理工具想看实时进度时关闭 |
--channel | 类型:string。使用复制通道连接服务器时指定通道名,用于 SHOW REPLICA STATUS 计算复制延迟 |
-A, --charset | 类型:string。默认字符集(utf8 时设置 binmode/mysql_enable_utf8 并执行 SET NAMES UTF8) |
--[no]check-alter | 默认:yes。解析 --alter 警告非预期行为:列重命名(旧版本会丢数据,现已识别,仍建议先 --dry-run --print 验证);DROP PRIMARY KEY(大小写空格不敏感,会警告并退出除非 --dry-run,因影响触发器须先验证) |
--[no]check-foreign-keys | 默认:yes。检查自引用外键;自引用外键暂不完全支持,默认有则拒绝运行;可用此选项关闭检查 |
--check-interval | 类型:time;默认:1。检查 --max-lag 的睡眠秒数 |
--[no]check-plan | 默认:yes。检查查询执行计划是否安全:跑 EXPLAIN 确认小数据量查询不会走全表,用 key_len 启发式判断,不安全则停止拷贝。会增加往返开销,故勿把 chunk 设太小 |
--[no]check-replication-filters | 默认:yes。若任何服务器设了复制过滤(binlog_ignore_db、replicate_do_db 等)则中止;避免改了源有而从库没有的库表导致复制失败 |
--check-replica-lag | 类型:string。DSN,暂停拷贝直到该从库延迟小于 --max-lag;覆盖"监控所有从库"的默认行为 |
--check-slave-lag | 类型:string。已废弃,未来移除,改用 --check-replica-lag |
--[no]check-unique-key-change | 默认:yes。若 --alter 试图新增唯一索引则拒绝运行(工具用 INSERT IGNORE 拷贝,重复键会静默丢数据)。会给出检测重复行的示例查询;即便当前无重复,运行后新写入仍可能重复而丢数据 |
--chunk-index | 类型:string。分块优先使用的索引;不存在则回退默认。以 FORCE INDEX 加入 SQL;选错会严重影响性能 |
--chunk-index-columns | 类型:int。只用 --chunk-index 最左 N 列(仅复合索引);规避优化器在多列索引上扫描大范围行的 bug(如 4 列以上) |
--chunk-size | 类型:size;默认:1000。每块拷贝的行数;后缀 k/M/G。未显式设置只作起点,之后由 --chunk-time 调整;显式设置且未设 --chunk-time 则禁用动态调整 |
--chunk-size-limit | 类型:float;默认:4.0。chunk 不得超过期望大小的此倍数;无唯一索引估算可能不准,用 EXPLAIN 估算超 --chunk-size × limit 则停止拷贝。最小 1(chunk 不能大于 chunk-size),0 关闭超限检查。也用于决定外键处理方式 |
--chunk-time | 类型:float;默认:0.5。动态调整 chunk 大小使每次拷贝查询约耗时这么久;用指数衰减均值跟踪速率,负载变化时自适应。设为 0 或显式 --chunk-size 可关闭动态调整 |
--config | 类型:Array。读取逗号分隔的配置文件列表;若指定必须放在命令行第一个选项 |
--critical-load | 类型:Array;默认:Threads_running=50。每块后查 SHOW GLOBAL STATUS,负载过高则中止(与 --max-load 类似但中止而非暂停;未给阈值按启动值翻倍)。防触发器把服务器压垮导致中断 |
-D, --database | 类型:string。连接该数据库 |
--data-dir | 类型:string。用 DATA DIRECTORY 在新分区创建新表;仅 5.6+。与 --remove-data-dir 同时使用时被忽略 |
--default-engine | 新表去掉 ENGINE,使用系统默认引擎;避免主从引擎不同导致的非预期复制变更 |
-F, --defaults-file | 类型:string。只从该文件读 mysql 选项,必须绝对路径 |
--[no]drop-new-table | 默认:yes。拷贝原表失败时删除新表。--no-drop-new-table 与 --no-swap-tables 会保留改动后的新表;与 alter-foreign-keys-method=drop_swap 不兼容 |
--[no]drop-old-table | 默认:yes。改名后删除原表(无错时删,有错则保留)。--no-swap-tables 时无旧表可删 |
--[no]drop-triggers | 默认:yes。删除旧表上的触发器;--no-drop-triggers 会强制 --no-drop-old-table |
--dry-run | 创建并修改新表,但不建触发器、不拷数据、不替换原表 |
--execute | 确认已读文档并要真正改表;不指定则只做安全检查后退出 |
--[no]fail-on-stopped-replication | 默认:yes。复制停止时报错退出(状态码 128)而非等待复制重启 |
--force | 绕过确认:当 alter-foreign-keys-method=none(可能破坏外键)时;允许 --where 配合 --no-drop-new-table --no-swap-tables 使用;绕过"禁止在 ROW/MIXED binlog 的从库上运行"的安全检查 |
--help | 显示帮助并退出 |
--history | 把任务进度写入表,未完成任务可由 --resume 重启;表结构含 job_id, db, tbl, new_table_name, altr, args, lower_boundary, upper_boundary, done, ts 等列 |
--history-table | 类型:string;默认:percona.pt_osc_history。--history 使用的库表名,不存在时自动创建 |
-h, --host | 类型:string。连接的主机 |
--max-flow-ctl | 类型:float。类似 --max-lag 但针对 PXC 集群,检查集群因 Flow Control 暂停的时间占比,超阈值则暂停;0 表示一检测到就暂停;默认不检查;仅 PXC 5.6+ |
--max-lag | 类型:time;默认:1s。暂停拷贝直到所有从库延迟小于此值;每块后查 Seconds_Behind_Source,超限睡 --check-interval 再查。会永远等到从库追平,从库停了也永远等其启动 |
--max-load | 类型:Array;默认:Threads_running=25。每块后查 SHOW GLOBAL STATUS,状态变量超阈值则暂停;可逗号分隔列表,可选 =值 或 :值;不给阈值按当前值加 20%。防工具给服务器加压 |
-s, --mysql_ssl | 类型:int。建立 SSL MySQL 连接 |
--preserve-triggers | 保留原表已有触发器:MySQL 5.7.2+ 支持同名同事件多触发器时,先把原触发器拷到新表再拷数据,拷完重放。不能用于 DROP 所引用列;与 --no-drop-triggers/--no-drop-old-table/--no-swap-tables 不兼容;--no-swap-tables 会使触发器留在原表 |
--new-table-name | 类型:string;默认:%T_new。交换前的新表名;%T 替换为原表名;默认加最多 10 个下划线找唯一名;指定名字则不前加下划线,故该表不能已存在 |
--null-to-not-null | 允许把可 NULL 列改为 NOT NULL;含 NULL 的现有行转按类型的默认值(数字 0、字符串 ''),新行用列定义的默认值 |
--only-same-schema-fks | 只检查同库(schema)内的外键;危险——其他库的外键引用不会被发现 |
-p, --password | 类型:string。连接密码(含逗号需转义) |
--pause-file | 类型:string。该文件存在期间暂停执行 |
--pid | 类型:string。创建给定 PID 文件;冲突规则与自动清理见官方文档 |
--plugin | 类型:string。定义 pt_online_schema_change_plugin 类的 Perl 模块文件,可挂接工具多个阶段(详见 PLUGIN) |
-P, --port | 类型:int。连接端口 |
--print | 把执行的 SQL 打印到 STDOUT;可与 --dry-run 同用 |
--progress | 类型:array;默认:time,30。拷贝时向 STDERR 打印进度;两部分:percentage/time/iterations 与打印频率 |
-q, --quiet | 不向 STDOUT 打印消息(禁用 --progress);错误与警告仍到 STDERR |
--recurse | 类型:int。发现从库时递归的层级数;默认无限。见 --recursion-method |
--recursion-method | 类型:array;默认:processlist,hosts。发现从库的方法:processlist(SHOW PROCESSLIST)、hosts(SHOW REPLICAS)、dsn=DSN(从表读)、none(不发现)。hosts 在非标准端口更好;dsn 表结构见上文 |
--remove-data-dir | 默认:no。若原表用了 DATA DIRECTORY,去掉并在 MySQL 目录建新表、不生成新 isl 文件 |
--remove-tablespace | 默认:no。若原表用了 TABLESPACE,去掉并以无 TABLESPACE 建新表 |
--replica-password | 类型:string。连接从库用的密码;与 --replica-user 配合,所有从库须一致 |
--replica-user | 类型:string。连接从库用的用户,可权限较低;该用户须存在于所有从库 |
--resume | 类型:int。从上次完成的 chunk 续跑;接受失败任务 ID(由 --history 打印并存于 --history-table)。续跑前失败运行须用 --history --no-drop-new-table --no-drop-triggers |
--reverse-triggers | 把拷贝期间建的触发器反向拷到新表,使新表改动反映回旧表;需 --no-drop-old-table。警告:改名后可能收到 Prepared statement needs to be re-prepared 错误,需重编译语句 |
--skip-check-replica-lag | 类型:DSN;可重复。检查延迟时跳过的从库 DSN,可多次;须写完整 IP(如 h=127.0.0.1 而非 h=127.1) |
--skip-check-slave-lag | 类型:DSN;可重复。已废弃,改用 --skip-check-replica-lag |
--slave-password | 类型:string。已废弃,改用 --replica-password |
--slave-user | 类型:string。已废弃,改用 --replica-user |
--set-vars | 类型:Array。逗号分隔的 变量=值 设置 MySQL 变量;默认 wait_timeout=10000、innodb_lock_wait_timeout=1、lock_wait_timeout=60;命令行覆盖默认;设不上打印警告继续。设 sql_mode 需转义引号与逗号 |
--sleep | 类型:float;默认:0。每拷完一块后睡眠秒数;--max-lag/--max-load 无法限流时用亚秒值如 0.1,否则大表很慢 |
-S, --socket | 类型:string。连接 socket 文件 |
--statistics | 打印内部计数器统计(如被抑制的警告 vs INSERT 数);失败与重试也记录于此 |
--[no]swap-tables | 默认:yes。交换新旧表完成改名;原表变旧表,除非禁用 --[no]drop-old-table 否则删除。--no-swap-tables 会跑完整流程但最后删新表,相当于更真实的 --dry-run |
--tries | 类型:array。关键操作重试次数与间隔:create_triggers/drop_triggers/swap_tables/update_foreign_keys/analyze_table 默认 10 次 1 秒,copy_rows 默认 10 次 0.25 秒;格式 operation:tries:wait。重试锁等待超时/死锁/连接被杀/断连等错误 |
-u, --user | 类型:string。登录用户(若非当前用户) |
--version | 显示版本并退出 |
--[no]version-check | 默认:yes。检查 Percona Toolkit、MySQL 等最新版本与已知问题版本(详见版本检查) |
--where | 类型:string。只拷贝匹配该 WHERE 子句的行(不含 WHERE 关键字);类似 mysqldump 的 -w。用于部分表或失败后续跑。注意:不配 --no-drop-new-table --no-swap-tables 可能丢数据,必须同时 --force |
示例(--where 部分拷贝):
pt-online-schema-change --where "id > 12345678" D=db,t=tbl --alter "..."示例(--tries 自定义重试):
pt-online-schema-change --tries create_triggers:5:0.5,drop_triggers:5:0.5 D=db,t=tbl --alter "..."示例(--set-vars 设 sql_mode):
pt-online-schema-change --set-vars sql_mode=\'STRICT_ALL_TABLES\\,ALLOW_INVALID_DATES\' D=db,t=tbl --alter "..."插件(PLUGIN)
--plugin 指定的 Perl 模块须定义 pt_online_schema_change_plugin 类(含 new()),工具实例化后会调用其定义的钩子(非必须)。按调用顺序的钩子:init、before_create_new_table、after_create_new_table、before_alter_new_table、after_alter_new_table、before_create_triggers、after_create_triggers、before_copy_rows、on_copy_rows_after_nibble、after_copy_rows、before_swap_tables、after_swap_tables、before_update_foreign_keys、after_update_foreign_keys、before_drop_old_table、after_drop_old_table、before_drop_triggers、before_die、before_exit、get_replica_lag(及已废弃的 get_slave_lag)。如需查看某钩子入参,请在工具源码中搜索 # --plugin hook。
DSN 选项
| 键 | DSN 部分 | 说明 |
|---|---|---|
A | charset | 默认字符集 |
D | database | 新旧表所在数据库 |
F | mysql_read_default_file | 只从给定文件读取默认选项 |
h | host | 要连接的主机 |
p | password | 连接密码(含逗号需转义) |
P | port | 连接端口 |
S | mysql_socket | 连接使用的 socket 文件 |
t | table | 要改的表 |
u | user | 登录用户(若非当前用户) |
s | mysql_ssl | 建立 SSL 连接 |
其他信息
系统要求:需要 Perl、DBI、DBD::mysql 及一些核心 Perl 包;仅支持 MySQL 5.0.2 及以上(更早版本无触发器)。所需权限:全局
PROCESS、SUPER、REPLICATION SLAVE,以及表级SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER、TRIGGER;从库仅需REPLICATION SLAVE与REPLICATION CLIENT。作者:Daniel Nichter 和 Baron Schwartz
通用说明:已知问题的反馈方式、PTDEBUG 调试安全提示、系统要求基线,见 通用说明。
更多细节请阅读 官方文档。