使用場(chǎng)景： 在線修改大數(shù)據(jù)量表結(jié)構(gòu)(ALTER tables without locking them)
文檔參考：https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html
好處:
降低主從延時(shí)的風(fēng)險(xiǎn)
可以限速、限資源蚌成，避免操作時(shí)MySQL負(fù)載過(guò)高
建議:
在業(yè)務(wù)低峰期做，將影響降到最低
直接原表修改缺點(diǎn)：
當(dāng)表的數(shù)據(jù)量很大的時(shí)候，如果直接在線修改表結(jié)構(gòu)，嚴(yán)重影響線上環(huán)境簿盅，而且耗時(shí)不可預(yù)估
注意：
需要確認(rèn)表必須包含主鍵或者唯一索引
工具會(huì)創(chuàng)建觸發(fā)器蜜自，所以原表上不能有觸發(fā)器
有外鍵的表需要注意使用參數(shù)--alter-foreign-keys-method(現(xiàn)在業(yè)務(wù)上不建議表中外鍵關(guān)聯(lián)，建議在業(yè)務(wù)中控制)
原理:
首先它會(huì)新建一張一模一樣的表泄私，表名一般是_new后綴
然后在這個(gè)新表執(zhí)行更改字段操作
然后在原表上加三個(gè)觸發(fā)器，DELETE/UPDATE/INSERT，將原表中要執(zhí)行的語(yǔ)句也在新表中執(zhí)行
最后將原表的數(shù)據(jù)拷貝到新表中晌端，然后替換掉原表
0. 備份數(shù)據(jù)（先做）
1. 安裝
下載安裝包：
解壓
tar -xvf percona-toolkit-3.3.1.tar.gz
安裝一些依賴包
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum -y install perl-Digest-MD5
2. 測(cè)試可用
在解壓包的bin目錄下執(zhí)行,看是否正常捅暴，查看命令
./pt-online-schema-change --help
3. 參數(shù)字段及含義
| 參數(shù) | 含義 |
|---|---|
| --user= | 連接用戶名 |
| --password= | 連接密碼 |
| --host= | 連接IP |
| P= | 端口 |
| --alter= | 執(zhí)行表變更的語(yǔ)句 |
| D= | database 庫(kù)名 |
| t= | table 表名 |
| --charset=utf8 | 使用utf8編碼，避免中文亂碼 |
| --no-check-alter | 不檢查alter語(yǔ)句 |
| 打印操作日志 | |
| --execute | 執(zhí)行修改表結(jié)構(gòu)咧纠，真正的執(zhí)行alter蓬痒，–dry-run與–execute必須指定一個(gè)，二者相互排斥 |
| –dry-run | 創(chuàng)建和修改新表漆羔，但不會(huì)創(chuàng)建觸發(fā)器梧奢、復(fù)制數(shù)據(jù)、和替換原表演痒。并不真正執(zhí)行亲轨，與--print配合查看執(zhí)行細(xì)節(jié) |
4. 具體操作
- 添加一個(gè)字段
如果執(zhí)行失敗，檢查alter語(yǔ)句鸟顺，如果確認(rèn)無(wú)誤 可以避免檢查
--no-check-alter
./pt-online-schema-change --user=xxxx --password=xxxx --host=xxx.xxx.xxx.xxxx --alter "add column group_id bigint(20) not NULL default '0' comment 'test' " P=30306,D=h_pushcenter,t=h_message --charset=utf8 --no-version-check --print --execute
- 修改字段
sql語(yǔ)句:
ALTER TABLE `h_message` MODIFY COLUMN `group_id` int(20) NOT NULL DEFAULT '1'
pt工具
--alter "MODIFY COLUMN group_id int(20) NOT NULL DEFAULT '1'"
- 修改字段名
sql語(yǔ)句
ALTER TABLE `h_message` CHANGE column group_id group_id_0 bigint(20);
pt工具
--alter "CHANGE group_id group_id_0 bigint(20)"
- 添加索引
sql語(yǔ)句
ALTER TABLE `h_message` ADD INDEX h_message_n1(group_id);
pt工具
--alter "ADD INDEX h_message_n1(group_id)"
5.操作日志
- 創(chuàng)建new結(jié)尾的新表
Creating new table...
CREATE TABLE `h_pushcenter`.`_h_message_new` .....
Created new table h_pushcenter._h_message_new OK.
- 新表執(zhí)行alter操作
Altering new table...
ALTER TABLE `h_pushcenter`.`_h_message_new` add column group_id bigint(20) not NULL default '0' comment 'test'
Altered `h_pushcenter`.`_h_message_new` OK.
- 原表上創(chuàng)建3個(gè)觸發(fā)器
Creating triggers...
Event : DELETE
Event : UPDATE
Event : INSERT
Created triggers OK.
- 復(fù)制數(shù)據(jù)到新表
Copying approximately 8187 rows...
Copied rows OK.
- 重命名新舊兩個(gè)表瓶埋，然后替換，刪除舊表
2021-05-19T10:33:08 Swapping tables...
RENAME TABLE `h_pushcenter`.`h_message` TO `h_pushcenter`.`_h_message_old`, `h_pushcenter`.`_h_message_new` TO `h_pushcenter`.`h_message`
2021-05-19T10:33:09 Swapped original and new tables OK.
2021-05-19T10:33:09 Dropping old table...
DROP TABLE IF EXISTS `h_pushcenter`.`_h_message_old`
2021-05-19T10:33:09 Dropped old table `h_pushcenter`.`_h_message_old` OK.
- 刪除觸發(fā)器
2021-05-19T10:33:09 Dropping triggers...
DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_del`
DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_upd`
DROP TRIGGER IF EXISTS `h_pushcenter`.`pt_osc_h_pushcenter_h_message_ins`
2021-05-19T10:33:09 Dropped triggers OK.
- 完成
