Skip to content

pt-online-schema-change

不锁表地变更 MySQL 表结构:建影子表、触发器同步增量、分块拷贝数据,最后原子改名替换。

语法

bash
pt-online-schema-change [OPTIONS] DSN

在 sakila.actor 上加一列:

bash
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

把 sakila.actor 改成 InnoDB(因其本就是 InnoDB,相当于无阻塞地做 OPTIMIZE TABLE):

bash
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、会不会触发安全检查:

bash
pt-online-schema-change --alter "ADD COLUMN status TINYINT NOT NULL DEFAULT 0" --dry-run --print D=appdb,t=orders

场景:大表在线加索引

生产大表加索引不锁表,业务无感知:

bash
pt-online-schema-change --alter "ADD INDEX idx_create_time (create_time)" --execute D=appdb,t=orders

场景:大表加字段

加一个可空字段(字段名/类型按需替换):

bash
pt-online-schema-change --alter "ADD COLUMN remark VARCHAR(255) DEFAULT NULL" --execute D=appdb,t=orders

场景:修改字段类型或长度

把字段从 VARCHAR(64) 扩到 VARCHAR(128)

bash
pt-online-schema-change --alter "MODIFY COLUMN name VARCHAR(128)" --execute D=appdb,t=orders

场景:表引擎 MyISAM 转 InnoDB

历史 MyISAM 表转 InnoDB,顺带完成碎片整理:

bash
pt-online-schema-change --alter "ENGINE=InnoDB" --execute D=appdb,t=orders

场景:在线删除索引

删除不再使用的索引:

bash
pt-online-schema-change --alter "DROP INDEX idx_old" --execute D=appdb,t=orders

场景:限流并控制从库延迟

大表变更的标准姿势:限制从库延迟、单块耗时与服务器负载,避免拖垮主从:

bash
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_dbreplicate_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 限制,工具建新表时给外键约束名加了前导下划线。例如要删:
bash
CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)

须写:

bash
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 即可,运行中途改表内容会被很快读取:

bash
CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
bash
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:110Threads_connected=110 指定显式阈值。

--dry-run 与 --execute

--dry-run--execute 互斥。默认两者都不给时,工具只做安全检查后退出。--dry-run 会建并改新表,但不建触发器、不拷数据、不替换原表,配合 --print 可看清将执行的语句。--execute 表示你已读文档并要真正改表。

输出

工具把活动信息打到 STDOUT,数据拷贝阶段把 --progress 进度报告打到 STDERR;--print 可看更多 SQL。指定 --statistics 时末尾打印内部事件计数,例如:

bash
# 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_dbreplicate_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=10000innodb_lock_wait_timeout=1lock_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 部分拷贝):

bash
pt-online-schema-change --where "id > 12345678" D=db,t=tbl --alter "..."

示例(--tries 自定义重试):

bash
pt-online-schema-change --tries create_triggers:5:0.5,drop_triggers:5:0.5 D=db,t=tbl --alter "..."

示例(--set-varssql_mode):

bash
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()),工具实例化后会调用其定义的钩子(非必须)。按调用顺序的钩子:initbefore_create_new_tableafter_create_new_tablebefore_alter_new_tableafter_alter_new_tablebefore_create_triggersafter_create_triggersbefore_copy_rowson_copy_rows_after_nibbleafter_copy_rowsbefore_swap_tablesafter_swap_tablesbefore_update_foreign_keysafter_update_foreign_keysbefore_drop_old_tableafter_drop_old_tablebefore_drop_triggersbefore_diebefore_exitget_replica_lag(及已废弃的 get_slave_lag)。如需查看某钩子入参,请在工具源码中搜索 # --plugin hook

DSN 选项

DSN 部分说明
Acharset默认字符集
Ddatabase新旧表所在数据库
Fmysql_read_default_file只从给定文件读取默认选项
hhost要连接的主机
ppassword连接密码(含逗号需转义)
Pport连接端口
Smysql_socket连接使用的 socket 文件
ttable要改的表
uuser登录用户(若非当前用户)
smysql_ssl建立 SSL 连接

其他信息

  • 系统要求:需要 Perl、DBI、DBD::mysql 及一些核心 Perl 包;仅支持 MySQL 5.0.2 及以上(更早版本无触发器)。所需权限:全局 PROCESSSUPERREPLICATION SLAVE,以及表级 SELECTINSERTUPDATEDELETECREATEDROPALTERTRIGGER;从库仅需 REPLICATION SLAVEREPLICATION CLIENT

  • 作者:Daniel Nichter 和 Baron Schwartz

  • 通用说明:已知问题的反馈方式、PTDEBUG 调试安全提示、系统要求基线,见 通用说明

更多细节请阅读 官方文档

Percona Toolkit 中文文档 · 社区维护的第三方学习站