MySQL使用pt-ost执行ddl语句

pt-online-schema-change工具，是通过对原始表的数据拷贝来完成的，执行过程中会短暂锁表，但是锁粒度较小，对原表影响很小。

pt-ost执行过程：
1)创建与原始表结构一样的新表，以"_table1_new"命名。
2)在新表上执行DDL。
3)在原始表上创建触发器，将原始表上的变化数据同步到新表。
4)拷贝原表数据到新表。
5)新旧表转换，RENAME 原表名 to 原表_old, rename 新表名 to 原表名， drop 原表_old;

执行过程中有两个问题需注意：
1)表上不能有触发器。
2)表上有外键，自动rename原始表和新表的操作就不能顺利进行，必须在数据拷贝完成后将外键更新到新表上，建议表上有外键时加参数--alter-foreign-keys-method=rebuild_constraints

参数解释：
h=10.2.139.10 数据库服务器地址
P=3306 数据库端口
u=root 数据库用户名
D=sysbenchdb 数据库schema
t=sbtest1 数据库schema中的表
--ask-pass 手工输入数据库密码
--alter ddl语句，标准语句去掉开头的alter table
--chunk-size=200 每次从原表中复制的行数
--print 打印操作信息
--execute 执行

示例如下：

创建索引

time pt-online-schema-change h=10.2.139.10,P=3306,u=root,D=sysbenchdb,t=sbtest1 --ask-pass --alter "add key idx_c(c)" --chunk-size=200 --print --execute
time pt-online-schema-change h=10.2.139.10,P=3306,u=root,D=sysbenchdb,t=sbtest1 --ask-pass --alter "add key idx_b(b), add key idx_c(c)" --chunk-size=200 --print --execute

删除索引

time pt-online-schema-change h=10.2.139.10,P=3306,u=root,D=sysbenchdb,t=sbtest1 --ask-pass --alter "drop key idx_c" --chunk-size=200 --print --execute

增加列

time pt-online-schema-change h=10.2.139.10,P=3306,u=root,D=sysbenchdb,t=sbtest1 --ask-pass --alter "ADD COLUMN c1 INT" --chunk-size=200 --print --execute

删除列

time pt-online-schema-change h=10.2.139.10,P=3306,u=root,D=sysbenchdb,t=sbtest1 --ask-pass --alter "DROP COLUMN c1 INT" --chunk-size=200 --print --execute

OPTIMIZE TABLE造成的负载较大，可以改用pt-OSC来执行

time pt-online-schema-change h=10.2.139.10,P=3306,u=root,D=sysbenchdb,t=sbtest1 --ask-pass --alter "engine=innodb" --chunk-size=200 --print --execute

【安全性保障措施】

1. 默认如果检测到有复制过滤会拒绝改表，修改参数为--[no]check-replication-filters
2. 默认如果检测到主从复制延迟会自动停止数据拷贝，调节参数为--max-lag
3. 默认如果检测到服务器负载过重会停止或中断操作，调节参数为--max-load和--critical-load
4. 默认会设置锁等待超时时间为1s来避免干扰其他事务的进行，调节参数为--lock-wait-timeout
5. 默认如果检测到外键冲突后会拒绝改表，调节参数为--alter-foreign-keys-method
6. 该工具不能在PXC（Percona XtraDB Cluster）集群中对myisam表进行改表操作

如果在执行pt-osc时，终端断开，此时pt-ost执行的ddl语句会中止，如下

Copying `sysbenchdb`.`sbtest1`:  57% 00:22 remain
^C# Exiting on SIGINT.  <---此处ctrl+C模拟中断
Not dropping triggers because the tool was interrupted.  To drop the triggers, execute:
DROP TRIGGER IF EXISTS `sysbenchdb`.`pt_osc_sysbenchdb_sbtest1_del`
DROP TRIGGER IF EXISTS `sysbenchdb`.`pt_osc_sysbenchdb_sbtest1_upd`
DROP TRIGGER IF EXISTS `sysbenchdb`.`pt_osc_sysbenchdb_sbtest1_ins`
Not dropping the new table `sysbenchdb`.`_sbtest1_new` because the tool was interrupted.  To drop the new table, execute:
DROP TABLE IF EXISTS `sysbenchdb`.`_sbtest1_new`;
`sysbenchdb`.`sbtest1` was not altered.

手工执行以上语句，清除原表上创建的trigger并删除新表"_table1_new"，再次执行即可。
如果不清除原表trigger，无法再次执行，如下：

The table `sysbenchdb`.`sbtest1` has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers.

查看sysbenchdb库下的触发器

mysql> show triggers from sysbenchdb\G; 
也可查询information_schema.triggers视图
*************************** 1. row ***************************
             Trigger: pt_osc_sysbenchdb_sbtest1_ins
               Event: INSERT
               Table: sbtest1
           Statement: REPLACE INTO `sysbenchdb`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)
              Timing: AFTER
             Created: NULL
            sql_mode: ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: pt_osc_sysbenchdb_sbtest1_upd
               Event: UPDATE
               Table: sbtest1
           Statement: BEGIN DELETE IGNORE FROM `sysbenchdb`.`_sbtest1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `sysbenchdb`.`_sbtest1_new`.`id` <=> OLD.`id`;REPLACE INTO `sysbenchdb`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`);END
              Timing: AFTER
             Created: NULL
            sql_mode: ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 3. row ***************************
             Trigger: pt_osc_sysbenchdb_sbtest1_del
               Event: DELETE
               Table: sbtest1
           Statement: DELETE IGNORE FROM `sysbenchdb`.`_sbtest1_new` WHERE `sysbenchdb`.`_sbtest1_new`.`id` <=> OLD.`id`
              Timing: AFTER
             Created: NULL
            sql_mode: ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
3 rows in set (0.00 sec)

ERROR: 
No query specified

查询sysbenchdb库下的表

mysql> show tables;
+----------------------+
| Tables_in_sysbenchdb |
+----------------------+
| _sbtest1_new         |
| sbtest1              |
| sbtest10             |
| sbtest2              |
| sbtest3              |
| sbtest4              |
| sbtest5              |
| sbtest6              |
| sbtest7              |
| sbtest8              |
| sbtest9              |
+----------------------+
11 rows in set (0.00 sec)

为避免终端断开造成执行操作中止，建议使用screen操作。