MySQL之一篇讀懂binlog redolog undolog

轉自：https://juejin.cn/post/6860252224930070536

日志是

mysql

資料庫的重要組成部分，記錄着資料庫運作期間各種狀态資訊。

mysql

日志主要包括錯誤日志、查詢日志、慢查詢日志、事務日志、二進制日志幾大類。作為開發，我們重點需要關注的是二進制日志(

binlog

)和事務日志(包括

redo log

和

undo log

)，本文接下來會詳細介紹這三種日志。

binlog

binlog

用于記錄資料庫執行的寫入性操作(不包括查詢)資訊，以二進制的形式儲存在磁盤中。

binlog

是

mysql

的邏輯日志，并且由

Server

層進行記錄，使用任何存儲引擎的

mysql

資料庫都會記錄

binlog

日志。

邏輯日志：可以簡單了解為記錄的就是sql語句。

實體日志：因為

mysql

資料最終是儲存在資料頁中的，實體日志記錄的就是資料頁變更。

binlog

是通過追加的方式進行寫入的，可以通過

max_binlog_size

參數設定每個

binlog

檔案的大小，當檔案大小達到給定值之後，會生成新的檔案來儲存日志。

binlog使用場景

在實際應用中，

binlog

的主要使用場景有兩個，分别是主從複制和資料恢複。

1. 主從複制：在

   Master

   端開啟

   binlog

   ，然後将

   binlog

   發送到各個

   Slave

   端，

   Slave

   端重放

   binlog

   進而達到主從資料一緻。
2. 資料恢複：通過使用

   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

是如何保證持久性的呢？最簡單的做法是在每次事務送出的時候，将該事務涉及修改的資料頁全部重新整理到磁盤中。但是這麼做會有嚴重的性能問題，主要展現在兩個方面：

1. 因為

   Innodb

   是以

   頁

   為機關進行磁盤互動的，而一個事務很可能隻修改一個資料頁裡面的幾個位元組，這個時候将完整的資料頁刷到磁盤的話，太浪費資源了！
2. 一個事務可能涉及修改多個資料頁，并且這些資料頁在實體上并不連續，使用随機IO寫入性能太差！

是以

mysql

設計了

redo log

，具體來說就是隻記錄事務對資料頁做了哪些修改，這樣就能完美地解決性能問題了(相對而言檔案更小并且是順序IO)。

redo log基本概念

redo log

包括兩部分：一個是記憶體中的日志緩沖(

redo log buffer

)，另一個是磁盤上的日志檔案(

redo log file

)。

mysql

每執行一條

DML

語句，先将記錄寫入

redo log buffer

，後續某個時間點再一次性将多個操作記錄寫到

redo log file

。這種先寫日志，再寫磁盤的技術就是

MySQL

裡經常說到的

WAL(Write-Ahead Logging)

技術。

在計算機作業系統中，使用者空間(

user space

)下的緩沖區資料一般情況下是無法直接寫入磁盤的，中間必須經過作業系統核心空間(

kernel space

)緩沖區(

OS Buffer

)。是以，

redo log buffer

寫入

redo log file

實際上是先寫入

OS Buffer

，然後再通過系統調用

fsync()

将其刷到

redo log file

中，過程如下：

mysql

支援三種将

redo log buffer

寫入

redo log file

的時機，可以通過

innodb_flush_log_at_trx_commit

參數配置，各參數值含義如下：

參數值	含義
0（延遲寫）	事務送出時不會将 redo log buffer 中日志寫入到 os buffer ，而是每秒寫入 os buffer 并調用 fsync() 寫入到 redo log file 中。也就是說設定為0時是(大約)每秒重新整理寫入到磁盤中的，當系統崩潰，會丢失1秒鐘的資料。
1（實時寫，實時刷）	事務每次送出都會将 redo log buffer 中的日志寫入 os buffer 并調用 fsync() 刷到 redo log file 中。這種方式即使系統崩潰也不會丢失任何資料，但是因為每次送出都寫入磁盤，IO的性能較差。
2（實時寫，延遲刷）	每次送出都僅寫入到 os buffer ，然後是每秒調用 fsync() 将 os buffer 中的日志寫入到 redo log file 。

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差別

redo log	binlog
檔案大小	redo log 的大小是固定的。	binlog 可通過配置參數 max_binlog_size 設定每個 binlog 檔案的大小。
實作方式	redo log 是 InnoDB 引擎層實作的，并不是所有引擎都有。	binlog 是 Server 層實作的，所有引擎都可以使用 binlog 日志
記錄方式	redo log 采用循環寫的方式記錄，當寫到結尾時，會回到開頭循環寫日志。	binlog 通過追加的方式記錄，當檔案大小大于給定值後，後續的日志會記錄到新的檔案上
适用場景	redo log 适用于崩潰恢複(crash-safe)	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

(多版本并發控制)實作的關鍵，這部分内容在面試中的老大難-mysql事務和鎖，一次性講清楚！中有介紹，不再贅述。