主頁 > 知識庫 > MySQL DDL 引發(fā)的同步延遲該如何解決

MySQL DDL 引發(fā)的同步延遲該如何解決
熱門標簽：大豐地圖標注app 400電話辦理服務價格最實惠 北京金倫外呼系統(tǒng) 武漢電銷機器人電話 南太平洋地圖標注 400電話變更申請 催天下外呼系統(tǒng) html地圖標注并導航 呂梁外呼系統(tǒng)

前言

寫作案例分析，主要是工具介紹推薦。MySQL 的同步機制比較單純，主庫上執(zhí)行過的 DML 和 DDL 會在從庫上再執(zhí)行一次，那么主庫上需要 10min 才能執(zhí)行完的 DDL 理論上在從庫至少也要花費 10min 才能執(zhí)行完，這意味著從庫的同步會延遲 10min 以上，等 DDL 執(zhí)行完之后才會繼續(xù)追同步。

解決方案

從 MySQL 的同步原理來看，主要是 DDL 這個單獨的操作會花費太久的時間，導致從庫也會被卡主。那么解決這個問題的辦法就很容易想到：“拆解” DDL 的操作，把一個大操作（大事務同理）拆分成多個小操作，減少單次操作的時間。

“拆解” DDL 操作一般會用到 MySQL Online DDL 的工具，比如 pt-osc，facebook-osc，oak-online-alter-table，gh-ost 等。這些工具的思路都比較類似，創(chuàng)建一個源表的鏡像表，先執(zhí)行完表結構變更，再把源表的全量數(shù)據(jù)和增量數(shù)據(jù)都同步過去，因此可以避免單個 DDL 操作引發(fā)的同步延遲。

工具介紹

本文會介紹 gh-ost，由 Github 維護的 MySQL online DDL 工具，同樣使用了鏡像表的形式，但是放棄了使用低效的 trigger，而是從 binlog 中提取需要的增量數(shù)據(jù)來保持鏡像表與源表的數(shù)據(jù)一致性。整個 Online DDL 操作僅在最終 rename 源表與鏡像表時會阻塞幾秒鐘的讀寫。

工作原理

go-ost 的操作流程大致如下：

  • 在 Master 中創(chuàng)建鏡像表（_tablename_gho）和心跳表（_tablename_ghc）。
  • 向心跳表中寫入 Online-DDL 的進度以及時間。
  • 在鏡像表上執(zhí)行 ALTER 操作。
  • 偽裝成 slave 連接到 Master 的某個 Slave 實例上獲取 binlog 的信息（默認連接 Slave，也可以連 Master）。
  • 在 Master 中完成鏡像表的數(shù)據(jù)同步：
    • 從源表中拷貝數(shù)據(jù)到鏡像表；
    • 依據(jù) Binlog 信息完成增量數(shù)據(jù)的變更;
  • 在源表上加鎖;
  • 確認心跳表中的時間，確保數(shù)據(jù)是完全同步的；
  • 用鏡像表替換源表。
  • Online DDL 完成。
  • 未來考慮會支持的功能或特性：
    • 支持外鍵。
    • gh-ost 進程意外中斷以后，可以新啟動一個進程繼續(xù)進行 Online DDL。

_tablename_ghc 內容如下：

使用限制

  • binlog 格式必須使用 row，且binlog_row_image必須是 FULL。
  • 需求的權限為SUPER, REPLICATION CLIENT, REPLICATION SLAVE on *.* and ALL on dbname.*
    • 如果確認 binlog 的格式為 row，那么可以加上 -assume-rbr，則不再需要 super 權限。
    • 由于不支持 REPLICATION 相關的權限，TiDB 無法使用。
  • 不支持外鍵。
    • 不論源表是主表還是子表，都無法使用。
  • 不支持觸發(fā)器。
  • 不支持包含 JSON 列的主鍵。
  • 遷移表需要有顯示定義的主鍵，或者有非空的唯一索引。
  • 遷移工具不區(qū)分大小寫英文字母，如果存在同名，但是大小寫不同的表則無法遷移。
  • 遷移表的主鍵或者非空唯一索引包含枚舉類型時，遷移效率會大幅度降低。

使用注意

  • 如果源表有非常多的數(shù)據(jù)，盡量分批次刪除。
    • delete from table tablename_old limit 5000;
    • 或者在業(yè)務空閑時段用truncate table tablename_old清空表數(shù)據(jù)之后再 drop 表。
  • 單個 MySQL 實例上啟動多個 gh-ost 來進行多個表的 Online DDL 操作時要制定-replica-server-id參數(shù)
  • 務必注意可用的磁盤空間，尤其是操作大表的時候。
    • gh-ost 的鏡像表包含源表的所有數(shù)據(jù)，會額外占用一倍的磁盤。
    • gh-ost 在操作的過程中會產(chǎn)生大量的 binlog，且binlog_row_image必須為 FULL，會占用比較多的磁盤空間。
  • rename 列的操作可能會有問題，考慮 drop 和 add 的操作結合起來。

使用示例

github 官網(wǎng)有安裝包可以下載，參考 release note。

實際命令可以參考下面這個（已開啟了 row 模式）：

gh-ost --max-load=Threads_running=50 \

            --critical-load=Threads_running=100 \

            --chunk-size=3000 --user="temp" --password="test" --host=10.10.1.10 \

            --allow-on-master --database="sbtest" --table="sbtest1" \

            --alter="engine=innodb" --cut-over=default \

            --exact-rowcount --concurrent-rowcount --default-retries=120 \

            --timestamp-old-table -assume-rbr --panic-flag-file=/tmp/ghost.panic.flag \

            --execute

部分參數(shù)說明

以上文的命令內容為準：

max-load=Threads_running=50         超過50個client在執(zhí)行SQL查詢時,暫停Online DDL操作
critical-load=Threads_running=100   超過100個client在執(zhí)行SQL查詢時,中斷Online DDL操作
chunk-size=3000                     每一次同步操作處理3000行數(shù)據(jù)
allow-on-master                     允許在主庫執(zhí)行Online DDL相關的所有操作
alter                               Online DDL的操作,僅需要部分alter語句(方括號部分)
                                     例：alter table sbtest.sbtest1 [add column t int not NULL]
cut-over=default                     數(shù)據(jù)同步完成后自動進行鏡像表與源表的切換
exact-rowcount                       精確計算行數(shù),提供更準確的進度
timestamp-old-table                 使用時間戳來命名舊表
assume-rbr                           跳過重啟slave線程與row format檢查,設置后無需super權限
panic-flag-file                      創(chuàng)建該文件后,會強制中斷Online DDL操作

除了這些參數(shù)以外，gh-ost 還提供了非常多的方式來從外部暫?；蛘邚娭浦兄?Online DDL 的操作，詳細的信息可以使用gh-ost --help命令進行查看。

輸出結果示例

# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`
# Migrating 10.10.1.10:3306; inspecting10.10.1.10:3306; executing on localhost-debian
# Migration started at Thu Jul 30 11:30:17 +0800 2020
# chunk-size: 3000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=50; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.sbtest.sbtest1.sock
Copy: 0/9863066 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000050:31635038; Lag: 0.03s, State: migrating; ETA: N/A
Copy: 0/9863066 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000050:31639503; Lag: 0.03s, State: migrating; ETA: N/A
Copy: 69000/9999998 0.7%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 2s(copy); streamer: mysql-bin.000050:44815698; Lag: 0.03s, State: migrating; ETA: 4m49s
Copy: 135000/9999998 1.4%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 3s(copy); streamer: mysql-bin.000050:57419220; Lag: 0.03s, State: migrating; ETA: 3m39s
Copy: 195000/9999998 2.0%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 4s(copy); streamer: mysql-bin.000050:68877374; Lag: 0.03s, State: migrating; ETA: 3m21s
......（省略）
Copy: 9729000/9999998 97.3%; Applied: 0; Backlog: 0/1000; Time: 3m16s(total), 3m16s(copy); streamer: mysql-bin.000057:8595335; Lag: 0.04s, State: migrating; ETA: 5s
[2020/07/30 11:33:32] [info] binlogsyncer.go:723 rotate to (mysql-bin.000057, 4)
Copy: 9774000/9999998 97.7%; Applied: 0; Backlog: 0/1000; Time: 3m17s(total), 3m17s(copy); streamer: mysql-bin.000057:17190073; Lag: 0.03s, State: migrating; ETA: 4s
[2020/07/30 11:33:32] [info] binlogsyncer.go:723 rotate to (mysql-bin.000057, 4)
Copy: 9822000/9999998 98.2%; Applied: 0; Backlog: 0/1000; Time: 3m18s(total), 3m18s(copy); streamer: mysql-bin.000057:26357495; Lag: 0.04s, State: migrating; ETA: 3s
Copy: 9861000/9999998 98.6%; Applied: 0; Backlog: 0/1000; Time: 3m19s(total), 3m19s(copy); streamer: mysql-bin.000057:33806865; Lag: 0.03s, State: migrating; ETA: 2s
Copy: 9903000/9999998 99.0%; Applied: 0; Backlog: 0/1000; Time: 3m20s(total), 3m20s(copy); streamer: mysql-bin.000057:41828922; Lag: 0.03s, State: migrating; ETA: 1s
Copy: 9951000/9999998 99.5%; Applied: 0; Backlog: 0/1000; Time: 3m21s(total), 3m21s(copy); streamer: mysql-bin.000057:50996347; Lag: 0.03s, State: migrating; ETA: 0s
Copy: 9999998/9999998 100.0%; Applied: 0; Backlog: 0/1000; Time: 3m22s(total), 3m21s(copy); streamer: mysql-bin.000057:60354465; Lag: 0.03s, State: migrating; ETA: due
# Migrating `sbtest`.`sbtest1`; Ghost table is `sbtest`.`_sbtest1_gho`
# Migrating 10.10.1.10:3306; inspecting 10.10.1.10:3306; executing onlocalhost-debian
# Migration started at Thu Jul 30 11:30:17 +0800 2020
# chunk-size: 3000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=50; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.sbtest.sbtest1.sock
Copy: 9999998/9999998 100.0%; Applied: 0; Backlog: 0/1000; Time: 3m23s(total), 3m21s(copy); streamer: mysql-bin.000057:60359997; Lag: 0.03s, State: migrating; ETA: due
[2020/07/30 11:33:41] [info] binlogsyncer.go:164 syncer is closing...
[2020/07/30 11:33:41] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2020/07/30 11:33:41] [info] binlogsyncer.go:179 syncer is closed

可以看到日志內容中輸出了詳細的進度百分比和遷移的剩余時間，在預估維護結束的時間，查看 DDL 執(zhí)行進度的時候會非常方便。

騰訊云數(shù)據(jù)庫 MySQL 使用注意

  • 騰訊云數(shù)據(jù)庫 MySQL 默認的binlog_row_image為 MINIMAL，使用前需要在控制主動調整為 FULL（在線變更，即時生效）。
  • 包括騰訊云數(shù)據(jù)庫，阿里云數(shù)據(jù)庫，容器中的 MySQL 等都可能會遇到端口的問題，加上--aliyun-rds參數(shù)即可。
    • 報錯信息類似于FATAL Unexpected database port reported。
    • 相關討論參考 issues。

總結一下

gh-ost 輸出的信息，遷移數(shù)據(jù)的效率，以及支持的功能都比 pt-osc 等工具要優(yōu)秀，而 gh-ost 工具的問題（例如磁盤空間）在其他工具也會遇到，因此在 DDL 操作又想避免延遲等問題時，推薦優(yōu)先考慮 gh-ost。

以上就是MySQL DDL 引發(fā)的同步延遲該如何解決的詳細內容，更多關于MySQL DDL 引發(fā)的同步延遲的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:
  • 詳解MySQL的Seconds_Behind_Master
  • MySQL主從同步延遲的原因及解決辦法
  • MYSQL主從不同步延遲原理分析及解決方案
  • 減少mysql主從數(shù)據(jù)同步延遲問題的詳解
  • MySQL 發(fā)生同步延遲時Seconds_Behind_Master還為0的原因

標簽：麗水 南充 無錫 自貢 迪慶 龍巖 徐州 西寧

巨人網(wǎng)絡通訊聲明：本文標題《MySQL DDL 引發(fā)的同步延遲該如何解決》，本文關鍵詞  MySQL,DDL,引發(fā),的,同步,延遲,；如發(fā)現(xiàn)本文內容存在版權問題，煩請?zhí)峁┫嚓P信息告之我們，我們將及時溝通與處理。本站內容系統(tǒng)采集于網(wǎng)絡，涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《MySQL DDL 引發(fā)的同步延遲該如何解決》相關的同類信息！
  • 本頁收集關于MySQL DDL 引發(fā)的同步延遲該如何解決的相關信息資訊供網(wǎng)民參考！
    • 推薦文章