拋出問(wèn)題：大家知道 binlog 可以用來(lái)歸檔，也可以用來(lái)做主備同步，但它的內(nèi)容是什么樣的呢？為什么備庫(kù)執(zhí)行了 binlog 就可以跟主庫(kù)保持一致了呢？

MySQL 主備的基本原理

圖 1 MySQL 主備切換流程

在狀態(tài) 1 中，客戶(hù)端的讀寫(xiě)都直接訪問(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ě)訪問(wèn)的都是節(jié)點(diǎn) B，而節(jié)點(diǎn) A 是 B 的備庫(kù)。
在狀態(tài) 1 中，雖然節(jié)點(diǎn) B 沒(méi)有被直接訪問(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)的角色。

我把備庫(kù)設(shè)置成只讀了，還怎么跟主庫(kù)保持同步更新呢？
這個(gè)問(wèn)題，你不用擔(dān)心。因?yàn)?readonly 設(shè)置對(duì)超級(jí) (super) 權(quán)限用戶(hù)是無(wú)效的，而用于同步更新的線程，就擁有超級(jí)權(quán)限。

圖 2 主備流程圖

主庫(kù)接收到客戶(hù)端的更新請(qǐng)求后，執(zhí)行內(nèi)部事務(wù)的更新邏輯，同時(shí)寫(xiě) binlog。備庫(kù) B 跟主庫(kù) A 之間維持了一個(gè)長(zhǎng)連接。主庫(kù) A 內(nèi)部有一個(gè)線程，專(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è)線程，就是圖中的 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í)行。

后來(lái)由于多線程復(fù)制方案的引入，sql_thread 演化成為了多個(gè)線程。

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

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

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ù)的話，我們來(lái)看看這個(gè) delete 語(yǔ)句的 binlog 是怎么記錄的。

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 示例

現(xiàn)在，我們來(lái)看一下圖 3 的輸出結(jié)果。

• 第一行 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)前的工作線程在哪個(gè)庫(kù)里，都能夠正確地更新到 test 庫(kù)的表 t。
• use 'test'命令之后的 delete 語(yǔ)句，就是我們輸入的 SQL 原文了?？梢钥吹?，binlog“忠實(shí)”地記錄了 SQL 命令，甚至連注釋也一并記錄了。
• 最后一行是一個(gè) COMMIT。你可以看到里面寫(xiě)著 xid=61。

為了說(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 的。為什么這么說(shuō)呢？這是因?yàn)?delete 帶 limit，很可能會(huì)出現(xiàn)主備數(shù)據(jù)不一致的情況。

如果 delete 語(yǔ)句使用的是索引 a，那么會(huì)根據(jù)索引 a 找到第一個(gè)滿足條件的行，也就是說(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，用于定義刪除的行為。

借助 mysqlbinlog 工具，用下面這個(gè)命令解析和查看 binlog 中的內(nèi)容。這個(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ù)被正確地提交了。

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

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

因?yàn)橛行?statement 格式的 binlog 可能會(huì)導(dǎo)致主備不一致，所以要使用 row 格式。

但 row 格式的缺點(diǎn)是，很占空間。比如你用一個(gè) delete 語(yǔ)句刪掉 10 萬(wàn)行數(shù)據(jù)，用 statement 的話就是一個(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)在越來(lái)越多的場(chǎng)景要求把 MySQL 的 binlog 格式設(shè)置成 row。這么做的理由有很多，舉一個(gè)可以直接看出來(lái)的好處：恢復(fù)數(shù)據(jù)。

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

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

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

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

圖 7 mixed 格式和 now()

MySQL 用的居然是 statement 格式。接下來(lái)，我們?cè)儆?mysqlbinlog 工具來(lái)看看：

圖 8 TIMESTAMP 命令

原來(lái) binlog 在記錄 event 的時(shí)候，多記了一條命令：SET TIMESTAMP=1546103491。它用 SET TIMESTAMP 命令約定了接下來(lái)的 now() 函數(shù)的返回時(shí)間。通過(guò)這條 SET TIMESTAMP 命令，MySQL 就確保了主備數(shù)據(jù)的一致性。

重放 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=2942 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

這個(gè)命令的意思是，將 master.000001 文件里面從第 2738 字節(jié)到第 2942 字節(jié)中間這段內(nèi)容解析出來(lái)，放到 MySQL 去執(zhí)行。

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

binlog 的特性確保了在備庫(kù)執(zhí)行相同的 binlog，可以得到與主庫(kù)相同的狀態(tài)。我們可以認(rèn)為正常情況下主備的數(shù)據(jù)是一致的。也就是說(shuō)，圖 1 中 A、B 兩個(gè)節(jié)點(diǎn)的內(nèi)容是一致的。其實(shí)，圖 1 中我畫(huà)的是 M-S 結(jié)構(gòu)，但實(shí)際生產(chǎn)上使用比較多的是雙 M 結(jié)構(gòu)，也就是圖 9 所示的主備切換流程。

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

節(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é)：

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

美國(guó)服務(wù)器租用

版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。