前言：

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

Binlog日志的兩個(gè)最重要的使用場(chǎng)景

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

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

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

[mysqld]
設(shè)置此參數(shù)表示啟用binlog功能，并制定二進(jìn)制日志的存儲(chǔ)目錄，開啟binlog日志大概會(huì)有1%的性能損耗
log-bin=/home/mysql/binlog/
# 高版本MySQL需要server-id這個(gè)參數(shù)，提供一個(gè)集群中不重復(fù)的id值即可
server-id=1
#mysql-bin.*日志文件最大字節(jié)（單位：字節(jié)）
#設(shè)置最大100MB
max_binlog_size=104857600
#設(shè)置了只保留7天BINLOG（單位：天）
expire_logs_days = 7
#binlog日志只記錄指定庫的更新
#binlog-do-db=db_name
#binlog日志不記錄指定庫的更新
#binlog-ignore-db=db_name
#寫緩沖多少次，刷一次磁盤，默認(rèn)0
sync_binlog=0

需要注意的是： max_binlog_size ：Binlog 最大和默認(rèn)值是 1G，該設(shè)置并不能嚴(yán)格控制 Binlog 的大小，尤其是 Binlog 比較靠近最大值而又遇到一個(gè)比較大事務(wù)時(shí)，為了保證事務(wù)的完整性不可能做切換日志的動(dòng)作，只能將該事務(wù)的所有 SQL 都記錄進(jìn)當(dāng)前日志直到事務(wù)結(jié)束。所以真實(shí)文件有時(shí)候會(huì)大于 max_binlog_size 設(shè)定值。 expire_logs_days ：Binlog 過期刪除不是服務(wù)定時(shí)執(zhí)行，是需要借助事件觸發(fā)才執(zhí)行，事件包括：

• 服務(wù)器重啟
• 服務(wù)器被更新
• 日志達(dá)到了最大日志長(zhǎng)度 max_binlog_size
• 日志被刷新

常用的Binlog操作命令

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

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

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

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

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

# 查看具體一個(gè)binlog文件的內(nèi)容 （in 后面為binlog的文件名）
show binlog events in 'master.000003';
#分頁顯示、過濾日志
pager less
pager grep "drop"
# 設(shè)置binlog文件保存事件，過期刪除，單位天
set global expire_log_days=3; 
# 刪除當(dāng)前的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的時(shí)機(jī)

對(duì)支持事務(wù)的引擎如InnoDB而言，必須要提交了事務(wù)才會(huì)記錄binlog。

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

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

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

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

Binlog文件以及擴(kuò)展

binlog日志包括兩類文件:

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

當(dāng)遇到以下3種情況時(shí)，MySQL會(huì)重新生成一個(gè)新的日志文件，文件序號(hào)遞增：

• MySQL服務(wù)器停止或重啟時(shí)；
• 使用 flush logs 命令；
• 當(dāng) binlog 文件大小超過 max_binlog_size 變量的值時(shí)；

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

Binlog與Redo log區(qū)別

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

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

Binlog寫入過程

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

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

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

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

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

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

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

二階段提交

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

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

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

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

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

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

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

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

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

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

因?yàn)槊總€(gè)事務(wù)都有一個(gè)唯一的事務(wù) id，redo log 和 binlog 在記錄日志時(shí)都會(huì)關(guān)聯(lián)相應(yīng)的事務(wù) id，所以 redo log 和 binlog 就通過事務(wù) id 關(guān)聯(lián)了起來。

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

redo 與 binlog 的刷盤時(shí)機(jī)

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

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

能否只用 redo log 不要 binlog？

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

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

總之，由于現(xiàn)在包括 MySQL 高可用在內(nèi)的很多系統(tǒng)機(jī)制都依賴于 binlog，所以單靠 redo log 還做不到。

Binlog 組提交機(jī)制

若事務(wù)為非只讀事務(wù)，則每次事務(wù)提交時(shí)需要進(jìn)行一次 fsync 操作，以保證 redo log 都寫入了磁盤。為了提高磁盤 fsync 的效率，MySQL 提供了組提交（group commit）功能，即一次 fsync 能夠?qū)⒍鄠€(gè)事務(wù)的日志刷新到磁盤的日志文件中，而不用將每個(gè)事務(wù)的日志單獨(dú)刷新到磁盤文件中，從而大大提升了日志刷盤的效率。

我們知道，如果開啟了 binlog，則 MySQL 為了保證 binlog 和事務(wù)日志的一致性，使用了兩階段提交。

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

• 先把 binlog 從 binlog cache 中寫到磁盤上的 binlog 文件；
• 調(diào)用 fsync 持久化。

下圖詳細(xì)展示了在二階段提交過程中的日志寫入時(shí)機(jī)：

MySQL 為了讓組提交的效果更好，把 redo log 做 fsync 的操作拖到了步驟 3 中。這么一來，binlog 也可以進(jìn)行組提交了，因?yàn)樵?binlog 的 write 和 fsync 操作之間有了一小段間隔，這允許其他提交的事務(wù)也將 binlog write 到操作系統(tǒng)緩存中，后續(xù)通過 fsync 一并刷新到磁盤中。這種實(shí)現(xiàn)方式稱為二進(jìn)制日志組提交（Binary Log Group Commit，BLGC）。

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

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

• binlog_group_commit_sync_delay：表示延遲多少微秒后才調(diào)用 fsync，默認(rèn)為 0
• binlog_group_commit_sync_no_delay_count：表示累積多少次以后才調(diào)用 fsync，默認(rèn)為 0

這兩個(gè)條件是或的關(guān)系，即只要有一個(gè)滿足條件就會(huì)調(diào)用 fsync 操作。注意，除非有大量的事務(wù)不斷地進(jìn)行寫入和更新操作，否則不建議修改這個(gè)變量的值，這是因?yàn)樾薷暮罂赡軙?huì)導(dǎo)致事務(wù)的響應(yīng)時(shí)間變長(zhǎng)。

Binlog的日志格式

記錄在二進(jìn)制日志中的事件的格式取決于二進(jìn)制記錄格式。支持三種格式類型：

• STATEMENT：基于SQL語句的復(fù)制（statement-based replication, SBR）
• ROW：基于行的復(fù)制（row-based replication, RBR）
• MIXED：混合模式復(fù)制（mixed-based replication, MBR）

在 MySQL 5.7.7 之前，默認(rèn)的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默認(rèn)值是 ROW。日志格式通過 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

Statement

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

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

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

Row

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

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

缺點(diǎn):所有的執(zhí)行的語句當(dāng)記錄到日志中的時(shí)候，都將以每行記錄的修改來記錄，這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容。

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

Mixed

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

Binlog 相關(guān)參數(shù)

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

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

示例如下：

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

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

清理過期的Binlog日志

在開啟MySQL的主從后，會(huì)產(chǎn)生大量的binlog日志文件，可能產(chǎn)生大量的磁盤空間

手工刪除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';

自動(dòng)刪除binlog

通過binlog參數(shù)（expire_logs_days）來實(shí)現(xiàn)MySQL自動(dòng)刪除binlog

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

用途

主從同步

同步原理：

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

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

備庫通過IO線程連接主庫，接收主庫推送過來的二進(jìn)制日志，并記錄到本地的中繼日志relaylog；同時(shí)也會(huì)啟動(dòng)SQL線程將中繼日志的數(shù)據(jù)變更應(yīng)用到備庫本地?cái)?shù)據(jù)庫中.

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

復(fù)制線程

Master線程：

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

Slave線程：

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

主從復(fù)制優(yōu)化

多線程復(fù)制：

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

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

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

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

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

半同步復(fù)制：

MySQL 的默認(rèn)復(fù)制模式是異步模式，即 MySQL 的主服務(wù)器上的 I/O 線程，將數(shù)據(jù)寫到 binlog 中就直接返回給客戶端數(shù)據(jù)更新成功，不考慮數(shù)據(jù)是否傳輸?shù)綇姆?wù)器，以及是否寫入到 relay log 中。在這種模式下，復(fù)制數(shù)據(jù)其實(shí)是有風(fēng)險(xiǎn)的，一旦數(shù)據(jù)只寫到了主庫的 binlog 中還沒來得急同步到從庫時(shí)主庫宕機(jī)了，此時(shí)就會(huì)造成數(shù)據(jù)的丟失。但這種模式也是效率最高的，因?yàn)樽兏鼣?shù)據(jù)的功能都只是在主庫中完成就可以了，從庫復(fù)制數(shù)據(jù)不會(huì)影響到主庫的寫數(shù)據(jù)操作。

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

半同步復(fù)制的原理是在 master 的 dump 線程去通知從庫時(shí)，增加了一個(gè) ACK 機(jī)制，也就是會(huì)確認(rèn)從庫是否收到事務(wù)的標(biāo)志碼，master 的 dump 線程不但要發(fā)送 binlog 到從庫，還要負(fù)責(zé)接收 slave 的 ACK。當(dāng) slave 出現(xiàn)異常沒有返回 ACK 時(shí)，主庫將自動(dòng)降級(jí)為異步復(fù)制，直到異常修復(fù)后再自動(dòng)變?yōu)榘胪綇?fù)制。

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

事務(wù)還沒發(fā)送到 slave上：若事務(wù)還沒發(fā)送 slave 上，客戶端在收到失敗結(jié)果后，會(huì)重新提交事務(wù)，因?yàn)橹匦绿峤坏氖聞?wù)是在新的 master 上執(zhí)行的，所以會(huì)執(zhí)行成功，后面若是之前的 master 恢復(fù)后，會(huì)以 slave 的身份加入到集群中，此時(shí)，之前的事務(wù)就會(huì)被執(zhí)行兩次，第一次是之前此機(jī)器作為 master 時(shí)執(zhí)行的，第二次是做為 slave 后從主庫中同步過來的。

事務(wù)已經(jīng)同步到 slave 上：因?yàn)槭聞?wù)已經(jīng)同步到 slave 了，所以當(dāng)客戶端收到失敗結(jié)果后，再次提交事務(wù)，那么此事務(wù)就會(huì)在當(dāng)前 slave 機(jī)器上執(zhí)行兩次。

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

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

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

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

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

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

上面命令的作用就是將 0 -500 位點(diǎn)的數(shù)據(jù)恢復(fù)到自定義的 SQL 文件中。同理 706 - 800 的數(shù)據(jù)也是一樣操作。之后我們執(zhí)行這兩個(gè) SQL 文件就行了。

mysqlbinlog 命令的使用

服務(wù)器以二進(jìn)制格式將binlog日志寫入binlog文件，如何要以文本格式顯示其內(nèi)容，可以使用 mysqlbinlog 命令。

# 查看bin-log二進(jìn)制文件（shell方式）
mysqlbinlog -v --base64-output=decode-rows /data/3306/binlog/mysql-bin.000005

# 查看bin-log二進(jìn)制文件（帶查詢條件）
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

# 臨時(shí)不記錄binlog日志
set sql_log_bin=0;

#實(shí)時(shí)拉取遠(yuǎn)程主機(jī)binlog文件中的數(shù)據(jù)
mysqlbinlog  -R --host=10.0.0.52 --user=mha --password=mha --raw  --stop-never mysql-bin.000003 &

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

最新評(píng)論