MySQL 日志相關(guān)知識總結(jié)

數(shù)據(jù)庫中用于存儲數(shù)據(jù)的文件稱為data file，日志文件稱為log file。此外，如果每次讀寫都是直接訪問磁盤，性能很差，所以數(shù)據(jù)庫是有緩存的，數(shù)據(jù)緩存是data buffer，日志緩存log buffer。

sql執(zhí)行順序

當我們執(zhí)行一條更新語句時，比如 update table set c=c+1 where id = 2，執(zhí)行順序如下：

• 執(zhí)行器通過存儲引擎獲取id=2的行記錄。如果id=2的行記錄所在的數(shù)據(jù)頁已經(jīng)在內(nèi)存中，則直接返回；否則，需要從磁盤讀取數(shù)據(jù)
• 執(zhí)行器拿到返回的行數(shù)據(jù)，把字段c的值+1，得到新的行數(shù)據(jù)，然后調(diào)用存儲引擎接口寫入行數(shù)據(jù)
• 引擎把這行數(shù)據(jù)更新到內(nèi)存，同時將這個更新操作記錄到redo log里面，此時redo log處于prepare狀態(tài)。然后告訴執(zhí)行器執(zhí)行完成，隨時可以提交事務(wù)
• 執(zhí)行器生成這個操作的bin log，并把bin log寫入磁盤
• 執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的redo log改成commit狀態(tài)，更新完成
  

補充：MySQL的基本存儲結(jié)構(gòu)是頁(記錄都存在頁里邊)，所以MySQL是先把這條記錄所在的頁找到，然后把該頁加載到內(nèi)存中，再修改對應(yīng)的記錄。

bin log

是什么

bin log稱為歸檔日志、二進制日志，屬于MySQL Server層面的，用于記錄數(shù)據(jù)庫表結(jié)構(gòu)和表數(shù)據(jù)的變更，可以簡單理解為存儲每條變更的sql語句，比如insert、delete、update（當然，不僅是sql，還有事務(wù)id，執(zhí)行時間等等）。

什么時候產(chǎn)生

事務(wù)提交的時候，一次性將事務(wù)中的sql語句按照一定格式記錄到bin log

有什么用

主要有兩個作用：主從復(fù)制和恢復(fù)數(shù)據(jù)

• 目前大部分數(shù)據(jù)庫架構(gòu)都是一主多從，從服務(wù)器通過訪問主服務(wù)器的bin log，保證數(shù)據(jù)一致性
• bin log記錄數(shù)據(jù)庫的變更，可以通過它恢復(fù)數(shù)據(jù)
  

什么時候落盤

區(qū)分innodb_flush_log_at_trx_commit和sync_binlog

​ 二進制日志取決于sync_binlog參數(shù)

• 0：事務(wù)提交后，由操作系統(tǒng)決定什么時候把緩存刷新到磁盤（性能最好，安全性最差）
• 1：每提交一次事務(wù)，調(diào)用一次fsync將緩存寫入到磁盤（安全性最好，性能最差）
• n：當提交n次事務(wù)后，調(diào)用一次fsync將緩存寫入到磁盤
  

文件記錄模式

bin log有三種文件記錄模式，分別是row、statement、mixed

• row（row-based replication，PBR）：記錄每一行數(shù)據(jù)的修改情況

優(yōu)點：能夠清楚記錄每行數(shù)據(jù)修改細節(jié)，能夠完全保證主從數(shù)據(jù)一致性
缺點：批量操作時會產(chǎn)生大量的日志，比如alter table

• statement：記錄每條修改數(shù)據(jù)的sql，可認為sql語句復(fù)制
  

優(yōu)點：日志數(shù)據(jù)量小，減少磁盤IO，提高存儲和恢復(fù)速度
缺點：在某些情況下會出現(xiàn)主從不一致，比如sql語句中包含**now()**等函數(shù)

• mixed：上面兩種模式的混合，MySQL會根據(jù)sql語句選擇寫入模式，一般使用statement模式保存bin log，對于statement模式無法復(fù)制的操作，使用row模式保存bin log。
  

redo log

是什么

redo log稱為重做日志，屬于InnoDB存儲引擎層的日志，記錄物理頁的修改信息，而不是某一行或幾行修改成什么樣

什么時候產(chǎn)生

事務(wù)開始，就會寫入redo log。redo log寫入到磁盤并不是隨著事務(wù)提交才寫入，而是在事務(wù)執(zhí)行過程中，就已經(jīng)寫入到磁盤

有什么用

可用于恢復(fù)數(shù)據(jù)。redo log是在事務(wù)開始后就寫入到磁盤，且是順序IO，寫入速度較快。如果服務(wù)器突然掉電，InnoDB引擎會使用redo log把數(shù)據(jù)庫恢復(fù)到掉電前的時刻，保證數(shù)據(jù)的完整性

什么時候落盤

InnoDB先把日志寫到緩沖區(qū)（log buffer），然后再把日志從log buffer刷到os buffer，最后調(diào)用文件系統(tǒng)的fsync函數(shù)將日志刷新到磁盤。重做日志寫入時機由參數(shù)innodb_flush_log_at_trx_commit決定

• 0：每秒一次，把log buffer寫入os buffer，并調(diào)用fsync刷到磁盤
• 1：每次提交事務(wù)時，把log buffer寫入os buffer，并調(diào)用fsync刷到磁盤
• 2：每次提交事務(wù)時，只是寫入到os buffer，然后每秒一次調(diào)用fsync將日志刷新到磁盤
  

一般取值為2，因為即使MySQL宕機，數(shù)據(jù)也沒有丟失。只有整個服務(wù)器掛了，才損失1秒的數(shù)據(jù)

bin log VS redo log

看了以上的介紹，感覺bin log和redo log很像，都是記錄數(shù)據(jù)變更，可用于恢復(fù)。其實，它們還是有明顯區(qū)別的。

• bin log屬于MySQL Server層面的，redo log屬于InnoDB存儲引擎層面
• bin log是邏輯日志，記錄的是sql語句的原始邏輯；redo log是物理日志，記錄的是物理頁面更新的內(nèi)容
• bin log是追加寫，文件達到限制后會更換下個文件，不會覆蓋；redo log是循環(huán)寫，文件大小固定，寫滿就重頭開始寫，覆蓋原來的內(nèi)容
• bin log作用是主從復(fù)制和恢復(fù)數(shù)據(jù)，當數(shù)據(jù)庫被刪除、或者從庫同步主庫數(shù)據(jù)時，由于bin log記錄變更數(shù)據(jù)的sql，所以可通過bin log恢復(fù)。而redo log作用是持久化，當發(fā)生服務(wù)器宕機或者掉電等情況，數(shù)據(jù)丟失，可以通過redo log恢復(fù)。
• bin log是提交事務(wù)時才寫入磁盤，而redo log在開啟事務(wù)時，就開始寫入到磁盤

如果整個數(shù)據(jù)庫被刪除，可以通過redo log恢復(fù)嗎？

不行！因為redo log側(cè)重點是保存某次事務(wù)的數(shù)據(jù)變更，當內(nèi)存中的數(shù)據(jù)刷到磁盤后，redo log的數(shù)據(jù)其實已經(jīng)沒有參考價值。此外，redo log會覆蓋歷史數(shù)據(jù)，也不可能通過它來恢復(fù)所有數(shù)據(jù)。

undo log

詳細分析MySQL事務(wù)日志

是什么

undo log稱為回滾日志，屬于InnoDB存儲引擎層，是邏輯日志，記錄每行數(shù)據(jù)。當我們變更數(shù)據(jù)時，就會產(chǎn)生undo log，可以認為insert一條數(shù)據(jù)，undo log會記錄一條對應(yīng)的delete日志，反之亦然。

什么時候產(chǎn)生

在事務(wù)開始前，將當前版本生成undo log

有什么用

主要作用：提供回滾和多版本并發(fā)控制（MVCC）

• 回滾：當需要rollback時，從undo log的邏輯記錄讀取相應(yīng)的內(nèi)容進行回滾
• MVCC：undo log記錄中存儲的是舊版本數(shù)據(jù)，當一個事務(wù)需要讀取數(shù)據(jù)時，會順著undo鏈找到滿足其可見性的記錄

以上就是MySQL 日志相關(guān)知識總結(jié)的詳細內(nèi)容，更多關(guān)于MySQL 日志的資料請關(guān)注腳本之家其它相關(guān)文章！