快捷導(dǎo)航

MySQL非常重要的日志bin log詳解

更新時間：2023年07月14日 11:18:49 作者：JAVA旭陽

bin log想必大家多多少少都有聽過，它是MySQL中一個非常重要的日志，因為它涉及到數(shù)據(jù)庫層面的主從復(fù)制、高可用等設(shè)計，所以本文就給大家詳細的講解MySQL非常重要的日志—bin log,需要的朋友可以參考下

bin log是什么？

bin log全稱binary log，二進制日志文件，它記錄了數(shù)據(jù)庫所有執(zhí)行的 DDL 和 DML 等數(shù)據(jù)庫更新的語句，但是不包含select或者show等沒有修改任何數(shù)據(jù)的語句。它是MySQL級別的日志，也就是說所有的存儲引擎都會產(chǎn)生bin log，而redo log或者undo log事務(wù)日志只有innoDB存儲引擎才有。

那bin log有什么用呢？

數(shù)據(jù)恢復(fù)，如果MySQL數(shù)據(jù)庫意外掛了，可以利用bin log進行數(shù)據(jù)恢復(fù)，因為該日志記錄所有數(shù)據(jù)庫所有的變更，保證數(shù)據(jù)的安全性。
數(shù)據(jù)復(fù)制，利用一定的機制將主節(jié)點MySQL的日志數(shù)據(jù)傳遞給從節(jié)點，實現(xiàn)數(shù)據(jù)的一致性，實現(xiàn)架構(gòu)的高可用和高性能。

所以bin log對于數(shù)據(jù)備份、主從、主主等都都起到了關(guān)鍵作用。

bin log和redo log區(qū)別？

看了上面的bin log介紹，是不是感覺和事務(wù)日志redo log特別像呢？也是在事務(wù)執(zhí)行的時候記錄日志，但是他們還是有區(qū)別的。

你知道redo log嗎，如果不了解的話請參考這篇文章：詳解MySQL事務(wù)日志redo log_Mysql_腳本之家 (jb51.net)

我們現(xiàn)在從多個角度對比下他們倆究竟有什么不一樣?

從使用場景角度來說：

redo log主要實現(xiàn)故障情況下的數(shù)據(jù)恢復(fù)，保證事務(wù)的持久性
bin log主要用于數(shù)據(jù)災(zāi)備、同步

從數(shù)據(jù)內(nèi)容角度來說：

redo log是"物理日志", 記錄的是具體數(shù)據(jù)頁上做了什么修改
bin log是"邏輯日志", 記錄內(nèi)容是語句的原始邏輯，類似于“給 ID=2 這一行的 name 改為alvin”

從生成范圍角度來說：

redo log是InnoDB存儲引擎生成的事務(wù)日志，其他存儲引擎沒有
bin log是MySQL Server生成的日志，所有的存儲引擎都有

從生成時機角度來說：

redo log是在事務(wù)執(zhí)行過程中就會write
bin log是在事務(wù)提交的時候write

bin log怎么寫的？

那bin log是什么時候?qū)懙?，寫入的機制又是怎么樣的呢？

bin log寫入的整體流程如下圖所示：

為了保證寫的效率，會將事務(wù)的bin log先寫到binlog cache中，注意，這個cache位于事務(wù)線程的內(nèi)存中，主要是一個事務(wù)的bin log不能被拆開，是一個整體
在提交事務(wù)的時候，將binlog cache中的數(shù)據(jù)統(tǒng)一寫道文件系統(tǒng)緩存page cache中，這個過程速度也很快
然后根據(jù)不同的策略，將文件系統(tǒng)緩存中的bin logfsync刷到磁盤中，這里的策略后面詳細講解。

3種刷盤策略：

bin log和 redo log類似，都有3種刷盤策略， bin log的write和fsync時機是由參數(shù) sync_binlog 控制，默認是 0 。

sync_binlog = 0

為0的時候，表示每次提交事務(wù)都只 write，由系統(tǒng)自行判斷什么時候執(zhí)行fsync。雖然性能得到提升，但是機器宕機，page cache里面的 binglog 會丟失。

sync_binlog = 1

表示每次提交事務(wù)都會執(zhí)行fsync,更加安全

sync_binlog = N

可以設(shè)置為N(N>1)，表示每次提交事務(wù)都write，但累積N個事務(wù)后才fsync

我們已經(jīng)知道，事務(wù)執(zhí)行時會同時記錄redo log和bin log兩種日志，那會有日志出錯不一致問題嗎？

redo log在事務(wù)執(zhí)行過程中可以不斷寫入
bin log只有在提交事務(wù)時才寫入

假如事務(wù)執(zhí)行sqlupdate T set c = 1 where id = 2,在寫完redo log日志后，bin log日志寫期間發(fā)生了異常，會出現(xiàn)什么情況呢？

由于bin log沒寫完就異常，這時候bin log里面沒有對應(yīng)的修改記錄。因此，之后用bin log日志恢復(fù)數(shù)據(jù)時，就會少這一次更新，恢復(fù)出來的這一行c值為0，而原庫因為redo log日志恢復(fù)，這一行c的值是1，最終數(shù)據(jù)不一致。

那有什么解決方案嗎？二階段提交方案。

為了解決兩份日志之間的一致性問題，InnoDB存儲引擎使用兩階段提交方案。將redo log的寫入拆成了兩個步驟prepare和commit。

假如現(xiàn)在寫入bin log時MySQL發(fā)生異常，這時候的redo log還處于prepare階段，重啟MySQL后，根據(jù)redo log記錄中的事務(wù)ID，發(fā)現(xiàn)沒有對應(yīng)的bin log日志，回滾前面已寫入的數(shù)據(jù)。
如果redo log 在commit階段發(fā)生移除，但是能通過事務(wù)id找到對應(yīng)的bin log日志，所以MySQL認為是完整的，就會提交事務(wù)恢復(fù)數(shù)據(jù)。

bin log寫到哪了？

前面講解了bin log寫入的過程，那么它寫到了哪里去了呢？

查看bin log位置

可以通過命令show variables like '%log_bin%';查看bin log最終輸出的位置。

log_bin_basename: 是bin log日志的基本文件名，后面會追加標識來表示每一個文件
log_bin_index: 是binlog文件的索引文件，這個文件管理了所有的binlog文件的目錄

通過 SHOW BINARY LOGS;查看當(dāng)前的二進制日志文件列表及大小，如下圖：

修改 bin log位置

修改MySQL的my.cfg或my.ini配置

#啟用二進制日志
log-bin=cxw-bin
binlog_expire_logs_seconds=600
max_binlog_size=100M

log-bin: bin log日志保存的位置
binlog_expire_logs_seconds: bin log日志保存的時間，單位是秒
max_binlog_size：單個bin log日志的容量

bin log內(nèi)容長啥樣？

我們已經(jīng)知道了bin log的位置了，那它里面的內(nèi)容長什么樣呢？

我們可以用show binlog events命令工具查看bin log日志中的內(nèi)容。

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

IN 'log_name' ：指定要查詢的binlog文件名（不指定就是第一個binlog文件）
FROM pos ：指定從哪個pos起始點開始查起（不指定就是從整個文件首個pos點開始算）
LIMIT [offset] ：偏移量(不指定就是0)
row_count :查詢總條數(shù)（不指定就是所有行）

bin log 格式

實際上bin log輸出的格式類型有3種，默認是ROW類型，就是上面例子中的格式。

Statement格式：每一條會修改數(shù)據(jù)的sql都會記錄在bin log中

優(yōu)點：不需要記錄每一行的變化，減少了bin log日志量，節(jié)約了IO，提高性能。
缺點：比如sql中存在函數(shù)如now()等，依賴環(huán)境的函數(shù)，會導(dǎo)致主從同步、恢復(fù)數(shù)據(jù)不一致

ROW格式：為了解決Statement缺點，記錄具體哪一個分區(qū)中的、哪一個頁中的、哪一行數(shù)據(jù)被修改了

優(yōu)點：清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)，不會出現(xiàn)某些特定情況下的存儲過程，或function無法被正確復(fù)制的問題。
缺點：比如對ID<600的所有數(shù)據(jù)進行了修改操作,那么意味著很多數(shù)據(jù)發(fā)生變化，最終導(dǎo)致同步的log很多，那么磁盤IO、網(wǎng)絡(luò)帶寬開銷會很高。