為什么備庫(kù)執(zhí)行了 binlog 就可以跟主庫(kù)保持一致了呢？今天正式地和你介紹一下它。

        在最開(kāi)始，MySQL 是以容易學(xué)習(xí)和方便的高可用架構(gòu)，被開(kāi)發(fā)人員青睞的。而它的幾乎所有的高可用架構(gòu)，都直接依賴(lài)于 binlog。雖然這些高可用架構(gòu)已經(jīng)呈現(xiàn)出越來(lái)越復(fù)雜的趨勢(shì)，但都是從最基本的一主一備演化過(guò)來(lái)的。

MySQL 主備的基本原理

圖 1 MySQL 主備切換流程

        在狀態(tài) 1 中，客戶(hù)端的讀寫(xiě)都直接訪(fǎng)問(wèn)節(jié)點(diǎn) A，而節(jié)點(diǎn) B 是 A 的備庫(kù)，只是將 A 的更新都同步過(guò)來(lái)，到本地執(zhí)行。這樣可以保持節(jié)點(diǎn) B 和 A 的數(shù)據(jù)是相同的。

        當(dāng)需要切換的時(shí)候，就切成狀態(tài) 2。這時(shí)候客戶(hù)端讀寫(xiě)訪(fǎng)問(wèn)的都是節(jié)點(diǎn) B，而節(jié)點(diǎn) A 是 B 的備庫(kù)。

        在狀態(tài) 1 中，雖然節(jié)點(diǎn) B 沒(méi)有被直接訪(fǎng)問(wèn)，但是我依然建議你把節(jié)點(diǎn) B（也就是備庫(kù)）設(shè)置成只讀（readonly）模式。這樣做，有以下幾個(gè)考慮：

• 有時(shí)候一些運(yùn)營(yíng)類(lèi)的查詢(xún)語(yǔ)句會(huì)被放到備庫(kù)上去查，設(shè)置為只讀可以防止誤操作；
• 防止切換邏輯有 bug，比如切換過(guò)程中出現(xiàn)雙寫(xiě)，造成主備不一致；
• 可以用 readonly 狀態(tài)，來(lái)判斷節(jié)點(diǎn)的角色。

        你可能會(huì)問(wèn)，我把備庫(kù)設(shè)置成只讀了，還怎么跟主庫(kù)保持同步更新呢？

        這個(gè)問(wèn)題，你不用擔(dān)心。因?yàn)?readonly 設(shè)置對(duì)超級(jí) (super) 權(quán)限用戶(hù)是無(wú)效的，而用于同步更新的線(xiàn)程，就擁有超級(jí)權(quán)限。

        接下來(lái)，我們?cè)倏纯?strong>節(jié)點(diǎn) A 到 B 這條線(xiàn)的內(nèi)部流程是什么樣的。圖 2 中畫(huà)出的就是一個(gè) update 語(yǔ)句在節(jié)點(diǎn) A 執(zhí)行，然后同步到節(jié)點(diǎn) B 的完整流程圖。

圖 2 主備流程圖

       圖 2 中，包含了在上一篇文章中講到的 binlog 和 redo log 的寫(xiě)入機(jī)制相關(guān)的內(nèi)容，可以看到：主庫(kù)接收到客戶(hù)端的更新請(qǐng)求后，執(zhí)行內(nèi)部事務(wù)的更新邏輯，同時(shí)寫(xiě) binlog。

        備庫(kù) B 跟主庫(kù) A 之間維持了一個(gè)長(zhǎng)連接。主庫(kù) A 內(nèi)部有一個(gè)線(xiàn)程，專(zhuān)門(mén)用于服務(wù)備庫(kù) B 的這個(gè)長(zhǎng)連接。一個(gè)事務(wù)日志同步的完整過(guò)程是這樣的：

• 在備庫(kù) B 上通過(guò) change master 命令，設(shè)置主庫(kù) A 的 IP、端口、用戶(hù)名、密碼，以及要從哪個(gè)位置開(kāi)始請(qǐng)求 binlog，這個(gè)位置包含文件名和日志偏移量。
• 在備庫(kù) B 上執(zhí)行 start slave 命令，這時(shí)候備庫(kù)會(huì)啟動(dòng)兩個(gè)線(xiàn)程，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負(fù)責(zé)與主庫(kù)建立連接。
• 主庫(kù) A 校驗(yàn)完用戶(hù)名、密碼后，開(kāi)始按照備庫(kù) B 傳過(guò)來(lái)的位置，從本地讀取 binlog，發(fā)給 B。
• 備庫(kù) B 拿到 binlog 后，寫(xiě)到本地文件，稱(chēng)為中轉(zhuǎn)日志（relay log）。
• sql_thread 讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行。

binlog 的三種格式對(duì)比

         binlog 有兩種格式，一種是 statement，一種是 row?？赡苣阍谄渌Y料上還會(huì)看到有第三種格式，叫作 mixed，其實(shí)它就是前兩種格式的混合。

        為了便于描述 binlog 的這三種格式間的區(qū)別，創(chuàng)建了一個(gè)表，并初始化幾行數(shù)據(jù)。

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');

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

        下面這個(gè)語(yǔ)句包含注釋?zhuān)绻阌?MySQL 客戶(hù)端來(lái)做這個(gè)實(shí)驗(yàn)的話(huà)，要記得加 -c 參數(shù)，否則客戶(hù)端會(huì)自動(dòng)去掉注釋。

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

         當(dāng) binlog_format=statement 時(shí)，binlog 里面記錄的就是 SQL 語(yǔ)句的原文。你可以用

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

         命令看 binlog 中的內(nèi)容。

圖 3 statement 格式 binlog 示例

• 第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’你可以先忽略，后面文章會(huì)在介紹主備切換的時(shí)候再提到；
• 第二行是一個(gè) BEGIN，跟第四行的 commit 對(duì)應(yīng)，表示中間是一個(gè)事務(wù)；
• 第三行就是真實(shí)執(zhí)行的語(yǔ)句了。可以看到，在真實(shí)執(zhí)行的 delete 命令之前，還有一個(gè)“use ‘test’”命令。這條命令不是我們主動(dòng)執(zhí)行的，而是 MySQL 根據(jù)當(dāng)前要操作的表所在的數(shù)據(jù)庫(kù)，自行添加的。這樣做可以保證日志傳到備庫(kù)去執(zhí)行的時(shí)候，不論當(dāng)前的工作線(xiàn)程在哪個(gè)庫(kù)里，都能夠正確地更新到 test 庫(kù)的表 t。
• use 'test’命令之后的 delete 語(yǔ)句，就是我們輸入的 SQL 原文了?？梢钥吹剑琤inlog“忠實(shí)”地記錄了 SQL 命令，甚至連注釋也一并記錄了。
• 最后一行是一個(gè) COMMIT。你可以看到里面寫(xiě)著 xid=61。你還記得這個(gè) XID 是做什么用的嗎？

        為了說(shuō)明 statement 和 row 格式的區(qū)別，我們來(lái)看一下這條 delete 命令的執(zhí)行效果圖：

圖 4 delete 執(zhí)行 warnings

         運(yùn)行這條 delete 命令產(chǎn)生了一個(gè) warning，原因是當(dāng)前 binlog 設(shè)置的是 statement 格式，并且語(yǔ)句中有 limit，所以這個(gè)命令可能是 unsafe 的。

        這是因?yàn)?delete 帶 limit，很可能會(huì)出現(xiàn)主備數(shù)據(jù)不一致的情況。比如上面這個(gè)例子：

• 如果 delete 語(yǔ)句使用的是索引 a，那么會(huì)根據(jù)索引 a 找到第一個(gè)滿(mǎn)足條件的行，也就是說(shuō)刪除的是 a=4 這一行；
• 但如果使用的是索引 t_modified，那么刪除的就是 t_modified='2018-11-09’也就是 a=5 這一行。

        由于 statement 格式下，記錄到 binlog 里的是語(yǔ)句原文，因此可能會(huì)出現(xiàn)這樣一種情況：在主庫(kù)執(zhí)行這條 SQL 語(yǔ)句的時(shí)候，用的是索引 a；而在備庫(kù)執(zhí)行這條 SQL 語(yǔ)句的時(shí)候，卻使用了索引 t_modified。因此，MySQL 認(rèn)為這樣寫(xiě)是有風(fēng)險(xiǎn)的。

        那么，如果我把 binlog 的格式改為 binlog_format=‘row’， 是不是就沒(méi)有這個(gè)問(wèn)題了呢？

圖 5 row 格式 binlog 示例 

         與 statement 格式的 binlog 相比，前后的 BEGIN 和 COMMIT 是一樣的。但是，row 格式的 binlog 里沒(méi)有了 SQL 語(yǔ)句的原文，而是替換成了兩個(gè) event：Table_map 和 Delete_rows。

• Table_map event，用于說(shuō)明接下來(lái)要操作的表是 test 庫(kù)的表 t;
• Delete_rows event，用于定義刪除的行為

        其實(shí)，我們通過(guò)圖 5 是看不到詳細(xì)信息的，還需要借助 mysqlbinlog 工具，用下面這個(gè)命令解析和查看 binlog 中的內(nèi)容。因?yàn)閳D 5 中的信息顯示，這個(gè)事務(wù)的 binlog 是從 8900 這個(gè)位置開(kāi)始的，所以可以用 start-position 參數(shù)來(lái)指定從這個(gè)位置的日志開(kāi)始解析。

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

圖 6 row 格式 binlog 示例的詳細(xì)信息 

        從這個(gè)圖中，我們可以看到以下幾個(gè)信息：

• server id 1，表示這個(gè)事務(wù)是在 server_id=1 的這個(gè)庫(kù)上執(zhí)行的。
• 每個(gè) event 都有 CRC32 的值，這是因?yàn)槲野褏?shù) binlog_checksum 設(shè)置成了 CRC32。
• Table_map event 跟在圖 5 中看到的相同，顯示了接下來(lái)要打開(kāi)的表，map 到數(shù)字 226?，F(xiàn)在我們這條 SQL 語(yǔ)句只操作了一張表，如果要操作多張表呢？每個(gè)表都有一個(gè)對(duì)應(yīng)的 Table_map event、都會(huì) map 到一個(gè)單獨(dú)的數(shù)字，用于區(qū)分對(duì)不同表的操作。
• 我們?cè)?mysqlbinlog 的命令中，使用了 -vv 參數(shù)是為了把內(nèi)容都解析出來(lái)，所以從結(jié)果里面可以看到各個(gè)字段的值（比如，@1=4、 @2=4 這些值）。
• binlog_row_image 的默認(rèn)配置是 FULL，因此 Delete_event 里面，包含了刪掉的行的所有字段的值。如果把 binlog_row_image 設(shè)置為 MINIMAL，則只會(huì)記錄必要的信息，在這個(gè)例子里，就是只會(huì)記錄 id=4 這個(gè)信息。
• 最后的 Xid event，用于表示事務(wù)被正確地提交了。

        當(dāng) binlog_format 使用 row 格式的時(shí)候，binlog 里面記錄了真實(shí)刪除行的主鍵 id，這樣 binlog 傳到備庫(kù)去的時(shí)候，就肯定會(huì)刪除 id=4 的行，不會(huì)有主備刪除不同行的問(wèn)題。

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

        基于上面的信息，我們來(lái)討論一個(gè)問(wèn)題：為什么會(huì)有 mixed 這種 binlog 格式的存在場(chǎng)景？推論過(guò)程是這樣的：

• 因?yàn)橛行?statement 格式的 binlog 可能會(huì)導(dǎo)致主備不一致，所以要使用 row 格式。
• 但 row 格式的缺點(diǎn)是，很占空間。比如你用一個(gè) delete 語(yǔ)句刪掉 10 萬(wàn)行數(shù)據(jù)，用 statement 的話(huà)就是一個(gè) SQL 語(yǔ)句被記錄到 binlog 中，占用幾十個(gè)字節(jié)的空間。但如果用 row 格式的 binlog，就要把這 10 萬(wàn)條記錄都寫(xiě)到 binlog 中。這樣做，不僅會(huì)占用更大的空間，同時(shí)寫(xiě) binlog 也要耗費(fèi) IO 資源，影響執(zhí)行速度。
• 所以，MySQL 就取了個(gè)折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己會(huì)判斷這條 SQL 語(yǔ)句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。

        也就是說(shuō)，mixed 格式可以利用 statment 格式的優(yōu)點(diǎn)，同時(shí)又避免了數(shù)據(jù)不一致的風(fēng)險(xiǎn)。

        因此，如果你的線(xiàn)上 MySQL 設(shè)置的 binlog 格式是 statement 的話(huà)，那基本上就可以認(rèn)為這是一個(gè)不合理的設(shè)置。你至少應(yīng)該把 binlog 的格式設(shè)置為 mixed。

        現(xiàn)在越來(lái)越多的場(chǎng)景要求把 MySQL 的 binlog 格式設(shè)置成 row。這么做的理由有很多，我來(lái)給你舉一個(gè)可以直接看出來(lái)的好處：恢復(fù)數(shù)據(jù)。

        接下來(lái)，我們就分別從 delete、insert 和 update 這三種 SQL 語(yǔ)句的角度，來(lái)看看數(shù)據(jù)恢復(fù)的問(wèn)題。

        通過(guò)圖 6 你可以看出來(lái)，即使我執(zhí)行的是 delete 語(yǔ)句，row 格式的 binlog 也會(huì)把被刪掉的行的整行信息保存起來(lái)。所以，如果你在執(zhí)行完一條 delete 語(yǔ)句以后，發(fā)現(xiàn)刪錯(cuò)數(shù)據(jù)了，可以直接把 binlog 中記錄的 delete 語(yǔ)句轉(zhuǎn)成 insert，把被錯(cuò)刪的數(shù)據(jù)插入回去就可以恢復(fù)了。

        如果你是執(zhí)行錯(cuò)了 insert 語(yǔ)句呢？那就更直接了。row 格式下，insert 語(yǔ)句的 binlog 里會(huì)記錄所有的字段信息，這些信息可以用來(lái)精確定位剛剛被插入的那一行。這時(shí)，你直接把 insert 語(yǔ)句轉(zhuǎn)成 delete 語(yǔ)句，刪除掉這被誤插入的一行數(shù)據(jù)就可以了。

        如果執(zhí)行的是 update 語(yǔ)句的話(huà)，binlog 里面會(huì)記錄修改前整行的數(shù)據(jù)和修改后的整行數(shù)據(jù)。所以，如果你誤執(zhí)行了 update 語(yǔ)句的話(huà)，只需要把這個(gè) event 前后的兩行信息對(duì)調(diào)一下，再去數(shù)據(jù)庫(kù)里面執(zhí)行，就能恢復(fù)這個(gè)更新操作了。

        其實(shí)，由 delete、insert 或者 update 語(yǔ)句導(dǎo)致的數(shù)據(jù)操作錯(cuò)誤，需要恢復(fù)到操作之前狀態(tài)的情況，也時(shí)有發(fā)生。MariaDB 的Flashback工具就是基于上面介紹的原理來(lái)回滾數(shù)據(jù)的。

        雖然 mixed 格式的 binlog 現(xiàn)在已經(jīng)用得不多了，但這里我還是要再借用一下 mixed 格式來(lái)說(shuō)明一個(gè)問(wèn)題，來(lái)看一下這條 SQL 語(yǔ)句：

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

         如果我們把 binlog 格式設(shè)置為 mixed，你覺(jué)得 MySQL 會(huì)把它記錄為 row 格式還是 statement 格式呢？

        先不要著急說(shuō)結(jié)果，我們一起來(lái)看一下這條語(yǔ)句執(zhí)行的效果。

圖 7 mixed 格式和 now() 

        可以看到，MySQL 用的居然是 statement 格式。你一定會(huì)奇怪，如果這個(gè) binlog 過(guò)了 1 分鐘才傳給備庫(kù)的話(huà)，那主備的數(shù)據(jù)不就不一致了嗎？

        接下來(lái)，我們?cè)儆?mysqlbinlog 工具來(lái)看看：

圖 8 TIMESTAMP 命令 

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

        因此，不論這個(gè) binlog 是 1 分鐘之后被備庫(kù)執(zhí)行，還是 3 天后用來(lái)恢復(fù)這個(gè)庫(kù)的備份，這個(gè) insert 語(yǔ)句插入的行，值都是固定的。也就是說(shuō)，通過(guò)這條 SET TIMESTAMP 命令，MySQL 就確保了主備數(shù)據(jù)的一致性。我之前看過(guò)有人在重放 binlog 數(shù)據(jù)的

        之前看過(guò)有人在重放 binlog 數(shù)據(jù)的時(shí)候，是這么做的：用 mysqlbinlog 解析出日志，然后把里面的 statement 語(yǔ)句直接拷貝出來(lái)執(zhí)行。你現(xiàn)在知道了，這個(gè)方法是有風(fēng)險(xiǎn)的。因?yàn)橛行┱Z(yǔ)句的執(zhí)行結(jié)果是依賴(lài)于上下文命令的，直接執(zhí)行的結(jié)果很可能是錯(cuò)誤的。

        所以，用 binlog 來(lái)恢復(fù)數(shù)據(jù)的標(biāo)準(zhǔn)做法是，用 mysqlbinlog 工具解析出來(lái)，然后把解析結(jié)果整個(gè)發(fā)給 MySQL 執(zhí)行。類(lèi)似下面的命令

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

 循環(huán)復(fù)制問(wèn)題

        我們可以認(rèn)為正常情況下主備的數(shù)據(jù)是一致的。也就是說(shuō)，圖 1 中 A、B 兩個(gè)節(jié)點(diǎn)的內(nèi)容是一致的。其實(shí)，圖 1 中的是 M-S 結(jié)構(gòu)，但實(shí)際生產(chǎn)上使用比較多的是雙 M 結(jié)構(gòu)，也就是圖 9 所示的主備切換流程。

圖 9 MySQL 主備切換流程 -- 雙 M 結(jié)構(gòu)

        對(duì)比圖 9 和圖 1，你可以發(fā)現(xiàn)，雙 M 結(jié)構(gòu)和 M-S 結(jié)構(gòu)，其實(shí)區(qū)別只是多了一條線(xiàn)，即：節(jié)點(diǎn) A 和 B 之間總是互為主備關(guān)系。這樣在切換的時(shí)候就不用再修改主備關(guān)系。

        但是，雙 M 結(jié)構(gòu)還有一個(gè)問(wèn)題需要解決。

        業(yè)務(wù)邏輯在節(jié)點(diǎn) A 上更新了一條語(yǔ)句，然后再把生成的 binlog 發(fā)給節(jié)點(diǎn) B，節(jié)點(diǎn) B 執(zhí)行完這條更新語(yǔ)句后也會(huì)生成 binlog。（我建議你把參數(shù) log_slave_updates 設(shè)置為 on，表示備庫(kù)執(zhí)行 relay log 后生成 binlog）

        那么，如果節(jié)點(diǎn) A 同時(shí)是節(jié)點(diǎn) B 的備庫(kù)，相當(dāng)于又把節(jié)點(diǎn) B 新生成的 binlog 拿過(guò)來(lái)執(zhí)行了一次，然后節(jié)點(diǎn) A 和 B 間，會(huì)不斷地循環(huán)執(zhí)行這個(gè)更新語(yǔ)句，也就是循環(huán)復(fù)制了。這個(gè)要怎么解決呢？

        從上面的圖 6 中可以看到，MySQL 在 binlog 中記錄了這個(gè)命令第一次執(zhí)行時(shí)所在實(shí)例的 server id。因此，我們可以用下面的邏輯，來(lái)解決兩個(gè)節(jié)點(diǎn)間的循環(huán)復(fù)制的問(wèn)題：

• 規(guī)定兩個(gè)庫(kù)的 server id 必須不同，如果相同，則它們之間不能設(shè)定為主備關(guān)系；
• 一個(gè)備庫(kù)接到 binlog 并在重放的過(guò)程中，生成與原 binlog 的 server id 相同的新的 binlog；
• 每個(gè)庫(kù)在收到從自己的主庫(kù)發(fā)過(guò)來(lái)的日志后，先判斷 server id，如果跟自己的相同，表示這個(gè)日志是自己生成的，就直接丟棄這個(gè)日志。

        按照這個(gè)邏輯，如果我們?cè)O(shè)置了雙 M 結(jié)構(gòu)，日志的執(zhí)行流就會(huì)變成這樣：

• 從節(jié)點(diǎn) A 更新的事務(wù)，binlog 里面記的都是 A 的 server id；
• 傳到節(jié)點(diǎn) B 執(zhí)行一次以后，節(jié)點(diǎn) B 生成的 binlog 的 server id 也是 A 的 server id；
• 再傳回給節(jié)點(diǎn) A，A 判斷到這個(gè) server id 與自己的相同，就不會(huì)再處理這個(gè)日志。所以，死循環(huán)在這里就斷掉了。

小結(jié)

        binlog 在 MySQL 的各種高可用方案上扮演了重要角色。今天介紹的可以說(shuō)是所有 MySQL 高可用方案的基礎(chǔ)。在這之上演化出了諸如多節(jié)點(diǎn)、半同步、MySQL group replication 等相對(duì)復(fù)雜的方案。

思考題： 說(shuō)到循環(huán)復(fù)制問(wèn)題的時(shí)候，我們說(shuō) MySQL 通過(guò)判斷 server id 的方式，斷掉死循環(huán)。但是，這個(gè)機(jī)制其實(shí)并不完備，在某些場(chǎng)景下，還是有可能出現(xiàn)死循環(huán)？又應(yīng)該怎么解決呢？

        答案：一種場(chǎng)景是，在一個(gè)主庫(kù)更新事務(wù)后，用命令 set global server_id=x 修改了 server_id。等日志再傳回來(lái)的時(shí)候，發(fā)現(xiàn) server_id 跟自己的 server_id 不同，就只能執(zhí)行了。

        另一種場(chǎng)景是，有三個(gè)節(jié)點(diǎn)的時(shí)候，如圖 7 所示，trx1 是在節(jié)點(diǎn) B 執(zhí)行的，因此 binlog 上的 server_id 就是 B，binlog 傳給節(jié)點(diǎn) A，然后 A 和 A’搭建了雙 M 結(jié)構(gòu)，就會(huì)出現(xiàn)循環(huán)復(fù)制。

到此這篇關(guān)于淺談如何保證Mysql主從一致的文章就介紹到這了,更多相關(guān)Mysql 主從一致內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！