[MySQL] 資料表結構遷移的好幫手 pt-online-schema-change

這邊跟大家介紹一套做 DB migration 的好東西 pt-online-schema-change,這是可以讓 DBA 省時省心力的工具,他可以用簡單的指令讓修改資料表結構時不鎖表(可寫入)與接近零 downtime 的好物。

在開發一套應用程式時,常常會根據不同的需求對資料庫結構進行變更,對 MySQL 而言更是令人頭痛,因為 MySQL 在修改資料表結構時會將資料表鎖住,這會讓伺服器無法正常服務進而造成使用者體驗不佳,但如果你的資料表又剛好是大型資料表更會讓鎖定時間拉長,所以一般的應用程式甚至是銀行業都會選擇在半夜進行修改,盡可能地讓 downtime 時間避開尖峰時段。

因為先前我都是使用 SQL Server 所以沒有遇過這個問題,為了測試我還請朋友幫我在接近 1T 的資料表中加個允許 Null 的欄位,輕輕鬆鬆一瞬間就做完了也沒有 Lock 過長的問題,難道這就是 M$ 有收保護費的成效?雖然 MySQL 5.6 開始為了避免鎖表,加強了這方面的功能讓鎖表變成可讀不可寫,但這使用體驗還是很差啊…

不過既然決議要用 MySQL 了那也沒辦法,我在找減少停機時間的解決方案時碰巧看到這款 tool,他可以用簡單的指令讓修改資料表結構時不鎖表(可寫入)與接近零 downtime … 這不就是我需要的嗎 !

 


 

pt-online-schema-change 的運作流程如下:

  1. 檢查外來鍵是否存在,做相應設置的處理。
  2. 新增一張新的資料表,其結構與來源資料表完全相同
    命名規則:_{source_table_name}_new
  3. 根據 ALTER 語法修改新資料表結構
  4. 在舊資料表上新增 INSERT, UPDATE, DELETE 觸發器,其目的為同步新資料至新資料表
  5. 將舊資料表的資料複製至新資料表
  6. 將新資料表加入外來鍵相關設定
  7. 將兩張資料表的名字做交換,並將原舊資料表名稱為 _old 的後綴,修改方式為原子操作(Atomic)以減少半殘狀態。
    命名規則:_{source_table_name}_old
    RENAME TABLE `exfast`.`tb_test` TO `exfast`.`_tb_test_old`, `exfast`.`_tb_test_new` TO `exfast`.`tb_test`

 


 

為了試驗這邊我準備了一張資料表其結構如下:

接著我要在這個資料表新增一個 age 的欄位,其指令為:

pt-online-schema-change \
--execute \
--progress=time,5 \
--print \
--alter-foreign-keys-method=rebuild_constraints \
--alter="ADD COLUMN age TINYINT" \
D=exfast,t=tb_test,h=172.17.0.2,P=3306,u=root,p=pass

由此可見這麼一小段簡單的指令這個 tool 就幫我們完成了麻煩的 7 個步驟實在是太佛心了吧!

 


 

這邊特別註解 --alter-foreign-keys-method 這個參數,這個參數共有三個值可以設定

  1. rebuild_constraints
    會先將舊 table 的外來鍵 Constraints 刪除再添加至新表中,如果資料表很大的話會造成長時間阻塞,但也是最安全的方法。
  2. drop_swap
    執行 FOREIGN_KEY_CHECKS = 0 禁止外來鍵 Constraints,先刪除原資料表再重命名新資料表。這種方式很快但是有風險:
    1. 在刪除原資料表和重命名新資料表的短時間內,資料表是不存在的程式會拋錯誤。
    2. 如果在重新命名的步驟出現錯誤無法復原。因為原表已經被刪除。
  3. none
    類似 drop_swap 的處理方式,但是它不刪除原資料表,並且外來鍵 Constraints 會通過重新命名轉到老資料表上面。

 

參考資料:

  1. pt-online-schema-change
  2. 使用 pt-online-schema-change 工具不锁表在线修改 MySQL 表结构


這裡的資訊對您有用嗎?歡迎斗內給我