pt-osc测试
pt-online-schema-change安装见pt-toolkit，这里不作介绍：
ddl语名：
alter table TXP.T_TXP_ORDER modify column merchant_orderno VARCHAR(50) COMMENT '商户订单号';
[root@sjno ~]# pt-online-schema-change -h127.0.0.1 -uroot -proot -P3306 --alter="modify column merchant_orderno VARCHAR(50) COMMENT '商户订单号'" --execute D=txp,t=t_txp_order;
Cannot connect to D=txp,P=3306,h=192.168.9.242,p=...,u=root
No slaves found. See --recursion-method if host sjnonbdb1 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `txp`.`t_txp_order`...
Creating new table...
Created new table txp._t_txp_order_new OK.
Altering new table...
Altered `txp`.`_t_txp_order_new` OK.
2016-12-05T15:19:52 Creating triggers...
2016-12-05T15:19:52 Created triggers OK.
2016-12-05T15:19:52 Copying approximately 1067052 rows...
Copying `txp`.`t_txp_order`: 40% 00:43 remain
Copying `txp`.`t_txp_order`: 79% 00:15 remain
2016-12-05T15:21:06 Copied rows OK.
2016-12-05T15:21:06 Analyzing new table...
2016-12-05T15:21:06 Swapping tables...
2016-12-05T15:21:06 Swapped original and new tables OK.
2016-12-05T15:21:06 Dropping old table...
2016-12-05T15:21:08 Dropped old table `txp`.`_t_txp_order_old` OK.
2016-12-05T15:21:08 Dropping triggers...
2016-12-05T15:21:08 Dropped triggers OK.
Successfully altered `txp`.`t_txp_order`.
要作的操作：alter table t2 add column class varchar(10);
在osc过程中是可以以表作dml操作的
注意：
t2表要有主键；
pt-online-schema-change --socket=/tmp/mysql3306.sock --alter="add column card varchar(255) not null default '' " --execute D=test,t=t2;
No slaves found. See --recursion-method if host sdw3 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `test`.`t2`...
Creating new table...
Created new table test._t2_new OK.
Altering new table...
Altered `test`.`_t2_new` OK.
2016-04-18T17:46:25 Creating triggers...
2016-04-18T17:46:25 Created triggers OK.
2016-04-18T17:46:25 Copying approximately 16327638 rows...
Copying `test`.`t2`: 12% 03:35 remain
Copying `test`.`t2`: 24% 03:02 remain
Copying `test`.`t2`: 38% 02:26 remain
Copying `test`.`t2`: 51% 01:51 remain
Copying `test`.`t2`: 65% 01:19 remain
Copying `test`.`t2`: 78% 00:48 remain
Copying `test`.`t2`: 92% 00:17 remain
2016-04-18T17:50:18 Copied rows OK.
2016-04-18T17:50:18 Analyzing new table...
2016-04-18T17:50:18 Swapping tables...
2016-04-18T17:50:18 Swapped original and new tables OK.
2016-04-18T17:50:18 Dropping old table...
2016-04-18T17:50:18 Dropped old table `test`.`_t2_old` OK.
2016-04-18T17:50:18 Dropping triggers...
2016-04-18T17:50:18 Dropped triggers OK.
Successfully altered `test`.`t2`.
[root@sdw3 mysql]#
创建的触发器：
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `pt_osc_test_t2_ins` AFTER INSERT ON `test`.`t2` FOR EACH ROW REPLACE INTO `test`.`_t2_new` (`a`, `b`, `class`, `student`, `stud`) VALUES (NEW.`a`, NEW.`b`, NEW.`class`, NEW.`student`, NEW.`stud`) */;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `pt_osc_test_t2_upd` AFTER UPDATE ON `test`.`t2` FOR EACH ROW REPLACE INTO `test`.`_t2_new` (`a`, `b`, `class`, `student`, `stud`) VALUES (NEW.`a`, NEW.`b`, NEW.`class`, NEW.`student`, NEW.`stud`) */;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `pt_osc_test_t2_del` AFTER DELETE ON `test`.`t2` FOR EACH ROW DELETE IGNORE FROM `test`.`_t2_new` WHERE `test`.`_t2_new`.`a` <=> OLD.`a` */;;
