必須瞭解的 MySQL 三大日誌

大家好，我是 Kuls。

日誌是 mysql 數據庫的重要組成部分，記錄着數據庫運行期間各種狀態信息。mysql日誌主要包括錯誤日誌、查詢日誌、慢查詢日誌、事務日誌、二進制日誌幾大類。

作爲開發，我們重點需要關注的是二進制日誌 ( binlog ) 和事務日誌 (包括redo log 和 undo log )，本文接下來會詳細介紹這三種日誌。

binlog

binlog 用於記錄數據庫執行的寫入性操作 (不包括查詢) 信息，以二進制的形式保存在磁盤中。binlog 是 mysql的邏輯日誌，並且由 Server 層進行記錄，使用任何存儲引擎的 mysql 數據庫都會記錄 binlog 日誌。

邏輯日誌：可以簡單理解爲記錄的就是 sql 語句。
物理日誌：mysql 數據最終是保存在數據頁中的，物理日誌記錄的就是數據頁變更。

binlog 是通過追加的方式進行寫入的，可以通過max_binlog_size 參數設置每個 binlog文件的大小，當文件大小達到給定值之後，會生成新的文件來保存日誌。

binlog 使用場景

在實際應用中， binlog 的主要使用場景有兩個，分別是 主從複製 和 數據恢復 。

主從複製 ：在 Master 端開啓 binlog ，然後將 binlog發送到各個 Slave 端， Slave 端重放 binlog 從而達到主從數據一致。
數據恢復 ：通過使用 mysqlbinlog 工具來恢復數據。

binlog 刷盤時機

對於 InnoDB 存儲引擎而言，只有在事務提交時纔會記錄biglog ，此時記錄還在內存中，那麼 biglog是什麼時候刷到磁盤中的呢？

mysql 通過 sync_binlog 參數控制 biglog 的刷盤時機，取值範圍是 0-N：

0：不去強制要求，由系統自行判斷何時寫入磁盤；
1：每次 commit 的時候都要將 binlog 寫入磁盤；
N：每 N 個事務，纔會將 binlog 寫入磁盤。

從上面可以看出， sync_binlog 最安全的是設置是 1 ，這也是MySQL 5.7.7之後版本的默認值。但是設置一個大一些的值可以提升數據庫性能，因此實際情況下也可以將值適當調大，犧牲一定的一致性來獲取更好的性能。

binlog 日誌格式

binlog 日誌有三種格式，分別爲 STATMENT 、 ROW 和 MIXED。

在 MySQL 5.7.7 之前，默認的格式是 STATEMENT ， MySQL 5.7.7 之後，默認值是 ROW。日誌格式通過 binlog-format 指定。

STATMENT：基於SQL 語句的複製 ( statement-based replication, SBR )，每一條會修改數據的 sql 語句會記錄到binlog 中。
優點：不需要記錄每一行的變化，減少了 binlog 日誌量，節約了 IO , 從而提高了性能；
缺點：在某些情況下會導致主從數據不一致，比如執行 sysdate() 、 slepp() 等。
ROW：基於行的複製 (row-based replication, RBR )，不記錄每條 sql 語句的上下文信息，僅需記錄哪條數據被修改了。
優點：不會出現某些特定情況下的存儲過程、或 function、或 trigger 的調用和觸發無法被正確複製的問題；
缺點：會產生大量的日誌，尤其是 alter table 的時候會讓日誌暴漲
MIXED：基於STATMENT 和 ROW 兩種模式的混合複製 (mixed-based replication, MBR )，一般的複製使用STATEMENT 模式保存 binlog ，對於 STATEMENT 模式無法複製的操作使用 ROW 模式保存 binlog

redo log

爲什麼需要 redo log

我們都知道，事務的四大特性裏面有一個是 持久性 ，具體來說就是只要事務提交成功，那麼對數據庫做的修改就被永久保存下來了，不可能因爲任何原因再回到原來的狀態 。

那麼 mysql是如何保證一致性的呢？

最簡單的做法是在每次事務提交的時候，將該事務涉及修改的數據頁全部刷新到磁盤中。但是這麼做會有嚴重的性能問題，主要體現在兩個方面：

因爲 Innodb 是以 頁 爲單位進行磁盤交互的，而一個事務很可能只修改一個數據頁裏面的幾個字節，這個時候將完整的數據頁刷到磁盤的話，太浪費資源了！
一個事務可能涉及修改多個數據頁，並且這些數據頁在物理上並不連續，使用隨機 IO 寫入性能太差！

因此 mysql 設計了 redo log ， 具體來說就是隻記錄事務對數據頁做了哪些修改，這樣就能完美地解決性能問題了 (相對而言文件更小並且是順序 IO)。

redo log 基本概念

redo log 包括兩部分：一個是內存中的日誌緩衝 ( redo log buffer )，另一個是磁盤上的日誌文件 ( redo logfile)。

mysql 每執行一條 DML 語句，先將記錄寫入 redo log buffer，後續某個時間點再一次性將多個操作記錄寫到 redo log file。這種 先寫日誌，再寫磁盤 的技術就是 MySQL
裏經常說到的 WAL(Write-Ahead Logging) 技術。

在計算機操作系統中，用戶空間 ( user space ) 下的緩衝區數據一般情況下是無法直接寫入磁盤的，中間必須經過操作系統內核空間 ( kernel space ) 緩衝區 ( OS Buffer )。

因此， redo log buffer 寫入 redo logfile 實際上是先寫入 OS Buffer ，然後再通過系統調用 fsync() 將其刷到 redo log file
中，過程如下：

mysql 支持三種將 redo log buffer 寫入 redo log file 的時機，可以通過 innodb_flush_log_at_trx_commit 參數配置，各參數值含義如下：

redo log 記錄形式

前面說過， redo log 實際上記錄數據頁的變更，而這種變更記錄是沒必要全部保存，因此 redo log實現上採用了大小固定，循環寫入的方式，當寫到結尾時，會回到開頭循環寫日誌。如下圖：

同時我們很容易得知，在 innodb 中，既有redo log 需要刷盤，還有 數據頁 也需要刷盤， redo log存在的意義主要就是降低對 數據頁 刷盤的要求 ** 。

在上圖中， write pos 表示 redo log 當前記錄的 LSN (邏輯序列號) 位置， check point 表示 數據頁更改記錄 刷盤後對應 redo log 所處的 LSN(邏輯序列號) 位置。

write pos 到 check point 之間的部分是 redo log 空着的部分，用於記錄新的記錄；check point 到 write pos 之間是 redo log 待落盤的數據頁更改記錄。當 write pos追上check point 時，會先推動 check point 向前移動，空出位置再記錄新的日誌。

啓動 innodb 的時候，不管上次是正常關閉還是異常關閉，總是會進行恢復操作。因爲 redo log記錄的是數據頁的物理變化，因此恢復的時候速度比邏輯日誌 (如 binlog ) 要快很多。

重啓innodb 時，首先會檢查磁盤中數據頁的 LSN ，如果數據頁的LSN 小於日誌中的 LSN ，則會從 checkpoint 開始恢復。

還有一種情況，在宕機前正處於checkpoint 的刷盤過程，且數據頁的刷盤進度超過了日誌頁的刷盤進度，此時會出現數據頁中記錄的 LSN 大於日誌中的 LSN，這時超出日誌進度的部分將不會重做，因爲這本身就表示已經做過的事情，無需再重做。

redo log 與 binlog 區別

由 binlog 和 redo log 的區別可知：binlog 日誌只用于歸檔，只依靠 binlog 是沒有 crash-safe 能力的。

但只有 redo log 也不行，因爲 redo log 是 InnoDB特有的，且日誌上的記錄落盤後會被覆蓋掉。因此需要 binlog和 redo log二者同時記錄，才能保證當數據庫發生宕機重啓時，數據不會丟失。

undo log

數據庫事務四大特性中有一個是 原子性 ，具體來說就是 原子性是指對數據庫的一系列操作，要麼全部成功，要麼全部失敗，不可能出現部分成功的情況。

實際上， 原子性 底層就是通過 undo log 實現的。undo log主要記錄了數據的邏輯變化，比如一條 INSERT 語句，對應一條DELETE 的 undo log ，對於每個 UPDATE 語句，對應一條相反的 UPDATE 的 undo log ，這樣在發生錯誤時，就能回滾到事務之前的數據狀態。

同時， undo log 也是 MVCC(多版本併發控制) 實現的關鍵。

轉自：陳添明

鏈接：https://juejin.im/post/6860252224930070536

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/DBzFe-fbT2OCOZNT3AyIuA