MySQL binlog日志記錄格式寫入機(jī)制及相關(guān)參數(shù)講解

更新時(shí)間：2023年09月27日 11:02:00 作者：jacheut

這篇文章主要為大家介紹了MySQL binlog日志記錄格式寫入機(jī)制及相關(guān)參數(shù)使用講解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪

基本概念

二進(jìn)制日志（binnary log）以事件形式記錄了對MySQL數(shù)據(jù)庫執(zhí)行更改的所有操作。

binlog 是邏輯日志，記錄內(nèi)容是語句的原始邏輯，類似于“給 ID=2 這一行的 c 字段加 1”，屬于MySQL Server 層。

binlog是記錄所有數(shù)據(jù)庫表結(jié)構(gòu)變更（例如CREATE、ALTER TABLE…）以及表數(shù)據(jù)修改（INSERT、UPDATE、DELETE…）的二進(jìn)制日志。不會(huì)記錄SELECT和SHOW這類操作，因?yàn)檫@類操作對數(shù)據(jù)本身并沒有修改，但可以通過查詢通用日志來查看MySQL執(zhí)行過的所有語句。

需要注意的一點(diǎn)是，即便update操作沒有造成數(shù)據(jù)變化，也是會(huì)記入binlog。

不管用什么存儲(chǔ)引擎，只要發(fā)生了表數(shù)據(jù)更新，都會(huì)產(chǎn)生 binlog 日志。

binlog有兩個(gè)常用的使用場景：

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

binlog會(huì)記錄所有涉及更新數(shù)據(jù)的邏輯操作，并且是順序?qū)憽?/p>

記錄格式

binlog 日志有三種格式，可以通過binlog_format參數(shù)指定。

statement
row
mixed

指定statement，記錄的是邏輯SQL語句，比如執(zhí)行一條update T set update_time=now() where id=1，記錄的內(nèi)容如下。優(yōu)點(diǎn)是日志文件小，節(jié)約IO，提高性能。

同步數(shù)據(jù)時(shí)，會(huì)執(zhí)行記錄的SQL語句，但是有個(gè)問題，update_time=now()這里會(huì)獲取當(dāng)前系統(tǒng)時(shí)間，直接執(zhí)行會(huì)導(dǎo)致與原庫的數(shù)據(jù)不一致。

row 記錄表的行更改情況，準(zhǔn)確性強(qiáng)，可以為數(shù)據(jù)庫的恢復(fù)、復(fù)制帶來更好的可靠性；但是二進(jìn)制文件的大小相較于STATEMENT會(huì)有所增加，較大的網(wǎng)絡(luò)IO和磁盤IO。

mixed：STATEMENT和ROW模式的混合。默認(rèn)采用STATEMENT格式進(jìn)行二進(jìn)制日志文件的記錄，但是在一些情況下會(huì)使用ROW格式。

寫入機(jī)制

binlog的寫入時(shí)機(jī)也非常簡單，事務(wù)執(zhí)行過程中，先把日志寫到binlog cache，事務(wù)提交的時(shí)候，再把binlog cache寫到binlog文件中。

因?yàn)橐粋€(gè)事務(wù)的binlog不能被拆開，無論這個(gè)事務(wù)多大，也要確保一次性寫入，所以系統(tǒng)會(huì)給每個(gè)線程分配一個(gè)塊內(nèi)存作為binlog cache。

我們可以通過binlog_cache_size參數(shù)控制單個(gè)線程 binlog cache 大小，如果存儲(chǔ)內(nèi)容超過了這個(gè)參數(shù)，就要暫存到磁盤（Swap）。

binlog日志刷盤流程如下

上圖的 write，是指把日志寫入到文件系統(tǒng)的 page cache（binlog file），并沒有把數(shù)據(jù)持久化到磁盤，所以速度比較快
上圖的 fsync，才是將數(shù)據(jù)持久化到磁盤的操作。一般情況下，我們認(rèn)為 fsync 才占磁盤的 IOPS。

write和fsync的時(shí)機(jī)，可以由參數(shù)sync_binlog控制，默認(rèn)是1。

N=1：表示采用同步寫磁盤的方式來寫二進(jìn)制日志，這時(shí)寫操作不使用操作系統(tǒng)的緩沖來寫二進(jìn)制日志，每次事務(wù)提交都會(huì)寫入文件。
N=0：為0的時(shí)候，表示每次提交事務(wù)都只write，由系統(tǒng)自行判斷什么時(shí)候執(zhí)行fsync。雖然性能得到提升，但是機(jī)器宕機(jī)，page cache里面的 binlog 會(huì)丟失。
最后還有一種折中方式，可以設(shè)置為N(N>1)，表示每次提交事務(wù)都write，但累積N個(gè)事務(wù)后才fsync。在出現(xiàn)IO瓶頸的場景里，將sync_binlog設(shè)置成一個(gè)比較大的值，可以提升性能。同樣的，如果機(jī)器宕機(jī)，會(huì)丟失最近N個(gè)事務(wù)的binlog日志。

binlog_cache相關(guān)參數(shù)介紹

屬性	說明
binlog_cache_size	表示binlog_cache的容量，默認(rèn)大小為32K.
binlog_cache_use	記錄了使用緩沖寫二進(jìn)制日志的次數(shù)
binlog_cache_disk_use	記錄了使用臨時(shí)文件寫二進(jìn)制日志的次數(shù)

binlog_cache_size是基于會(huì)話(session)的。每個(gè)線程一個(gè)，參數(shù) binlog_cache_size 用于控制單個(gè)線程內(nèi) binlog cache 所占內(nèi)存的大小。如果超過了這個(gè)參數(shù)規(guī)定的大小，就要暫存到磁盤（臨時(shí)文件中）。