gh-ost 無鎖編輯資料結構

gh-ost （Github online schema migration）是針對 MySQL 的無觸發器（triggerless）線上架構遷移方案，一個主要的用途就是在不鎖定資料表（或僅在切換階段短暫鎖表）的情況下進行表格變更。相較於其他工具如 Percona 的 pt-online-schema-change、Facebook 的 OSC 或 oak-online-alter-table，gh-ost 避免使用觸發器，改用二進制日誌（binlog）來同步資料，降低對主庫的負載。

傳統方式的流程

1. 建立新的資料表（鏡像表）應用目標結構變更。
2. 在原本的資料表建立觸發器，同步 DML 操作（INSERT、UPDATE、DELETE）到鏡像表。
3. 複製現有資料到鏡像表。
4. 完成後切換表名（原表與鏡像表交換）。

缺點：觸發器增加主庫負載，可能影響效能，且不適用於高併發場景。

gh-ost 無觸發器的方式

1. 解析 MySQL 二進制日誌 binlog 取得數據變更
2. 在鏡像表上直接應用 binlog 事件，同步資料。
3. 完成資料同步後，於主庫執行短暫鎖表，切換原表與鏡像表。

優點：

• 無鎖表：遷移過程（除 cut-over 階段）不鎖表，DML 操作不受影響。
• 可測試性：支援 dry-run 驗證可行性。
• 可暫停性：可通過 flag 檔案暫停或中止遷移。
• 動態控制：可調整 chunk-size、負載閾值等參數。
• 審計功能：詳細日誌記錄遷移過程。

它提供了可測試性，可暫停性，動態控制和重新配置，審計等。簡單說可以只驗證可行性不實際執行，執行時可以暫停，動態調整併發等優點。 gh-ost 整個變更過程在主機上耗費的效能很小，將變更和現有工作負載分離。

安裝

在 macOS 上使用 Homebrew 安裝

$ brew install gh-ost
$ gh-ost --version

前置條件

gh-ost 要求主庫和從庫（若使用安全模式）啟用二進制日誌並配置為 ROW 格式。以下是不同情境的設置要求：

情境	範例架構	連線目標	需要設定的參數	說明
直連 Master	gh-ost 操作 Master	Master	log_bin=ON， binlog_format=ROW	Master 必須開 binlog 並使用 RBR，gh-ost 才能讀取事件並安全複製資料。RBR = Row-Based Replication。RBR (binlog_format=ROW)。binlog_row_image=FULL 確保記錄完整行資料。
連 Replica（安全模式）	gh-ost 操作 Replica	Master	log_bin=ON ，binlog_format=ROW	Master 要能寫 binlog，並且是 RBR
		Replica	log_bin=ON，binlog_format=ROW，log_slave_updates=ON	Replica 需將 Master 的 binlog 事件寫入自身 binlog，供 gh-ost 讀取。log_slave_updates=ON 確保從庫記錄複製事件。

-- 檢查 binlog 設置
SHOW VARIABLES LIKE 'log_bin';                    -- 必須是 ON
SHOW VARIABLES LIKE 'binlog_format';              -- 必須是 ROW  
SHOW VARIABLES LIKE 'log_replica_updates';        -- 如有 replica 必須是 ON
SHOW VARIABLES LIKE 'binlog_row_image';         -- 建議為 FULL
SHOW VARIABLES LIKE 'log_slave_updates';

關於 binlog_row_image：

官方文件未明確要求 binlog_row_image=FULL，但這是最佳實踐。FULL 確保 binlog 記錄完整行資料，適用於複雜表結構或大量列的情況。若設為 MINIMAL 或 NOBLOB，可能導致 gh-ost 無法正確追蹤變更，造成資料不一致。

若 Cloud SQL log_bin 為 OFF

若 Cloud SQL 的 log_bin 為 OFF，需通過 GCP 指令設置。

$ gcloud config list
$ gcloud projects list
$ gcloud config set project [YOUR-PROJECT]

$ gcloud sql instances patch [SQL_INSTANCE_REPLICA_NAME] --enable-bin-log

⚠️ 重啟實例後生效，可能導致短暫停機。

• 問題參考

建立 gh-ost 使用者

gh-ost 需要特定權限以執行遷移，可以建立專用用戶，或者可以使用原本已經建立的帳號，但是權限須符合。

GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE ON my_schema.* TO 'gh-ost'@'%' IDENTIFIED BY '123456';

GRANT SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'gh-ost'@'%';

測試指令（dry-run）

$ gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=500 \
--throttle-control-replicas="[REPLICA IP]" \
--max-lag-millis=1500 \
--user="[使用者帳號]" \
--password="[密碼]" \
--host=[REPLICA IP] \
--database="[資料庫名稱]" \
--table="[資料表]" \
--verbose \
--alter="範例：ADD COLUMN utm json COMMENT '加入欄位'" \
--assume-rbr \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag

參數說明

• --max-load=Threads_running=25：若伺服器線程數超過 25，暫停遷移。
• --critical-load=Threads_running=1000：若線程數超過 1000，中止遷移。
• --chunk-size=500：每次處理 500 行，適合 1051 萬筆資料。
• --throttle-control-replicas="34.80.17.106"：監控 Replica 延遲。
• --max-lag-millis=1500：若 Replica 延遲超過 1.5 秒，暫停遷移。
• --assume-rbr：假設 binlog 為 ROW 格式，繞過 Cloud SQL 權限限制。Cloud SQL 須避免使用 --switch-to-rbr
• --cut-over=default：短暫鎖表切換，確保資料一致。
• --postpone-cut-over-flag-file：延遲切換直到移除 flag 檔案。

檢查是否有鎖表：

SHOW FULL PROCESSLIST;

執行

$ gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=500 \
--throttle-control-replicas="34.80.17.106" \
--max-lag-millis=1500 \
--user="andy.you" \
--password="k3]%Rf_?9]^4" \
--host=34.80.17.106 \
--database="uspace_prod" \
--table="orders" \
--verbose \
--alter="ADD COLUMN utm json COMMENT '加入欄位'" \
--assume-rbr \
--allow-master-master \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--initially-drop-ghost-table \
--execute

⚠️ 重點

gh-ost 為了避免在機器執行繁重的任務時直接切換表格造成中斷，因此提供一個讓我們控制切換的機制。

當 100% 執行完成之後，要完成最後的切換需要刪除控制的暫時檔案 rm /tmp/ghost.postpone.flag

2025-08-18 11:43:26 INFO Copy: 10754189/10754189 100.0%; Applied: 0; Backlog: 0/1000; Time: 1h46m53s(total), 1h32m6s(copy); streamer: mysql-bin.000066:42833482; Lag: 0.07s, HeartbeatLag: 0.07s, State: postponing cut-over; ETA: due []
Copy: 10754189/10754189 100.0%; Applied: 0; Backlog: 0/1000; Time: 1h46m54s(total), 1h32m6s(copy); streamer: mysql-bin.000066:42837620; Lag: 0.21s, HeartbeatLag: 0.17s, State: postponing cut-over; ETA: due


$ rm /tmp/ghost.postpone.flag

參考資源

• MySQL 最佳实践：gh-ost 工具使用详解