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

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

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

基本概念

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

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

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

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

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

binlog有兩個常用的使用場景：

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

binlog會記錄所有涉及更新數(shù)據(jù)的邏輯操作，并且是順序寫。

記錄格式

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

statement
row
mixed

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

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

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

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

寫入機制

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

因為一個事務的binlog不能被拆開，無論這個事務多大，也要確保一次性寫入，所以系統(tǒng)會給每個線程分配一個塊內存作為binlog cache。

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

binlog日志刷盤流程如下

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

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

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

binlog_cache相關參數(shù)介紹

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

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