記一次 MySQL 主從同步異常的排查記錄，百轉千回！

?

relay log 損壞了，導致從庫的 SQL 線程解析 relay log 時出現異常。從庫恢復方式是通過手動設置當時出錯的 GTID 的下一個值，讓從庫不從主庫同步這個 GTID，最后從庫就能正常同步這個 GTID 之后的 binlog 了，后續(xù) SQL 線程也能正常解析 relay log 了。

你好，我是悟空。

本文主要內容如下：

目錄

一、現象

最近項目的測試環(huán)境遇到一個主備同步的問題：

備庫的同步線程停止了，無法同步主庫的數據更改。

備庫報錯如下：

從庫同步報錯信息

完整的錯誤信息：

復制

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

• 1.

上面的報錯信息是什么意思呢？

翻譯一下就是主庫的 binlog 或者從庫的 relay log 損壞了，造成這個問題的原因：

• 可能是網絡問題。
• 也可能是主庫或備庫的代碼 bug。

首先我們還是得復習下主從同步的原理才能更好地分析原因。

二、主從同步的原理

主從同步的原理

• 從庫會生成兩個線程，一個 I/O 線程，名字叫做 Slave_IO_Running，另外一個是 SQL 線程，名字叫做 Slave_SQL_Running;
• 從庫的 I/O 線程會去請求主庫的?binlog?日志文件，并將得到的 binlog 日志文件寫到本地的?relay log?(中繼日志)文件中;
• 主庫會生成一個 dump 線程，用來給從庫 I/O 線程傳 binlog;
• 從庫 SQL 線程，會讀取?relay log?文件中的日志，并解析成 SQL 語句逐一執(zhí)行。

三、排查思路

3.1 分析從庫的同步狀態(tài)

我們可以打印下從庫的同步狀態(tài)，看到如下幾個關鍵信息：

分析從庫的同步狀態(tài)

Master_Log_File: mysql-bin.000956，代表從庫讀到的主庫的 binlog file，

Read_Master_Log_Pos: 528071913，代表從庫讀到的主庫的 binlog file 的日志偏移量

Relay_Log_File: relay-bin.000094，代表從庫執(zhí)行到了哪一個 relay log

Relay_Log_Pos: 123408769，代表從庫執(zhí)行的 relay log file 的日志偏移量

Relay_Master_Log_File: mysql-bin.000955，代表從庫已經重放到了主庫的哪個 binlog file。

Exec_Master_Log_Pos: 123408556，代表從庫已經重放到了主庫 binlog file 的偏移量。

Slave_IO_Running: Yes，說明 I/O 線程正在運行，可以正常獲取 binlog 并生成 relay log。

Slave_SQL_Running: No，說明 SQL 線程已經停止運行，不能正常解析 relay log，也就不能執(zhí)行主庫上已經執(zhí)行的命令。

Master_Log_File??和?Read_Master_Log_Pos?這兩個參數合起來表示的是讀到的主庫的最新位點。

Relay_Master_Log_File??和?Exec_Master_Log_Pos，這兩個參數合起來表示的是從庫執(zhí)行的最新位點。

如果紅色框起來的兩個參數：Master_Log_File??和?Relay_Master_Log_File?相等，則說明從庫讀到的最新文件和主庫上生成的文件相同，這里前者是 mysql-bin.000956，后者是 mysql-bin.000955，說明兩者不相同，存在主從不同步。

如果藍色框起來的兩個參數?Read_Master_Log_Pos??和?Exec_Master_Log_Pos?相等，則說明從庫讀到的日志文件的位置和從庫上執(zhí)行日志文件的位置相同，這里不相等，說明主從不同步。

當上面兩組參數都相等時，則說明主從同步正常，且沒有延遲。只要有任意一組不相等，則說明主從不同步，可能是從庫停止同步了，或者從庫存在同步延遲。由于上面的 SQL 線程已經停止了，說明是從庫同步出現問題了。

從庫同步出現的問題在最開始的報錯信息里面已經提到了，可能是網絡問題導致，還有可能是 binlog 或 relay log 損壞。

3.2 重啟萬能大法

先通過重啟來恢復從庫的 SQL 線程試試看？重啟方式就是兩種：

• 方式一：從庫重新開啟同步。就是執(zhí)行 stop slave; 和 start slave; 命令。
• 方式二：重啟從庫實例。就是重啟 mysql 實例或 mysql 容器。

這兩種方式試了后，都不能恢復從庫的 SQL 線程。

3.3 查看 binlog

再來看下 binlog 是否有損壞，在主庫上通過這個命令打開 mysql-bin.000955 文件。

復制

mysqlbinlog /var/lib/mysql/log/mysql-bin.000955

• 1.

沒有報錯信息，如下圖所示：

binlog 日志

3.4 查看 relay log

看到從庫同步的 Relay_Log_File 到 relay-bin.00094 就停止同步了，如下圖所示，可能是這個文件損壞了。

relay log 日志

在從庫上通過 mysqlbinlog 命令打開這個文件

復制

mysqlbinlog /var/lib/mysql/log/relay-bin.000094

• 1.

可以看到有個報錯信息：

復制

ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 7644, event_type: 31
ERROR: Could not read entry at offset 243899899: Error in log format or read error.

• 1.
• 2.

這段文字翻譯過來就是讀取錯誤，數據長度 7644，在讀取偏移量為 243899899 的日志時發(fā)生了錯誤，可能是日志文件格式錯誤或是讀取文件錯誤。

relay log 報錯信息

3.5 找原因

3.5.1 猜測事務日志太大

根據這個報錯信息可以知道這個事務日志數據太長了，data_len: 7644，而導致讀取錯誤。

而且上面還有很多 Update_rows 的操作。

猜測：會不會是主庫執(zhí)行了一個大事務，造成該事務生成的一條 binlog 日志太大了，從庫生成的對應的一條 relay log 日志也很大， SQL 線程去解析這條 relay log 日志解析報錯。

3.5.2 驗證

到主庫上查看下 binlog 日志里面有沒有在那個時間點做特殊操作。

感覺快找到原因了。執(zhí)行以下命令來查看

復制

mysqlbinlog File --stop-datetime=T --start-datetime=T

• 1.

stop-datetime?指定為讀取 relay log 報錯的時刻 2023-04-04 16:47:16，

start-datetime?指定為讀取 relay log 報錯的時刻 2023-04-04 16:47:30。

發(fā)現并沒有找到 Update_rows 的操作。繼續(xù)把時間往后加一點，經過多次嘗試，把時間鎖定在了 2023-04-04 17:00:30~17:00:31。這 1s 內能找到 2023-04-04 16:47:16 的操作日志。

日志如下，這個命令會打印 N 多日志，直接把屏幕打滿了?。?/p>

難道真的 binlog 對應的這條事務日志太大了嗎？？？

存疑：?2023-04-04 16:47:16 時刻對數據庫中的表做了某個大事務的操作，造成該事務對應的這條 binlog 日志很大很大。生成的 relay log 也很大，SQL 線程解析 relay log 報錯。

3.6 這是真相嗎？

問了下熟悉這張表的同事，有沒有在這個時刻做什么大事務操作。

同事看了下代碼，發(fā)現有個批量插入的操作，一次執(zhí)行 400 條，難道是 400 條太多了？？？這不應該是真正的原因，400 條也不多。

不經意間問了下這張表的數據量有多大，該同事在 4月4號 16:45:25 做了一個手動備份?xx_dance?表的操作，這張表有 25 萬條數據。

備份表 xx_dance_0404 的信息

這個備份操作是在一個事務里面執(zhí)行的，生成的一條 binlog 日志很大。

這里只是一個猜測，還未得到驗證，文末會說明真正的原因。

如果真的是這樣，那我可以先恢復從庫的同步，備份表的操作在從庫上其實不需要。

3.7 GTID

不知道細心的你是否有發(fā)現上面的 binlog 里面有一個GTID，

復制

'c5d74746-d7ec-11ec-bf8f-0242ac110002:8634832

• 1.

binlog 中 ?GTID

記住 GTID 中的數字?8634832，后面恢復從庫同步時要用到。

我們再來看下從庫的狀態(tài)，發(fā)現也有一個 GTID，如下圖所示，值為?8634831，正好相差 1，感覺這兩個 GTID 值之間有不可告人的秘密。

從庫的狀態(tài)，GTID 集合

那么從庫 SQL 線程停止運行的原因就是卡在 8634832 這里了，我們可否跳過這個 GTID 呢？這里就需要了解 GTID 的原理了。

你可能對 GTID 的原理很感興趣，可以查看之前悟空寫的一篇文章：

MySQL 主從模式采用 GTID 的實踐

四、GTID 同步方式的原理

這里還是把主從同步采用 GTID 方式的流程拿出來看下，幫助大家快速回顧下，熟悉的同學可以跳過本節(jié)內容。

GTID 方案：主庫計算主庫 GTID 集合和從庫 GTID 的集合的差集，然后主庫推送差集 binlog 給從庫。

當從庫設置完同步參數后，假定主庫 A 的GTID 集合記為集合 x，從庫 B 的 GTID 集合記為 y。

從庫同步的邏輯如下：

GTID 同步方式的原理

• 從庫 B 指定主庫 A，基于主備協議簡歷連接。
• 從庫 B 把集合 y 發(fā)給主庫 A。
• 主庫 A 計算出集合 x 和集合 y 的差集，也就是集合 x 中存在，集合 y 中不存在的 GTID 集合。比如集合 x 是 1~100，集合 y 是 1~90，那么這個差集就是 91~100。這里會判斷集合 x 是不是包含有集合 y 的所有 GTID，如果不是則說明主庫 A 刪除了從庫 B 需要的 binlog，主庫 A 直接返回錯誤。
• 主庫 A 從自己的 binlog 文件里面，找到第一個不在集合 y 中的事務 GTID，也就是找到了 91。
• 主庫 A 從 GTID = 91 的事務開始，往后讀 binlog 文件，按順序取 binlog，然后發(fā)給 B。
• 從庫 B 的 I/O 線程讀取 binlog 文件生成 relay log，SQL 線程解析 relay log，然后執(zhí)行 SQL 語句。

GTID 同步方案和位點同步的方案區(qū)別是：

• 位點同步方案是通過人工在從庫上指定哪個位點，主庫就發(fā)哪個位點，不做日志的完整性判斷。
• 而 GTID 方案是通過主庫來自動計算位點的，不需要人工去設置位點，對運維人員友好。

五、恢復從庫的同步

5.1 查看從庫執(zhí)行 GTID 的進度

在從庫上執(zhí)行?show slave status \G來查看 GTID 集合。

Retrieved_Gtid_Set?表示從庫收到的所有日志的 GTID 集合。

Executed_Gtid_Set?表示從庫已經執(zhí)行完成的 GTID 集合。

如果 Executed_Gtid_Set 集合是包含 Retrieved_Gtid_Set，則表示從庫接收到的日志已經同步完成。

這里 Executed_Gtid_Set 的集合為 1-8634831，而 Retrieved_Gtid_Set 為 1-9101426，說明從庫有些 GTID 是沒有執(zhí)行的。從庫已經執(zhí)行到了 8634831，下一個要執(zhí)行的 GTID 為 8634832。

因為我們采用的同步方式是 GTID 方式，所以只要讓從庫跳過這個 GTID ，從下一個 GTID 開始同步就行。

帶來的問題就是這個 GTID 對應的事務沒有執(zhí)行。因為報錯的操作是從庫備份一張大表，所以從庫跳過這個備份操作也是可以接受的。

5.2 手動設置 GTID

來，手動設置一把 GTID 試下。

5.2.1 重置從庫進度

首先重置下從庫同步的進度 reset slave，這條命令會把所有的?relog 給清理掉，重新啟用一個新的 relay log文件。

復制

stop slave;
reset slave;

• 1.
• 2.

重新開啟同步后，主庫會計算主庫 GTID 集合和從庫 GTID 的集合的差集，然后主庫推送差集 binlog 給從庫。

5.2.1 設置 GTID 為一個值

執(zhí)行以下命令設置 GTID 為下一個值。

復制

set gtid_next='c5d74746-d7ec-11ec-bf8f-0242ac110002:8634832';
begin;
commit;
set gtid_next=automatic;
start slave;

• 1.
• 2.
• 3.
• 4.
• 5.

gtid_next 表示設置下一個 GTID = 8634832，這個值是在原來的 8634831 加 1。后面的 begin 和 commit 是提交了一個空事務，把這個 GTID 加到從庫的 GTID 集合中。那么從庫的 GTID 集合就變成了

復制

'c5d74746-d7ec-11ec-bf8f-0242ac110002:1-8634832';

• 1.

5.2.2 查看當前 GTID 集合

我們可以通過 show master status\G 命令來查看從庫的 GTID 集合。下方截圖是執(zhí)行上述命令之前的。GTID集合為 1-8634831。另外 GTID 集合 為 1 和 GTID 集合為 1-4 的可以忽略，因為它們前面的 Master_UUID 不是當前主庫的 uuid。

show master status\G 的結果

也可以通過 show slave status\G 命令來查看 GTID 集合，結果也是一樣的。

5.3 開啟從庫同步

再次啟動從庫的同步（start slave 命令)，I/O 線程和 SQL 線程的狀態(tài)都為 YES，說明啟動成功了。

而且查看從庫的同步狀態(tài)時，觀察到從庫的同步是存在延遲的。通過觀察這個字段 Seconds_Behind_Master 在不斷減小，說明主從同步的延遲越來越小了。

兩個線程都是正常運行，主從同步延遲越來越小

過一段時間后，執(zhí)行的 GTID 等于收到的 GTID 集合，Seconds_Behind_Master = 0，說明主從完全同步了。

六、原因

上面的推測：備份大表造成 binlog 的一條日志太大，relay log 也跟著變大，SQL 線程無法正常解析。

但這是真相嗎？

雖然從庫重新開啟了同步，且跳過了這條日志，但帶來的是從庫上就不會出現這個備用表 xx_dance_0404 。

但出現了兩個奇怪的問題：

問題 1：從庫開啟同步后，居然出現了這個備份表 xx_dance_0404。不是跳過這個備份操作了嗎？目前沒想到原因。

問題 2：為了重現這個問題，我到主庫上做了一個備份表的操作，表名為 xx_dance_0412，從庫也同步了這個新的備份表 xx_dance_0412。而且 binlog 出現的日志現象也是一樣的，對應的這條 binlog 日志也很大，但是從庫同步正常。我又備份了一張 300 萬的大表，依然沒重現。

通過問題 2 可以說明上面的推測是錯誤的，備份大表并不會影響主從同步。

那么 relay log 報錯的原因是什么？

只有一個原因了，relay log 文件真的是損壞的，從庫的狀態(tài)上也說明了原因，relay log is corrupted（損壞）。SQL 線程去解析 relay log 時報錯了，導致 SQL 線程停止，從庫不能正常執(zhí)行同步。

小結：relay log 損壞了，導致從庫的 SQL 線程解析 relay log 時出現異常。從庫恢復方式是通過手動設置當時出錯的 GTID 的下一個值，讓從庫不從主庫同步這個 GTID，最后從庫就能正常同步這個 GTID?之后的 binlog 了，后續(xù) SQL 線程也能正常解析 relay log 了。

relay log 損壞了 刪除了重新傳不就可以了

?

一個月前，我們在測試環(huán)境部署了一套 MySQL 高可用架構，也就是 MySQL 雙主 + Keepalived 的模式。詳情看這篇：

實戰(zhàn) MySQL 高可用架構

在這一個月遇到了很多坑：

• 因為兩個 MySQL 節(jié)點都可以寫入，極其容易造成主鍵重復，進而導致主從同步失敗。--可以設置主鍵每次加2

• 同步失敗后，Slave_SQL_Thread 線程就停了，除非解決了同步的錯誤，才能繼續(xù)進行同步。

• 同步失敗的錯誤，不會只有一條記錄有問題，往往是一大片的同步問題。

• 兩個節(jié)點互相缺少對方的數據。

• 主從的同步延遲，切換到新主庫后，數據不是最新。

• 當出現不一致時，無法確定以哪個庫為準。

造成上面問題的主要原因就是因為兩個節(jié)點都支持寫入 + 雙主可以隨時切換。

解決這種問題的方案有 改進自增主鍵的步長（影響未評估），使用 GTID 方案（未驗證）。即使這樣，雙主同步的風險還是有，而且不同步后，如何處理是個大難題。

那么回到我們最初的想法：為什么會選擇雙主？

最開始的目的就是為了高可用。雙主就是說有一臺 MySQL 節(jié)點掛了，另外一臺能夠頂上，對于用戶來說是無感的，給運維人員一定的緩沖時間來排查 MySQL 故障。另外老的主節(jié)點恢復后，不用改配置就能立即成為從節(jié)點。

經過這一個月的 MySQL 雙主模式的試運行，最后我們還是決定切換到 MySQL 主 - 從模式。

雙主模式就是兩個節(jié)點即是主節(jié)點也是從節(jié)點，那我們現在切換到一主一從模式，就可以認為是降級。接下來我們聊聊雙主換成主從的思路和步驟。

二、雙主降為主從

雙主模式

雙主模式的原理圖如下：

兩個主節(jié)點，都安裝了 KeepAlived 高可用組件，對外提供了一個 VIP，只有一個節(jié)點接管 VIP，客戶端訪問的請求都是到這個 VIP，另外一個節(jié)點處于待機狀態(tài)。

主從模式

和雙主不一樣的地方如下，從節(jié)點是只讀的。

一主一從是主從模式中的一種，具有以下特點：

• 一個主節(jié)點，一個從節(jié)點，主節(jié)點提供給客戶端訪問，從節(jié)點只通過主節(jié)點的 binlog 進行數據同步。

• 從節(jié)點是只讀的。從節(jié)點可以作為只讀節(jié)點提供類似報表查詢等耗時讀操作。

• 主節(jié)點宕機后，從節(jié)點成為主節(jié)點，也是高可用的一種方案。

相對于雙主的高可用方案，不同之處如下：

• 主從切換需要用腳本將從庫設置為可讀可寫。

• 主從切換后，需要將從庫設置為不同步老主庫。

• 主從切換后，老的主庫恢復后，需要人工設置為只讀，且開啟同步新主庫的功能。

這樣來看，主從模式在異常情況下，多了些人工操作。

在異常情況下，主從切換一般是這樣處理的：通過腳本監(jiān)測主節(jié)點是否宕機，如果主庫宕機了，則從庫自動切換為新的主庫，待老主庫恢復后，就作為從庫同步新主庫數據，新主庫上的 Keepalived 接管 VIP。

目前改為主從模式有兩種方式：

• 簡單方式：人工切換模式，主節(jié)點故障后需要人工切換主從。

• 復雜方式：高可用方式，主節(jié)點故障后，主從自動切換，讀寫分離自動切換。

本篇只涉及簡單方式，復雜方式的原理和配置步驟放到下篇專門講解。

三、改為主從的簡單方式

簡單方式的主從切換流程如下：

和雙主模式的主從切換的區(qū)別是，從節(jié)點是只讀的，Keepalived 沒有啟動，需要人工操作主從切換和啟動 Keepalived。

修改配置的步驟如下：

① 為了避免從節(jié)點上的 Keepalived 自動接管 VIP 的情況出現，將從節(jié)點的 Keepalived 停止，如果遇到主節(jié)點故障，則需要人工干預來進行主從切換。從節(jié)點切換為主節(jié)點后，重新啟動從節(jié)點 Keepalived。

systemctl status keepalived

復制代碼

② 保留主節(jié)點的 Keepalived，保證 MySQL 的連接信息都不需要變。

③ 主節(jié)點 node1 停用 MySQL 的同步線程。

STOP SLAVE

復制代碼

④ 從節(jié)點 node2 設置 MySQL 為只讀模式。

# 修改 my.cnf 文件read_only = 1

復制代碼

⑤ 移除主節(jié)點 node1 同步 node2 MySQL 的權限。

⑥ 從節(jié)點 node1 的開機啟動項中移除 keepalived 服務自啟動。

# 修改啟動項配置sudo vim /etc/rc.local# 移除以下腳本systemctl start keepalived

復制代碼

四、總結

雙主高可用的坑確實比較多，沒有 MySQL 的硬核知識真的很難搞定。筆者在這一個月的實踐中，深刻體會到了雙主同步的難點所在，最后還是選擇了一主一從的模式。

另外因為最開始的配置都是雙主模式下的，所以要修改一些配置，來改為主從模式。因項目時間比較緊，目前采取的是非高可用的主從模式。

對于高可用的主從模式，因涉及的原理和步驟較多，我會在下篇中進行講解。各位卷王也請給我一點時間進行探索和實踐~

一、背景

為了保證高可用，之前在測試環(huán)境部署了一套 MySQL 雙主模式，當一個主庫服務出現異常，可以將流量切到另外一個主庫，兩個主庫之間相互同步數據。

雙主模式

雙主模式的原理圖如下：

但是經常出現數據沖突的問題，于是我們又把??雙主模式??改為了??主從讀寫分離模式??。

這樣設置解決，所以mysql的邏輯同步不能保證完全一致。適合互聯網行業(yè)。（解決復制異常的事務。無法解決時就需要手動跳過指定類型的錯誤，比如通過設置slave_skip_errors=1032,1062。當然這個前提條件是跳過這類錯誤是無損的。（1062 錯誤是插入數據時唯一鍵沖突；1032 錯誤是刪除數據時找不到行））

主庫作為讀寫庫，再加上一個從庫用來做?I/O 密集型的任務（如大量的數據統(tǒng)計操作）。如下圖所示：

另外從庫復制的模式采用??位點??的方式：指定 binlog 文件和 binlog 位置，這樣從庫就知道了復制的起始位置。(下文會講解這種方式）

雖然改為了主從模式，但依舊遇到了些問題：

• 問題 1：從庫 B 復制數據時，出現了主鍵沖突問題，導致同步失敗，從庫停止復制。猜測因主庫配置的 binlog 日志的格式為?mixed，從庫同步時出現不一致的情況。
• 問題 2：從庫 B 停止復制后，導致很多數據未同步到從庫，出現主從大量數據不一致的情況。
• 問題 3：從庫 B 想要恢復復制，必須先解決同步失敗的問題才能恢復。排查難度較大，耗時。
• 問題 4：從庫 B 恢復時，必須知道同步位點，也就是從哪個 binlog 文件和 binlog 位置斷開復制的，且即使找到了位點，也不是精確的。
• 問題 5：從庫 B 因同步異常導致停止復制到恢復復制這段期間，主庫 A 自動清理了幾天前的 binlog 日志，而這些日志從庫 B 還未來得及同步，進而導致再次同步失敗。
• 問題 6：主從存在同步延遲。

這篇我們來探討下問題 4 和問題 6。

其中問題 4?是一個比較頭疼的問題，我們一般是通過查看從庫 B 當前的同步狀態(tài)拿到同步位點，然后設置同步位點后。但是重新啟動同步的時候又會出現同步異常，比如從庫 B 可能會出現 Duplicate entry ‘id_of_R’ for key ‘PRIMARY’ 錯誤，提示出現了主鍵沖突，然后停止同步。

為了減少位點同步引入的復雜度，我們切換成了 GTID 模式。

對于問題 6，本篇也僅限于探討如何觀察延遲，對于如何減少延遲不在本篇探討范圍之內。

接下來我們來展開看下位點同步的痛點。

二、位點同步的痛點

2.1 通過位點同步的原理圖

為了更清晰地理解主從采用位點同步的原理，這里有一個原理圖：

1、主庫會生成多個 binlog 日志文件。

2、從庫的I/O 線程請求指定文件和指定位置的 binlog 日志文件（位點）。

3、主庫 dump 線程獲取指定位點的 binlog 日志。

4、主庫按照從庫發(fā)送給來的位點信息讀取 binlog，然后推送 binlog 給從庫。

5、從庫將得到的 binlog 寫到本地的 relay log (中繼日志) 文件中。

6、從庫的 SQL 線程讀取和解析 relay log 文件。

7、從庫的 SQL 線程重放 relay log 中的命令。

當我們使用位點同步的方式時，兩種場景下的操作步驟比較復雜。

2.2 痛點

痛點1：首次開啟主從復制的步驟復雜

• 第一次開啟主從同步時，要求從庫和主庫是一致的。
• 找到主庫的 binlog 位點。
• 設置從庫的 binlog 位點。
• 開啟從庫的復制線程。

痛點2：恢復主從復制的步驟復雜

• 找到從庫復制線程停止時的位點。
• 解決復制異常的事務。無法解決時就需要手動跳過指定類型的錯誤，比如通過設置slave_skip_errors=1032,1062。當然這個前提條件是跳過這類錯誤是無損的。（1062 錯誤是插入數據時唯一鍵沖突；1032 錯誤是刪除數據時找不到行）

不論是首次開啟同步時需要找位點和設置位點，還是恢復主從復制時，設置位點和忽略錯誤，這些步驟都顯得過于復雜，而且容易出錯。所以 MySQL 5.6 版本引入了 GTID，徹底解決了這個困難。

三、GTID 方案

3.1 GTID 是什么？

GTID 的全稱是 Global Transaction Identifier，全局事務 ID，當一個事務提交時，就會生成一個 GTID，相當于事務的唯一標識。

GTID 長這樣：

復制

c5d74746-d7ec-11ec-bf8f-0242ac110002:1

• 1.

結構：

復制

GTID=server_uuid:gno

• 1.

server_uuid 是一個實例第一次啟動時自動生成的，是一個全局唯一的值；

gno 是一個整數，初始值是 1，每次提交事務的時候分配給這個事務，并加 1。

每個 MySQL 實例都維護了一個 GTID 集合，用來對應“這個實例執(zhí)行過的所有事務”。

3.2 GTID 的優(yōu)勢

• 更簡單的實現 failover，不用以前那樣在需要找位點（log_file 和 log_pos）。
• 更簡單的搭建主從復制。
• 比傳統(tǒng)的復制更加安全。
• GTID是連續(xù)的沒有空洞的，保證數據的一致性，零丟失。

3.3 如何啟用 GTID

修改主庫和從庫的配置文件：

復制

#GTID:
gtid_mode=on
enforce_gtid_cnotallow=on

• 1.
• 2.
• 3.

從庫配置同步的參數：

復制

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_positinotallow=1 

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

其中 master_auto_position 標識主從關系使用的 GTID 協議。

相比之前的配置，MASTER_LOG_FILE 和 MASTER_LOG_POS 參數已經不需要了。

3.4 GTID 同步方案

GTID 同步的原理圖。

GTID 方案：主庫計算主庫 GTID 集合和從庫 GTID 的集合的差集，主庫推送差集 binlog 給從庫。

當從庫設置完同步參數后，主庫 A 的GTID 集合記為集合 x，從庫 B 的 GTID 集合記為 y。從庫同步的邏輯如下：

• 從庫 B 指定主庫 A，基于主備協議簡歷連接。
• 從庫 B 把集合 y 發(fā)給主庫 A。
• 主庫 A 計算出集合 x 和集合 y 的差集，也就是集合 x 中存在，集合 y 中不存在的 GTID 集合。比如集合 x 是 1~100，集合 y 是 1~90，那么這個差集就是 91~100。這里會判斷集合 x 是不是包含有集合 y 的所有 GTID，如果不是則說明主庫 A 刪除了從庫 B 需要的 binlog，主庫 A 直接返回錯誤。
• 主庫 A 從自己的 binlog 文件里面，找到第一個不在集合 y 中的事務 GTID，也就是找到了 91。
• 主庫 A 從 GTID = 91 的事務開始，往后讀 binlog 文件，按順序取 binlog，然后發(fā)給 B。
• 從庫 B 的 I/O 線程讀取 binlog 文件生成 relay log，SQL 線程解析 relay log，然后執(zhí)行 SQL 語句。

GTID 同步方案和位點同步的方案區(qū)別是：

• 位點同步方案是通過人工在從庫上指定哪個位點，主庫就發(fā)哪個位點，不做日志的完整性判斷。
• 而 GTID 方案是通過主庫來自動計算位點的，不需要人工去設置位點，對運維人員友好。

四、如何判斷主從庫是否有延遲

上面提到的問題 6 是主從讀寫分離后，從庫復制存在延遲，接下來我們來探討下如何觀察主從延遲多少的問題。

方案一：判斷從庫的同步狀態(tài)參數 seconds_behind_master 是否為 0。（不準確）

方案二：對比位點確保主備無延遲。

方案三：對比 GTID 集合確保主備無延遲。

方案一：查看 seconds_behind_master

可以在從庫上執(zhí)行 slow slave status 命令來看執(zhí)行結果里面的???seconds_behind_master???參數的值，如下圖所示，Seconds_Behind_Master 等于 0

Seconds_Behind_Master 的單位是秒，所以精度不準確。

所以為了保證查詢的數據是和主庫一致的，就需要先判斷 seconds_behind_master 是否已經等于 0，如果不等于 0，就必須等到這個參數變?yōu)?0 才能執(zhí)行查詢請求。

方案二：對比位點

可以通過查看從庫當前的同步位點來確認從庫同步是否有延遲。下圖是在從庫上執(zhí)行???show slave status \G??命令后的結果：

Master_Log_File??和?Read_Master_Log_Pos?這兩個參數合起來表示的是讀到的主庫的最新位點，第一參數是代表讀取到了哪個文件，第二個是讀取到的文件的位置。

Relay_Master_Log_File??和?Exec_Master_Log_Pos，這兩個參數合起來表示的是從庫執(zhí)行的最新位點。

如果紅色框起來的兩個參數：Master_Log_File??和?Relay_Master_Log_File?相等，則說明從庫讀到的最新文件和主庫上生成的文件相同，這里都是 mysql-bin.000934。

如果藍色框起來的兩個參數?Read_Master_Log_Pos??和?Exec_Master_Log_Pos?相等，則說明從庫讀到的日志文件的位置和從庫上執(zhí)行日志文件的位置相同，這里都是 59521082。

當上面兩組參數都相等時，則說明沒有延遲。

方案三：對比 GTID 集合

方案三是對比 GTID 集合。首先我們在從庫上執(zhí)行 ?show slave status \G來查看 GTID 集合。

如下圖所示：

Master_UUID?表示當前連接的主庫的 ID。

Auto_Position: 1 表示主備使用了 GTID 協議。

Retrieved_Gtid_Set?表示從庫收到的所有日志的 GTID 集合。

Executed_Gtid_Set?表示從庫已經執(zhí)行完成的 GTID 集合。

如果 Executed_Gtid_Set 集合是包含 Retrieved_Gtid_Set，則表示從庫接收到的日志已經同步完成。

比如上圖中?Retrieved_Gtid_Set?值為

復制

c5d74746-d7ec-11ec-bf8f-0242ac110002:1-87323

• 1.

前面一段是主庫 id，后面一段 1-87383 是 GTID 范圍。而Executed_Gtid_Set?的值有兩個集合

復制

7083ae1f-d7ef-11ec-a329-0242ac110002:1-2,
c5d74746-d7ec-11ec-bf8f-0242ac110002:1-87323

• 1.
• 2.

Executed_Gtid_Set?的第二個集合和第一個集合完全一致，第一個集合 id 和 集合范圍是上次同步另外一個主庫的記錄。這里說明從庫已經和當前主庫同步完成了。

方案二對比位點和方案三的 GTID 比對都要比方案一的seconds_behind_master?更準確。但是還是沒有達到精確的程度，需要配合半同步復制（semi-sync replication）才能達到。

小結：本篇通過 GTID 的方式更好地實現了主從節(jié)點的同步，以及如何觀察主從同步的延遲。

----------------------------------------------------------------------------

事故現場

• 環(huán)境：測試環(huán)境

• 時間：上午10:30

• 反饋人員：測試群，炸鍋了，研發(fā)同事初步排查后，發(fā)現可能是數據庫問題。

然后就開始找原因吧。因為這套集群環(huán)境是我部署的，所以我來排查的話輕車熟路。

系統(tǒng)部署圖

先說下系統(tǒng)的部署圖，方便大家理解。

兩個數據庫部署在 node55 和 node56 節(jié)點上，它們互為主從關系，所以叫做雙主。

還有兩個 Keepalived 部署在 node55 和 node56 上面，分別監(jiān)控 MySQL 容器的狀態(tài)。

報錯原因和解決方案

我第一個想法就是，不是有 Keepalived 來保證高可用么？即使 MySQL 掛了，也可以通過 Keepalived 來自動重啟才對。即使一臺重啟不起來，還有另外一臺可以用的吧？

那就到服務器上看下 MySQL 容器的狀態(tài)吧。到 MySQL 的兩臺服務器上，先看下 MySQL 容器的狀態(tài)，docker ps ?命令，發(fā)現兩臺 MySQL 容器都不在列表中，這代表容器沒正常運行。

這不可能。我可是安裝了 Keepalived 高可用組件的，難道 Keepalived 也掛了？

趕緊檢查一波 Keepalived，發(fā)現兩臺 Keepalived 是正常運行的。通過執(zhí)行命令查看：systemctl status keepalived。

納尼？Keepalived 也是正常的。Keepalived 每隔幾秒會重啟 MySQL，可能我在那一小段空閑時間沒看到 MySQL 容器啟動？換個命令執(zhí)行下，docker ps -a，列出所有容器的狀態(tài)?？梢钥吹?MySQL 啟動后又退出了，說明 MySQL 確實是在重啟。

那說明 Keepalived 雖然重啟了 MySQL 容器，但是 MySQL 自身有問題，那 Keepalived 的高可用也沒辦法了。

那怎么整？只能看下 MySQL 報什么錯了。執(zhí)行查看容器日志的命令。docker logs <容器 id>。找到最近發(fā)生的日志：

提示 mysql-bin.index 文件不存在，這個文件是配置在主從同步那里的，在 my.cnf 配置里面。

這個配置好后，然后執(zhí)行主從同步的時候，就會在 var/lib/mysql/log 目錄下生成多個 mysql-bin.xxx 的文件。還有一個 mysql-bin.index 索引文件，它會標記現在 binlog 日志文件記錄到哪里了。

mysql-bin.index 文件里面的內容如下：

/var/lib/mysql/log/mysql-bin.000001

這個 mysql-bin.000001 文件還是帶序號的，這里還有坑，后面我再說。

報錯信息是提示缺少 mysql-bin.index，那我們就去檢查下唄，確實沒有?。∠炔还苓@個文件怎么消失的吧，趕緊把這個 log 文件夾先創(chuàng)建出來，然后 mysql 會自動給我們生成這個文件的。

解決方案：執(zhí)行以下命令創(chuàng)建文件夾和添加權限。

mkdir logchmod?777?log?-R

兩臺服務器上都有這個 log 目錄后，Keepalived 也幫我們自動重啟好了 MySQL 容器，再來訪問下其中一個節(jié)點 node56 的 MySQL 的狀態(tài)。咦？居然報錯了。

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

可以看到幾個關鍵信息：

• Slave_IO_Running: NO，當前同步的 I/O 線程沒有運行，這個 I/O 線程是從庫的，它會去請求主庫的 binlog，并將得到的 binlog 寫到本地的 relay-log (中繼日志)文件中。沒有運行，則代表從庫同步是沒有正常運行。

• Master_Log_File: mysql-bin.000014，說明當前同步的日志文件為?000014，之前我們看到節(jié)點 node56 上 mysql.index 里面寫的是 000001，這個 000014 根本就不在 index 文件里面，所以就會報錯了。

這里涉及到主從同步的原理，上一張圖：

• 從庫會生成兩個線程,一個 I/O 線程,一個 SQL 線程;

• I/O線程會去請求主庫的 binlog 日志文件,并將得到的 binlog 日志文件 寫到本地的 relay-log (中繼日志)文件中;

• 主庫會生成一個 dump 線程,用來給從庫 I/O 線程傳 binlog;

• SQL線程，會讀取 relay log 文件中的日志,并解析成 SQL 語句逐一執(zhí)行。

那好辦啊，我們重新指定下同步哪個日志文件，以及同步的位置就好了。

解決方案

看下主庫 node55 上日志文件狀態(tài)。

記下這兩個信息：File=mysql-bin.00001，Position=117748。

（這里也有個坑：先要鎖表，再看這兩個值，從庫開始同步后，再解鎖表）。

具體執(zhí)行的命令如下：

FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUSUNLOCK TABLES

然后在從庫 node56 上重新指定同步的日志文件和位置：

# 停止從庫同步STOP SLAVE;
# 設置同步文件和位置CHANGE MASTER TO MASTER_HOST='10.2.1.55',MASTER_PORT=3306,MASTER_USER='vagrant',MASTER_PASSWORD='vagrant',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=117748;
# 開啟同步START SLAVE;

再次查看就不報錯了，I/O 線程也跑起來了，

然后將 node55 當做從庫，node56 當做主庫，同樣執(zhí)行上面的幾步，狀態(tài)顯示正常了，然后用 navicat 工具連下數據庫，都是正常的。在測試群反饋下結果，搞定收工。

好像忘了一個問題，為啥 log 文件夾被干掉了？？

為什么會出現問題？

然后問了一波當時有沒有人刪除這個 /var/lib/mysql/log 目錄，也沒有人會隨便刪除這個目錄的吧。

但是發(fā)現 log 的上級目錄 /var/lib/mysql 有很多其他文件夾，比如 xxcloud, xxcenter 等。這不就是我們項目中幾個數據庫的名字么，只要在這個目錄的文件夾，都會顯示在 navicat 上，是一一對應的，如下圖所示。其中也顯示了 log 數據庫。

那會不會有人從 navicat 上干掉了 log 數據庫？極有可能??！

果然，有位同事之前在遷移升級的過程中，發(fā)現這個 log 數據庫在老的系統(tǒng)是沒有的，所以就清理了。這就相當于把 log 數據庫干掉了，同時也會把 log 文件夾干掉了。好了，終于水落石出了！這個其實也是我前期沒有考慮到 log 目錄的一個問題。沒錯，這是我的鍋~

改進

其實操作同步數據庫的時候，不應該用這種覆蓋同步的方式?？梢圆扇螏焱降姆绞?，也就不會干掉 log 數據庫了。但是，這個 log 數據庫放在這里有點奇怪啊，能不能不要出現在這里呢？

每次新建庫都要設置同步

我們只要指定這個 log 目錄不在 ?/var/lib/mysql 目錄下就好了。

東哥建議：log 文件和數據庫 data 文件進行隔離：

datadir?=?/var/lib/mysql/datalog_bin = /var/lib/mysql/log

另外一個問題，我們的高可用真的高可用了嗎？

至少沒有做到及時報警，MySQL 數據庫掛了，我是不知道的，都是通過測試同學反饋的。

能不能及時感知到 MySQL 異常呢？

這里可以利用 Keepalived 發(fā)送郵件的功能，或者通過日志報警系統(tǒng)。這個是后面需要改進的地方。文章來源地址http://www.zghlxwxcb.cn/news/detail-413009.html

到了這里，關于mysql 主從同步案例集合的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！