一、pt-online-schema-change介紹

pt-online-schema-change是percona公司開發(fā)的一個工具，在percona-toolkit包里面可以找到這個功能，它可以在線修改表結(jié)構(gòu)。而避免被鎖表的情況出現(xiàn)。

1.1原理

step1: 它會新建一張一模一樣的表，表名一般是_new后綴
step2: 在這個新表執(zhí)行更改字段操作
step3: 在原表上加三個觸發(fā)器，DELETE/UPDATE/INSERT，將原表中要執(zhí)行的語句也在新表中執(zhí)行
step4: 最后將原表的數(shù)據(jù)拷貝到新表中，然后替換掉原表

二、pt-online-schema-change安裝

2.1 pt-online安裝

1.去官網(wǎng)下載對應(yīng)的版本，官網(wǎng)下載地址:https://www.percona.com/downloads/percona-toolkit/LATEST/

2.下載解壓之后就可以看到pt-online-schema-change

3.該工具需要一些依賴包，直接執(zhí)行不成功時一般會有提示，這里可以提前yum安裝

yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL

2.2 常用參數(shù)說明

--dry-run ：打印輸出
--execute：執(zhí)行
--alter:通過此選項，不需要alter table關(guān)鍵字了。可以通過逗號指定多個修改操作。

--alter使用一些限制:
?
1、 原來必須有主鍵或唯一鍵，因為delete觸發(fā)器需要用到。否則會報錯。
2、 rename子句，不允許給表重命令
3、 不能通過刪除一列，然后再新增一列的方式來完成對列的重命名操作。
4、 新增字段如果是not null，必須指定default值，否則報錯。
5、 如果是DROP FOREIGN KEY constraint_name ， 那么必須指定 _ 加上 constraint_name ， 而不是 constraint_name。
 舉例： CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)
 你必須指定： --alter "DROP FOREIGN KEY _fk_foo" 而不是 --alter "DROP FOREIGN KEY fk_foo".
6、 確保數(shù)據(jù)庫版本在5.0以上。

--alter-foreign-keys-method

該工具有兩種方法,可以自動找到子表,并修改約束關(guān)系。
1、auto： 在rebuild_constraints和drop_swap兩種處理方式中選擇一個。
2、rebuild_constraints：使用 ALTER TABLE語句先刪除外鍵約束,然后再添加.如果子表很大的話,會導(dǎo)致長時間的阻塞。
3、drop_swap： 執(zhí)行FOREIGN_KEY_CHECKS=0,禁止外鍵約束,刪除原表,再重命名新表。這種方式很快,也不會產(chǎn)生阻塞,但是有風(fēng)險：
 （1） 在刪除原表和重命名新表的短時間內(nèi),表是不存在的,程序會返回錯誤。
 （2） 如果重命名表出現(xiàn)錯誤,也不能回滾了.因為原表已經(jīng)被刪除。
4、none： 類似"drop_swap"的處理方式,但是它不刪除原表,并且外鍵關(guān)系會隨著重命名轉(zhuǎn)到老表上面。

--host=xxx --user=xxx --password=xxx
連接數(shù)據(jù)庫的主機、用戶和密碼，可以縮寫-h xxx -u xxx -p xxx，密碼可以使用參數(shù)--ask-pass 手動輸入。
D=db_name,t=table_name
指定要ddl的數(shù)據(jù)庫名和表名
--charset
最好設(shè)置為MySQL默認(rèn)字符集： utf8
--[no]swap-tables
默認(rèn)yes。交換原始表和新表，除非你禁止--[no]drop-old-table。
--check-interval
默認(rèn)1秒，檢測--max-lag
--[no]check-replication-filters
默認(rèn)值為yes,如果發(fā)現(xiàn)任何服務(wù)器有 binlog_ignore_db and replicate_do_db ，那么就報錯。
--check-slave-lag
指定一個從庫的DSN連接地址,如果從庫超過--max-lag參數(shù)設(shè)置的值,就會暫停操作。
--[no]swap-tables
默認(rèn)yes。交換原始表和新表，除非你禁止--[no]drop-old-table。
--max-lag
默認(rèn)1s。每個chunk拷貝完成后，會查看所有復(fù)制Slave的延遲情況。
要是延遲大于該值，則暫停復(fù)制數(shù)據(jù)，直到所有從的滯后小于這個值。
如果有任何從滯后超過此選項的值，則該工具將睡眠--check-interval指定的時間，再檢查。如果從被停止，將會永遠(yuǎn)等待，直到從開始同步，并且延遲小于該值。
如果指定--check-slave-lag，該工具只檢查該服務(wù)器的延遲，而不是所有服務(wù)器。
--print
打印SQL語句到標(biāo)準(zhǔn)輸出。指定此選項可以讓你看到該工具所執(zhí)行的語句，和--dry-run配合最佳。
--progress
復(fù)制數(shù)據(jù)的進(jìn)度報告，二部分組成：第一部分是百分比，第二部分是時間
--set-vars
設(shè)置mysql變量，多個用逗號分割。默認(rèn)該工具設(shè)置的是： wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60

三、pt-online-schema-change使用展示

1.參數(shù)

./bin/pt-online-schema-change --help 可以查看參數(shù)的使用，我們只是要修改個表結(jié)構(gòu)，只需要知道幾個簡單的參數(shù)就可以了

--user= 連接mysql的用戶名
--password= 連接mysql的密碼
--host= 連接mysql的地址
P=3306 連接mysql的端口號
D= 連接mysql的庫名
t= 連接mysql的表名
--alter 修改表結(jié)構(gòu)的語句
--execute 執(zhí)行修改表結(jié)構(gòu)
--charset=utf8 使用utf8編碼，避免中文亂碼
--no-version-check 不檢查版本，在阿里云服務(wù)器中一般加入此參數(shù)，否則會報錯

2.為避免每次都要輸入一堆參數(shù)，寫個腳本pt.sh

#!/bin/bash
table=$1
alter_conment=$2
?
cnn_host='127.0.0.1'
cnn_user='user'
cnn_pwd='password'
cnn_db='database_name'
?
echo "$table"
echo "$alter_conment"
/root/percona-toolkit-2.2.19/bin/pt-online-schema-change --charset=utf8 --no-version-check --user=${cnn_user} --password=${cnn_pwd} --host=${cnn_host} P=3306,D=${cnn_db},t=$table --alter 
"${alter_conment}" --execute

3.添加表字段

如添加表字段SQL語句為:

ALTER TABLE `tb_test` ADD COLUMN `column1` tinyint(4) DEFAULT NULL;

那么使用pt-online-schema-change則可以這樣寫

sh pt.sh tb_test "ADD COLUMN column1 tinyint(4) DEFAULT NULL"

4.修改表字段

SQL語句：

ALTER TABLE `tb_test` MODIFY COLUMN `num` int(11) unsigned NOT NULL DEFAULT '0';

pt-online-schema-change工具:

sh pt.sh tb_test "MODIFY COLUMN num int(11) unsigned NOT NULL DEFAULT '0'"

5.修改表字段名

SQL語句:

ALTER TABLE `tb_test` CHANGE COLUMN age adress varchar(30);

pt-online-schema-change工具:

sh pt.sh tb_test "CHANGE COLUMN age address varchar(30)"

6.添加索引

SQL語句:

ALTER TABLE `tb_test` ADD INDEX idx_address(address);

pt-online-schema-change工具:

sh pt.sh tb_test "ADD INDEX idx_address(address)"

四、注意事項

禁止的一些ddl

1. 禁止創(chuàng)建唯一索引，會丟失數(shù)據(jù)，更加不允許添加 --alter-check=no，--check-unique-key-change=no
2. 如果原表沒有主鍵，或者也沒有唯一索引，這些表是不允許用pt做DDL的
3. 禁止對外鍵的表進(jìn)行pt ddl
4. 禁止對表進(jìn)行重命名
5. 禁止對列進(jìn)行重命名，如果一定要做，也必須先print出來檢測清楚列名是否正確
6. 新增字段，NOT NULL必須要指定默認(rèn)值
7. 不允許刪除主鍵

由于rowcopy會產(chǎn)業(yè)很多的binlog，所以做之前要確保binlog空間、數(shù)據(jù)空間有足夠空間可用。
禁止在業(yè)務(wù)高峰期進(jìn)行pt-online-schema-change操作
原表不能有觸發(fā)器
MySQL最好設(shè)置為innodb_autoinc_lock_mode=2，否則在高并發(fā)的寫入情況下，很容易產(chǎn)生所等待以及死鎖
master的表結(jié)構(gòu)必須跟slave的表結(jié)構(gòu)一致，不允許異構(gòu)，否則pt-online-schema-change的原理就是會rename，然后slave不一致的表結(jié)構(gòu)會被master覆蓋，切記!