MySQL中的 Binlog 深度解析及使用詳情

前言：

MySQL 的二進位制紀錄檔 binlog 可以說是 MySQL 最重要的紀錄檔，它記錄了所有的 DDL 和 DML 語句（除了資料查詢語句select、show等），以事件形式記錄，還包含語句所執行的消耗的時間，MySQL的二進位制紀錄檔是事務安全型的。binlog 的主要目的是複製和恢復。

Binlog紀錄檔的兩個最重要的使用場景

• MySQL主從複製：MySQL Replication在Master端開啟binlog，Master把它的二進位制紀錄檔傳遞給slaves來達到master-slave資料一致的目的
• 資料恢復：通過使用 mysqlbinlog工具來使恢復資料

組態檔引數說明

Binlog 紀錄檔功能預設是開啟的，線上情況下 Binlog 紀錄檔的增長速度是很快的，在 MySQL 的組態檔 my.cnf 中提供一些引數來對 Binlog 進行設定。

[mysqld]
設定此參數列示啟用binlog功能，並制定二進位制紀錄檔的儲存目錄，開啟binlog紀錄檔大概會有1%的效能損耗
log-bin=/home/mysql/binlog/
# 高版本MySQL需要server-id這個引數，提供一個叢集中不重複的id值即可
server-id=1
#mysql-bin.*紀錄檔檔案最大位元組（單位：位元組）
#設定最大100MB
max_binlog_size=104857600
#設定了只保留7天BINLOG（單位：天）
expire_logs_days = 7
#binlog紀錄檔只記錄指定庫的更新
#binlog-do-db=db_name
#binlog紀錄檔不記錄指定庫的更新
#binlog-ignore-db=db_name
#寫緩衝多少次，刷一次磁碟，預設0
sync_binlog=0

需要注意的是： max_binlog_size ：Binlog 最大和預設值是 1G，該設定並不能嚴格控制 Binlog 的大小，尤其是 Binlog 比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性不可能做切換紀錄檔的動作，只能將該事務的所有 SQL 都記錄進當前紀錄檔直到事務結束。所以真實檔案有時候會大於 max_binlog_size 設定值。 expire_logs_days ：Binlog 過期刪除不是服務定時執行，是需要藉助事件觸發才執行，事件包括：

• 伺服器重啟
• 伺服器被更新
• 紀錄檔達到了最大紀錄檔長度 max_binlog_size
• 紀錄檔被重新整理

常用的Binlog操作命令

# 是否啟用binlog紀錄檔
show variables like 'log_bin';

# 檢視binlog的目錄
show global variables like "%log_bin%";

# 檢視當前伺服器使用的biglog檔案個數及大小
show binary logs;

# 檢視最新一個binlog紀錄檔檔名稱和Position
show master status;

# 事件查詢命令
## IN 'log_name' ：指定要查詢的binlog檔名(不指定就是第一個binlog檔案)
## FROM pos ：指定從哪個pos起始點開始查起(不指定就是從整個檔案首個pos點開始算)
## LIMIT [offset,] ：偏移量(不指定就是0)
## row_count ：查詢總條數(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

# 檢視具體一個binlog檔案的內容 （in 後面為binlog的檔名）
show binlog events in 'master.000003';
#分頁顯示、過濾紀錄檔
pager less
pager grep "drop"
# 設定binlog檔案儲存事件，過期刪除，單位天
set global expire_log_days=3; 
# 刪除當前的binlog檔案
reset master; 
# 刪除slave的中繼紀錄檔
reset slave;
# 刪除指定日期前的紀錄檔索引中binlog紀錄檔檔案
purge master logs before '2019-03-09 14:00:00';
# 刪除指定紀錄檔檔案
purge master logs to 'master.000003';

寫Binlog的時機

對支援事務的引擎如InnoDB而言，必須要提交了事務才會記錄binlog。

binlog 什麼時候重新整理到磁碟跟引數 sync_binlog 相關。

• 如果設定為0，則表示MySQL不控制binlog的重新整理，由檔案系統去控制它快取的重新整理；
• 如果設定為不為0的值，則表示每 sync_binlog 次事務，MySQL呼叫檔案系統的重新整理操作重新整理binlog到磁碟中。
• 設為1是最安全的，在系統故障時最多丟失一個事務的更新，但是會對效能有所影響。

如果 sync_binlog=0 或 sync_binlog大於1，當發生電源故障或作業系統崩潰時，可能有一部分已提交但其binlog未被同步到磁碟的事務會被丟失，恢復程式將無法恢復這部分事務。

在MySQL 5.7.7之前，預設值 sync_binlog 是0，MySQL 5.7.7和更高版本使用預設值1，這是最安全的選擇。一般情況下會設定為100或者0，犧牲一定的一致性來獲取更好的效能。

Binlog檔案以及擴充套件

binlog紀錄檔包括兩類檔案:

• 二進位制紀錄檔索引檔案（檔名字尾為.index）用於記錄所有有效的的二進位制檔案
• 二進位制紀錄檔檔案（檔名字尾為.00000*）記錄資料庫所有的DDL和DML語句事件

當遇到以下3種情況時，MySQL會重新生成一個新的紀錄檔檔案，檔案序號遞增：

• MySQL伺服器停止或重啟時；
• 使用 flush logs 命令；
• 當 binlog 檔案大小超過 max_binlog_size 變數的值時；

max_binlog_size 的最小值是4096位元組，最大值和預設值是 1GB (1073741824位元組)。事務被寫入到binlog的一個塊中，所以它不會在幾個二進位制紀錄檔之間被拆分。因此，如果你有很大的事務，為了保證事務的完整性，不可能做切換紀錄檔的動作，只能將該事務的紀錄檔都記錄到當前紀錄檔檔案中，直到事務結束，你可能會看到binlog檔案大於 max_binlog_size 的情況。

Binlog與Redo log區別

最開始 MySQL 裡並沒有 InnoDB 引擎，MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 只能用於歸檔。而 InnoDB 是另一個公司以外掛形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套紀錄檔系統——也就是 redo log 來實現 crash-safe 能力。binlog 和 redo log 在一定程度上都能恢復資料，但是二者有著本質的區別，具體內容如下：

• binlog 是 MySQL 本身就擁有的，不管使用何種儲存引擎，binlog 都存在，而 redo log 是 InnoDB 儲存引擎特有的，只有 InnoDB 儲存引擎才會輸出 redo log。
• binlog 是一種邏輯紀錄檔，記錄的是這個語句的原始邏輯，比如 "給 ID=2 這一行的 c 欄位加 1"。而 redo log 是一種物理紀錄檔，記錄的是 "在某個資料頁上做了什麼修改"。
• redo log 具有冪等性，多次操作的前後狀態是一致的，而 binlog 不具有冪等性，記錄的是所有影響資料庫的操作。例如插入一條資料後再將其刪除，則 redo log 前後的狀態未發生變化，而 binlog 就會記錄相應的插入操作和刪除操作。
• binlog 只會在事務提交時一次性寫入，其紀錄檔的記錄方式與事務的提交順序有關，並且一個事務的 binlog 中間不會插入其他事務的 binlog。而 redo log 記錄的是物理頁的修改，最後一個提交的事務記錄會覆蓋之前所有未提交的事務記錄，並且一個事務的 redo log 中間會插入其他事務的 redo log。
• binlog 是追加寫入，寫完一個紀錄檔檔案再寫下一個紀錄檔檔案，不會覆蓋使用，而 redo log 是迴圈寫入，紀錄檔空間的大小是固定的，會覆蓋使用。
• binlog 一般用於主從複製和資料恢復，並且不具備崩潰自動恢復的能力，而 redo log 是在伺服器發生故障後重啟 MySQL，用於恢復事務已提交但未寫入資料表的資料。

Binlog寫入過程

其實 binlog 的寫入邏輯比較簡單：事務執行過程中，先把紀錄檔寫到 binlog cache（用於快取 binlog 的記憶體緩衝區）中，等到事務提交時，再把 binlog cache 寫到 binlog 檔案中。注意，這裡是每個事務執行緒都有一個自己的緩衝區。一個事務的 binlog 不能被拆分，因此不論這個事務多大，也會確保一個事務中產生的 binlog 要被一次性寫入到磁碟中，所以一個事務的 binlog 是完整的，中間不會插入其他事務的 binlog。

系統給 binlog cache 分配了一片記憶體，每個執行緒一個，引數 binlog_cache_size 用於控制單個執行緒內 binlog cache 所佔記憶體的大小。如果超過了這個引數規定的大小，就要暫存到磁碟。事務提交時，執行器會把 binlog cache 裡的完整事務寫入到 binlog 中，並清空 binlog cache。

可以看到，每個執行緒有自己 binlog cache，但是共用同一份 binlog 檔案。

• 圖中的 write 是指把紀錄檔寫入到檔案系統的 page cache，但並沒有把資料持久化到磁碟。
• 圖中的 fsync 才是將資料持久化到磁碟的操作。一般我們認為 fsync 才佔磁碟的 IOPS。

write 和 fsync 的時機，是由引數 ****sync_binlog 控制的：

• sync_binlog = 0 時，表示每次提交事務都只 write，不 fsync，fsync 交由作業系統去實現。
• sync_binlog = 1 時，表示每次提交事務都會執行 fsync。
• sync_binlog = N(N>1) 時，表示每次提交事務都 write，但累積 N 個事務後才 fsync。

因此，在出現 IO 瓶頸的場景裡，將 sync_binlog 設定成一個比較大的值，可以提升效能。在實際的業務場景中，考慮到丟失紀錄檔量的可控性，一般不建議將這個引數設成 0，比較常見的是將其設定為 100~1000 中的某個數值。但是對應的風險是：如果主機發生異常重啟，會丟失最近 N 個事務的 binlog 紀錄檔。但我建議你設定成 1，這樣可以保證 MySQL 異常重啟後 binlog 不丟失。

二階段提交

MySQL 事務在提交的時候，會記錄事務紀錄檔和二進位制紀錄檔，也就是 redo log 和 binlog。這裡就存在一個問題：對於事務紀錄檔和二進位制紀錄檔，MySQL 會先記錄哪種呢？我們通過下面這個語句，來看一下 MySQL 在執行這個簡單的 UPDATE 語句時的內部流程：

mysql> update T set c=c+1 where ID=2;

執行流程如下圖所示：

可以看到，MySQL 將 redo log 的寫入拆成了兩個步驟：prepare 和 commit，這就是兩階段提交。兩階段提交的目的是為了讓兩份紀錄檔之間的邏輯一致。由於 redo log 和 binlog 是兩個獨立的邏輯，如果不用兩階段提交，要麼就是先寫完 redo log 再寫 binlog，或者反過來。那這兩種方式會有什麼問題呢？假設在執行 UPDATE 語句的過程中在寫完第一個紀錄檔後，第二個紀錄檔還沒寫完時發生了 crash，會出現什麼情況？

假設先寫 redo log，那麼當 redo log 寫完，binlog 還沒有寫完時發生了 crash。因為 MySQL 崩潰恢復時依賴的是 redo log 做資料恢復，所以恢復後存在這條更新語句。但由於 binlog 沒寫完就 crash 了，所以 binlog 裡就沒有這條語句。因為 MySQL 資料複製依賴的是 binlog，所以如果需要用這個 binlog 來恢復臨時庫的話，由於這個語句的 binlog 丟失，這個臨時庫就會少了這一次更新，恢復出來的資料就會與原庫不同。

假設先寫 binlog，那麼當 binlog 寫完，redo log 還沒有寫完時發生了 crash。崩潰恢復後這個事務是無效的。但 binlog 裡已經記錄了這個改動。所以，在之後用 binlog 來恢復時就多了一個事務出來，也與原庫資料不同。

兩階段提交是怎麼保證邏輯一致的呢？

當未開啟 binlog 時，如果要執行一條 UPDATE 語句，MySQL 會先寫 redo log buffer（便於事務回滾），然後再在 Buffer Pool 中修改對應的快取頁，當準備提交事物時會把 redo log 重新整理到磁碟，然後事務就提交了。如果開啟 binlog 後，我們就不能簡單地寫完 redo log 就提交事務了，否則 redo log 與 binlog 之間的邏輯是不一致的。此時，寫完 redo log 檔案後並不直接提交事務，而是將事務標記為處於 prepare 階段，等到 binlog 也寫入到檔案後，再將事務標記為 commit 狀態，表示可以提交事務了，此時才會提交事務。

當 binlog 寫完，redo log 還沒 commit 前發生 crash，那崩潰恢復後 MySQL 如何處理？

MySQL 在崩潰恢復時會判斷 redo log 中記錄的事務紀錄檔是否完整，即是否有 commit 標識。如果有 commit 標識則直接提交事務，如果沒有則需要判斷對應的事務在 binlog 上是否存在並完整。如果在 binlog 上是完整的則也要提交事務（因為 binlog 已經寫入了，之後會被從庫用，所以主庫也要提交這個事務），否則回滾事務。因此如果在上圖中的時刻 B 發生了 crash，崩潰恢復後該事務會被提交。

因為每個事務都有一個唯一的事務 id，redo log 和 binlog 在記錄紀錄檔時都會關聯相應的事務 id，所以 redo log 和 binlog 就通過事務 id 關聯了起來。

另外，在 MySQL 5.6.2 版本後，還引入了 binlog-checksum 引數，用來驗證 binlog 內容的正確性。對於 binlog 紀錄檔由於磁碟原因，可能會在紀錄檔中間出錯的情況，MySQL 可以通過校驗 checksum 值來發現。

redo 與 binlog 的刷盤時機

在兩階段提交過程中，時序上 redo log 先 prepare，再寫 binlog 檔案，最後再把 redo log 修改為 commit。這個過程中 redo log 檔案需要修改兩次。如果把 innodb_flush_log_at_trx_commit 引數設定成 1，那麼 redo log 在 prepare 階段就要進行一次持久化，由於崩潰恢復邏輯可以依賴於 prepare 的 redo log 加上 binlog 來恢復，以及每秒一次的後臺輪詢對 redo log 的刷盤操作。因此，InnoDB 認為 redo log 在 commit 時就不需要再 fsync 了，只 write 到檔案系統的 page cache 中就夠了，所以，redo log 的 commit 階段就不會刷盤了。

通常所說的 MySQL 的雙 1 設定，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都設定成 1。也就是一個事務完整提交前，需要等待兩次刷盤，一次是 redo log（prepare 階段），一次是 binlog。

能否只用 redo log 不要 binlog？

如果只從崩潰恢復的角度來講是可以的。你可以把 binlog 關掉，這樣就沒有兩階段提交的過程了，而系統依然是 crash-safe 的。但 binlog 有著 redo log 無法替代的功能。

• 一個是歸檔。redo log 是迴圈寫，寫到末尾是要回到開頭繼續寫的。這樣歷史紀錄檔沒法保留，redo log 也就起不到歸檔的作用。
• 一個就是 MySQL 系統依賴於 binlog。binlog 作為 MySQL 一開始就有的功能，被用在了很多地方。其中，MySQL 系統高可用的基礎，就是 binlog 複製。還有一些資料分析系統就靠消費 MySQL 的 binlog 來更新自己的資料。關掉 binlog 的話，這些下游系統就沒法輸入了。

總之，由於現在包括 MySQL 高可用在內的很多系統機制都依賴於 binlog，所以單靠 redo log 還做不到。

Binlog 組提交機制

若事務為非唯讀事務，則每次事務提交時需要進行一次 fsync 操作，以保證 redo log 都寫入了磁碟。為了提高磁碟 fsync 的效率，MySQL 提供了組提交（group commit）功能，即一次 fsync 能夠將多個事務的紀錄檔重新整理到磁碟的紀錄檔檔案中，而不用將每個事務的紀錄檔單獨重新整理到磁碟檔案中，從而大大提升了紀錄檔刷盤的效率。

我們知道，如果開啟了 binlog，則 MySQL 為了保證 binlog 和事務紀錄檔的一致性，使用了兩階段提交。

在兩階段提交寫 binlog 的過程中，實際上是分成兩步的：

• 先把 binlog 從 binlog cache 中寫到磁碟上的 binlog 檔案；
• 呼叫 fsync 持久化。

下圖詳細展示了在二階段提交過程中的紀錄檔寫入時機：

MySQL 為了讓組提交的效果更好，把 redo log 做 fsync 的操作拖到了步驟 3 中。這麼一來，binlog 也可以進行組提交了，因為在 binlog 的 write 和 fsync 操作之間有了一小段間隔，這允許其他提交的事務也將 binlog write 到作業系統快取中，後續通過 fsync 一併重新整理到磁碟中。這種實現方式稱為二進位制紀錄檔組提交（Binary Log Group Commit，BLGC）。

不過通常情況下第 3 步執行得會很快，所以 binlog 的 write 和 fsync 操作的間隔時間很短，導致能集合到一起持久化的 binlog 比較少，因此 binlog 的組提交的效果通常不如 redo log 的效果那麼好。如果想提升 binlog 組提交的效果，

可設定如下引數：

• binlog_group_commit_sync_delay：表示延遲多少微秒後才呼叫 fsync，預設為 0
• binlog_group_commit_sync_no_delay_count：表示累積多少次以後才呼叫 fsync，預設為 0

這兩個條件是或的關係，即只要有一個滿足條件就會呼叫 fsync 操作。注意，除非有大量的事務不斷地進行寫入和更新操作，否則不建議修改這個變數的值，這是因為修改後可能會導致事務的響應時間變長。

Binlog的紀錄檔格式

記錄在二進位制紀錄檔中的事件的格式取決於二進位制記錄格式。支援三種格式型別：

• STATEMENT：基於SQL語句的複製（statement-based replication, SBR）
• ROW：基於行的複製（row-based replication, RBR）
• MIXED：混合模式複製（mixed-based replication, MBR）

在 MySQL 5.7.7 之前，預設的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，預設值是 ROW。紀錄檔格式通過 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

Statement

每一條會修改資料的sql都會記錄在binlog中

優點：不需要記錄每一行的變化，減少了binlog紀錄檔量，節約了IO, 提高了效能。

缺點：由於記錄的只是執行語句，為了這些語句能在slave上正確執行，因此還必須記錄每條語句在執行的時候的一些相關資訊，以保證所有語句能在slave得到和在master端執行的時候相同的結果。另外mysql的複製，像一些特定函數的功能，slave與master要保持一致會有很多相關問題。

Row

5.1.5版本的MySQL才開始支援 row level 的複製,它不記錄sql語句上下文相關資訊，僅儲存哪條記錄被修改。

優點： binlog中可以不記錄執行的sql語句的上下文相關的資訊，僅需要記錄那一條記錄被修改成什麼了。所以row的紀錄檔內容會非常清楚的記錄下每一行資料修改的細節。而且不會出現某些特定情況下的儲存過程，或function，以及trigger的呼叫和觸發無法被正確複製的問題.

缺點:所有的執行的語句當記錄到紀錄檔中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的紀錄檔內容。

注：將二進位制紀錄檔格式設定為ROW時，有些更改仍然使用基於語句的格式，包括所有DDL語句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

Mixed

從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結合。 在Mixed模式下，一般的語句修改使用statment格式儲存binlog，如一些函數，statement無法完成主從複製的操作，則採用row格式儲存binlog，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的紀錄檔形式，也就是在Statement和Row之間選擇一種。

Binlog 相關引數

在 MySQL 中，輸入如下命令可以檢視與 binlog 相關的引數。

# 是否啟用binlog紀錄檔
show variables like 'log_bin';

範例如下：

其中，幾個重要的引數如下所示：

• max_binlog_size：表示單個 binlog 檔案的最大值，如果超過該值，則產生新的 binlog 檔案，並且字尾名會加一。預設值為 1GB。
• max_binlog_ cache_size：表示 binlog 佔用的最大記憶體。
• binlog_cache_size：MySQL 執行事務時，所有未提交的二進位制紀錄檔會被記錄到緩衝區中，等該事務提交時直接將緩衝中的二進位制紀錄檔重新整理到磁碟。這個緩衝的大小就是由 binlog_cache_size 決定，預設 32 KB。並且該引數是基於對談的，也就是說，當一個執行緒開始一個事務時，MySQL 會自動分配一個大小為 binlog_cache_size 的快取，因此該值的設定需要相當小心，不能設定過大。當一個事務的記錄大於設定的 binlog_cache_size 時，MySQL 會把緩衝中的紀錄檔寫入一個臨時檔案中，因此該值又不能設得太小。
• binlog_cache_use：表示使用 binlog_cache 的事務數量。
• binlog_cache_disk_use：表示使用 binlog_cache 但超過 binlog_cache_ size 的值，並且使用臨時檔案來儲存 SQL 語句中的事務數量。

清理過期的Binlog紀錄檔

在開啟MySQL的主從後，會產生大量的binlog紀錄檔檔案，可能產生大量的磁碟空間

手工刪除binlog

# 刪除master的binlog，慎用
reset master;
# 刪除slave的中繼紀錄檔
reset slave;
# 刪除指定日期以前的紀錄檔索引中binlog紀錄檔檔案
purge master logs before ‘2019-11-22 16:39:01';
# 刪除指定紀錄檔檔案的紀錄檔索引中binlog紀錄檔檔案
purge master logs to ‘binlog.000002';

自動刪除binlog

通過binlog引數（expire_logs_days）來實現MySQL自動刪除binlog

show binary logs;
show variables like ‘expire_logs_days';
set global expire_logs_days=3;

用途

主從同步

同步原理：

如下圖所示，MySQL主備複製基於二進位制紀錄檔binlog。任何資料更改都會寫入二進位制紀錄檔。

資料庫管理員搭建主備複製時，只需要在備庫change master to指定主庫的IP、埠、同步開始的二進位制檔案和檔案偏移量（MySQL 5.6以後支援GTID模式，二進位制檔案和檔案偏移量可以用GTID號集合替換）就可以了。

備庫通過IO執行緒連線主庫，接收主庫推播過來的二進位制紀錄檔，並記錄到原生的中繼紀錄檔relaylog；同時也會啟動SQL執行緒將中繼紀錄檔的資料變更應用到備庫本地資料庫中.

主庫接受到備庫IO執行緒的請求，會專門對該slave啟用獨立的binlog dump執行緒，從IO執行緒指定的二進位制檔案和檔案偏移量開始傳送二進位制紀錄檔；並且在主庫有任何新的變更後，在記錄到自己的二進位制紀錄檔的同時也會通過網路推播給備庫的IO執行緒。

複製執行緒

Master執行緒：

• binlog dump執行緒 dump執行緒的作用是讀取主庫上二進位制紀錄檔中的事件。在複製執行緒處於正常執行狀態時，當事務提交的時候，binlog紀錄檔sync到磁碟上之後，MySQL會呼叫signal_update()函數，這個函數的作用是通知binlog dump執行緒，binlog紀錄檔有更新了，dump執行緒將產生的增量binlog推播到從庫的IO執行緒；在主從之間建議複製連線的時候，從庫IO執行緒將binlog檔名以及位置點（GTID模式下是傳送GTID集合）傳送給主句dump執行緒拉取從庫所需binlog。 對於一主多從的情況，master上會有多個binlog dump執行緒。

Slave執行緒：

• I/O執行緒： I/O執行緒的作用就是拉取主庫上的binlog紀錄檔，在從庫上存貯為relay log紀錄檔。方便SQL執行緒中relay log中重放事務。 I/O執行緒在與主庫建立連線的時候，超過slave_net_timeout時間沒有建立連線成功，從庫就認為這次連線失敗，需要重試連線，重試連線的次數由MASTER_RETRY_COUNT決定。 I/O執行緒在與主庫成功建立連線之後，針對可能主庫很長時間都沒有更新資料的情況，I/O執行緒採用了心跳機制，I/O執行緒在空閒的時候，每隔MASTER_HEARTBEAT_PERIOD時間間隔，I/O執行緒就向主庫傳送一個心跳包，測試與主庫的連線是否正常。 使用mysqlbinlog工具也可以解析relay log紀錄檔。
• SQL執行緒： 讀取relay log，並且重放relay log中的事務，以達到複製的目的。

主從複製優化

多執行緒複製：

在官方的 5.6 版本之前，MySQL 只支援單執行緒複製，因此在主庫並行高、TPS 高時就會出現嚴重的主備延遲問題。從單執行緒複製到最新版本的多執行緒複製，中間的演化經歷了好幾個版本。但說到底，所有的多執行緒複製機制，都是要把之前從庫中的單個 sql_thread 執行緒拆成多個執行緒，io 執行緒還是隻有一個。即下面這個模型：

當 IO 執行緒將主庫的 binlog 寫入 relay log 後，會有一個多執行緒協調器（multithreaded slave coordinator）對多個 SQL 執行緒進行排程，讓它們按照一定的規則去執行 relay log 中的事件。即圖中的 coordinator 就是原來的 sql_thread，不過現在它不再直接更新資料了，而是隻負責讀取中轉紀錄檔和分發事務。真正更新紀錄檔的變成了 worker 執行緒。而 work 執行緒的個數由引數 決定，預設為 0，即關閉多執行緒功能。

為了保證事務執行的先後順序，coordinator 在分發時，需要滿足以下這兩個基本要求：

●不能造成更新覆蓋。這就要求更新同一行的兩個事務，必須被分發到同一個 worker 中序列執行。
●同一個事務不能被拆開，必須放到同一個 worker 中。

在 MySQL 5.6 版本的並行複製中，支援的粒度是按庫並行。這個策略的並行效果，取決於壓力模型。如果在主庫上有多個 DB，並且各個 DB 的壓力均衡，使用這個策略的效果會很好。

半同步複製：

MySQL 的預設複製模式是非同步模式，即 MySQL 的主伺服器上的 I/O 執行緒，將資料寫到 binlog 中就直接返回給使用者端資料更新成功，不考慮資料是否傳輸到從伺服器，以及是否寫入到 relay log 中。在這種模式下，複製資料其實是有風險的，一旦資料只寫到了主庫的 binlog 中還沒來得急同步到從庫時主庫宕機了，此時就會造成資料的丟失。但這種模式也是效率最高的，因為變更資料的功能都只是在主庫中完成就可以了，從庫複製資料不會影響到主庫的寫資料操作。

為了解決非同步複製的資料可靠性問題，MySQL 從 5.5 版本開始允許通過以外掛的形式開始支援半同步的主從複製模式。半同步複製模式是介於非同步和同步之間的一種複製模式，主庫在執行完使用者端提交的事務後，要等待至少一個從庫接收到 binlog 並將資料寫入到 relay log 中才返回給使用者端成功結果，可通過引數 設定至少得到 slave 的 ACK 個數，預設為 1。半同步複製模式比非同步模式提高了資料的可用性，但也產生了一定的效能延遲。

半同步複製的原理是在 master 的 dump 執行緒去通知從庫時，增加了一個 ACK 機制，也就是會確認從庫是否收到事務的標誌碼，master 的 dump 執行緒不但要傳送 binlog 到從庫，還要負責接收 slave 的 ACK。當 slave 出現異常沒有返回 ACK 時，主庫將自動降級為非同步複製，直到異常修復後再自動變為半同步複製。

但半同步複製模式也存在一定的資料風險，當事務在主庫提交完後等待從庫 ACK 的過程中，如果 master 宕機了，這個時候就會有兩種情況的問題：

事務還沒傳送到 slave上：若事務還沒傳送 slave 上，使用者端在收到失敗結果後，會重新提交事務，因為重新提交的事務是在新的 master 上執行的，所以會執行成功，後面若是之前的 master 恢復後，會以 slave 的身份加入到叢集中，此時，之前的事務就會被執行兩次，第一次是之前此機器作為 master 時執行的，第二次是做為 slave 後從主庫中同步過來的。

事務已經同步到 slave 上：因為事務已經同步到 slave 了，所以當用戶端收到失敗結果後，再次提交事務，那麼此事務就會在當前 slave 機器上執行兩次。

為了解決上面的隱患，MySQL 從 5.7 版本開始，增加了一種新的半同步方式。新的半同步方式的執行過程是將 "Storage Commit" 這一步移動到了 "Write Slave dump" 後面。這樣保證了只有 slave 的事務 ACK 後，才提交主庫事務。MySQL 5.7.2 版本新增了一個 引數用來設定半同步方式，該引數有兩個值可設定：

AFTER_SYNC：引數值為AFTER_SYNC時，代表採用的是新的半同步複製方式。 AFTER_COMMIT：代表採用的是之前的舊方式的半同步複製模式。

MySQL 從 5.7.2 版本開始，預設的半同步複製方式就是 方式了，但這種複製方式也不是萬能的，因為 AFTER_SYNC 方式是在事務同步到 slave 後才提交主庫事務的，若是當主庫等待 slave 同步成功的過程中 master 掛了，這個 master 事務提交就失敗了，使用者端也收到了事務執行失敗的結果了，但是 slave 上已經將 binlog 的內容寫到 relay log 裡了，此時 slave 資料就會多了，但是多了資料一般問題不算嚴重，多了總比少了好。MySQL 在沒辦法解決分散式資料一致性問題的情況下，它只能保證的是不丟資料。

資料恢復

上面說過每一條 event 都有位點資訊，如果我們當前的 MySQL 庫被無操作或者誤刪除了，那麼該如何通過 Binlog 來恢復到刪除之前的資料狀態呢？ 首先發現誤操作之後，先停止 MySQL 服務，防止繼續更新。 接著通過 mysqlbinlog命令對二進位制檔案進行分析，檢視誤操作之前的位點資訊在哪裡。 接下來肯定就是恢復資料，當前資料庫的資料已經是錯的，那麼就從開始位置到誤操作之前位點的資料肯定的都是正確的；如果誤操作之後也有正常的資料進來，這一段時間的位點資料也要備份。 比如說： 誤操作的位點開始值為 501，誤操作結束的位置為705，之後到800的位點都是正確資料。 那麼從 0 - 500 ，706 - 800 都是有效資料，接著我們就可以進行資料恢復了。 先將資料庫備份並清空。 接著使用 mysqlbinlog 來恢復資料： 0 - 500 的資料：

mysqlbinlog --start-position=0  --stop-position=500  bin-log.000003 > /root/back.sql;

上面命令的作用就是將 0 -500 位點的資料恢復到自定義的 SQL 檔案中。同理 706 - 800 的資料也是一樣操作。之後我們執行這兩個 SQL 檔案就行了。

mysqlbinlog 命令的使用

伺服器以二進位制格式將binlog紀錄檔寫入binlog檔案，如何要以文字格式顯示其內容，可以使用 mysqlbinlog 命令。

# 檢視bin-log二進位制檔案（shell方式）
mysqlbinlog -v --base64-output=decode-rows /data/3306/binlog/mysql-bin.000005

# 檢視bin-log二進位制檔案（帶查詢條件）
mysqlbinlog -v --base64-output=decode-rows /data/3306/binlog/mysql-bin.000005 
    --start-position="5000"    
    --stop-position="20000"    
    --start-datetime="2021-05-01 08:01:00"  
    --stop-datetime="2012-03-10 08:20:00"
    
# 擷取紀錄檔（GTID）
cd /data/3306/binlog/
mysqlbinlog --skip-gtids --include-gtids='aeb87061-aa0a-11eb-8f23-000c2927c91a:2-629' mysql-bin.000002 mysql-bin.000005

mysqlbinlog --skip-gtids --include-gtids='aeb87061-aa0a-11eb-8f23-000c2927c91a:2-629' --exclude-gtids='aeb87061-aa0a-11eb-8f23-000c2927c91a:300-629' mysql-bin.000002 mysql-bin.000005

# 臨時不記錄binlog紀錄檔
set sql_log_bin=0;

#實時拉取遠端主機binlog檔案中的資料
mysqlbinlog  -R --host=10.0.0.52 --user=mha --password=mha --raw  --stop-never mysql-bin.000003 &

到此這篇關於MySQL中的 Binlog 深度解析的文章就介紹到這了,更多相關MySQL Binlog內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！