微信搜 歡少的成長之路

介紹

大家好，我是Leo。前面文章我們介紹了WAL的安全機制?？梢员ＷC數(shù)據(jù)的安全性。通過安全性我們分析了binlog，redolog日志的寫入機制。今天我們分析一下主從庫的實現(xiàn)原理！MySQL是如何保證主從庫的數(shù)據(jù)是一致的呢？

寫作思路

根據(jù)讀者與朋友的反饋，每篇文章我會加一塊寫作思路。讓讀者能更好的吸收相關(guān)知識，以及判斷是否是自己所需要的知識。

主從同步的基本流程

如下圖所示，這是主從庫的狀態(tài)圖。

• 狀態(tài)1：用戶端訪問MySQLA，A是主庫，B是從庫，B同步A的數(shù)據(jù)。

• 狀態(tài)2：用戶端訪問MySQLB，B是主庫，A是從庫，A同步B的數(shù)據(jù)。

主從在需要切換的時候就是由狀態(tài)1轉(zhuǎn)變成狀態(tài)2的這個過程。

數(shù)據(jù)在從A同步B或者B同步到A。同步的線程具有超級管理員的權(quán)限。所以建議把從庫設置成readonly模式的。因為這樣可以避免主從同步的一個 “坑” 就是下面的雙寫。所以設置readonly百利而無一害。

1. 可以防止其他運營的類的查詢語句的誤操作。造成數(shù)據(jù)不一致的問題

2. 可以防止狀態(tài)1和狀態(tài)2在切換的時候也會有一些邏輯性的BUG問題

接下來我們把流程的每一步分析一下，如下圖所示

1. 在備庫 B 上通過 change master 命令，設置主庫 A 的 IP、端口、用戶名、密碼，以及要從哪個位置開始請求 binlog，這個位置包含文件名和日志偏移量

2. 在備庫 B 上執(zhí)行 start slave 命令，這時候備庫會啟動兩個線程，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負責與主庫建立連接。

3. 主庫 A 校驗完用戶名、密碼后，開始按照備庫 B 傳過來的位置，從本地讀取 binlog，發(fā)給 B。

4. 備庫 B 拿到 binlog 后，寫到本地文件，稱為中轉(zhuǎn)日志（relay log）。

5. sql_thread 讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行。

sql_thread線程我們在今后的文章中會詳細介紹。這里就不做過多解釋了！

根據(jù)上面的流程，我們一點一點剖析底層的流程。先來了解一下binlog傳輸吧

binlog格式的華山論劍

說到binlog傳輸?shù)脑?，我們肯定要聊到它的格式問題。binlog常見的格式有兩種，一種是statement，一種是row。還有一種格式叫作mixed。這種格式是前面兩種格式的混合體。

下面我們舉例論述一下

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

我們先簡單的執(zhí)行一條刪除語句，查看一下對應的binlog日志到底是什么樣的。

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

當binlog格式屬于第一種情況時。 statement

binlog里面記錄的是SQL語句的原文?？梢杂?nbsp;mysql> show binlog events in 'master.000001'; 查看

分析圖上的結(jié)果。

• 第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’你可以先忽略，后面文章我們會在介紹主備切換的時候再提到；

• 第二行是一個 BEGIN，跟第四行的 commit 對應，表示中間是一個事務；

• 第三行就是真實執(zhí)行的語句了?？梢钥吹剑谡鎸崍?zhí)行的 delete 命令之前，還有一個“use ‘test’”命令。這條命令不是我們主動執(zhí)行的，而是 MySQL 根據(jù)當前要操作的表所在的數(shù)據(jù)庫，自行添加的。這樣做可以保證日志傳到備庫去執(zhí)行的時候，不論當前的工作線程在哪個庫里，都能夠正確地更新到 test 庫的表 t。use 'test’命令之后的 delete 語句，就是我們輸入的 SQL 原文了。可以看到，binlog“忠實”地記錄了 SQL 命令，甚至連注釋也一并記錄了。

• 最后一行是一個 COMMIT。你可以看到里面寫著 xid=61。

還記得xid是啥意思嗎，我們一起回顧一下吧。

xid是binlog與redo log共同的數(shù)據(jù)字段，崩潰恢復的時候，會按順序掃描redo log

• 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；

• 如果碰到只有 parepare、而沒有 commit 的 redo log，就拿著 XID 去 binlog 找對應的事務。

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

可以看到，這條delete產(chǎn)生了一條warning。是因為當前binlog設置的是statement格式的。并且delete帶有l(wèi)imit，很可能會出現(xiàn)主從庫數(shù)據(jù)不一致的情況。比如上面這個例子。

1. 如果 delete 語句使用的是索引 a，那么會根據(jù)索引 a 找到第一個滿足條件的行，也就是說刪除的是 a=4 這一行；

2. 但如果使用的是索引 t_modified，那么刪除的就是 t_modified='2018-11-09’也就是 a=5 這一行。

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

那么，如果我把 binlog 的格式改為 binlog_format=‘row’， 是不是就沒有這個問題了呢？我們先來看看這時候 binog 中的內(nèi)容吧。

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

• Table_map event，用于說明接下來要操作的表是 test 庫的表 t;

• Delete_rows event，用于定義刪除的行為。

把格式改成row的話，我們是看不到詳細信息的。還需要借助mysqlbinlog工具，用下面這個命令解析和查看binlog中的內(nèi)容。從上圖可以得知，這個事務的binlog是從8900這個位置開始的。所以可以用 start-position 參數(shù)來指定從這個位置的日志開始解析。

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

• server id 1，表示這個事務是在 server_id=1 的這個庫上執(zhí)行的。

• 每個 event 都有 CRC32 的值，這是因為我把參數(shù) binlog_checksum 設置成了 CRC32。

• Table_map event 跟在圖 5 中看到的相同，顯示了接下來要打開的表，map 到數(shù)字 226?，F(xiàn)在我們這條 SQL 語句只操作了一張表，如果要操作多張表呢？每個表都有一個對應的 Table_map event、都會 map 到一個單獨的數(shù)字，用于區(qū)分對不同表的操作。

• 我們在 mysqlbinlog 的命令中，使用了 -vv 參數(shù)是為了把內(nèi)容都解析出來，所以從結(jié)果里面可以看到各個字段的值（比如，@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 的行，不會有主備刪除不同行的問題。

miexed是啥，給binlog起到了哪些作用

想要解決這個問題， 就需要說明一下row格式的binlog與statement格式的binlog有啥優(yōu)缺點！

statement 記錄的是大概的信息，幾乎是我們的執(zhí)行信息，我們看不到具體的邏輯是什么。所以如果同步到從庫上，很容易會發(fā)現(xiàn)數(shù)據(jù)不一致的情況，所以出現(xiàn)了row格式。

row row格式解決了statement的缺點?？梢圆榈綀?zhí)行的詳細信息，但是缺點也是相應暴露了出來，過于詳細導致內(nèi)存占用過大。比如刪除一個幾萬的數(shù)據(jù)。row格式的binlog會記錄每個數(shù)值記錄。這樣不僅會占用過多的空間，還會占用磁盤IO，影響整個MySQL的執(zhí)行效率

miexed 橫空出世，解決了statement不一致的問題，同時也解決了row格式的占用內(nèi)存過大的缺點。主要的實現(xiàn)就是他會判斷一下，這個binlog會不會引起數(shù)據(jù)不一致這個問題。如果會引起，那么久采用row格式的。如果不會引起，那么久采用statement格式的日志。

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

比如我們這個例子，設置為 mixed 后，就會記錄為 row 格式；而如果執(zhí)行的語句去掉 limit 1，就會記錄為 statement 格式。

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

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

如果你是執(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í)行，就能恢復這個更新操作了。

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

案例問題

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

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

由輸出結(jié)果得知，走的是statement格式。那如果傳給主庫同步的話，那里的時間肯定是不準的，造成主從庫數(shù)據(jù)不一致啊。

接下來我們拿xid 用mysqlbinlog工具看一下

這里多了一個指令：SET TIMESTAMP=1546103491 它用 SET TIMESTAMP 命令約定了接下來的 now() 函數(shù)的返回時間。

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

error： 一定不要用mysqlbinlog工具解析出數(shù)據(jù)，然后直接把里面的statement語句直接拷貝出來執(zhí)行。這樣的操作是有風險的。所以一定要把整個結(jié)構(gòu)都發(fā)給MySQL執(zhí)行。

主從同步的循環(huán)復制問題

在我們真實的開發(fā)場景中，往往主庫不會一直是主庫，從庫不會一直是從庫。為了保證安全性。往往是這樣設計的。

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

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

解決方案：

1. 規(guī)定兩個庫的 server id 必須不同，如果相同，則它們之間不能設定為主備關(guān)系；

2. 一個備庫接到 binlog 并在重放的過程中，生成與原 binlog 的 server id 相同的新的 binlog；

3. 每個庫在收到從自己的主庫發(fā)過來的日志后，先判斷 server id，如果跟自己的相同，表示這個日志是自己生成的，就直接丟棄這個日志。

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

1. 從節(jié)點 A 更新的事務，binlog 里面記的都是 A 的 server id；

2. 傳到節(jié)點 B 執(zhí)行一次以后，節(jié)點 B 生成的 binlog 的 server id 也是 A 的 server id；

3. 再傳回給節(jié)點 A，A 判斷到這個 server id 與自己的相同，就不會再處理這個日志。所以，死循環(huán)在這里就斷掉了。

總結(jié)

這篇文章，我們介紹了MySQL是怎么保證主從庫數(shù)據(jù)一致的原因，實現(xiàn)流程，binlog三種格式的優(yōu)缺點，線上場景的MySQL主從庫應用配置，主從庫互相切換的循環(huán)復制問題以及解決方案。

知道的越多，不知道的就越多！愿今后的歲月，不忘初心，努力學習！都有一個不辜負的人生！

有任何問題都可以在一起討論。點贊+評論+關(guān)注是對博主最好的支持！