为innodb的表在线加字段，加索引
pt-online-schema-change的版本是2.0.3
注意，要使用--bin-log参数，以保证master和slave的数据一致性。不使用--bin-log时，有部分数据更改的操作不写入binlog。
在更高的版本中为了避免出现slave数据不一致的情况，干脆去掉了--bin-log参数。强制把所有数据修改都同步到slave去。
--sleep的单位是秒，在每插入--chunk-size行后，sleep --sleep指定的秒，可以用小数。
--chunk-size单位是行，每次insert select的行数，缺省是1000
当修改过程中出现死锁导致的异常退出时，把--chunk-size改小再试。
重试之前先清理一下pt-online-schema-change产生的临时表和trigger。
执行过程中除了观察当前mysql实例的压力以外，还要注意其slave是否有延迟，是否出现slave线程异常停止的情况。
在使用了--bin-log参数，或者是更高版本（不支持--bin-log的版本）时，没有遇到过slave线程异常中止的情况。
pt-online-schema-change h=127.0.0.1,P=3306,t=db_1.table_0 --alter "add key pid(pid),add flag tinyint" --sleep 0.1 --bin-log
# 2012-10-12T14:05:52 /usr/bin/pt-online-schema-change started
# 2012-10-12T14:05:52 USE `db_1`
# 2012-10-12T14:05:52 Alter table table_0 using temporary table __tmp_table_0
# 2012-10-12T14:05:52 Checking if table table_0 can be altered
# 2012-10-12T14:05:52 SHOW TRIGGERS FROM `db_1` LIKE 'table_0'
# 2012-10-12T14:05:52 Table table_0 can be altered
# 2012-10-12T14:05:52 Chunk column id, index PRIMARY
# 2012-10-12T14:05:52 Chunked table table_0 into 7475 chunks
# 2012-10-12T14:05:52 Starting online schema change
# 2012-10-12T14:05:52 CREATE TABLE `db_1`.`__tmp_table_0` LIKE `db_1`.`table_0`
# 2012-10-12T14:05:52 ALTER TABLE `db_1`.`__tmp_table_0` add key pid(pid),add flag tinyint DEFAULT NULL COMMENT 'msg类型'
# 2012-10-12T14:05:52 Shared columns: id, pid, sid, create_on
# 2012-10-12T14:05:52 Calling OSCCaptureSync::capture()
# 2012-10-12T14:05:52 CREATE TRIGGER mk_osc_del AFTER DELETE ON `db_1`.`table_0` FOR EACH ROW DELETE IGNORE FROM `db_1`.`__tmp_table_0` WHERE `db_1`.`__tmp_table_0`.id = OLD.id
# 2012-10-12T14:05:52 CREATE TRIGGER mk_osc_upd AFTER UPDATE ON `db_1`.`table_0` FOR EACH ROW REPLACE INTO `db_1`.`__tmp_table_0` (id, pid, sid, create_on) VALUES (NEW.id, NEW.pid, NEW.sid, NEW.create_on)
# 2012-10-12T14:05:52 CREATE TRIGGER mk_osc_ins AFTER INSERT ON `db_1`.`table_0` FOR EACH ROW REPLACE INTO `db_1`.`__tmp_table_0` (id, pid, sid, create_on) VALUES(NEW.id, NEW.pid, NEW.sid, NEW.create_on)
# 2012-10-12T14:05:52 Calling CopyRowsInsertSelect::copy()
Copying rows: 2% 16:48 remain
Copying rows: 5% 15:45 remain
Copying rows: 8% 15:11 remain
Copying rows: 12% 14:40 remain
Copying rows: 14% 14:12 remain
Copying rows: 17% 13:47 remain
Copying rows: 20% 13:16 remain
Copying rows: 23% 12:46 remain
Copying rows: 26% 12:16 remain
Copying rows: 29% 11:48 remain
Copying rows: 32% 11:20 remain
Copying rows: 35% 10:50 remain
Copying rows: 38% 10:19 remain
Copying rows: 41% 09:48 remain
Copying rows: 44% 09:18 remain
Copying rows: 47% 08:47 remain
Copying rows: 50% 08:17 remain
Copying rows: 53% 07:45 remain
Copying rows: 56% 07:14 remain
Copying rows: 59% 06:43 remain
Copying rows: 63% 06:08 remain
Copying rows: 66% 05:32 remain
Copying rows: 69% 04:58 remain
Copying rows: 73% 04:23 remain
Copying rows: 76% 03:49 remain
Copying rows: 79% 03:16 remain
Copying rows: 83% 02:43 remain
Copying rows: 86% 02:11 remain
Copying rows: 89% 01:39 remain
Copying rows: 92% 01:08 remain
Copying rows: 96% 00:37 remain
Copying rows: 99% 00:06 remain
# 2012-10-12T14:21:57 Calling OSCCaptureSync::sync()
# 2012-10-12T14:21:57 Renaming tables
# 2012-10-12T14:21:57 RENAME TABLE `db_1`.`table_0` TO `db_1`.`__old_table_0`, `db_1`.`__tmp_table_0` TO `db_1`.`table_0`
# 2012-10-12T14:21:57 Original table table_0 renamed to __old_table_0
# 2012-10-12T14:21:57 Calling CopyRowsInsertSelect::cleanup()
# 2012-10-12T14:21:57 Calling OSCCaptureSync::cleanup()
# 2012-10-12T14:21:57 DROP TRIGGER IF EXISTS `db_1`.`mk_osc_del`
# 2012-10-12T14:21:57 DROP TRIGGER IF EXISTS `db_1`.`mk_osc_ins`
# 2012-10-12T14:21:57 DROP TRIGGER IF EXISTS `db_1`.`mk_osc_upd`
# 2012-10-12T14:21:57 /usr/bin/pt-online-schema-change ended, exit status 0
上面过程中观察mysql本身压力不大，没出现连接堆积。并且slave没出现延迟，也未中断。
为了提高速度，去掉--sleep
pt-online-schema-change h=127.0.0.1,P=3306,t=db_1.table_1 --alter "add key pid(pid),add flag tinyint DEFAULT NULL" --bin-log
# 2012-10-12T14:23:39 /usr/bin/pt-online-schema-change started
# 2012-10-12T14:23:40 USE `db_1`
# 2012-10-12T14:23:40 Alter table table_1 using temporary table __tmp_table_1
# 2012-10-12T14:23:40 Checking if table table_1 can be altered
# 2012-10-12T14:23:40 SHOW TRIGGERS FROM `db_1` LIKE 'table_1'
# 2012-10-12T14:23:40 Table table_1 can be altered
# 2012-10-12T14:23:40 Chunk column id, index PRIMARY
# 2012-10-12T14:23:40 Chunked table table_1 into 7693 chunks
# 2012-10-12T14:23:40 Starting online schema change
# 2012-10-12T14:23:40 CREATE TABLE `db_1`.`__tmp_table_1` LIKE `db_1`.`table_1`
# 2012-10-12T14:23:40 ALTER TABLE `db_1`.`__tmp_table_1` add key pid(pid),add flag tinyint DEFAULT NULL
# 2012-10-12T14:23:40 Shared columns: id, pid, sid, create_on
# 2012-10-12T14:23:40 Calling OSCCaptureSync::capture()
# 2012-10-12T14:23:40 CREATE TRIGGER mk_osc_del AFTER DELETE ON `db_1`.`table_1` FOR EACH ROW DELETE IGNORE FROM `db_1`.`__tmp_table_1` WHERE `db_1`.`__tmp_table_1`.id = OLD.id
# 2012-10-12T14:23:40 CREATE TRIGGER mk_osc_upd AFTER UPDATE ON `db_1`.`table_1` FOR EACH ROW REPLACE INTO `db_1`.`__tmp_table_1` (id, pid, sid, create_on) VALUES (NEW.id, NEW.pid, NEW.sid, NEW.create_on)
# 2012-10-12T14:23:40 CREATE TRIGGER mk_osc_ins AFTER INSERT ON `db_1`.`table_1` FOR EACH ROW REPLACE INTO `db_1`.`__tmp_table_1` (id, pid, sid, create_on) VALUES(NEW.id, NEW.pid, NEW.sid, NEW.create_on)
# 2012-10-12T14:23:40 Calling CopyRowsInsertSelect::copy()
Copying rows: 16% 02:30 remain
Copying rows: 33% 02:01 remain
Copying rows: 49% 01:32 remain
Copying rows: 67% 00:56 remain
Copying rows: 86% 00:23 remain
# 2012-10-12T14:26:34 Calling OSCCaptureSync::sync()
# 2012-10-12T14:26:34 Renaming tables
# 2012-10-12T14:26:34 RENAME TABLE `db_1`.`table_1` TO `db_1`.`__old_table_1`, `db_1`.`__tmp_table_1` TO `db_1`.`table_1`
# 2012-10-12T14:26:34 Original table table_1 renamed to __old_table_1
# 2012-10-12T14:26:34 Calling CopyRowsInsertSelect::cleanup()
# 2012-10-12T14:26:34 Calling OSCCaptureSync::cleanup()
# 2012-10-12T14:26:34 DROP TRIGGER IF EXISTS `db_1`.`mk_osc_del`
# 2012-10-12T14:26:34 DROP TRIGGER IF EXISTS `db_1`.`mk_osc_ins`
# 2012-10-12T14:26:34 DROP TRIGGER IF EXISTS `db_1`.`mk_osc_upd`
# 2012-10-12T14:26:34 /usr/bin/pt-online-schema-change ended, exit status 0
经过一段时间的使用，在线改过数据文件达到几十G的innodb表。基本都成功了。
使用--bin-log后，没出现过slave中断的情况。
出现死锁时，把--trunck-size减小，通常就能成功。
