快捷導(dǎo)航

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

更新時(shí)間：2023年07月14日 11:18:49 作者：JAVA旭陽(yáng)

bin log想必大家多多少少都有聽(tīng)過(guò)，它是MySQL中一個(gè)非常重要的日志，因?yàn)樗婕暗綌?shù)據(jù)庫(kù)層面的主從復(fù)制、高可用等設(shè)計(jì)，所以本文就給大家詳細(xì)的講解MySQL非常重要的日志—bin log,需要的朋友可以參考下

bin log是什么？

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

那bin log有什么用呢？

數(shù)據(jù)恢復(fù)，如果MySQL數(shù)據(jù)庫(kù)意外掛了，可以利用bin log進(jìn)行數(shù)據(jù)恢復(fù)，因?yàn)樵撊罩居涗浰袛?shù)據(jù)庫(kù)所有的變更，保證數(shù)據(jù)的安全性。
數(shù)據(jù)復(fù)制，利用一定的機(jī)制將主節(jié)點(diǎn)MySQL的日志數(shù)據(jù)傳遞給從節(jié)點(diǎn)，實(shí)現(xiàn)數(shù)據(jù)的一致性，實(shí)現(xiàn)架構(gòu)的高可用和高性能。

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

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

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

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

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

從使用場(chǎng)景角度來(lái)說(shuō)：

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

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

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

從生成范圍角度來(lái)說(shuō)：

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

從生成時(shí)機(jī)角度來(lái)說(shuō)：

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

bin log怎么寫(xiě)的？

那bin log是什么時(shí)候?qū)懙模瑢?xiě)入的機(jī)制又是怎么樣的呢？

bin log寫(xiě)入的整體流程如下圖所示：

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

3種刷盤(pán)策略：

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

sync_binlog = 0

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

sync_binlog = 1

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

sync_binlog = N

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

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

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

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

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

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

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

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

bin log寫(xiě)到哪了？

前面講解了bin log寫(xiě)入的過(guò)程，那么它寫(xiě)到了哪里去了呢？

查看bin log位置

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

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

通過(guò) 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日志保存的時(shí)間，單位是秒
max_binlog_size：單個(gè)bin log日志的容量

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

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

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

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

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

bin log 格式

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

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

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

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

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