MySQL使用pt-ost執(zhí)行ddl語句
pt-online-schema-change工具刷钢，是通過對原始表的數(shù)據(jù)拷貝來完成的，執(zhí)行過程中會短暫鎖表，但是鎖粒度較小，對原表影響很小麻汰。
pt-ost執(zhí)行過程：
1)創(chuàng)建與原始表結(jié)構(gòu)一樣的新表，以"_table1_new"命名戚篙。
2)在新表上執(zhí)行DDL五鲫。
3)在原始表上創(chuàng)建觸發(fā)器，將原始表上的變化數(shù)據(jù)同步到新表岔擂。
4)拷貝原表數(shù)據(jù)到新表位喂。
5)新舊表轉(zhuǎn)換，RENAME 原表名 to 原表_old, rename 新表名 to 原表名乱灵， drop 原表_old;
執(zhí)行過程中有兩個問題需注意：
1)表上不能有觸發(fā)器忆某。
2)表上有外鍵，自動rename原始表和新表的操作就不能順利進行阔蛉，必須在數(shù)據(jù)拷貝完成后將外鍵更新到新表上弃舒，建議表上有外鍵時加參數(shù)--alter-foreign-keys-method=rebuild_constraints
參數(shù)解釋：
h=10.2.139.10 數(shù)據(jù)庫服務(wù)器地址
P=3306 數(shù)據(jù)庫端口
u=root 數(shù)據(jù)庫用戶名
D=sysbenchdb 數(shù)據(jù)庫schema
t=sbtest1 數(shù)據(jù)庫schema中的表
--ask-pass 手工輸入數(shù)據(jù)庫密碼
--alter ddl語句，標(biāo)準(zhǔn)語句去掉開頭的alter table
--chunk-size=200 每次從原表中復(fù)制的行數(shù)
--print 打印操作信息
--execute 執(zhí)行
示例如下：
創(chuàng)建索引
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造成的負(fù)載較大状原，可以改用pt-OSC來執(zhí)行
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
【安全性保障措施】
- 默認(rèn)如果檢測到有復(fù)制過濾會拒絕改表聋呢，修改參數(shù)為--[no]check-replication-filters
- 默認(rèn)如果檢測到主從復(fù)制延遲會自動停止數(shù)據(jù)拷貝，調(diào)節(jié)參數(shù)為--max-lag
- 默認(rèn)如果檢測到服務(wù)器負(fù)載過重會停止或中斷操作颠区，調(diào)節(jié)參數(shù)為--max-load和--critical-load
- 默認(rèn)會設(shè)置鎖等待超時時間為1s來避免干擾其他事務(wù)的進行削锰，調(diào)節(jié)參數(shù)為--lock-wait-timeout
- 默認(rèn)如果檢測到外鍵沖突后會拒絕改表，調(diào)節(jié)參數(shù)為--alter-foreign-keys-method
- 該工具不能在PXC（Percona XtraDB Cluster）集群中對myisam表進行改表操作
如果在執(zhí)行pt-osc時毕莱，終端斷開器贩，此時pt-ost執(zhí)行的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.
手工執(zhí)行以上語句，清除原表上創(chuàng)建的trigger并刪除新表"_table1_new"蛹稍，再次執(zhí)行即可吧黄。
如果不清除原表trigger，無法再次執(zhí)行唆姐，如下：
The table `sysbenchdb`.`sbtest1` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.
查看sysbenchdb庫下的觸發(fā)器
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)
為避免終端斷開造成執(zhí)行操作中止拗慨，建議使用screen操作。
