MySQL执行DDL操作如何不锁表_使用pt-online-schema-change工具

pt-online-schema-change：如何实现无锁表结构变更

说到在线修改大表结构，ALTER TABLE 命令那把沉重的全表独占锁，恐怕是很多DBA的噩梦。业务高峰期不敢动，半夜操作心惊胆战。那么，有没有办法能优雅地绕开这把锁呢？答案就是 pt-online-schema-change（简称 pt-osc）。

pt-online-schema-change 为什么能不锁表

它的原理其实并不神秘，核心在于一套“影子表+触发器+增量同步”的组合拳。简单来说，工具会先创建一个与目标表结构一致的新表（影子表），然后通过触发器，将原表上所有的数据写入操作（INSERT, UPDATE, DELETE）实时同步到新表。与此同时，旧有数据会被分成一个个小块（chunk）逐步拷贝到新表，每个小块操作只加极短时间的锁，对业务的影响微乎其微。

pt-online-schema-change能不锁表是因为采用影子表+触发器+增量同步机制，绕过ALTER TABLE的全表独占锁；原表持续可读写，变更由触发器实时捕获并应用到新表，数据分chunk拷贝且每chunk加锁极短。

当然，天下没有免费的午餐。这套机制有两个关键前提：首先，目标表必须存在主键或唯一非空索引，否则无法安全地进行数据分片拷贝。其次，在高并发写入的场景下，触发器本身会带来额外的开销，可能对写性能产生一定影响，这一点在评估时务必纳入考量。

执行前必须检查的 4 个前提条件

工欲善其事，必先利其器。在按下执行键之前，下面这四个检查项一个都不能少，否则很可能中途“翻车”：

• 权限检查：使用的 MySQL 账号必须具备 SELECT、INSERT、UPDATE、DELETE、DROP、CREATE、ALTER、INDEX、TRIGGER 这些权限。缺了任何一个，工具都会在中途报出 Access denied 错误。
• 外键约束：目标表不能存在外键（FOREIGN KEY）约束，否则工具会直接退出。如果存在，需要先手动删除外键，待表结构变更完成后再重新添加。
• 磁盘空间：确保数据目录所在磁盘的剩余空间至少是原表大小的两倍。这包括了影子表占用的空间，以及操作过程中可能增长的 binlog 日志空间。别嫌麻烦，用 df -h /var/lib/mysql 命令亲眼确认一下。
• 复制延迟：如果环境中有主从复制，务必确认从库的延迟状态（Seconds_Behind_Master）为 0。否则，在主从切换或后续 DDL 同步时很容易出现数据不一致的混乱局面。

最常用命令参数怎么配才稳妥

直接使用默认参数在线上环境执行，无异于一场豪赌。面对动辄千万行的大表，精细化的参数控制才是稳健之道：

• 控制搬运节奏：使用 --chunk-size=1000 参数指定每次拷贝的数据行数（默认是1000）。对于特别大的表，可以适当调小到500，以减轻单次操作对系统的冲击。
• 设置负载保护：通过 --max-load="Threads_running=25" 参数设置一个负载阈值。当 SHOW STATUS LIKE 'Threads_running' 查询到的并发线程数超过25时，工具会自动暂停数据拷贝，避免把数据库实例压垮。
• 明确指定主键：在命令中强制指定主键字段，例如 --primary-key=id。尤其是在表存在多个唯一索引的情况下，工具可能会选错分片依据，明确指定可以避免意外。
• 执行预演：务必先加上 --dry-run 和 --print 参数跑一遍。这个步骤会完整展示工具计划创建的表结构、触发器等信息，但不会做任何实际修改。跳过这一步，就等于蒙着眼睛过河。

执行中遇到 ERROR 1105 怎么快速定位

操作过程中，如果遇到 ERROR 1105 (HY000): Cannot execute statement: binlog format is not supported 这个报错，先别慌。问题的根源十有八九出在 MySQL 的二进制日志格式上。

pt-osc 的内部机制依赖于行级复制（Row-Based Replication）的语义，因此要求 binlog_format 必须设置为 ROW 或 MIXED，而不能是 STATEMENT。

解决办法很明确：

• 临时修改（会话级）：在当前会话中执行 SET SESSION binlog_format = ROW;。这只对当前连接生效，重启后失效。
• 永久修改（全局）：在 MySQL 配置文件 my.cnf 的 [mysqld] 段中加入 binlog_format = ROW，然后重启 MySQL 服务。这是推荐的生产环境做法。
• 验证配置：修改后，连接数据库执行 SELECT @@binlog_format;，确认输出结果为 ROW。

这个错误点卡住过不少人，有时候对着报错信息琢磨半天，其实根源就是一行配置。所以，下次再遇到类似问题，第一反应就应该是：查一下 @@binlog_format 吧。