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í)行過程中就會writebin 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 log
fsync刷到磁盤中,這里的策略后面詳細講解。
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)絡帶寬開銷會很高。
- Mixed格式: 混合模式,即Statment、Row的結(jié)合版
- 對于可以復制的SQL采用Statment模式記錄,對于無法復制的SQL采用Row記錄。
總結(jié)
本文講解了MySQL中的一個非常重要的日志bin log,它主要用來做數(shù)據(jù)恢復和同步的,所以作為程序員的我們,還是很有必要對它有一個深入的認識。
以上就是MySQL非常重要的日志bin log詳解的詳細內(nèi)容,更多關(guān)于MySQL日志bin log的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
MySQL?時區(qū)與?serverTimezone詳解
存儲?TIMESTAMP?類型數(shù)據(jù)時,MySQL 會根據(jù)當前會話的時區(qū)將時間轉(zhuǎn)換為 UTC 時間,MySQL 實際存儲的是 UTC 時間,這篇文章主要介紹了MySQL?時區(qū)與?serverTimezone,需要的朋友可以參考下2024-12-12mysql?中的備份恢復,分區(qū)分表,主從復制,讀寫分離
這篇文章主要介紹了mysql?中的備份恢復,分區(qū)分表,主從復制,讀寫分離,文章圍繞主題展開詳細的內(nèi)容戒殺,具有一定的參考價值,需要的小伙伴可以參考一下2022-09-09MySQL做讀寫分離提高性能緩解數(shù)據(jù)庫壓力
這篇文章主要為大家介紹了MySQL做讀寫分離提高性能緩解數(shù)據(jù)庫壓力的技巧詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2023-05-05Mysql Error Code : 1436 Thread stack overrun
I meet with the error while calling stored procedures from the MySql in my Mac system server. It similar as the description below2011-07-07