Mysql在线修改Scheme工具原理

MySQL在线更改schema的工具很多，如Percona的pt-online-schema-change、 Facebook的 OSC和 LHM 等，这些都是基于触发器（Trigger）的； gh-ost（这是目前快手dba使用的工具） 声称是github自己开源的不需要触发器（Triggerless）支持的在线更改表结构的工具

 

目前最常用的工具是pt-online-schema-change，大概原理

1）、如果修改表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行

2）、被修改表必须要有主键，否则报错：Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.

3）、被修改表上不能有针对after delete|insert|update三个触发器，否则修改表结构操作失败

 

 

具体过程

1）、首先使用帐号密码连接到mysql后，获取指定表的状态信息，检查是否有触发器，检查表是否有主键。

 

2）、接着按照修改表的表定义，新建一个名为'_tb_new'不可见的临时表，对这个表执行alter添加字段，并校验是否执行成功。

 

3）、然后针对源表创建三个触发器，分别如下：

 

create trigger db_tb_del after delete on db.tb for each row delete ignore from db._tb_new where db._tb_new.id <=> OLD.id #删掉新表中db._tb_new.id <=> OLD.id的数据，否则忽略操作

 

create trigger db_tb_del after update on db.tb for each row replace into db._tb_new(id,...) values(new.id,...)  #源表执行update的时候，把对应的数据replace into的方式写入新表

 

create trigger db_tb_del after insert on db.tb for each row replace into db._tb_new(id,...) values(new.id,...)  #源表执行iinsert操作的时候，把对应的数据replace into的方式写入新表

 

4）、触发器创建好之后会执行insert low_priority ignore into db._tb_new(id,..) select id,... from tb lock in share mode语句复制源表数据到新表

 

5）、复制完成之后执行语句：rename table db.tb to db._tb_old,db._tb_new to db.tb，这一步是原子操作

 

6）、接着，如果没有加不删除old表的选项，那么就会删除Old表，然后删除三个触发器。到这里就完成了在线表结构的修改 。整个过程只在rename表的时间会锁一下表，其他时候不锁表。