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

更新時間：2021年02月02日 16:04:00 作者：Joe學(xué)習(xí)生活

這篇文章主要介紹了MySQL 日志相關(guān)知識總結(jié)，幫助大家更好的理解和實用MySQL，感興趣的朋友可以了解下

sql執(zhí)行順序

當(dāng)我們執(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（當(dāng)然，不僅是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：當(dāng)提交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ù)，當(dāng)數(shù)據(jù)庫被刪除、或者從庫同步主庫數(shù)據(jù)時，由于bin log記錄變更數(shù)據(jù)的sql，所以可通過bin log恢復(fù)。而redo log作用是持久化，當(dāng)發(fā)生服務(wù)器宕機或者掉電等情況，數(shù)據(jù)丟失，可以通過redo log恢復(fù)。
bin log是提交事務(wù)時才寫入磁盤，而redo log在開啟事務(wù)時，就開始寫入到磁盤

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

不行！因為redo log側(cè)重點是保存某次事務(wù)的數(shù)據(jù)變更，當(dāng)內(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ù)。當(dāng)我們變更數(shù)據(jù)時，就會產(chǎn)生undo log，可以認為insert一條數(shù)據(jù)，undo log會記錄一條對應(yīng)的delete日志，反之亦然。