MySQL45講 第二十四講 MySQL是怎么保證主備一致的？——閱讀總結(jié)

在 MySQL 數(shù)據(jù)庫的世界里，主備一致性是確保數(shù)據(jù)可靠性和高可用性的關(guān)鍵。就深入探討 MySQL 是如何保證主備一致的，這涉及到 binlog 的多種格式、主備復(fù)制的詳細(xì)流程以及雙 M 結(jié)構(gòu)下的循環(huán)復(fù)制問題等諸多重要知識(shí)點(diǎn)。

---

一、MySQL 主備基本原理

（一）主備切換流程

1. 狀態(tài) 1

   ：如圖 1 所示，客戶端的讀寫直接訪問節(jié)點(diǎn) A，節(jié)點(diǎn) B 作為 A 的備庫，通過同步 A 的更新來保持?jǐn)?shù)據(jù)一致。此時(shí)，建議將備庫 B 設(shè)置為只讀（readonly）模式，原因如下：

   • 防止運(yùn)營類查詢語句在備庫上的誤操作。
   • 避免切換邏輯出現(xiàn) bug，如雙寫導(dǎo)致主備不一致。
   • 可依據(jù) readonly 狀態(tài)判斷節(jié)點(diǎn)角色。
   • 設(shè)置為 readonly，那主庫怎么和備庫進(jìn)行同步？同步更新的線程擁有超級(jí)（super）權(quán)限，仍可進(jìn)行數(shù)據(jù)同步。

2. 狀態(tài) 2：當(dāng)需要切換時(shí)，客戶端讀寫訪問切換到節(jié)點(diǎn) B，節(jié)點(diǎn) A 成為 B 的備庫。

（二）主備數(shù)據(jù)同步流程

在這里插入圖片描述

1. 備庫設(shè)置：在備庫 B 上通過 change master 命令設(shè)置主庫 A 的相關(guān)信息，包括 IP、端口、用戶名、密碼以及 binlog 的起始位置（包含文件名和日志偏移量），然后執(zhí)行 start slave 命令啟動(dòng)圖中 io_thread 和 sql_thread 兩個(gè)線程。其中 io_thread 負(fù)責(zé)與主庫建立連接。
2. 主庫操作：主庫 A 校驗(yàn)完用戶名和密碼后，按照備庫 B 的請(qǐng)求位置讀取 binlog，并將其發(fā)送給 B。
3. 備庫接收與執(zhí)行：備庫 B 收到 binlog 后，寫入本地中轉(zhuǎn)日志（relay log），sql_thread 讀取 relay log，解析并執(zhí)行其中的命令。

---

二、binlog 格式及相關(guān)問題

（一）binlog 的三種格式

如果要在表中刪除一行數(shù)據(jù)的話，我們來看看這個(gè)delete語句的binlog是怎么記錄的。

mysql> CREATE TABLE `t` (
`id` int(11) NOTNULL,
`a` int(11) DEFAULTNULL,
`t_modified` timestamp NOTNULL DEFAULTCURRENT_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');

1. statement 格式：記錄 SQL 語句原文，例如執(zhí)行delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1語句時(shí)，binlog 中會(huì)如實(shí)記錄該 SQL 語句，包括自動(dòng)添加的use 'test'命令（確保在備庫能正確更新到指定庫的表），同時(shí)還會(huì)記錄注釋等信息。然而，這種格式可能導(dǎo)致主備數(shù)據(jù)不一致，如上述 delete 語句，若主庫和備庫使用不同索引執(zhí)行，可能刪除不同行。

   

2. row 格式：不記錄 SQL 語句原文，而是通過 Table_map 和 Delete_rows 等事件來定義操作。例如上述 delete 語句，會(huì)記錄操作的表信息（Table_map event）以及刪除行為（Delete_rows event），且會(huì)記錄真實(shí)刪除行的主鍵 id，保證備庫執(zhí)行時(shí)刪除正確的行。此外，對(duì)于 insert 和 update 語句，row 格式的 binlog 也會(huì)記錄足夠信息以便數(shù)據(jù)恢復(fù)，如 insert 語句會(huì)記錄所有字段信息，update 語句會(huì)記錄修改前和修改后的整行數(shù)據(jù)。

   

   該事務(wù)從8900開始，通過mysqlbinlog -vvdata/master.000001 --start-position=8900;指令，查看

   

   binlog里面記錄了真實(shí)刪除行的主鍵id，這樣 binlog傳到備庫去的時(shí)候，就肯定會(huì)刪除id=4的行，不會(huì)有主備刪除不同行的問題。

   為什么會(huì)有mixed格式的binlog？

   有些statement格式的binlog雖然簡單但可能會(huì)導(dǎo)致主備不一致，所以要使用row格式。但row格式又比較占用空間，所以算是一種折中的辦法。mixed格式可以利用statment格式的優(yōu)點(diǎn)，同時(shí)又避免了數(shù)據(jù)不一致的風(fēng)險(xiǎn)。

3. mixed 格式：MySQL 會(huì)判斷 SQL 語句是否可能引起主備不一致，若有可能則使用 row 格式，否則使用 statement 格式，是一種折中的方案。但在某些情況下，如執(zhí)行insert into t values(10,10, now())時(shí)，雖可能存在主備不一致風(fēng)險(xiǎn)，但仍會(huì)記錄為 statement 格式，不過會(huì)通過 SET TIMESTAMP 命令約定 now () 函數(shù)的返回時(shí)間，確保主備數(shù)據(jù)一致性。

   

（二）格式對(duì)比與選擇

1. 數(shù)據(jù)恢復(fù)優(yōu)勢：row 格式在數(shù)據(jù)恢復(fù)方面具有明顯優(yōu)勢，可通過反轉(zhuǎn)操作恢復(fù)誤執(zhí)行的 delete、insert 或 update 語句，而 statement 格式相對(duì)較難實(shí)現(xiàn)精確恢復(fù)。
2. 空間占用與性能：row 格式占用空間較大，寫 binlog 時(shí)耗費(fèi) IO 資源，影響執(zhí)行速度；statement 格式占用空間小，但可能存在數(shù)據(jù)不一致風(fēng)險(xiǎn)。因此，越來越多的場景推薦使用 row 格式的 binlog，不過也可根據(jù)實(shí)際情況選擇 mixed 格式，而一般不建議使用單純的 statement 格式。

---

三、循環(huán)復(fù)制問題及解決

（一）循環(huán)復(fù)制現(xiàn)象

在雙 M 結(jié)構(gòu)中，業(yè)務(wù)邏輯在節(jié)點(diǎn) A 更新語句后生成 binlog 發(fā)給節(jié)點(diǎn) B，B 執(zhí)行后也生成 binlog，若節(jié)點(diǎn) A 同時(shí)是節(jié)點(diǎn) B 的備庫，可能會(huì)導(dǎo)致循環(huán)執(zhí)行更新語句，即循環(huán)復(fù)制。

（二）解決邏輯

1. server id 設(shè)置：規(guī)定兩個(gè)庫的 server id 必須不同，否則不能設(shè)定為主備關(guān)系。
2. binlog 生成規(guī)則：備庫在重放 binlog 過程中生成與原 binlog 的 server id 相同的新 binlog。
3. 日志接收判斷：每個(gè)庫收到主庫發(fā)來的日志后，先判斷 server id，若與自己相同則直接丟棄，從而避免循環(huán)復(fù)制。例如，節(jié)點(diǎn) A 更新事務(wù)的 binlog 記有 A 的 server id，傳給節(jié)點(diǎn) B 執(zhí)行后，B 生成的 binlog 的 server id 也是 A 的 server id，再傳回 A 時(shí)，A 會(huì)因 server id 相同而不處理該日志。

---

四、總結(jié)與思考

MySQL 通過 binlog 實(shí)現(xiàn)主備同步，binlog 的三種格式各有優(yōu)劣，在保證主備一致性方面發(fā)揮著關(guān)鍵作用。主備復(fù)制流程涉及多個(gè)步驟和線程協(xié)作，雙 M 結(jié)構(gòu)雖有循環(huán)復(fù)制問題，但可通過 server id 機(jī)制解決。