跳转到主内容
趣航编程网 - 趣学编程,启航技术之路!

mysql如何执行不锁表的在线结构变更_pt-online-schema-change应用

MySQL 5.6前ALTER TABLE全程锁表,5.6+虽支持INPLACE但加索引等仍可能锁表;pt-online-schema-change通过新建表、触发器同步、分块拷贝和原子切换实现无锁变更,但依赖主键/唯一索引,且不支持改主键、分区重组等操作。 为什么直接 ALTER TABLE 会锁表 MySQL 5.6 之前,
ALTER TABLE
基本全程锁表(尤其是 MyISAM 和旧版 InnoDB),DML 操作全部阻塞;5.6+ 虽支持部分
ALGORITHM=INPLACE
操作,但像加索引、改列类型、增删列仍可能触发表拷贝或元数据锁升级,导致主从延迟飙升、应用超时。真实线上环境里,一个耗时 20 分钟的
ALTER
很容易引发雪崩。 pt-online-schema-change 是怎么绕过锁的 它不直接改原表,而是另起炉灶: 建新表 → 同步数据 → 逐行迁移 → 原子切换 。核心靠三样东西:触发器捕获变更、
REPLACE INTO ... SELECT
分块拷贝、
RENAME TABLE
瞬间交换。整个过程原表始终可读写,但要注意:触发器本身有开销,大事务可能堆积,且必须保证主键或唯一索引存在(否则无法定位变更行)。 常见错误现象包括:
Cannot find a unique index for the table
—— 缺少主键或唯一索引,工具无法安全同步增量 长时间卡在
Copying rows
阶段 —— 表太大或
--chunk-size
设得太小,建议从 1000 开始调优 从库复制中断 —— 触发器操作被写入 binlog,若从库启用了
replicate_ignore_table
或过滤规则,需确认是否放行临时表名(如
_tbl_new
) 关键参数和最小安全执行命令 不要裸跑默认参数。生产环境至少要加这四个: MySQL(Linux) MySQL 9.6.0是面向Linux平台的2026年创新版本,核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层,确保所有数据变更均被完整记录至Binlog,彻底解决了CDC(变更数据捕获)与主从复制中的数据不一致难题。此外,该版本引入container_aware启动选项以原生适配容器环境,并对审计日志进行了组件化重构,为追求极致数据一致性与云原生体验的开发者提供了全新选择。 下载
--alter "ADD COLUMN status TINYINT DEFAULT 0"
—— DDL 语句必须用双引号包裹,空格和大小写敏感
--chunk-size=5000
—— 控制每次迁移行数,太小增加 round-trip 次数,太大加重主库负载
--max-load="Threads_running=25"
—— 当前活跃线程超 25 就暂停拷贝,防雪崩
--critical-load="Threads_running=50"
—— 达到 50 直接退出,避免恶化
--dry-run --execute
先试跑,确认无报错再真正执行 最简可用命令示例:
pt-online-schema-change \ --host=localhost \ --user=root \ --password=xxx \ --database=testdb \ --table=user_info \ --alter "ADD COLUMN updated_at DATETIME" \ --chunk-size=2000 \ --max-load="Threads_running=20" \ --critical-load="Threads_running=40" \ --dry-run
哪些操作它也搞不定 不是所有 DDL 都能“在线”。以下情况
pt-online-schema-change
会直接拒绝或行为异常: 修改
PRIMARY KEY
或删除主键 —— 工具依赖主键做增量同步,禁用 对分区表执行
--alter
且涉及
REORGANIZE PARTITION
—— 不支持,得用
ALTER TABLE ... REORGANIZE PARTITION
手动做 目标列名含反引号或特殊字符(如
`col name`
)—— 解析失败,改用标准命名 MySQL 5.7+ 开启了
sql_log_bin=OFF
的会话中运行 —— 触发器不记 binlog,从库彻底失联 真正上线前,务必在从库上先停掉 SQL 线程,检查
SHOW PROCESSLIST
是否有残留的
pt_osc_*
触发器,以及
information_schema.TRIGGERS
里有没有漏删的条目 —— 这些细节不清理,下次变更就可能冲突。

相关文章