文中案例說明

需要在大表中新增一個欄位group_id,表中有個1千萬的資料量，而且有幾個索引欄位，如果直接在客戶端通過sql ALTER TABLE h_app_message ADD group_id bigint(20) 會造成資料庫卡頓，耗費時間很長,直接影響線上正常使用。

一般對於較大資料量的修改方式，如果線上併發不是很高的情況下是可以手動處理的，方式如下：

• 首先備份，備份，備份，重要的事情說三遍
• 刪除表中的索引
• 修改表結構
• 修復資料
• 恢復索引
• 表結構修改完成

我們的處理方式是通過pt-online-schame-change 工具線上修改正式環境的欄位，新增group_id。

此工具的好處：

• 降低主從延時的風險
• 可以限速、限資源，避免操作時MySQL負載過高

建議：

在業務低峰期做，將影響降到最低

直接原表修改缺點：

當表的資料量很大的時候，如果直接線上修改表結構，嚴重影響線上環境，而且耗時不可預估

注意：

• 需要確認表必須包含主鍵或者唯一索引
• 工具會創建觸發器，所以原表上不能有觸發器
• 有外來鍵的表需要注意使用參數--alter-foreign-keys-method(現在業務上不建議表中外來鍵關聯，建議在業務中控制)

原理:

• 首先它會新建一張一模一樣的表，表名一般是_new字尾
• 然後在這個新表執行更改欄位操作
• 然後在原表上加三個觸發器，DELETE/UPDATE/INSERT，將原表中要執行的語句也在新表中執行
• 最後將原表的資料拷貝到新表中，然後替換掉原表

1、資料備份

無論操作多麼有把握，也要把備份做好(萬一很可怕的)

2、安裝

下載安裝包：

wget  https://downloads.percona.com/downloads/percona-toolkit/3.3.1/source/tarball/percona-toolkit-3.3.1.tar.gz

解壓：

tar -xvf percona-toolkit-3.3.1.tar.gz

安裝一些依賴包：

yum install perl-DBIyum install perl-DBD-MySQLyum install perl-Time-HiResyum install perl-IO-Socket-SSLyum -y install perl-Digest-MD5

3、測試可用

在解壓包的bin目錄下執行,看是否正常，檢視命令

./pt-online-schema-change --help

4、參數欄位及含義

5、具體操作

1. 新增一個欄位

如果執行失敗，檢查alter語句，如果確認無誤 可以避免檢查 --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

2. 修改欄位

sql語句：

ALTER TABLE `h_message` MODIFY COLUMN `group_id` int(20)  NOT NULL DEFAULT '1';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'"

3. 修改欄位名

sql語句:

ALTER TABLE `h_message` CHANGE column group_id group_id_0 bigint(20);

pt命令:

--alter "CHANGE group_id group_id_0 bigint(20)"

4. 新增索引

sql語句:

ALTER TABLE `h_message` ADD INDEX h_message_n1(group_id);

pt命令：

--alter "ADD INDEX h_message_n1(group_id)"

6、操作日誌

• 創建new結尾的新表

Creating new table...CREATE TABLE `h_pushcenter`.`_h_message_new` .....Created new table h_pushcenter._h_message_new OK.

• 新表執行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.

• 原表上創建3個觸發器

Creating triggers...Event : DELETEEvent : UPDATEEvent : INSERTCreated triggers OK.

• 複製資料到新表

Copying approximately 8187 rows...Copied rows OK.

• 重新命名新舊兩個表，然後替換，刪除舊錶

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.

• 刪除觸發器

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.

• 打完收工

作者：紀先生
連結：https://juejin.cn/post/6993339537824940046
來源：掘金