MySQL DDL 引發(fā)的同步延遲該如何解決
更新時(shí)間：2021年05月08日 09:58:26 作者：王文安@DBA
這篇文章主要介紹了MySQL DDL 引發(fā)的同步延遲該如何解決，幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下
前言
寫作案例分析，主要是工具介紹&推薦。MySQL 的同步機(jī)制比較單純，主庫(kù)上執(zhí)行過(guò)的 DML 和 DDL 會(huì)在從庫(kù)上再執(zhí)行一次，那么主庫(kù)上需要 10min 才能執(zhí)行完的 DDL 理論上在從庫(kù)至少也要花費(fèi) 10min 才能執(zhí)行完，這意味著從庫(kù)的同步會(huì)延遲 10min 以上，等 DDL 執(zhí)行完之后才會(huì)繼續(xù)追同步。
解決方案
從 MySQL 的同步原理來(lái)看，主要是 DDL 這個(gè)單獨(dú)的操作會(huì)花費(fèi)太久的時(shí)間，導(dǎo)致從庫(kù)也會(huì)被卡主。那么解決這個(gè)問(wèn)題的辦法就很容易想到：“拆解” DDL 的操作，把一個(gè)大操作（大事務(wù)同理）拆分成多個(gè)小操作，減少單次操作的時(shí)間。
“拆解” DDL 操作一般會(huì)用到 MySQL Online DDL 的工具，比如 pt-osc，facebook-osc，oak-online-alter-table，gh-ost 等。這些工具的思路都比較類似，創(chuàng)建一個(gè)源表的鏡像表，先執(zhí)行完表結(jié)構(gòu)變更，再把源表的全量數(shù)據(jù)和增量數(shù)據(jù)都同步過(guò)去，因此可以避免單個(gè) DDL 操作引發(fā)的同步延遲。
工具介紹
本文會(huì)介紹 gh-ost，由 Github 維護(hù)的 MySQL online DDL 工具，同樣使用了鏡像表的形式，但是放棄了使用低效的 trigger，而是從 binlog 中提取需要的增量數(shù)據(jù)來(lái)保持鏡像表與源表的數(shù)據(jù)一致性。整個(gè) Online DDL 操作僅在最終 rename 源表與鏡像表時(shí)會(huì)阻塞幾秒鐘的讀寫。
工作原理
go-ost 的操作流程大致如下：
- 在 Master 中創(chuàng)建鏡像表（_tablename_gho）和心跳表（_tablename_ghc）。
- 向心跳表中寫入 Online-DDL 的進(jìn)度以及時(shí)間。
- 在鏡像表上執(zhí)行 ALTER 操作。
- 偽裝成 slave 連接到 Master 的某個(gè) Slave 實(shí)例上獲取 binlog 的信息（默認(rèn)連接 Slave，也可以連 Master）。
- 在 Master 中完成鏡像表的數(shù)據(jù)同步：
- 從源表中拷貝數(shù)據(jù)到鏡像表；
- 依據(jù) Binlog 信息完成增量數(shù)據(jù)的變更;
- 在源表上加鎖;
- 確認(rèn)心跳表中的時(shí)間，確保數(shù)據(jù)是完全同步的；
- 用鏡像表替換源表。
- Online DDL 完成。
- 未來(lái)考慮會(huì)支持的功能或特性：
- 支持外鍵。
- gh-ost 進(jìn)程意外中斷以后，可以新啟動(dòng)一個(gè)進(jìn)程繼續(xù)進(jìn)行 Online DDL。
_tablename_ghc 內(nèi)容如下：
使用限制
- binlog 格式必須使用 row，且binlog_row_image必須是 FULL。
- 需求的權(quán)限為SUPER, REPLICATION CLIENT, REPLICATION SLAVE on *.* and ALL on dbname.*
- 如果確認(rèn) binlog 的格式為 row，那么可以加上 -assume-rbr，則不再需要 super 權(quán)限。
- 由于不支持 REPLICATION 相關(guān)的權(quán)限，TiDB 無(wú)法使用。
- 不支持外鍵。
- 不論源表是主表還是子表，都無(wú)法使用。
- 不支持觸發(fā)器。
- 不支持包含 JSON 列的主鍵。
- 遷移表需要有顯示定義的主鍵，或者有非空的唯一索引。
- 遷移工具不區(qū)分大小寫英文字母，如果存在同名，但是大小寫不同的表則無(wú)法遷移。
- 遷移表的主鍵或者非空唯一索引包含枚舉類型時(shí)，遷移效率會(huì)大幅度降低。
使用注意
- 如果源表有非常多的數(shù)據(jù)，盡量分批次刪除。
- delete from table tablename_old limit 5000;
- 或者在業(yè)務(wù)空閑時(shí)段用truncate table tablename_old清空表數(shù)據(jù)之后再 drop 表。
- 單個(gè) MySQL 實(shí)例上啟動(dòng)多個(gè) gh-ost 來(lái)進(jìn)行多個(gè)表的 Online DDL 操作時(shí)要制定-replica-server-id參數(shù)
- 務(wù)必注意可用的磁盤空間，尤其是操作大表的時(shí)候。
- gh-ost 的鏡像表包含源表的所有數(shù)據(jù)，會(huì)額外占用一倍的磁盤。
- gh-ost 在操作的過(guò)程中會(huì)產(chǎn)生大量的 binlog，且binlog_row_image必須為 FULL，會(huì)占用比較多的磁盤空間。
- rename 列的操作可能會(huì)有問(wèn)題，考慮 drop 和 add 的操作結(jié)合起來(lái)。
使用示例
github 官網(wǎng)有安裝包可以下載，參考 release note。
實(shí)際命令可以參考下面這個(gè)（已開啟了 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ù)說(shuō)明
以上文的命令內(nèi)容為準(zhǔn)：
max-load=Threads_running=50 超過(guò)50個(gè)client在執(zhí)行SQL查詢時(shí),暫停Online DDL操作
critical-load=Threads_running=100 超過(guò)100個(gè)client在執(zhí)行SQL查詢時(shí),中斷Online DDL操作
chunk-size=3000 每一次同步操作處理3000行數(shù)據(jù)
allow-on-master 允許在主庫(kù)執(zhí)行Online DDL相關(guān)的所有操作
alter Online DDL的操作,僅需要部分alter語(yǔ)句(方括號(hào)部分)
例：alter table sbtest.sbtest1 [add column t int not NULL]
cut-over=default 數(shù)據(jù)同步完成后自動(dòng)進(jìn)行鏡像表與源表的切換
exact-rowcount 精確計(jì)算行數(shù),提供更準(zhǔn)確的進(jìn)度
timestamp-old-table 使用時(shí)間戳來(lái)命名舊表
assume-rbr 跳過(guò)重啟slave線程與row format檢查,設(shè)置后無(wú)需super權(quán)限
panic-flag-file 創(chuàng)建該文件后,會(huì)強(qiáng)制中斷Online DDL操作
除了這些參數(shù)以外，gh-ost 還提供了非常多的方式來(lái)從外部暫?；蛘邚?qiáng)制中止 Online DDL 的操作，詳細(xì)的信息可以使用gh-ost --help命令進(jìn)行查看。
輸出結(jié)果示例
# 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
可以看到日志內(nèi)容中輸出了詳細(xì)的進(jìn)度百分比和遷移的剩余時(shí)間，在預(yù)估維護(hù)結(jié)束的時(shí)間，查看 DDL 執(zhí)行進(jìn)度的時(shí)候會(huì)非常方便。
騰訊云數(shù)據(jù)庫(kù) MySQL 使用注意
- 騰訊云數(shù)據(jù)庫(kù) MySQL 默認(rèn)的binlog_row_image為 MINIMAL，使用前需要在控制主動(dòng)調(diào)整為 FULL（在線變更，即時(shí)生效）。
- 包括騰訊云數(shù)據(jù)庫(kù)，阿里云數(shù)據(jù)庫(kù)，容器中的 MySQL 等都可能會(huì)遇到端口的問(wèn)題，加上--aliyun-rds參數(shù)即可。
- 報(bào)錯(cuò)信息類似于FATAL Unexpected database port reported。
- 相關(guān)討論參考 issues。
總結(jié)一下
gh-ost 輸出的信息，遷移數(shù)據(jù)的效率，以及支持的功能都比 pt-osc 等工具要優(yōu)秀，而 gh-ost 工具的問(wèn)題（例如磁盤空間）在其他工具也會(huì)遇到，因此在 DDL 操作又想避免延遲等問(wèn)題時(shí)，推薦優(yōu)先考慮 gh-ost。
以上就是MySQL DDL 引發(fā)的同步延遲該如何解決的詳細(xì)內(nèi)容，更多關(guān)于MySQL DDL 引發(fā)的同步延遲的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL中create_time和update_time實(shí)現(xiàn)自動(dòng)更新時(shí)間
mysql建表的時(shí)候有兩個(gè)列,一個(gè)是createtime、另一個(gè)是updatetime，這兩個(gè)都是mysql自動(dòng)填充時(shí)間的方式，本文就詳細(xì)的介紹這兩種方式的實(shí)現(xiàn)，感興趣的可以了解一下2023-05-05
MySQL使用binlog日志恢復(fù)數(shù)據(jù)的方法步驟
binlog日志是用于記錄所有修改數(shù)據(jù)庫(kù)內(nèi)容的操作,本文主要介紹了MySQL使用binlog日志恢復(fù)數(shù)據(jù)的方法步驟,具有一定的參考價(jià)值,感興趣的可以了解一下2025-03-03
mysql5.7 設(shè)置遠(yuǎn)程訪問(wèn)的實(shí)現(xiàn)
這篇文章主要介紹了mysql5.7 設(shè)置遠(yuǎn)程訪問(wèn)的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
常用的mysql截取函數(shù)有：left(), right(), substring(), substring_index(),很多新手朋友不是很了解，接下來(lái)詳細(xì)說(shuō)明，需要的朋友可以參考下2012-12-12
Unity連接MySQL并讀取表格數(shù)據(jù)的實(shí)現(xiàn)代碼
本文給大家介紹Unity連接MySQL并讀取表格數(shù)據(jù)的實(shí)現(xiàn)代碼，實(shí)例化的同時(shí)調(diào)用MySqlConnection，傳入?yún)?shù)，這里的傳入?yún)?shù)個(gè)人認(rèn)為是CMD里面的直接輸入了，string格式直接類似手敲到cmd里面，完整代碼參考下本文2021-06-06
MySQL優(yōu)化之如何寫出高質(zhì)量sql語(yǔ)句
在數(shù)據(jù)庫(kù)日常維護(hù)中，最常做的事情就是SQL語(yǔ)句優(yōu)化，因?yàn)檫@個(gè)才是影響性能的最主要因素。這篇文章主要給大家介紹了關(guān)于MySQL優(yōu)化之如何寫出高質(zhì)量sql語(yǔ)句的相關(guān)資料,需要的朋友可以參考下2021-05-05
Linux中 MySQL 授權(quán)遠(yuǎn)程連接的方法步驟
如果需要遠(yuǎn)程連接 Linux 系統(tǒng)上的 MySQL 時(shí)，必須為其 IP 和 具體用戶 進(jìn)行 授權(quán)，本篇文章主要介紹了Linux中 MySQL 授權(quán)遠(yuǎn)程連接的方法步驟，感興趣的小伙伴們可以參考一下2018-10-10
MySQL數(shù)據(jù)庫(kù)索引及優(yōu)化的示例詳解
在日常的數(shù)據(jù)庫(kù)使用過(guò)程中，我們經(jīng)常需要對(duì)數(shù)據(jù)進(jìn)行查詢、插入、刪除等操作，為了提高這些操作的效率，數(shù)據(jù)庫(kù)的性能優(yōu)化顯得尤為重要，本文就來(lái)講講MySQL中是如何優(yōu)化索引的吧2023-05-05
這篇文章主要介紹了MySQL中的全表掃描和索引樹掃描?，從本文的學(xué)習(xí)可以輕松的知道，全表掃描的效率相比于索引樹掃描相對(duì)較低一點(diǎn)，但是差距不是很大，具體示例代碼詳解跟隨小編一起看看吧2022-05-05
MySQL動(dòng)態(tài)創(chuàng)建表，數(shù)據(jù)分表的存儲(chǔ)過(guò)程
MySQL動(dòng)態(tài)創(chuàng)建表，數(shù)據(jù)分表的存儲(chǔ)過(guò)程，需要的朋友可以參考下。2011-08-08