mysql在線修改表結構，如何避免鎖表？一、pt-online-schema-change介紹二、pt-online-schema-change安裝三、pt-online-schema-change使

發布時間：2023-07-03 16:03:22 作者：網友整理

一、pt-online-schema-change介紹

pt-online-schema-change是percona公司開發的一個工具，在percona-toolkit包里面可以找到這個功能，它可以在線修改表結構。而避免被鎖表的情況出現。

1.1原理

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

二、pt-online-schema-change安裝

2.1 pt-online安裝

1.去官網下載對應的版本，官網下載地址:https://www.percona.com/downloads/percona-toolkit/LATEST/

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

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

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

2.2 常用參數說明

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

--alter使用一些限制:
?
1、 原來必須有主鍵或唯一鍵，因為delete觸發器需要用到。否則會報錯。
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、 確保數據庫版本在5.0以上。

--alter-foreign-keys-method

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

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

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

1.參數

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

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

2.為避免每次都要輸入一堆參數，寫個腳本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. 禁止創建唯一索引，會丟失數據，更加不允許添加 --alter-check=no，--check-unique-key-change=no
2. 如果原表沒有主鍵，或者也沒有唯一索引，這些表是不允許用pt做DDL的
3. 禁止對外鍵的表進行pt ddl
4. 禁止對表進行重命名
5. 禁止對列進行重命名，如果一定要做，也必須先print出來檢測清楚列名是否正確
6. 新增字段，NOT NULL必須要指定默認值
7. 不允許刪除主鍵

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