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

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

MySQL 主備的基本原理

圖 1 MySQL 主備切換流程

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

        當(dāng)需要切換的時候，就切成狀態(tài) 2。這時候客戶端讀寫訪問的都是節(jié)點 B，而節(jié)點 A 是 B 的備庫。

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

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

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

        這個問題，你不用擔(dān)心。因為 readonly 設(shè)置對超級 (super) 權(quán)限用戶是無效的，而用于同步更新的線程，就擁有超級權(quán)限。

        接下來，我們再看看節(jié)點 A 到 B 這條線的內(nèi)部流程是什么樣的。圖 2 中畫出的就是一個 update 語句在節(jié)點 A 執(zhí)行，然后同步到節(jié)點 B 的完整流程圖。

圖 2 主備流程圖

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

        備庫 B 跟主庫 A 之間維持了一個長連接。主庫 A 內(nèi)部有一個線程，專門用于服務(wù)備庫 B 的這個長連接。一個事務(wù)日志同步的完整過程是這樣的：

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

binlog 的三種格式對比

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

        為了便于描述 binlog 的這三種格式間的區(qū)別，創(chuàng)建了一個表，并初始化幾行數(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ù)的話，我們來看看這個 delete 語句的 binlog 是怎么記錄的。

        下面這個語句包含注釋，如果你用 MySQL 客戶端來做這個實驗的話，要記得加 -c 參數(shù)，否則客戶端會自動去掉注釋。

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

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

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

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

圖 3 statement 格式 binlog 示例

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

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

圖 4 delete 執(zhí)行 warnings

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

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

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

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

        那么，如果我把 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 中的內(nèi)容。因為圖 5 中的信息顯示，這個事務(wù)的 binlog 是從 8900 這個位置開始的，所以可以用 start-position 參數(shù)來指定從這個位置的日志開始解析。

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

圖 6 row 格式 binlog 示例的詳細信息 

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

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

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

為什么會有 mixed 格式的 binlog？

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

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

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

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

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

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

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

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

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

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

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

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

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

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

圖 7 mixed 格式和 now() 

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

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

圖 8 TIMESTAMP 命令 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

小結(jié)

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

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

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

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

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