pt-online-schema-change在线DDL工具可以做到DDL操作不锁表，不影响线上操作。对于线上超过100W的大表，一般情况下都用这个工具做DDL，最重要的考虑点还是“不影响线上操作”
pt-online-schema-change内部操作流程
1）创新新的临时表，临时表为DDL后的目标表结构
2）在原表上创建增删改三个触发器，当原表有数据DML操作时，通过触发器同步数据到新的临时表
3）把原表的数据分批倒入到新的临时表
4）新表，老表做表名称互换操作
5）删除修改后表的触发器
删除失败验证步骤
1）创建表，ID自增，作为主键，10条记录
mysql> CREATE TABLE `zxy_test` ( -> `id` bigint(20) NOT NULL AUTO_INCREMENT, -> `FUSERID` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `idx` (`FUSERID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.00 sec) mysql> insert into zxy_test values(1,1),(2,2),(3,3),(4,4); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
2）把这个表的主键从ID改成FUSERID，用pt这个工具，在改的过程中，对原表做增删改的操作。表记录数很小，不做真实的ddl全部操作，print出pt操作过程，人为的把DDL时间延长，以求有足够的时间做中间操作。模拟pt工具，创建new表，创建触发器
pt执行的命令是：pt-online-schema-change --user=root --password='XXXX' --host=10.3.172.112 D=test,t=zxy_test --alter "DROP ID,ADD PRIMARY KEY(FUSERID);" --charset=utf8 --no-check-replication-filters --execute
mysql> CREATE TABLE `test`.`_zxy_test_new` ( -> `id` bigint(20) NOT NULL AUTO_INCREMENT, -> `FUSERID` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `idx` (`FUSERID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE `test`.`_zxy_test_new` DROP ID,ADD PRIMARY KEY(FUSERID); Query OK, 0 rows affected (1.30 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE TRIGGER `pt_osc_test_zxy_test_del` AFTER DELETE ON `test`.`zxy_test` FOR EACH ROW DELETE IGNORE FROM `test`.`_zxy_test_new` WHERE `test`.`_zxy_test_new`.`id` <=> OLD.`id`; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TRIGGER `pt_osc_test_zxy_test_upd` AFTER UPDATE ON `test`.`zxy_test` FOR EACH ROW REPLACE INTO `test`.`_zxy_test_new` (`fuserid`) VALUES (NEW.`fuserid`); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TRIGGER `pt_osc_test_zxy_test_ins` AFTER INSERT ON `test`.`zxy_test` FOR EACH ROW REPLACE INTO `test`.`_zxy_test_new` (`fuserid`) VALUES (NEW.`fuserid`); Query OK, 0 rows affected (0.00 sec) mysql>
3）开始对原表的数据做增删改操作，操作记录会通过触发器同步到新表
新增的记录：新增没有问题
mysql> select * from zxy_test; +----+---------+ | id | FUSERID | +----+---------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+---------+ 4 rows in set (0.00 sec) mysql> select * from _zxy_test_new; Empty set (0.00 sec) mysql> insert into zxy_test values(5,5); Query OK, 1 row affected (0.00 sec) mysql> select * from zxy_test; +----+---------+ | id | FUSERID | +----+---------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+---------+ 5 rows in set (0.00 sec) mysql> select * from _zxy_test_new; +---------+ | FUSERID | +---------+ | 5 | +---------+ 1 row in set (0.00 sec)
修改记录：修改没有问题
mysql> select * from zxy_test; +----+---------+ | id | FUSERID | +----+---------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+---------+ 5 rows in set (0.00 sec) mysql> select * from _zxy_test_new; +---------+ | FUSERID | +---------+ | 5 | +---------+ 1 row in set (0.00 sec) mysql> update zxy_test set fuserid=10 where id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from zxy_test; +----+---------+ | id | FUSERID | +----+---------+ | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 1 | 10 | +----+---------+ 5 rows in set (0.00 sec) mysql> select * from _zxy_test_new; +---------+ | FUSERID | +---------+ | 5 | | 10 | +---------+ 2 rows in set (0.00 sec)
删除记录，删除记录失败，原表数据不变，新表数据不变
mysql> select * from zxy_test; +----+---------+ | id | FUSERID | +----+---------+ | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 1 | 10 | +----+---------+ 5 rows in set (0.00 sec) mysql> select * from _zxy_test_new; +---------+ | FUSERID | +---------+ | 5 | | 10 | +---------+ 2 rows in set (0.00 sec) mysql> delete from zxy_test where fuserid=2; ERROR 1054 (42S22): Unknown column 'test._zxy_test_new.id' in 'where clause' mysql> select * from zxy_test; +----+---------+ | id | FUSERID | +----+---------+ | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 1 | 10 | +----+---------+ 5 rows in set (0.00 sec) mysql> select * from _zxy_test_new; +---------+ | FUSERID | +---------+ | 5 | | 10 | +---------+ 2 rows in set (0.00 sec)
看报错信息“ERROR 1054 (42S22): Unknown column 'test._zxy_test_new.id' in 'where clause'”，提示test._zxy_test_new.id 不存在，修改后的表是没有id字段的，这个错误是由delete触发器报出。delete触发器命令：CREATE TRIGGER `pt_osc_test_zxy_test_del` AFTER DELETE ON `test`.`zxy_test` FOR EACH ROW DELETE IGNORE FROM `test`.`_zxy_test_new` WHERE `test`.`_zxy_test_new`.`id` <=> OLD.`id`; 在删除原表记录的时候，通过主键去定位被触发的记录，在new表里删除，这样做的目的是保证删除触发删除的记录在两个表里绝对是一致的。
最后贴出pt执行时，在MYSQL里面执行的全部SQL命令，关键信息蓝色加粗，完全对应文章开篇的步骤
13134 Query SHOW TABLES FROM `test` LIKE 'zxy\_test' 13134 Query SHOW TRIGGERS FROM `test` LIKE 'zxy\_test' 13134 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 13134 Query USE `test` 13134 Query SHOW CREATE TABLE `test`.`zxy_test` 13134 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 13134 Query EXPLAIN SELECT * FROM `test`.`zxy_test` WHERE 1=1 13134 Query SHOW INDEXES FROM `test`.`zxy_test` WHERE Key_name = 'idx' 13134 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='test' AND referenced_table_name='zxy_test' 151125 15:36:59 13134 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 13134 Query USE `test` 13134 Query SHOW CREATE TABLE `test`.`zxy_test` 13134 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 13134 Query CREATE TABLE `test`.`_zxy_test_new` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `FUSERID` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx` (`FUSERID`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 13134 Query ALTER TABLE `test`.`_zxy_test_new` DROP ID,ADD PRIMARY KEY(FUSERID) 151125 15:37:01 13134 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 13134 Query USE `test` 13134 Query SHOW CREATE TABLE `test`.`_zxy_test_new` 13134 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 13134 Query CREATE TRIGGER `pt_osc_test_zxy_test_del` AFTER DELETE ON `test`.`zxy_test` FOR EACH ROW DELETE IGNORE FROM `test`.`_zxy_test_new` WHERE `test`.`_zxy_test_new`.`id` <=> OLD.`id` 13134 Query CREATE TRIGGER `pt_osc_test_zxy_test_upd` AFTER UPDATE ON `test`.`zxy_test` FOR EACH ROW REPLACE INTO `test`.`_zxy_test_new` (`fuserid`) VALUES (NEW.`fuserid`) 13134 Query CREATE TRIGGER `pt_osc_test_zxy_test_ins` AFTER INSERT ON `test`.`zxy_test` FOR EACH ROW REPLACE INTO `test`.`_zxy_test_new` (`fuserid`) VALUES (NEW.`fuserid`) 13134 Query EXPLAIN SELECT * FROM `test`.`zxy_test` WHERE 1=1 13134 Query SHOW INDEXES FROM `test`.`zxy_test` WHERE Key_name = 'idx' 13134 Query INSERT LOW_PRIORITY IGNORE INTO `test`.`_zxy_test_new` (`fuserid`) SELECT `fuserid` FROM `test`.`zxy_test` /*pt-online-schema-change 27995 copy table*/ 13134 Query SHOW WARNINGS 13134 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 13134 Query RENAME TABLE `test`.`zxy_test` TO `test`.`_zxy_test_old`, `test`.`_zxy_test_new` TO `test`.`zxy_test` 13134 Query DROP TABLE IF EXISTS `test`.`_zxy_test_old` 13134 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_zxy_test_del` 13134 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_zxy_test_upd` 13134 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_zxy_test_ins` 13134 Query SHOW TABLES FROM `test` LIKE '\_zxy\_test\_new'
结论：
用PT工具做主键删除的DDL,在DDL执行的过程中，如果有对原表的增删改操作，增改操作正常运行，删除操作会失败。