淺談如何保證Mysql主從一致

2022-03-10 13:01:36

在備庫 B 上通過 change master 命令，設定主庫 A 的 IP、埠、使用者名稱、密碼，以及要從哪個位置開始請求 binlog，這個位置包含檔名和紀錄檔偏移量。
在備庫 B 上執行 start slave 命令，這時候備庫會啟動兩個執行緒，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負責與主庫建立連線。
主庫 A 校驗完使用者名稱、密碼後，開始按照備庫 B 傳過來的位置，從本地讀取 binlog，發給 B。
備庫 B 拿到 binlog 後，寫到本地檔案，稱為中轉紀錄檔（relay log）。
sql_thread 讀取中轉紀錄檔，解析出紀錄檔裡的命令，並執行。

binlog 的三種格式對比

binlog 有兩種格式，一種是 statement，一種是 row。可能你在其他資料上還會看到有第三種格式，叫作 mixed，其實它就是前兩種格式的混合。

為了便於描述 binlog 的這三種格式間的區別，建立了一個表，並初始化幾行資料。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;
 
insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

如果要在表中刪除一行資料的話，我們來看看這個 delete 語句的 binlog 是怎麼記錄的。

下面這個語句包含註釋，如果你用 MySQL 使用者端來做這個實驗的話，要記得加 -c 引數，否則使用者端會自動去掉註釋。

mysql> delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;

當 binlog_format=statement 時，binlog 裡面記錄的就是 SQL 語句的原文。你可以用

mysql> show binlog events in 'master.000001';

命令看 binlog 中的內容。

圖 3 statement 格式 binlog 範例

第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’你可以先忽略，後面文章會在介紹主備切換的時候再提到；
第二行是一個 BEGIN，跟第四行的 commit 對應，表示中間是一個事務；
第三行就是真實執行的語句了。可以看到，在真實執行的 delete 命令之前，還有一個“use ‘test’”命令。這條命令不是我們主動執行的，而是 MySQL 根據當前要操作的表所在的資料庫，自行新增的。這樣做可以保證紀錄檔傳到備庫去執行的時候，不論當前的工作執行緒在哪個庫裡，都能夠正確地更新到 test 庫的表 t。
use 'test’命令之後的 delete 語句，就是我們輸入的 SQL 原文了。可以看到，binlog“忠實”地記錄了 SQL 命令，甚至連註釋也一併記錄了。
最後一行是一個 COMMIT。你可以看到裡面寫著 xid=61。你還記得這個 XID 是做什麼用的嗎？

為了說明 statement 和 row 格式的區別，我們來看一下這條 delete 命令的執行效果圖：

圖 4 delete 執行 warnings

執行這條 delete 命令產生了一個 warning，原因是當前 binlog 設定的是 statement 格式，並且語句中有 limit，所以這個命令可能是 unsafe 的。

這是因為 delete 帶 limit，很可能會出現主備資料不一致的情況。比如上面這個例子：

如果 delete 語句使用的是索引 a，那麼會根據索引 a 找到第一個滿足條件的行，也就是說刪除的是 a=4 這一行；
但如果使用的是索引 t_modified，那麼刪除的就是 t_modified='2018-11-09’也就是 a=5 這一行。

由於 statement 格式下，記錄到 binlog 裡的是語句原文，因此可能會出現這樣一種情況：在主庫執行這條 SQL 語句的時候，用的是索引 a；而在備庫執行這條 SQL 語句的時候，卻使用了索引 t_modified。因此，MySQL 認為這樣寫是有風險的。

那麼，如果我把 binlog 的格式改為 binlog_format=‘row’，是不是就沒有這個問題了呢？

圖 5 row 格式 binlog 範例

與 statement 格式的 binlog 相比，前後的 BEGIN 和 COMMIT 是一樣的。但是，row 格式的 binlog 裡沒有了 SQL 語句的原文，而是替換成了兩個 event：Table_map 和 Delete_rows。

Table_map event，用於說明接下來要操作的表是 test 庫的表 t;
Delete_rows event，用於定義刪除的行為

其實，我們通過圖 5 是看不到詳細資訊的，還需要藉助 mysqlbinlog 工具，用下面這個命令解析和檢視 binlog 中的內容。因為圖 5 中的資訊顯示，這個事務的 binlog 是從 8900 這個位置開始的，所以可以用 start-position 引數來指定從這個位置的紀錄檔開始解析。

mysqlbinlog -vv data/master.000001 --start-position=8900;

圖 6 row 格式 binlog 範例的詳細資訊

從這個圖中，我們可以看到以下幾個資訊：

server id 1，表示這個事務是在 server_id=1 的這個庫上執行的。
每個 event 都有 CRC32 的值，這是因為我把引數 binlog_checksum 設定成了 CRC32。
Table_map event 跟在圖 5 中看到的相同，顯示了接下來要開啟的表，map 到數位 226。現在我們這條 SQL 語句只操作了一張表，如果要操作多張表呢？每個表都有一個對應的 Table_map event、都會 map 到一個單獨的數位，用於區分對不同表的操作。
我們在 mysqlbinlog 的命令中，使用了 -vv 引數是為了把內容都解析出來，所以從結果裡面可以看到各個欄位的值（比如，@1=4、 @2=4 這些值）。
binlog_row_image 的預設設定是 FULL，因此 Delete_event 裡面，包含了刪掉的行的所有欄位的值。如果把 binlog_row_image 設定為 MINIMAL，則只會記錄必要的資訊，在這個例子裡，就是隻會記錄 id=4 這個資訊。
最後的 Xid event，用於表示事務被正確地提交了。

當 binlog_format 使用 row 格式的時候，binlog 裡面記錄了真實刪除行的主鍵 id，這樣 binlog 傳到備庫去的時候，就肯定會刪除 id=4 的行，不會有主備刪除不同行的問題。

為什麼會有 mixed 格式的 binlog？

基於上面的資訊，我們來討論一個問題：為什麼會有 mixed 這種 binlog 格式的存在場景？推論過程是這樣的：

因為有些 statement 格式的 binlog 可能會導致主備不一致，所以要使用 row 格式。
但 row 格式的缺點是，很佔空間。比如你用一個 delete 語句刪掉 10 萬行資料，用 statement 的話就是一個 SQL 語句被記錄到 binlog 中，佔用幾十個位元組的空間。但如果用 row 格式的 binlog，就要把這 10 萬條記錄都寫到 binlog 中。這樣做，不僅會佔用更大的空間，同時寫 binlog 也要耗費 IO 資源，影響執行速度。
所以，MySQL 就取了個折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己會判斷這條 SQL 語句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。

也就是說，mixed 格式可以利用 statment 格式的優點，同時又避免了資料不一致的風險。

因此，如果你的線上 MySQL 設定的 binlog 格式是 statement 的話，那基本上就可以認為這是一個不合理的設定。你至少應該把 binlog 的格式設定為 mixed。

現在越來越多的場景要求把 MySQL 的 binlog 格式設定成 row。這麼做的理由有很多，我來給你舉一個可以直接看出來的好處：恢復資料。

接下來，我們就分別從 delete、insert 和 update 這三種 SQL 語句的角度，來看看資料恢復的問題。

通過圖 6 你可以看出來，即使我執行的是 delete 語句，row 格式的 binlog 也會把被刪掉的行的整行資訊儲存起來。所以，如果你在執行完一條 delete 語句以後，發現刪錯資料了，可以直接把 binlog 中記錄的 delete 語句轉成 insert，把被錯刪的資料插入回去就可以恢復了。

如果你是執行錯了 insert 語句呢？那就更直接了。row 格式下，insert 語句的 binlog 裡會記錄所有的欄位資訊，這些資訊可以用來精確定位剛剛被插入的那一行。這時，你直接把 insert 語句轉成 delete 語句，刪除掉這被誤插入的一行資料就可以了。

如果執行的是 update 語句的話，binlog 裡面會記錄修改前整行的資料和修改後的整行資料。所以，如果你誤執行了 update 語句的話，只需要把這個 event 前後的兩行資訊對調一下，再去資料庫裡面執行，就能恢復這個更新操作了。

其實，由 delete、insert 或者 update 語句導致的資料操作錯誤，需要恢復到操作之前狀態的情況，也時有發生。MariaDB 的Flashback工具就是基於上面介紹的原理來回滾資料的。

雖然 mixed 格式的 binlog 現在已經用得不多了，但這裡我還是要再借用一下 mixed 格式來說明一個問題，來看一下這條 SQL 語句：

mysql> insert into t values(10,10, now());

如果我們把 binlog 格式設定為 mixed，你覺得 MySQL 會把它記錄為 row 格式還是 statement 格式呢？

先不要著急說結果，我們一起來看一下這條語句執行的效果。

圖 7 mixed 格式和 now()

可以看到，MySQL 用的居然是 statement 格式。你一定會奇怪，如果這個 binlog 過了 1 分鐘才傳給備庫的話，那主備的資料不就不一致了嗎？

接下來，我們再用 mysqlbinlog 工具來看看：

圖 8 TIMESTAMP 命令

從圖中的結果可以看到，原來 binlog 在記錄 event 的時候，多記了一條命令：SET TIMESTAMP=1546103491。它用 SET TIMESTAMP 命令約定了接下來的 now() 函數的返回時間。

因此，不論這個 binlog 是 1 分鐘之後被備庫執行，還是 3 天后用來恢復這個庫的備份，這個 insert 語句插入的行，值都是固定的。也就是說，通過這條 SET TIMESTAMP 命令，MySQL 就確保了主備資料的一致性。我之前看過有人在重放 binlog 資料的

之前看過有人在重放 binlog 資料的時候，是這麼做的：用 mysqlbinlog 解析出紀錄檔，然後把裡面的 statement 語句直接拷貝出來執行。你現在知道了，這個方法是有風險的。因為有些語句的執行結果是依賴於上下文命令的，直接執行的結果很可能是錯誤的。

所以，用 binlog 來恢復資料的標準做法是，用 mysqlbinlog 工具解析出來，然後把解析結果整個發給 MySQL 執行。類似下面的命令

mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

迴圈複製問題

我們可以認為正常情況下主備的資料是一致的。也就是說，圖 1 中 A、B 兩個節點的內容是一致的。其實，圖 1 中的是 M-S 結構，但實際生產上使用比較多的是雙 M 結構，也就是圖 9 所示的主備切換流程。

圖 9 MySQL 主備切換流程 -- 雙 M 結構

對比圖 9 和圖 1，你可以發現，雙 M 結構和 M-S 結構，其實區別只是多了一條線，即：節點 A 和 B 之間總是互為主備關係。這樣在切換的時候就不用再修改主備關係。

但是，雙 M 結構還有一個問題需要解決。

業務邏輯在節點 A 上更新了一條語句，然後再把生成的 binlog 發給節點 B，節點 B 執行完這條更新語句後也會生成 binlog。（我建議你把引數 log_slave_updates 設定為 on，表示備庫執行 relay log 後生成 binlog）

那麼，如果節點 A 同時是節點 B 的備庫，相當於又把節點 B 新生成的 binlog 拿過來執行了一次，然後節點 A 和 B 間，會不斷地迴圈執行這個更新語句，也就是迴圈複製了。這個要怎麼解決呢？

從上面的圖 6 中可以看到，MySQL 在 binlog 中記錄了這個命令第一次執行時所在範例的 server id。因此，我們可以用下面的邏輯，來解決兩個節點間的迴圈複製的問題：

規定兩個庫的 server id 必須不同，如果相同，則它們之間不能設定為主備關係；
一個備庫接到 binlog 並在重放的過程中，生成與原 binlog 的 server id 相同的新的 binlog；
每個庫在收到從自己的主庫發過來的紀錄檔後，先判斷 server id，如果跟自己的相同，表示這個紀錄檔是自己生成的，就直接丟棄這個紀錄檔。

按照這個邏輯，如果我們設定了雙 M 結構，紀錄檔的執行流就會變成這樣：

從節點 A 更新的事務，binlog 裡面記的都是 A 的 server id；
傳到節點 B 執行一次以後，節點 B 生成的 binlog 的 server id 也是 A 的 server id；
再傳回給節點 A，A 判斷到這個 server id 與自己的相同，就不會再處理這個紀錄檔。所以，死迴圈在這裡就斷掉了。

小結

binlog 在 MySQL 的各種高可用方案上扮演了重要角色。今天介紹的可以說是所有 MySQL 高可用方案的基礎。在這之上演化出了諸如多節點、半同步、MySQL group replication 等相對複雜的方案。

思考題：說到迴圈複製問題的時候，我們說 MySQL 通過判斷 server id 的方式，斷掉死迴圈。但是，這個機制其實並不完備，在某些場景下，還是有可能出現死迴圈？又應該怎麼解決呢？

答案：一種場景是，在一個主庫更新事務後，用命令 set global server_id=x 修改了 server_id。等紀錄檔再傳回來的時候，發現 server_id 跟自己的 server_id 不同，就只能執行了。

另一種場景是，有三個節點的時候，如圖 7 所示，trx1 是在節點 B 執行的，因此 binlog 上的 server_id 就是 B，binlog 傳給節點 A，然後 A 和 A’搭建了雙 M 結構，就會出現迴圈複製。

到此這篇關於淺談如何保證Mysql主從一致的文章就介紹到這了,更多相關Mysql 主從一致內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

淺談如何保證Mysql主從一致

目錄

binlog 的三種格式對比

為什麼會有 mixed 格式的 binlog？

迴圈複製問題

小結

熱門文章