MySQL 日志相關知識總結

Posted on by

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

sql執行順序

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

  • 執行器通過存儲引擎獲取id=2的行記錄。如果id=2的行記錄所在的數據頁已經在內存中，則直接返回；否則，需要從磁盤讀取數據
  • 執行器拿到返回的行數據，把字段c的值+1，得到新的行數據，然後調用存儲引擎接口寫入行數據
  • 引擎把這行數據更新到內存，同時將這個更新操作記錄到redo log裡面，此時redo log處於prepare狀態。然後告訴執行器執行完成，隨時可以提交事務
  • 執行器生成這個操作的bin log，並把bin log寫入磁盤
  • 執行器調用引擎的提交事務接口，引擎把剛剛寫入的redo log改成commit狀態，更新完成

補充：MySQL的基本存儲結構是頁(記錄都存在頁裡邊)，所以MySQL是先把這條記錄所在的頁找到，然後把該頁加載到內存中，再修改對應的記錄。

bin log

是什麼

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

什麼時候產生

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

有什麼用

主要有兩個作用：主從復制和恢復數據

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

什麼時候落盤

區分innodb_flush_log_at_trx_commit和sync_binlog

​ 二進制日志取決於sync_binlog參數

  • 0：事務提交後，由操作系統決定什麼時候把緩存刷新到磁盤（性能最好，安全性最差）
  • 1：每提交一次事務，調用一次fsync將緩存寫入到磁盤（安全性最好，性能最差）
  • n：當提交n次事務後，調用一次fsync將緩存寫入到磁盤

文件記錄模式

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

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

優點：能夠清楚記錄每行數據修改細節，能夠完全保證主從數據一致性
缺點：批量操作時會產生大量的日志，比如alter table

  • statement：記錄每條修改數據的sql，可認為sql語句復制

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

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

redo log

是什麼

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

什麼時候產生

事務開始，就會寫入redo log。redo log寫入到磁盤並不是隨著事務提交才寫入，而是在事務執行過程中，就已經寫入到磁盤

有什麼用

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

什麼時候落盤

InnoDB先把日志寫到緩沖區（log buffer），然後再把日志從log buffer刷到os buffer，最後調用文件系統的fsync函數將日志刷新到磁盤。重做日志寫入時機由參數innodb_flush_log_at_trx_commit決定

  • 0：每秒一次，把log buffer寫入os buffer，並調用fsync刷到磁盤
  • 1：每次提交事務時，把log buffer寫入os buffer，並調用fsync刷到磁盤
  • 2：每次提交事務時，隻是寫入到os buffer，然後每秒一次調用fsync將日志刷新到磁盤

一般取值為2，因為即使MySQL宕機，數據也沒有丟失。隻有整個服務器掛瞭，才損失1秒的數據

bin log VS redo log

看瞭以上的介紹，感覺bin log和redo log很像，都是記錄數據變更，可用於恢復。其實，它們還是有明顯區別的。

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

如果整個數據庫被刪除，可以通過redo log恢復嗎？

不行！因為redo log側重點是保存某次事務的數據變更，當內存中的數據刷到磁盤後，redo log的數據其實已經沒有參考價值。此外，redo log會覆蓋歷史數據，也不可能通過它來恢復所有數據。

undo log

詳細分析MySQL事務日志

是什麼

undo log稱為回滾日志，屬於InnoDB存儲引擎層，是邏輯日志，記錄每行數據。當我們變更數據時，就會產生undo log，可以認為insert一條數據，undo log會記錄一條對應的delete日志，反之亦然。

什麼時候產生

在事務開始前，將當前版本生成undo log

有什麼用

主要作用：提供回滾和多版本並發控制（MVCC）

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

以上就是MySQL 日志相關知識總結的詳細內容，更多關於MySQL 日志的資料請關註WalkonNet其它相關文章！

推薦閱讀：