腳本之家服務器常用軟件

快捷導航

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

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

bin log想必大家多多少少都有聽過，它是MySQL中一個非常重要的日志，因為它涉及到數(shù)據(jù)庫層面的主從復制、高可用等設計，所以本文就給大家詳細的講解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事務日志只有innoDB存儲引擎才有。

那bin log有什么用呢？

數(shù)據(jù)恢復，如果MySQL數(shù)據(jù)庫意外掛了，可以利用bin log進行數(shù)據(jù)恢復，因為該日志記錄所有數(shù)據(jù)庫所有的變更，保證數(shù)據(jù)的安全性。
數(shù)據(jù)復制，利用一定的機制將主節(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介紹，是不是感覺和事務日志redo log特別像呢？也是在事務執(zhí)行的時候記錄日志，但是他們還是有區(qū)別的。

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

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

從使用場景角度來說：

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

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

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

從生成范圍角度來說：

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

從生成時機角度來說：

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

bin log怎么寫的？

那bin log是什么時候?qū)懙模瑢懭氲臋C制又是怎么樣的呢？

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

為了保證寫的效率，會將事務的bin log先寫到binlog cache中，注意，這個cache位于事務線程的內(nèi)存中，主要是一個事務的bin log不能被拆開，是一個整體
在提交事務的時候，將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的時候，表示每次提交事務都只 write，由系統(tǒng)自行判斷什么時候執(zhí)行fsync。雖然性能得到提升，但是機器宕機，page cache里面的 binglog 會丟失。

sync_binlog = 1

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

sync_binlog = N

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

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

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

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

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

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

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

假如現(xiàn)在寫入bin log時MySQL發(fā)生異常，這時候的redo log還處于prepare階段，重啟MySQL后，根據(jù)redo log記錄中的事務ID，發(fā)現(xiàn)沒有對應的bin log日志，回滾前面已寫入的數(shù)據(jù)。
如果redo log 在commit階段發(fā)生移除，但是能通過事務id找到對應的bin log日志，所以MySQL認為是完整的，就會提交事務恢復數(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;查看當前的二進制日志文件列表及大小，如下圖：

修改 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ù)，會導致主從同步、恢復數(shù)據(jù)不一致

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

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