[MySQL] 资料表结构迁移的好帮手 pt-online-schema-change

原文：[MySQL] 资料表结构迁移的好帮手 pt-online-schema-change

这边跟大家介绍一套做 DB migration 的好东西 pt-online-schema-change，这是可以让 DBA 省时省心力的工具，他可以用简单的指令让修改资料表结构时不锁表(可写入)与接近零 downtime 的好物。

在开发一套应用程式时，常常会根据不同的需求对资料库结构进行变更，对 MySQL 而言更是令人头痛，因为 MySQL 在修改资料表结构时会将资料表锁住，这会让伺服器无法正常服务进而造成使用者体验不佳，但如果你的资料表又刚好是大型资料表更会让锁定时间拉长，所以一般的应用程式甚至是银行业都会选择在半夜进行修改，尽可能地让 downtime 时间避开尖峰时段。

因为先前我都是使用 SQL Server 所以没有遇过这个问题，为了测试我还请朋友帮我在接近 1T 的资料表中加个允许 Null 的栏位，轻轻鬆鬆一瞬间就做完了也没有 Lock 过长的问题，难道这就是 M$ 有收保护费的成效？虽然 MySQL 5.6 开始为了避免锁表，加强了这方面的功能让锁表变成可读不可写，但这使用体验还是很差啊...

不过既然决议要用 MySQL 了那也没办法，我在找减少停机时间的解决方案时碰巧看到这款 tool，他可以用简单的指令让修改资料表结构时不锁表(可写入)与接近零 downtime ... 这不就是我需要的吗 ！

---

pt-online-schema-change 的运作流程如下：

检查外来键是否存在，做相应设置的处理。新增一张新的资料表，其结构与来源资料表完全相同
命名规则：_{source_table_name}_new根据 ALTER 语法修改新资料表结构在旧资料表上新增 INSERT, UPDATE, DELETE 触发器，其目的为同步新资料至新资料表将旧资料表的资料複製至新资料表将新资料表加入外来键相关设定将两张资料表的名字做交换，并将原旧资料表名称为 old 的后缀，修改方式为原子操作(Atomic)以减少半残状态。
命名规则：{source_table_name}_old

RENAME TABLE `exfast`.`tb_test` TO `exfast`.`_tb_test_old`, `exfast`.`_tb_test_new` TO `exfast`.`tb_test`

---

为了试验这边我準备了一张资料表其结构如下：

接着我要在这个资料表新增一个 age 的栏位，其指令为：

pt-online-schema-change \--execute \--progress=time,5 \--print \--alter-foreign-keys-method=rebuild_constraints \--alter="ADD COLUMN age TINYINT" \D=exfast,t=tb_test,h=172.17.0.2,P=3306,u=root,p=pass

由此可见这么一小段简单的指令这个 tool 就帮我们完成了麻烦的 7 个步骤实在是太佛心了吧！

---

这边特别注解 --alter-foreign-keys-method 这个参数，这个参数共有三个值可以设定

rebuild_constraints
会先将旧 table 的外来键 Constraints 删除再添加至新表中，如果资料表很大的话会造成长时间阻塞，但也是最安全的方法。drop_swap
执行 FOREIGN_KEY_CHECKS = 0 禁止外来键 Constraints，先删除原资料表再重命名新资料表。这种方式很快但是有风险：
2.1. 在删除原资料表和重命名新资料表的短时间内，资料表是不存在的程式会抛错误。
2.2. 如果在重新命名的步骤出现错误无法复原。因为原表已经被删除。none
类似 drop_swap 的处理方式，但是它不删除原资料表，并且外来键 Constraints 会通过重新命名转到老资料表上面。

参考资料：

online-schema-changept-online-schema-change 工具不锁表在线修改 MySQL 表结构