1. MDL 鎖介紹

MySQL 的鎖可以分為四類：MDL 鎖、表鎖、行鎖、GAP 鎖，其中除了 MDL 鎖是在 Server 層加的之外，其它三種都是在 InnoDB 層加的。

下面主要介紹一下：MDL 元數(shù)據(jù)鎖，主要作用就是維護 DDL 過程中數(shù)據(jù)的安全性 & 正確性。

當(dāng)對一個表進行 DML 時，需要加 MDL 讀鎖，當(dāng)需要對一張表結(jié)構(gòu)進行變更時，需要加 MDL 寫鎖。讀鎖之間不互斥，即可以多個線程對一張表進行并發(fā)增刪改。讀寫鎖與寫鎖，之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執(zhí)行完才能開始執(zhí)行。

讀操作與寫操作，都需要加 MDL 讀鎖，而 DDL 需要加 MDL 寫鎖，兩者互斥的，那么 Online DDL 如何保障并發(fā) DML 呢，這里就要介紹 Online DDL 加鎖過程：

• 首先，在開始進行 DDL 時，需要拿到對應(yīng)表的 MDL 寫鎖，然后進行一系列的準備工作；
• 然后MDL 寫鎖降級為MDL 讀鎖，開始真正的 DDL；
• 最后再次將 MDL 讀鎖升級為 MDL 寫鎖，完成 DDL 操作，釋放 MDL 鎖；

其中第二階段占用了 DDL 整個過程的大量時間，在這段時間 DDL 才是真正的 online。

那么問題來了，如果有一個大查詢持有 MDL 讀鎖，那么我們此時進行一個 DDL 操作后，因為查詢持有讀鎖，DDL 需要加寫鎖，所以變更必須等待查詢結(jié)束才能進行，此時再進行一個查詢就會被卡住，請看案例??

Session 1 開啟一個事物，執(zhí)行一條查詢，此時 Session 1 持有 MDL 讀鎖；
Session 2 也執(zhí)行一條查詢，執(zhí)行一條查詢，正常執(zhí)行；
Session 3 執(zhí)行一條 DDL 語句，因為需要 MDL 寫鎖，被 Session 1 讀鎖；
Session 4 執(zhí)行一條查詢，因為需要讀鎖，但是因為 Session 3 也處于等待狀態(tài)，后面的增刪改查都處于等待狀態(tài)。也就是說這張表完全不可用了。

即使變更支持 online 也非常怕長事務(wù)，所以生產(chǎn)環(huán)境大表變更前，我們可以去查會話和 innodb_trx 表，確認沒有長事務(wù)，避免變更被 MDL 讀鎖堵塞。

看了上面的案例，我們理想狀態(tài)下 DDL 變更是什么樣的呢？

• DDL 請求鎖時可以帶上超時時間，即使拿不到鎖也不會影響后面的業(yè)務(wù)語句；
• 對數(shù)據(jù)庫的性能影響最小，大表變更非常令人頭疼；
• 不會給從庫帶來太大延遲，大表變更后從庫往往延遲十幾分鐘，讀寫分離的話會影響業(yè)務(wù)查詢；

2. 變更工具

MySQL 變更比較常用的工具有大名鼎鼎的 PT 生態(tài) **percona pt-online-schema-change **和 Facebook OSC 但是它們都是基于觸發(fā)器來實現(xiàn)的，簡單來講就是通過數(shù)據(jù)庫的觸發(fā)器把作用在源表的操作在一個事務(wù)內(nèi)同步到修改后的表中，這在業(yè)務(wù)高峰期時會極大的加重主庫的負載。

gh-ost 是由 Github 開發(fā)的一款開源 **online DDL **工具，使用訂閱和過濾 binlog 的方式代替原來的觸發(fā)器來做的增量數(shù)據(jù)同步，這樣可以降低主庫負載，異步的執(zhí)行。

使用 gh-ost 的優(yōu)點：

• 不會給主庫帶來太大負載；
• 不會導(dǎo)致從庫有太大延遲，大表變更經(jīng)常會導(dǎo)致從庫延遲很長時間；
• 支持阿里云 RDS，為云上變更提供了參數(shù) –aliyun-rds；
• 通過 binlog 實現(xiàn)增量數(shù)據(jù)的獲取，基本做到了原子性的切換；
• 可暫停，動態(tài)控制限流，切換時間可設(shè)定；

限制：

• 使用 DTS 做數(shù)據(jù)同步，可能會導(dǎo)致同步失敗，僅影響同步對象；
• 不能對外鍵關(guān)系及觸發(fā)器的表進行 online DDL；
• 若有同名但是字母大小寫不同的表則無法進行修改；
• 表必須有主鍵或者唯一索引；

3. gh-ost 原理解析

官方圖解 (https://github.com/github/gh-ost)

主要執(zhí)行過程：

• 檢查是否有外鍵觸發(fā)器及主鍵信息；
• 檢查是否主庫或從庫，是否開啟 log_slave_updates 以及 binlog 信息；
• 檢查 gho 和 ghc 結(jié)尾的臨時表是否存在；
• 創(chuàng)建 ghc 結(jié)尾的表，存數(shù)據(jù)遷移的信息，以及 binlog 信息等；
• 初始化 stream 的連接，添加 binlog 的監(jiān)聽；
• 根據(jù) alter 語句創(chuàng)建 gho 結(jié)尾的幽靈表；
• 開啟遷移數(shù)據(jù)，按照主鍵把源表數(shù)據(jù)寫入到 gho 結(jié)尾的表上，以及 binlog apply；
• 進入 cut-over 階段，鎖住主庫的源表，等待 binlog 應(yīng)用完畢，然后替換 gh-ost 表為源表；
• 清理 ghc 表，刪除 socket 文件。

cut-over 即表 rename 階段，gh-ost 利用了 MySQL 的一個特性，原子性的 rename 請求，在所有被 blocked 的請求中，rename 優(yōu)先級永遠是最高的。gh-ost 基于此設(shè)計了該方案：一個連接對原表加鎖，另啟一個連接嘗試 rename 操作，此時會被阻塞住，當(dāng)釋放 lock 的時候，rename 會首先被執(zhí)行，其他被阻塞的請求會繼續(xù)應(yīng)用到新表。

4. 安裝部署

下載地址 gh-ost GA 版本存檔 二進制包，開箱即用。

5. 操作演示

5.1. 重點參數(shù)介紹

下面介紹 gh-ost 中重點參數(shù)：

• -execute：如果不添加該參數(shù)，僅進行環(huán)境檢查測試，不實際執(zhí)行；
• -assume-rbr：如果用戶沒有 Super 權(quán)限的話，需要加上這個參數(shù)，這樣 gh-ost 會認為 Binlog 本身就是 row 模式，不會再去修改；
• -max-load：可以添加一些 MySQL 狀態(tài)變量閾值，例如：--max-load=Threads_running=20則表示 MySQL 線程數(shù)大于 20 則進入限流模式，使用逗號分隔指定多個狀態(tài)變量；
• -critical-load：可以添加一些 MySQL 狀態(tài)變量閾值，如果超過閾值則暫停工作，等待一段時間后重試，由 -critical-load-interval-milli來設(shè)置等待多少毫秒；
• -chunk-size：在每次迭代中處理的行數(shù)量(允許范圍：100-100000)，默認值為1000；
• -dml-batch-size：在單個事務(wù)中應(yīng)用 DML 事件的批量大?。ǚ秶?-100），默認值為10；
• -cut-over-lock-timeout-seconds：gh-ost 在 cut-over 階段最大鎖等待時間，當(dāng)鎖超時時，gh-ost 的 cut-over 將重試；
• -discard-foreign-keys：有風(fēng)險，加上該參數(shù)，表如果與外鍵約束，幽靈表遷移后不會保留，如果是故意想清理外鍵時，比較有用；
• -exact-rowcount：如果指定該參數(shù)，則使用 select count() 來統(tǒng)計表行數(shù)，不添加則是使用 explain 來預(yù)估行數(shù)，會影響進度統(tǒng)計，大表 count() 還是有風(fēng)險的，建議不使用；
• -heartbeat-interval-millis：多久寫入一條日志信息，默認 100 毫秒；
• -ok-to-drop-table：切換完成后，是否刪除源表？drop 大表是一個非常消耗 IO 的操作，默認切換后不刪除源表，如果需要切換完成立即刪除源表需要指定該參數(shù)；
• -timestamp-old-table：在舊表名中使用時間戳。這會使舊表名稱具有唯一且無沖突的交叉遷移；
• -postpone-cut-over-flag-file：創(chuàng)建 flag 文件，表遷移完成后，進入等待 cut-over 階段，直到該文件被刪除，通過該參數(shù)用戶可以自定義切換時間；
• -panic-flag-file：當(dāng)該文件被創(chuàng)建，則立即停止 gh-ost 工作，直接停止，不會進行環(huán)境清理；
• -throttle-additional-flag-file：當(dāng)該文件被創(chuàng)建，則暫停 gh-ost 工作；
• -aliyun-rds：阿里云 RDS 使用需要添加該參數(shù)，可以繞開非法字符校驗，正常使用。

上面就是常用的參數(shù)，用戶可以控制切換時間，鎖超時時間，以及遷移階段對源庫的負載。除此之外 gh-ost 還有三種操作模式：

• 連接到從庫，在主庫做遷移；
• 直接在主庫上面操作；
• 在從庫上修改和測試；

大部分變更我們使用 connect to master 模式，其它兩種模式感興趣詳細可以參數(shù)官方文檔，在此不詳細介紹：https://github.com/github/gh-ost/blob/master/doc/cheatsheet.md

5.2. 執(zhí)行變更

/myinstall/gh-ost \
--max-load=Threads_running=30 \
--critical-load=Threads_running=50 \
--critical-load-interval-millis=5000 \
--chunk-size=1000 \
--dml-batch-size=10 \
--user="cooh" \
--password="112233" \
--host='127.0.0.1' \
--port=3306 \
--database="sbtest" \
--table="sbtest1" \
--alter="engine=innodb" \
--verbose \
--assume-rbr \
--cut-over=default \
--cut-over-lock-timeout-seconds=1 \
--allow-on-master \
--concurrent-rowcount \
--default-retries=30 \
--heartbeat-interval-millis=2000 \
--panic-flag-file=/myinstall/ddl_room/ghost.panic.flag \
--postpone-cut-over-flag-file=/myinstall/ddl_room/ghost.postpone.flag \
--serve-socket-file=/myinstall/ddl_room/ghost.sock \
--throttle-additional-flag-file=/myinstall/ddl_room/ghost.pause.flag \
--timestamp-old-table \
--execute 2>&1 | tee  /myinstall/ddl_room/ddl_sbtest1.log &

# 參數(shù)解釋和設(shè)置技巧
# 這兩個參數(shù)可以先使用 show status like 'Threads_running'; 
# 了解平時數(shù)據(jù)庫線程數(shù)，再進行設(shè)置。
--max-load=Threads_running=30
--critical-load=Threads_running=50
# 下面兩個參數(shù)影響著遷移階段的速度，如果開啟后觀察數(shù)據(jù)庫壓力較大，可以動態(tài)調(diào)整
--chunk-size=1000
--dml-batch-size=10
# 各種操作在 panick 前重試次數(shù)，默認 60 次
--default-retries
# 切換完成后，給源表添加時間戳，如果不要求切換完立即刪除源表，建議使用該參數(shù)
--timestamp-old-table
# 切換完后，立即刪除源表，如果表比較大會影響 IO 可根據(jù)實際情況設(shè)定
--ok-to-drop-table
# 設(shè)置暫停、退出 flag 的文件位置，建議為 gh-ost 創(chuàng)建一個單獨文件夾存放 flag 文件
--panic-flag-file
--throttle-additional-flag-file
--serve-socket-file
# 如果想自定義切換時間，可以使用該參數(shù)，常被嵌入自動化 DDL 平臺
--postpone-cut-over-flag-file
# 步驟失敗后，重試次數(shù)，例如切換獲取鎖，嘗試多少次，如果一直沒有則終止任務(wù)
--default-retries

執(zhí)行 DDL：操作過程中會自動創(chuàng)建兩個中間狀態(tài)表 _gho 幽靈表也就是目標表，_ghc 是記錄 gh-ost 執(zhí)行狀態(tài)的表：

cooh@mysql 16:36:  [sbtest]>show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| _sbtest1_ghc     |
| _sbtest1_gho     |
| sbtest1          |
+------------------+
3 rows in set (0.00 sec)

可以通過查詢 _ghc 表來查詢變更進度和狀態(tài)：

cooh@mysql 16:40:  [sbtest]>select * from _sbtest1_ghc order by id desc limit 1\G
*************************** 1. row ***************************
         id: 344
last_update: 2022-04-06 16:40:12
       hint: copy iteration 100 at 1649234412
      value: Copy: 99999/99999 100.0%; Applied: 0; Backlog: 0/1000; Time: 4m0s(total), 2s(copy); streamer: mysql-bin.000005:344430440; Lag: 0.99s, HeartbeatLag: 1.00s, State: postponing cut-over; ETA: due
1 row in set (0.00 sec)


Copy: 99999/99999 100.0%; 需要遷移 99999 行，目前已遷移 99999 行，進度 100.0%
Applied: 0，指在二進制日志中處理的 event 數(shù)量。在上面的例子中，遷移表沒有流量，因此沒有被處理日志 event。
Backlog: 0/1000，表示我們在讀取二進制日志方面表現(xiàn)良好，在二進制日志隊列中沒有任何積壓（Backlog）事件。
Backlog: 7/1000，當(dāng)復(fù)制行時，在二進制日志中積壓了一些事件，并且需要應(yīng)用。
Backlog: 1000/1000，表示我們的 1000 個事件的緩沖區(qū)已滿（程序?qū)懰赖?1000 個事件緩沖區(qū)，低版本是 100 個）此時就注意 binlog 寫入量非常大，gh-ost 處理不過來 event 了，可能需要暫停 binlog 讀取，需要優(yōu)先應(yīng)用緩沖區(qū)的事件。
State: 目前 gh-ost 的狀態(tài)
streamer: mysql-bin.000005:344430440; 表示當(dāng)前已經(jīng)應(yīng)用到 binlog 文件位置

此時狀態(tài)為：postponing cut-over 即等待切換，因為我們使用
--postpone-cut-over-flag-file=/myinstall/ddl_room/ghost.postpone.flag 參數(shù)，由用戶來控制切換時間，如果我們不刪除 ghost.postpone.flag 就會一直處理等待狀態(tài)，我們刪除后：

Copy: 99999/99999 100.0%; Applied: 0; Backlog: 0/1000; Time: 8m38s(total), 2s(copy); streamer: mysql-bin.000005:344547927; Lag: 0.99s, HeartbeatLag: 0.04s, State: migrating; ETA: due
2022-04-06 16:44:50 INFO Setting RENAME timeout as 1 seconds
2022-04-06 16:44:50 INFO Session renaming tables is 200
2022-04-06 16:44:50 INFO Issuing and expecting this to block: rename /* gh-ost */ table `sbtest`.`sbtest1` to `sbtest`.`_sbtest1_20220406163612_del`, `sbtest`.`_sbtest1_gho` to `sbtest`.`sbtest1`
2022-04-06 16:44:50 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2022-04-06 16:44:50 INFO Checking session lock: gh-ost.194.lock
2022-04-06 16:44:50 INFO Connection holding lock on original table still exists
2022-04-06 16:44:50 INFO Will now proceed to drop magic table and unlock tables
2022-04-06 16:44:50 INFO Dropping magic cut-over table
2022-04-06 16:44:50 INFO Releasing lock from `sbtest`.`sbtest1`, `sbtest`.`_sbtest1_20220406163612_del`
2022-04-06 16:44:50 INFO Tables unlocked
2022-04-06 16:44:50 INFO Tables renamed
2022-04-06 16:44:50 INFO Lock & rename duration: 1.006946435s. During this time, queries on `sbtest1` were blocked
[2022/04/06 16:44:50] [info] binlogsyncer.go:164 syncer is closing...
2022-04-06 16:44:51 INFO Closed streamer connection. err=<nil>
2022-04-06 16:44:51 INFO Dropping table `sbtest`.`_sbtest1_ghc`
[2022/04/06 16:44:51] [error] binlogsyncer.go:631 connection was bad
[2022/04/06 16:44:51] [error] binlogstreamer.go:77 close sync with err: Sync was closed
[2022/04/06 16:44:51] [info] binlogsyncer.go:179 syncer is closed
2022-04-06 16:44:51 INFO Table dropped
2022-04-06 16:44:51 INFO Am not dropping old table because I want this operation to be as live as possible. If you insist I should do it, please add `--ok-to-drop-table` next time. But I prefer you do not. To drop the old table, issue:
2022-04-06 16:44:51 INFO -- drop table `sbtest`.`_sbtest1_20220406163612_del`
2022-04-06 16:44:51 INFO Done migrating `sbtest`.`sbtest1`
2022-04-06 16:44:51 INFO Removing socket file: /myinstall/ddl_room/ghost.sock
2022-04-06 16:44:51 INFO Tearing down inspector
2022-04-06 16:44:51 INFO Tearing down applier
2022-04-06 16:44:51 INFO Tearing down streamer
2022-04-06 16:44:51 INFO Tearing down throttler
# Done

表示 gh-ost 已執(zhí)行完成，ghc 表已清理，源表名改為：_sbtest1_20220406163612_del

cooh@mysql 16:46:  [sbtest]>show tables;
+-----------------------------+
| Tables_in_sbtest            |
+-----------------------------+
| _sbtest1_20220406163612_del |
| sbtest1                     |
+-----------------------------+
2 rows in set (0.00 sec)

5.3. 動態(tài)控制

用戶可以通過操作特定的文件對正在執(zhí)行的 gh-ost 進行動態(tài)控制，請看下面案例：

• 停止 gh-ost：

  指定 --panic-flag-file=/myinstall/ddl_room/ghost.panic.flag 參數(shù)，我們通過創(chuàng)建 ghost.panic.flag 來終止 gh-ost。
  touch /myinstall/ddl_room/ghost.panic.flag
  會輸出下面日志：
  2022-04-06 16:56:14 FATAL Found panic-file /myinstall/ddl_room/ghost.panic.flag. Aborting without cleanup

• 暫停/開始：

  指定 --throttle-additional-flag-file=/myinstall/ddl_room/ghost.pause.flag 參數(shù)，我們可以通過創(chuàng)建刪除 ghost.pause.flag 文件來控制。

• 開啟限流：

  echo throttle | socat - /myinstall/ddl_room/ghost.sock
  

• 關(guān)閉限流：

  echo no-throttle | socat - /myinstall/ddl_room/ghost.sock
  

• 動態(tài)修改參數(shù)：

  echo chunk-size=1024 | socat - /myinstall/ddl_room/ghost.sock
  echo max-lag-millis=100 | socat - /myinstall/ddl_room/ghost.sock
  echo max-load=Thread_running=23 | socat - /myinstall/ddl_room/ghost.sock
  

6. 風(fēng)險提示

• gh-ost 執(zhí)行 cut-over 階段，會短暫堵塞變更表的讀寫操作，可以選擇業(yè)務(wù)低峰期進行切換操作，減少對業(yè)務(wù)影響。

• 大表變更前需要注意 存儲空間是否充裕，可以通過?? SQL 查詢元數(shù)據(jù)。

  SELECT TABLE_NAME,
         round(SUM(data_length + index_length) / 1024 / 1024, 2) AS TOTAL_MB,
         round(SUM(data_length) / 1024 / 1024, 2)                AS DATA_MB,
         round(SUM(index_length) / 1024 / 1024, 2)               AS INDEX_MB
  FROM INFORMATION_SCHEMA.tables
  WHERE TABLE_SCHEMA = 'sbtest' and TABLE_NAME = 'sbtest1'
  GROUP BY TABLE_NAME;
  
  -- 輸出結(jié)果：
  +------------+----------+---------+----------+
  | TABLE_NAME | TOTAL_MB | DATA_MB | INDEX_MB |
  +------------+----------+---------+----------+
  | sbtest1    |    24.06 |   21.55 |     2.52 |
  +------------+----------+---------+----------+
  

• 大表變更周期長，如果一直獲取不到鎖，可能會變更失敗，若變更失敗不會影響現(xiàn)有的業(yè)務(wù)，但表結(jié)構(gòu)變更需要重新開始，因此建議線上表僅保留線上熱點活躍數(shù)據(jù)，歷史數(shù)據(jù)及時歸檔，將表保持在一種健康狀態(tài)。

• 添加唯一索引時，會造成索引字段重復(fù)數(shù)據(jù)丟失，如果使用 MySQL 原生 DDL 添加唯一索引，如果有重復(fù)值會報錯 [23000][1062] Duplicate entry 因為 gh-ost 在數(shù)據(jù)遷移階段使用的是 insert ignore所以不會報錯，但是索引字段的重復(fù)條目會丟失，這點需要確認風(fēng)險。

到此這篇關(guān)于MySQL gh-ost DDL 變更工具的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL gh-ost DDL 變更內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論