MySQL 日志相關知識總結

數據庫中用於存儲數據的文件稱為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其它相關文章!