前言：

MySQL 的二進制日志 binlog 可以說是 MySQL 最重要的日志，它記錄了所有的 DDL 和 DML 語句（除了數(shù)據(jù)查詢語句select、show等），以事件形式記錄，還包含語句所執(zhí)行的消耗的時間，MySQL的二進制日志是事務安全型的。binlog 的主要目的是復制和恢復。

Binlog日志的兩個最重要的使用場景

• MySQL主從復制：MySQL Replication在Master端開啟binlog，Master把它的二進制日志傳遞給slaves來達到master-slave數(shù)據(jù)一致的目的
• 數(shù)據(jù)恢復：通過使用 mysqlbinlog工具來使恢復數(shù)據(jù)

配置文件參數(shù)說明

Binlog 日志功能默認是開啟的，線上情況下 Binlog 日志的增長速度是很快的，在 MySQL 的配置文件 my.cnf 中提供一些參數(shù)來對 Binlog 進行設置。

[mysqld]
設置此參數(shù)表示啟用binlog功能，并制定二進制日志的存儲目錄，開啟binlog日志大概會有1%的性能損耗
log-bin=/home/mysql/binlog/
# 高版本MySQL需要server-id這個參數(shù)，提供一個集群中不重復的id值即可
server-id=1
#mysql-bin.*日志文件最大字節(jié)（單位：字節(jié)）
#設置最大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 過期刪除不是服務定時執(zhí)行，是需要借助事件觸發(fā)才執(zhí)行，事件包括：

• 服務器重啟
• 服務器被更新
• 日志達到了最大日志長度 max_binlog_size
• 日志被刷新

常用的Binlog操作命令

# 是否啟用binlog日志
show variables like 'log_bin';

# 查看binlog的目錄
show global variables like "%log_bin%";

# 查看當前服務器使用的biglog文件個數(shù)及大小
show binary logs;

# 查看最新一個binlog日志文件名稱和Position
show master status;

# 事件查詢命令
## IN 'log_name' ：指定要查詢的binlog文件名(不指定就是第一個binlog文件)
## FROM pos ：指定從哪個pos起始點開始查起(不指定就是從整個文件首個pos點開始算)
## LIMIT [offset,] ：偏移量(不指定就是0)
## row_count ：查詢總條數(shù)(不指定就是所有行)
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';

寫B(tài)inlog的時機

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

binlog 什么時候刷新到磁盤跟參數(shù) sync_binlog 相關。

• 如果設置為0，則表示MySQL不控制binlog的刷新，由文件系統(tǒng)去控制它緩存的刷新；
• 如果設置為不為0的值，則表示每 sync_binlog 次事務，MySQL調用文件系統(tǒng)的刷新操作刷新binlog到磁盤中。
• 設為1是最安全的，在系統(tǒng)故障時最多丟失一個事務的更新，但是會對性能有所影響。

如果 sync_binlog=0 或 sync_binlog大于1，當發(fā)生電源故障或操作系統(tǒng)崩潰時，可能有一部分已提交但其binlog未被同步到磁盤的事務會被丟失，恢復程序將無法恢復這部分事務。

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

Binlog文件以及擴展

binlog日志包括兩類文件:

• 二進制日志索引文件（文件名后綴為.index）用于記錄所有有效的的二進制文件
• 二進制日志文件（文件名后綴為.00000*）記錄數(shù)據(jù)庫所有的DDL和DML語句事件

當遇到以下3種情況時，MySQL會重新生成一個新的日志文件，文件序號遞增：

• MySQL服務器停止或重啟時；
• 使用 flush logs 命令；
• 當 binlog 文件大小超過 max_binlog_size 變量的值時；

max_binlog_size 的最小值是4096字節(jié)，最大值和默認值是 1GB (1073741824字節(jié))。事務被寫入到binlog的一個塊中，所以它不會在幾個二進制日志之間被拆分。因此，如果你有很大的事務，為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的日志都記錄到當前日志文件中，直到事務結束，你可能會看到binlog文件大于 max_binlog_size 的情況。

Binlog與Redo log區(qū)別

最開始 MySQL 里并沒有 InnoDB 引擎，MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 只能用于歸檔。而 InnoDB 是另一個公司以插件形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來實現(xiàn) crash-safe 能力。binlog 和 redo log 在一定程度上都能恢復數(shù)據(jù)，但是二者有著本質的區(qū)別，具體內容如下：

• binlog 是 MySQL 本身就擁有的，不管使用何種存儲引擎，binlog 都存在，而 redo log 是 InnoDB 存儲引擎特有的，只有 InnoDB 存儲引擎才會輸出 redo log。
• binlog 是一種邏輯日志，記錄的是這個語句的原始邏輯，比如 "給 ID=2 這一行的 c 字段加 1"。而 redo log 是一種物理日志，記錄的是 "在某個數(shù)據(jù)頁上做了什么修改"。
• redo log 具有冪等性，多次操作的前后狀態(tài)是一致的，而 binlog 不具有冪等性，記錄的是所有影響數(shù)據(jù)庫的操作。例如插入一條數(shù)據(jù)后再將其刪除，則 redo log 前后的狀態(tài)未發(fā)生變化，而 binlog 就會記錄相應的插入操作和刪除操作。
• binlog 只會在事務提交時一次性寫入，其日志的記錄方式與事務的提交順序有關，并且一個事務的 binlog 中間不會插入其他事務的 binlog。而 redo log 記錄的是物理頁的修改，最后一個提交的事務記錄會覆蓋之前所有未提交的事務記錄，并且一個事務的 redo log 中間會插入其他事務的 redo log。
• binlog 是追加寫入，寫完一個日志文件再寫下一個日志文件，不會覆蓋使用，而 redo log 是循環(huán)寫入，日志空間的大小是固定的，會覆蓋使用。
• binlog 一般用于主從復制和數(shù)據(jù)恢復，并且不具備崩潰自動恢復的能力，而 redo log 是在服務器發(fā)生故障后重啟 MySQL，用于恢復事務已提交但未寫入數(shù)據(jù)表的數(shù)據(jù)。

Binlog寫入過程

其實 binlog 的寫入邏輯比較簡單：事務執(zhí)行過程中，先把日志寫到 binlog cache（用于緩存 binlog 的內存緩沖區(qū)）中，等到事務提交時，再把 binlog cache 寫到 binlog 文件中。注意，這里是每個事務線程都有一個自己的緩沖區(qū)。一個事務的 binlog 不能被拆分，因此不論這個事務多大，也會確保一個事務中產生的 binlog 要被一次性寫入到磁盤中，所以一個事務的 binlog 是完整的，中間不會插入其他事務的 binlog。

系統(tǒng)給 binlog cache 分配了一片內存，每個線程一個，參數(shù) binlog_cache_size 用于控制單個線程內 binlog cache 所占內存的大小。如果超過了這個參數(shù)規(guī)定的大小，就要暫存到磁盤。事務提交時，執(zhí)行器會把 binlog cache 里的完整事務寫入到 binlog 中，并清空 binlog cache。

可以看到，每個線程有自己 binlog cache，但是共用同一份 binlog 文件。

• 圖中的 write 是指把日志寫入到文件系統(tǒng)的 page cache，但并沒有把數(shù)據(jù)持久化到磁盤。
• 圖中的 fsync 才是將數(shù)據(jù)持久化到磁盤的操作。一般我們認為 fsync 才占磁盤的 IOPS。

write 和 fsync 的時機，是由參數(shù) ****sync_binlog 控制的：

• sync_binlog = 0 時，表示每次提交事務都只 write，不 fsync，fsync 交由操作系統(tǒng)去實現(xiàn)。
• sync_binlog = 1 時，表示每次提交事務都會執(zhí)行 fsync。
• sync_binlog = N(N>1) 時，表示每次提交事務都 write，但累積 N 個事務后才 fsync。

因此，在出現(xiàn) IO 瓶頸的場景里，將 sync_binlog 設置成一個比較大的值，可以提升性能。在實際的業(yè)務場景中，考慮到丟失日志量的可控性，一般不建議將這個參數(shù)設成 0，比較常見的是將其設置為 100~1000 中的某個數(shù)值。但是對應的風險是：如果主機發(fā)生異常重啟，會丟失最近 N 個事務的 binlog 日志。但我建議你設置成 1，這樣可以保證 MySQL 異常重啟后 binlog 不丟失。

二階段提交

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

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

執(zhí)行流程如下圖所示：

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

假設先寫 redo log，那么當 redo log 寫完，binlog 還沒有寫完時發(fā)生了 crash。因為 MySQL 崩潰恢復時依賴的是 redo log 做數(shù)據(jù)恢復，所以恢復后存在這條更新語句。但由于 binlog 沒寫完就 crash 了，所以 binlog 里就沒有這條語句。因為 MySQL 數(shù)據(jù)復制依賴的是 binlog，所以如果需要用這個 binlog 來恢復臨時庫的話，由于這個語句的 binlog 丟失，這個臨時庫就會少了這一次更新，恢復出來的數(shù)據(jù)就會與原庫不同。

假設先寫 binlog，那么當 binlog 寫完，redo log 還沒有寫完時發(fā)生了 crash。崩潰恢復后這個事務是無效的。但 binlog 里已經記錄了這個改動。所以，在之后用 binlog 來恢復時就多了一個事務出來，也與原庫數(shù)據(jù)不同。

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

當未開啟 binlog 時，如果要執(zhí)行一條 UPDATE 語句，MySQL 會先寫 redo log buffer（便于事務回滾），然后再在 Buffer Pool 中修改對應的緩存頁，當準備提交事物時會把 redo log 刷新到磁盤，然后事務就提交了。如果開啟 binlog 后，我們就不能簡單地寫完 redo log 就提交事務了，否則 redo log 與 binlog 之間的邏輯是不一致的。此時，寫完 redo log 文件后并不直接提交事務，而是將事務標記為處于 prepare 階段，等到 binlog 也寫入到文件后，再將事務標記為 commit 狀態(tài)，表示可以提交事務了，此時才會提交事務。

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

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

因為每個事務都有一個唯一的事務 id，redo log 和 binlog 在記錄日志時都會關聯(lián)相應的事務 id，所以 redo log 和 binlog 就通過事務 id 關聯(lián)了起來。

另外，在 MySQL 5.6.2 版本后，還引入了 binlog-checksum 參數(shù)，用來驗證 binlog 內容的正確性。對于 binlog 日志由于磁盤原因，可能會在日志中間出錯的情況，MySQL 可以通過校驗 checksum 值來發(fā)現(xiàn)。

redo 與 binlog 的刷盤時機

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

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

能否只用 redo log 不要 binlog？

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

• 一個是歸檔。redo log 是循環(huán)寫，寫到末尾是要回到開頭繼續(xù)寫的。這樣歷史日志沒法保留，redo log 也就起不到歸檔的作用。
• 一個就是 MySQL 系統(tǒng)依賴于 binlog。binlog 作為 MySQL 一開始就有的功能，被用在了很多地方。其中，MySQL 系統(tǒng)高可用的基礎，就是 binlog 復制。還有一些數(shù)據(jù)分析系統(tǒng)就靠消費 MySQL 的 binlog 來更新自己的數(shù)據(jù)。關掉 binlog 的話，這些下游系統(tǒng)就沒法輸入了。

總之，由于現(xiàn)在包括 MySQL 高可用在內的很多系統(tǒng)機制都依賴于 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 到操作系統(tǒng)緩存中，后續(xù)通過 fsync 一并刷新到磁盤中。這種實現(xiàn)方式稱為二進制日志組提交（Binary Log Group Commit，BLGC）。

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

可設置如下參數(shù)：

• 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

每一條會修改數(shù)據(jù)的sql都會記錄在binlog中

優(yōu)點：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO, 提高了性能。

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

Row

5.1.5版本的MySQL才開始支持 row level 的復制,它不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。

優(yōu)點： binlog中可以不記錄執(zhí)行的sql語句的上下文相關的信息，僅需要記錄那一條記錄被修改成什么了。所以row的日志內容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)。而且不會出現(xiàn)某些特定情況下的存儲過程，或function，以及trigger的調用和觸發(fā)無法被正確復制的問題.

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

注：將二進制日志格式設置為ROW時，有些更改仍然使用基于語句的格式，包括所有DDL語句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

Mixed

從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結合。 在Mixed模式下，一般的語句修改使用statment格式保存binlog，如一些函數(shù)，statement無法完成主從復制的操作，則采用row格式保存binlog，MySQL會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式，也就是在Statement和Row之間選擇一種。

Binlog 相關參數(shù)

在 MySQL 中，輸入如下命令可以查看與 binlog 相關的參數(shù)。

# 是否啟用binlog日志
show variables like 'log_bin';

示例如下：

其中，幾個重要的參數(shù)如下所示：

• max_binlog_size：表示單個 binlog 文件的最大值，如果超過該值，則產生新的 binlog 文件，并且后綴名會加一。默認值為 1GB。
• max_binlog_ cache_size：表示 binlog 占用的最大內存。
• binlog_cache_size：MySQL 執(zhí)行事務時，所有未提交的二進制日志會被記錄到緩沖區(qū)中，等該事務提交時直接將緩沖中的二進制日志刷新到磁盤。這個緩沖的大小就是由 binlog_cache_size 決定，默認 32 KB。并且該參數(shù)是基于會話的，也就是說，當一個線程開始一個事務時，MySQL 會自動分配一個大小為 binlog_cache_size 的緩存，因此該值的設置需要相當小心，不能設置過大。當一個事務的記錄大于設定的 binlog_cache_size 時，MySQL 會把緩沖中的日志寫入一個臨時文件中，因此該值又不能設得太小。
• binlog_cache_use：表示使用 binlog_cache 的事務數(shù)量。
• binlog_cache_disk_use：表示使用 binlog_cache 但超過 binlog_cache_ size 的值，并且使用臨時文件來保存 SQL 語句中的事務數(shù)量。

清理過期的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參數(shù)（expire_logs_days）來實現(xiàn)MySQL自動刪除binlog

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

用途

主從同步

同步原理：

如下圖所示，MySQL主備復制基于二進制日志binlog。任何數(shù)據(jù)更改都會寫入二進制日志。

數(shù)據(jù)庫管理員搭建主備復制時，只需要在備庫change master to指定主庫的IP、端口、同步開始的二進制文件和文件偏移量（MySQL 5.6以后支持GTID模式，二進制文件和文件偏移量可以用GTID號集合替換）就可以了。

備庫通過IO線程連接主庫，接收主庫推送過來的二進制日志，并記錄到本地的中繼日志relaylog；同時也會啟動SQL線程將中繼日志的數(shù)據(jù)變更應用到備庫本地數(shù)據(jù)庫中.

主庫接受到備庫IO線程的請求，會專門對該slave啟用獨立的binlog dump線程，從IO線程指定的二進制文件和文件偏移量開始發(fā)送二進制日志；并且在主庫有任何新的變更后，在記錄到自己的二進制日志的同時也會通過網絡推送給備庫的IO線程。

復制線程

Master線程：

• binlog dump線程 dump線程的作用是讀取主庫上二進制日志中的事件。在復制線程處于正常運行狀態(tài)時，當事務提交的時候，binlog日志sync到磁盤上之后，MySQL會調用signal_update()函數(shù)，這個函數(shù)的作用是通知binlog dump線程，binlog日志有更新了，dump線程將產生的增量binlog推送到從庫的IO線程；在主從之間建議復制連接的時候，從庫IO線程將binlog文件名以及位置點（GTID模式下是發(fā)送GTID集合）發(fā)送給主句dump線程拉取從庫所需binlog。 對于一主多從的情況，master上會有多個binlog dump線程。

Slave線程：

• I/O線程： I/O線程的作用就是拉取主庫上的binlog日志，在從庫上存貯為relay log日志。方便SQL線程中relay log中重放事務。 I/O線程在與主庫建立連接的時候，超過slave_net_timeout時間沒有建立連接成功，從庫就認為這次連接失敗，需要重試連接，重試連接的次數(shù)由MASTER_RETRY_COUNT決定。 I/O線程在與主庫成功建立連接之后，針對可能主庫很長時間都沒有更新數(shù)據(jù)的情況，I/O線程采用了心跳機制，I/O線程在空閑的時候，每隔MASTER_HEARTBEAT_PERIOD時間間隔，I/O線程就向主庫發(fā)送一個心跳包，測試與主庫的連接是否正常。 使用mysqlbinlog工具也可以解析relay log日志。
• SQL線程： 讀取relay log，并且重放relay log中的事務，以達到復制的目的。

主從復制優(yōu)化

多線程復制：

在官方的 5.6 版本之前，MySQL 只支持單線程復制，因此在主庫并發(fā)高、TPS 高時就會出現(xiàn)嚴重的主備延遲問題。從單線程復制到最新版本的多線程復制，中間的演化經歷了好幾個版本。但說到底，所有的多線程復制機制，都是要把之前從庫中的單個 sql_thread 線程拆成多個線程，io 線程還是只有一個。即下面這個模型：

當 IO 線程將主庫的 binlog 寫入 relay log 后，會有一個多線程協(xié)調器（multithreaded slave coordinator）對多個 SQL 線程進行調度，讓它們按照一定的規(guī)則去執(zhí)行 relay log 中的事件。即圖中的 coordinator 就是原來的 sql_thread，不過現(xiàn)在它不再直接更新數(shù)據(jù)了，而是只負責讀取中轉日志和分發(fā)事務。真正更新日志的變成了 worker 線程。而 work 線程的個數(shù)由參數(shù) 決定，默認為 0，即關閉多線程功能。

為了保證事務執(zhí)行的先后順序，coordinator 在分發(fā)時，需要滿足以下這兩個基本要求：

●不能造成更新覆蓋。這就要求更新同一行的兩個事務，必須被分發(fā)到同一個 worker 中串行執(zhí)行。
●同一個事務不能被拆開，必須放到同一個 worker 中。

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

半同步復制：

MySQL 的默認復制模式是異步模式，即 MySQL 的主服務器上的 I/O 線程，將數(shù)據(jù)寫到 binlog 中就直接返回給客戶端數(shù)據(jù)更新成功，不考慮數(shù)據(jù)是否傳輸?shù)綇姆掌鳎约笆欠駥懭氲?relay log 中。在這種模式下，復制數(shù)據(jù)其實是有風險的，一旦數(shù)據(jù)只寫到了主庫的 binlog 中還沒來得急同步到從庫時主庫宕機了，此時就會造成數(shù)據(jù)的丟失。但這種模式也是效率最高的，因為變更數(shù)據(jù)的功能都只是在主庫中完成就可以了，從庫復制數(shù)據(jù)不會影響到主庫的寫數(shù)據(jù)操作。

為了解決異步復制的數(shù)據(jù)可靠性問題，MySQL 從 5.5 版本開始允許通過以插件的形式開始支持半同步的主從復制模式。半同步復制模式是介于異步和同步之間的一種復制模式，主庫在執(zhí)行完客戶端提交的事務后，要等待至少一個從庫接收到 binlog 并將數(shù)據(jù)寫入到 relay log 中才返回給客戶端成功結果，可通過參數(shù) 配置至少得到 slave 的 ACK 個數(shù)，默認為 1。半同步復制模式比異步模式提高了數(shù)據(jù)的可用性，但也產生了一定的性能延遲。

半同步復制的原理是在 master 的 dump 線程去通知從庫時，增加了一個 ACK 機制，也就是會確認從庫是否收到事務的標志碼，master 的 dump 線程不但要發(fā)送 binlog 到從庫，還要負責接收 slave 的 ACK。當 slave 出現(xiàn)異常沒有返回 ACK 時，主庫將自動降級為異步復制，直到異常修復后再自動變?yōu)榘胪綇椭啤?/p>

但半同步復制模式也存在一定的數(shù)據(jù)風險，當事務在主庫提交完后等待從庫 ACK 的過程中，如果 master 宕機了，這個時候就會有兩種情況的問題：

事務還沒發(fā)送到 slave上：若事務還沒發(fā)送 slave 上，客戶端在收到失敗結果后，會重新提交事務，因為重新提交的事務是在新的 master 上執(zhí)行的，所以會執(zhí)行成功，后面若是之前的 master 恢復后，會以 slave 的身份加入到集群中，此時，之前的事務就會被執(zhí)行兩次，第一次是之前此機器作為 master 時執(zhí)行的，第二次是做為 slave 后從主庫中同步過來的。

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

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

AFTER_SYNC：參數(shù)值為AFTER_SYNC時，代表采用的是新的半同步復制方式。 AFTER_COMMIT：代表采用的是之前的舊方式的半同步復制模式。

MySQL 從 5.7.2 版本開始，默認的半同步復制方式就是 方式了，但這種復制方式也不是萬能的，因為 AFTER_SYNC 方式是在事務同步到 slave 后才提交主庫事務的，若是當主庫等待 slave 同步成功的過程中 master 掛了，這個 master 事務提交就失敗了，客戶端也收到了事務執(zhí)行失敗的結果了，但是 slave 上已經將 binlog 的內容寫到 relay log 里了，此時 slave 數(shù)據(jù)就會多了，但是多了數(shù)據(jù)一般問題不算嚴重，多了總比少了好。MySQL 在沒辦法解決分布式數(shù)據(jù)一致性問題的情況下，它只能保證的是不丟數(shù)據(jù)。

數(shù)據(jù)恢復

上面說過每一條 event 都有位點信息，如果我們當前的 MySQL 庫被無操作或者誤刪除了，那么該如何通過 Binlog 來恢復到刪除之前的數(shù)據(jù)狀態(tài)呢？ 首先發(fā)現(xiàn)誤操作之后，先停止 MySQL 服務，防止繼續(xù)更新。 接著通過 mysqlbinlog命令對二進制文件進行分析，查看誤操作之前的位點信息在哪里。 接下來肯定就是恢復數(shù)據(jù)，當前數(shù)據(jù)庫的數(shù)據(jù)已經是錯的，那么就從開始位置到誤操作之前位點的數(shù)據(jù)肯定的都是正確的；如果誤操作之后也有正常的數(shù)據(jù)進來，這一段時間的位點數(shù)據(jù)也要備份。 比如說： 誤操作的位點開始值為 501，誤操作結束的位置為705，之后到800的位點都是正確數(shù)據(jù)。 那么從 0 - 500 ，706 - 800 都是有效數(shù)據(jù)，接著我們就可以進行數(shù)據(jù)恢復了。 先將數(shù)據(jù)庫備份并清空。 接著使用 mysqlbinlog 來恢復數(shù)據(jù)： 0 - 500 的數(shù)據(jù)：

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

上面命令的作用就是將 0 -500 位點的數(shù)據(jù)恢復到自定義的 SQL 文件中。同理 706 - 800 的數(shù)據(jù)也是一樣操作。之后我們執(zhí)行這兩個 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文件中的數(shù)據(jù)
mysqlbinlog  -R --host=10.0.0.52 --user=mha --password=mha --raw  --stop-never mysql-bin.000003 &

到此這篇關于MySQL中的 Binlog 深度解析的文章就介紹到這了,更多相關MySQL Binlog內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論