mysql5.6和mysql5.7对online DDL做了大幅度功能增强，但是仍然存在主库执行DDL，从库存在大幅延迟的情况，故目前生产环境还是通过pt-online-schema-change工具来实现online DDL。但是pt-online-schema-change的使用是否就没有限制呢？

先看看官方文档对pt-online-schema-change的工作原理的描述：

pt-online-schema-changeworksbycreatinganemptycopyofthetabletoalter,modifyingitasdesired,andthencopyingrowsfromtheoriginaltableintothenewtable.Whenthecopyiscomplete,itmovesawaytheoriginaltableandreplacesitwiththenewone.Bydefault,italsodropstheoriginaltable.Thedatacopyprocessisperformedinsmallchunksofdata,whicharevariedtoattempttomakethemexecuteinaspecificamountoftime(see--chunk-time).Thisprocessisverysimilartohowothertools,suchaspt-tablechecksum,work.Anymodificationstodataintheoriginaltablesduringthecopywillbereflectedinthenewtable,becausethetoolcreatestriggersontheoriginaltabletoupdatethecorrespondingrowsinthenewtable.Theuseoftriggersmeansthatthetoolwillnotworkifanytriggersarealreadydefinedonthetable.Whenthetoolfinishescopyingdataintothenewtable,itusesanatomicRENAMETABLEoperation

接下来通过实验的方式看看pt-online-schema-change是如何工作的，记得打开mysql的general log。通过查看general日志验证pt-online-schema-change的工作机理。

shell>pt-online-schema-change -u linzj -h 192.168.110.131 -p linzj --alter='add column vid3 int' --execute D=sbtest,t=sbtest

1 创建一个和你要执行 alter 操作的表一样的空表结构：

11QueryCREATETABLE`sbtest`.`_sbtest_new`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`k`int(10)unsignedNOTNULLDEFAULT'0',`c`char(120)NOTNULLDEFAULT'',`pad`char(60)NOTNULLDEFAULT'',`vid`int(11)DEFAULTNULL,`vid2`int(11)DEFAULTNULL,PRIMARYKEY(`id`),KEY`k`(`k`))ENGINE=InnoDBAUTO_INCREMENT=4294967295DEFAULTCHARSET=utf8

2、执行表结构修改

17040715:45:4611QueryALTERTABLE`sbtest`.`_sbtest_new`addcolumnvid3int

3、在原表上创建触发器，如果表中已经定义了触发器这个工具就不能工作了。

11QueryCREATETRIGGER`pt_osc_sbtest_sbtest_del`AFTERDELETEON`sbtest`.`sbtest`FOREACHROWDELETEIGNOREFROM`sbtest`.`_sbtest_new`WHERE`sbtest`.`_sbtest_new`.`id`<=>OLD.`id`11QueryCREATETRIGGER`pt_osc_sbtest_sbtest_upd`AFTERUPDATEON`sbtest`.`sbtest`FOREACHROWREPLACEINTO`sbtest`.`_sbtest_new`(`id`,`k`,`c`,`pad`,`vid`,`vid2`)VALUES(NEW.`id`,NEW.`k`,NEW.`c`,NEW.`pad`,NEW.`vid`,NEW.`vid2`)11QueryCREATETRIGGER`pt_osc_sbtest_sbtest_ins`AFTERINSERTON`sbtest`.`sbtest`FOREACHROWREPLACEINTO`sbtest`.`_sbtest_new`(`id`,`k`,`c`,`pad`,`vid`,`vid2`)VALUES(NEW.`id`,NEW.`k`,NEW.`c`,NEW.`pad`,NEW.`vid`,NEW.`vid2`)

4、按主键or唯一索引进行排序，分成若干chunk进行数据copy

11QueryEXPLAINSELECT*FROM`sbtest`.`sbtest`WHERE1=111QuerySELECT/*!40001SQL_NO_CACHE*/`id`FROM`sbtest`.`sbtest`FORCEINDEX(`PRIMARY`)ORDERBY`id`LIMIT1/*firstlowerboundary*/11QuerySELECT/*!40001SQL_NO_CACHE*/`id`FROM`sbtest`.`sbtest`FORCEINDEX(`PRIMARY`)WHERE`id`ISNOTNULLORDERBY`id`LIMIT1/*key_len*/11QueryEXPLAINSELECT/*!40001SQL_NO_CACHE*/*FROM`sbtest`.`sbtest`FORCEINDEX(`PRIMARY`)WHERE`id`>='1'/*key_len*/11QueryEXPLAINSELECT/*!40001SQL_NO_CACHE*/`id`FROM`sbtest`.`sbtest`FORCEINDEX(`PRIMARY`)WHERE((`id`>='1'))ORDERBY`id`LIMIT999,2/*nextchunkboundary*/11QuerySELECT/*!40001SQL_NO_CACHE*/`id`FROM`sbtest`.`sbtest`FORCEINDEX(`PRIMARY`)WHERE((`id`>='1'))ORDERBY`id`LIMIT999,2/*nextchunkboundary*/11QuerySHOWWARNINGS11QuerySHOWGLOBALSTATUSLIKE'Threads_running'11QueryEXPLAINSELECT/*!40001SQL_NO_CACHE*/`id`FROM`sbtest`.`sbtest`FORCEINDEX(`PRIMARY`)WHERE((`id`>='1001'))ORDERBY`id`LIMIT19329,2/*nextchunkboundary*/11QuerySELECT/*!40001SQL_NO_CACHE*/`id`FROM`sbtest`.`sbtest`FORCEINDEX(`PRIMARY`)WHERE((`id`>='1001'))ORDERBY`id`LIMIT19329,2/*nextchunkboundary*/11QueryEXPLAINSELECT`id`,`k`,`c`,`pad`,`vid`,`vid2`FROM`sbtest`.`sbtest`FORCEINDEX(`PRIMARY`)WHERE((`id`>='1001'))AND((`id`<='20330'))LOCKINSHAREMODE/*explainpt-online-schema-change17219copynibble*/11QueryINSERTLOW_PRIORITYIGNOREINTO`sbtest`.`_sbtest_new`(`id`,`k`,`c`,`pad`,`vid`,`vid2`)SELECT`id`,`k`,`c`,`pad`,`vid`,`vid2`FROM`sbtest`.`sbtest`FORCEINDEX(`PRIMARY`)WHERE((`id`>='1001'))AND((`id`<='20330'))LOCKINSHAREMODE/*pt-online-schema-change17219copynibble*/

5、rename表，默认删除旧表

11QueryRENAMETABLE`sbtest`.`sbtest`TO`sbtest`.`_sbtest_old`,`sbtest`.`_sbtest_new`TO`sbtest`.`sbtest`11QueryDROPTABLEIFEXISTS`sbtest`.`_sbtest_old`

那这样的话，如果我们在使用pt-online-schema-change工具在线online DDL某个表的时候，同时对该表的主键or唯一索引字段进行DML，是否会存在异常呢？

实验场景如下：

第一个窗口：

shell>pt-online-schema-change-ulinzj-h192.168.110.131-plinzj--alter='addcolumnvid3int'--executeD=sbtest,t=sbtestFound2slaves:mysql2ansibleWillcheckslavelagon:mysql2ansibleOperation,tries,wait:copy_rows,10,0.25create_triggers,10,1drop_triggers,10,1swap_tables,10,1update_foreign_keys,10,1Altering`sbtest`.`sbtest`...Creatingnewtable...Creatednewtablesbtest._sbtest_newOK.Waitingforeverfornewtable`sbtest`.`_sbtest_new`toreplicatetomysql2...Alteringnewtable...Altered`sbtest`.`_sbtest_new`OK.2017-04-07T14:52:50Creatingtriggers...2017-04-07T14:52:50CreatedtriggersOK.2017-04-07T14:52:50Copyingapproximately986400rows...Copying`sbtest`.`sbtest`:86%00:04remain2017-04-07T14:53:27CopiedrowsOK.2017-04-07T14:53:27Swappingtables...2017-04-07T14:53:27SwappedoriginalandnewtablesOK.2017-04-07T14:53:27Droppingoldtable...2017-04-07T14:53:27Droppedoldtable`sbtest`.`_sbtest_old`OK.2017-04-07T14:53:27Droppingtriggers...2017-04-07T14:53:27DroppedtriggersOK.Successfullyaltered`sbtest`.`sbtest`.

第二个窗口：

root@localhost:mysql3306.sock15:44:[sbtest]>selectcount(*)fromsbtest;+----------+|count(*)|+----------+|1000000|+----------+1rowinset(0.17sec)

root@localhost:mysql3306.sock15:44:[sbtest]>updatesbtestsetid=9999999whereid=110;QueryOK,1rowaffected(1.33sec)Rowsmatched:1Changed:1Warnings:0

root@localhost:mysql3306.sock15:45:[sbtest]>updatesbtestsetid=9999998whereid=111;QueryOK,1rowaffected(0.84sec)Rowsmatched:1Changed:1Warnings:0

root@localhost:mysql3306.sock15:46:[sbtest]>updatesbtestsetid=9999997whereid=112;QueryOK,1rowaffected(0.75sec)Rowsmatched:1Changed:1Warnings:0

root@localhost:mysql3306.sock15:46:[sbtest]>selectcount(*)fromsbtest;+----------+|count(*)|+----------+|1000003|+----------+1rowinset(0.70sec)

root@localhost:mysql3306.sock15:46:[sbtest]>select*fromsbtestorderbyiddesclimit5;+---------+---+---+----------------------------------------------------+------+------+------+|id|k|c|pad|vid|vid2|vid3|+---------+---+---+----------------------------------------------------+------+------+------+|9999999|0||qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|NULL|NULL|NULL||9999998|0||qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|NULL|NULL|NULL||9999997|0||qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|NULL|NULL|NULL||1000000|0||qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|NULL|NULL|NULL||999999|0||qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|NULL|NULL|NULL|+---------+---+---+----------------------------------------------------+------+------+------+5rowsinset(0.00sec)

root@localhost:mysql3306.sock15:46:[sbtest]>select*fromsbtestwhereidin(110,111,112);+-----+---+---+----------------------------------------------------+------+------+------+|id|k|c|pad|vid|vid2|vid3|+-----+---+---+----------------------------------------------------+------+------+------+|110|0||qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|NULL|NULL|NULL||111|0||qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|NULL|NULL|NULL||112|0||qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|NULL|NULL|NULL|+-----+---+---+----------------------------------------------------+------+------+------+3rowsinset(0.02sec)

同时对表的主键or唯一索引进行修改的话，这时候就会出现新表的数据比旧表数据多的情况发现。这应该算是pt-online-schema-change工具的一个bug，为何会出现这种情况，请仔细观察下pt-online-schema-change工具在原表创建的3个触发器的定义就可以很容易发现了。

建议大家，在使用pt-online-schema-change的时候，暂停对表主键or唯一索引列的数据更新。

pt_online_schema_change典型的用法：

1）添加一列，并不真正执行
pt-online-schema-change –alter “add column c1 int” D=mydb,t=mytable –dry-run

2）更新存储引擎为InnoDB，不删除原表
pt-online-schema-change –alter “ENGINE=InnoDB” –no-drop-old-table –print –statistics –execute D=mydb,t=mytable –execute

3）复制环境下，忽略日志筛选和Slave复制延迟，删除表字段
pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop company_type,drop channel_code” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –print –statistics –execute

4）更新被子表引用到的父表
pt-online-schema-change –alter “add newcol int” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –print –statistics –execute

5）在我们的双主复制环境中，设定了忽略mysql库的复制，不是很在乎复制的延迟，有时有外键影响，希望尽量保留原表数据，必要时自行删除。
pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop newcol” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –no-drop-old-table –print –statistics –execute