bin log是什么？

bin log全稱binary log，二進(jìn)制日志文件，它記錄了數(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進(jìn)行數(shù)據(jù)恢復(fù)，因為該日志記錄所有數(shù)據(jù)庫所有的變更，保證數(shù)據(jù)的安全性。
• 數(shù)據(jù)復(fù)制，利用一定的機(jī)制將主節(jié)點(diǎn)MySQL的日志數(shù)據(jù)傳遞給從節(jié)點(diǎn)，實現(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生成的日志，所有的存儲引擎都有

從生成時機(jī)角度來說：

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

bin log怎么寫的？

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

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刷到磁盤中，這里的策略后面詳細(xì)講解。

3種刷盤策略：

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

• sync_binlog = 0

為0的時候，表示每次提交事務(wù)都只 write，由系統(tǒng)自行判斷什么時候執(zhí)行fsync。雖然性能得到提升，但是機(jī)器宕機(jī)，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認(rèn)為是完整的，就會提交事務(wù)恢復(fù)數(shù)據(jù)。

bin log寫到哪了？

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

• 查看bin log位置

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

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

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

• 修改 bin log位置

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

#啟用二進(jìn)制日志
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起始點(diǎn)開始查起（不指定就是從整個文件首個pos點(diǎn)開始算）
• LIMIT [offset] ：偏移量(不指定就是0)
• row_count :查詢總條數(shù)（不指定就是所有行）

bin log 格式

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

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

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

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

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

• Mixed格式: 混合模式，即Statment、Row的結(jié)合版

• 對于可以復(fù)制的SQL采用Statment模式記錄，對于無法復(fù)制的SQL采用Row記錄。

總結(jié)

本文講解了MySQL中的一個非常重要的日志bin log，它主要用來做數(shù)據(jù)恢復(fù)和同步的，所以作為程序員的我們，還是很有必要對它有一個深入的認(rèn)識。

以上就是MySQL非常重要的日志bin log詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL日志bin log的資料請關(guān)注腳本之家其它相關(guān)文章！