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表,创建触发器
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表里删除,这样做的目的是保证删除触发删除的记录在两个表里绝对是一致的。
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'
结论: