前言：

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

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

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

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

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

[mysqld]
設(shè)置此參數(shù)表示啟用binlog功能，并制定二進(jìn)制日志的存儲(chǔ)目錄，開(kāi)啟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日志只記錄指定庫(kù)的更新
#binlog-do-db=db_name
#binlog日志不記錄指定庫(kù)的更新
#binlog-ignore-db=db_name
#寫(xiě)緩沖多少次，刷一次磁盤(pán)，默認(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 過(guò)期刪除不是服務(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)開(kāi)始查起(不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開(kāi)始算)
## 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';
#分頁(yè)顯示、過(guò)濾日志
pager less
pager grep "drop"
# 設(shè)置binlog文件保存事件，過(guò)期刪除，單位天
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';

寫(xiě)B(tài)inlog的時(shí)機(jī)

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

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

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

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

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

Binlog文件以及擴(kuò)展

binlog日志包括兩類文件:

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

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

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

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

Binlog與Redo log區(qū)別

最開(kāi)始 MySQL 里并沒(méi)有 InnoDB 引擎，MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒(méi)有 crash-safe 的能力，binlog 只能用于歸檔。而 InnoDB 是另一個(gè)公司以插件形式引入 MySQL 的，既然只依靠 binlog 是沒(méi)有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來(lái)實(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è)語(yǔ)句的原始邏輯，比如 "給 ID=2 這一行的 c 字段加 1"。而 redo log 是一種物理日志，記錄的是 "在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改"。
• redo log 具有冪等性，多次操作的前后狀態(tài)是一致的，而 binlog 不具有冪等性，記錄的是所有影響數(shù)據(jù)庫(kù)的操作。例如插入一條數(shù)據(jù)后再將其刪除，則 redo log 前后的狀態(tài)未發(fā)生變化，而 binlog 就會(huì)記錄相應(yīng)的插入操作和刪除操作。
• binlog 只會(huì)在事務(wù)提交時(shí)一次性寫(xiě)入，其日志的記錄方式與事務(wù)的提交順序有關(guān)，并且一個(gè)事務(wù)的 binlog 中間不會(huì)插入其他事務(wù)的 binlog。而 redo log 記錄的是物理頁(yè)的修改，最后一個(gè)提交的事務(wù)記錄會(huì)覆蓋之前所有未提交的事務(wù)記錄，并且一個(gè)事務(wù)的 redo log 中間會(huì)插入其他事務(wù)的 redo log。
• binlog 是追加寫(xiě)入，寫(xiě)完一個(gè)日志文件再寫(xiě)下一個(gè)日志文件，不會(huì)覆蓋使用，而 redo log 是循環(huán)寫(xiě)入，日志空間的大小是固定的，會(huì)覆蓋使用。
• binlog 一般用于主從復(fù)制和數(shù)據(jù)恢復(fù)，并且不具備崩潰自動(dòng)恢復(fù)的能力，而 redo log 是在服務(wù)器發(fā)生故障后重啟 MySQL，用于恢復(fù)事務(wù)已提交但未寫(xiě)入數(shù)據(jù)表的數(shù)據(jù)。

Binlog寫(xiě)入過(guò)程

其實(shí) binlog 的寫(xiě)入邏輯比較簡(jiǎn)單：事務(wù)執(zhí)行過(guò)程中，先把日志寫(xiě)到 binlog cache（用于緩存 binlog 的內(nèi)存緩沖區(qū)）中，等到事務(wù)提交時(shí)，再把 binlog cache 寫(xiě)到 binlog 文件中。注意，這里是每個(gè)事務(wù)線程都有一個(gè)自己的緩沖區(qū)。一個(gè)事務(wù)的 binlog 不能被拆分，因此不論這個(gè)事務(wù)多大，也會(huì)確保一個(gè)事務(wù)中產(chǎn)生的 binlog 要被一次性寫(xiě)入到磁盤(pán)中，所以一個(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)存的大小。如果超過(guò)了這個(gè)參數(shù)規(guī)定的大小，就要暫存到磁盤(pán)。事務(wù)提交時(shí)，執(zhí)行器會(huì)把 binlog cache 里的完整事務(wù)寫(xiě)入到 binlog 中，并清空 binlog cache。

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

• 圖中的 write 是指把日志寫(xiě)入到文件系統(tǒng)的 page cache，但并沒(méi)有把數(shù)據(jù)持久化到磁盤(pán)。
• 圖中的 fsync 才是將數(shù)據(jù)持久化到磁盤(pán)的操作。一般我們認(rèn)為 fsync 才占磁盤(pán)的 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，比較常見(jiàn)的是將其設(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è)問(wèn)題：對(duì)于事務(wù)日志和二進(jìn)制日志，MySQL 會(huì)先記錄哪種呢？我們通過(guò)下面這個(gè)語(yǔ)句，來(lái)看一下 MySQL 在執(zhí)行這個(gè)簡(jiǎn)單的 UPDATE 語(yǔ)句時(shí)的內(nèi)部流程：

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

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

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

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

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

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

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

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

MySQL 在崩潰恢復(fù)時(shí)會(huì)判斷 redo log 中記錄的事務(wù)日志是否完整，即是否有 commit 標(biāo)識(shí)。如果有 commit 標(biāo)識(shí)則直接提交事務(wù)，如果沒(méi)有則需要判斷對(duì)應(yīng)的事務(wù)在 binlog 上是否存在并完整。如果在 binlog 上是完整的則也要提交事務(wù)（因?yàn)?binlog 已經(jīng)寫(xiě)入了，之后會(huì)被從庫(kù)用，所以主庫(kù)也要提交這個(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 就通過(guò)事務(wù) id 關(guān)聯(lián)了起來(lái)。

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

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

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

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

能否只用 redo log 不要 binlog？

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

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

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

Binlog 組提交機(jī)制

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

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

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

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

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

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

不過(guò)通常情況下第 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)行寫(xiě)入和更新操作，否則不建議修改這個(gè)變量的值，這是因?yàn)樾薷暮罂赡軙?huì)導(dǎo)致事務(wù)的響應(yīng)時(shí)間變長(zhǎng)。

Binlog的日志格式

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

• STATEMENT：基于SQL語(yǔ)句的復(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。日志格式通過(guò) 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í)行語(yǔ)句，為了這些語(yǔ)句能在slave上正確運(yùn)行，因此還必須記錄每條語(yǔ)句在執(zhí)行的時(shí)候的一些相關(guān)信息，以保證所有語(yǔ)句能在slave得到和在master端執(zhí)行的時(shí)候相同的結(jié)果。另外mysql的復(fù)制，像一些特定函數(shù)的功能，slave與master要保持一致會(huì)有很多相關(guān)問(wèn)題。

Row

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

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

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

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

Mixed

從5.1.8版本開(kāi)始，MySQL提供了Mixed格式，實(shí)際上就是Statement與Row的結(jié)合。 在Mixed模式下，一般的語(yǔ)句修改使用statment格式保存binlog，如一些函數(shù)，statement無(wú)法完成主從復(fù)制的操作，則采用row格式保存binlog，MySQL會(huì)根據(jù)執(zhí)行的每一條具體的sql語(yǔ)句來(lái)區(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 文件的最大值，如果超過(guò)該值，則產(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)制日志刷新到磁盤(pán)。這個(gè)緩沖的大小就是由 binlog_cache_size 決定，默認(rèn) 32 KB。并且該參數(shù)是基于會(huì)話的，也就是說(shuō)，當(dāng)一個(gè)線程開(kāi)始一個(gè)事務(wù)時(shí)，MySQL 會(huì)自動(dòng)分配一個(gè)大小為 binlog_cache_size 的緩存，因此該值的設(shè)置需要相當(dāng)小心，不能設(shè)置過(guò)大。當(dāng)一個(gè)事務(wù)的記錄大于設(shè)定的 binlog_cache_size 時(shí)，MySQL 會(huì)把緩沖中的日志寫(xiě)入一個(gè)臨時(shí)文件中，因此該值又不能設(shè)得太小。
• binlog_cache_use：表示使用 binlog_cache 的事務(wù)數(shù)量。
• binlog_cache_disk_use：表示使用 binlog_cache 但超過(guò) binlog_cache_ size 的值，并且使用臨時(shí)文件來(lái)保存 SQL 語(yǔ)句中的事務(wù)數(shù)量。

清理過(guò)期的Binlog日志

在開(kāi)啟MySQL的主從后，會(huì)產(chǎn)生大量的binlog日志文件，可能產(chǎn)生大量的磁盤(pá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

通過(guò)binlog參數(shù)（expire_logs_days）來(lái)實(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ì)寫(xiě)入二進(jìn)制日志。

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

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

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

復(fù)制線程

Master線程：

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

Slave線程：

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

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

多線程復(fù)制：

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

當(dāng) IO 線程將主庫(kù)的 binlog 寫(xiě)入 relay log 后，會(huì)有一個(gè)多線程協(xié)調(diào)器（multithreaded slave coordinator）對(duì)多個(gè) SQL 線程進(jìn)行調(diào)度，讓它們按照一定的規(guī)則去執(zhí)行 relay log 中的事件。即圖中的 coordinator 就是原來(lái)的 sql_thread，不過(guò)現(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ù)不能被拆開(kāi)，必須放到同一個(gè) worker 中。

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

半同步復(fù)制：

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

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

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

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

事務(wù)還沒(méi)發(fā)送到 slave上：若事務(wù)還沒(méi)發(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 后從主庫(kù)中同步過(guò)來(lái)的。

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

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

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

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

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

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