MySQL：Redo & binlog

Ryan_Bai發表於2021-04-19
MySql

示例

mysql> create table T(ID int primary key, c int);
mysql> update T set c=c+1 where ID=2;

Redo

InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個檔案，每個檔案的大小是 1 GB，那麼總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭迴圈寫。

  • write pos是當前記錄的位置，一邊寫一邊後移，寫到第 3 號檔案末尾後就回到 0 號檔案開頭。checkpoint 是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄更新到資料檔案。

  • write pos 和 checkpoint 之間空著的部分，可以用來記錄新的操作。如果 write pos 追上 checkpoint，表示滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進一下。

  • 有了 redo log，InnoDB 就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為 crash-safe。

在 InnoDB 儲存引擎目錄下會有兩個名為 ib_logfile0 和 ib_logfile1 的檔案。每個 InnoDB 儲存引擎至少有 1 個重做日誌檔案組，每個檔案組下至少有 2 個重做日誌檔案。在日誌組中每個重做日誌檔案的大小一致，並以迴圈寫入的方式執行。

引數

  • innodb_log_file_size：指定每個重做日誌檔案的大小

  • innodb_log_files_in_group：指定了日誌檔案組中重做日誌檔案的數量，預設為 2

  • innodb_mirrored_log_groups：指定了日誌映象檔案組的數量，預設為 1

  • innodb_log_group_home_dir：指定了日誌檔案組所在路徑，預設為 ./，表示在 MySQL 資料庫的資料目錄下。

  • innodb_flush_log_zt_trx_commit：表示在提交操作時，處理重做日誌的方式

    • 0：當提交事務時，並不將事務的重做日誌寫入磁碟上的日誌檔案，而是等待主執行緒每秒的重新整理。

    • 1：在執行 commit 時，將重做日誌緩衝同步寫到磁碟，即伴有 fsync 的呼叫

    • 2：將重做日誌非同步寫到磁碟，即寫到檔案系統的快取中。

binlog

redo log 是 InnoDB 引擎特有的日誌，而 Server 層也有自己的日誌，稱為 binlog（歸檔日誌）。

作用

  • 恢復（recovery）：某些資料的恢復需要二進位制日誌

  • 複製（replication）：其原理與恢復類似，通過複製和執行二進位制日誌使一臺遠端的 MySQL 資料庫與一臺 MySQL 資料庫進行實時同步。

  • 審計（audit）：永和可以通過二進位制日誌中的資訊來進行審計，判斷是否有對資料庫進行注入的攻擊

引數

  • log-bin[=name]：啟用二進位制日誌。預設二進位制日誌檔名為主機名，字尾名為二進位制日誌的序列號，所在路徑為資料庫所在的目錄。

  • max_binlog_size：指定了單個二進位制日誌檔案的最大值，如果超過該值，則產生二進位制日誌檔案，字尾名 +1，預設大小為 1G。

  • binlog_cache_size：binlog 緩衝的大小由 binlog_cache_size 決定，預設大小為 32K。binlog_cache_size 是基於會話的，如夠用時會把緩衝日誌寫入一個臨時檔案中。通過 SHOW GLOBAL STATUS 命令檢視 binlog_cache_use（使用緩衝寫二進位制日誌的次數）、binlog_cache_disk_use（記錄使用臨時檔案寫二進位制日誌的次數） 的狀態，判斷是否合適

  • sync_binlog=[N]：表示每寫緩衝多少次就同步到磁碟。如果將 N 設為 1，則寫操作不使用作業系統的緩衝來寫二進位制日誌。

  • binlog-do-db：表示需要寫入哪些庫的日誌

  • binlog-ignore-db：表示需要忽略哪些庫的日誌

  • log-slave-update：如果當前資料庫是複製中的 slave 角色，則它不會從 master 取得並執行的二進位制日誌寫入自己的二進位制日誌檔案中去。如果需要，則需要設定。

  • binlog_format：

    • STATEMENT：二進位制日誌檔案記錄的是日誌的邏輯 SQL 語句。

    • ROW：記錄表的更改情況。

    • MIXED：MySQL 預設採用 STATEMENT 格式進行二進位制日誌檔案的記錄，但是在一些情況下會使用 ROW 格式

      • NDB

      • 使用 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT() 等不確定函式。

      • 使用了 INSERT DELAY 語句

      • 使用了使用者定義函式（UDF）

      • 使用了臨時表

區別

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現的，所有引擎都可以使用。

  2. redo log 是物理日誌，記錄的是“在某個資料頁上做了什麼修改”；binlog 是邏輯日誌，記錄的是這個語句的原始邏輯，比如“給 ID=2 這一行的 c 欄位加 1 ”。

  3. redo log 是迴圈寫的，空間固定會用完；binlog 是可以追加寫入的。“追加寫”是指 binlog 檔案寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。

兩階段提交

為了讓兩份日誌之間的邏輯一致

流程

  1. 執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜尋找到這一行。如果 ID=2 這一行所在的資料頁本來就在記憶體中，就直接返回給執行器；否則，需要先從磁碟讀入記憶體，然後再返回。

  2. 執行器拿到引擎給的行資料，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行資料，再呼叫引擎介面寫入這行新資料。

  3. 引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到 redo log 裡面，此時 redo log 處於 prepare 狀態。然後告知執行器執行完成了，隨時可以提交事務。

  4. 執行器生成這個操作的 binlog，並把 binlog 寫入磁碟。

  5. 執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的 redo log 改成提交（commit）狀態，更新完成。

原因

可以看到，如果不使用“兩階段提交”，那麼資料庫的狀態就有可能和用它的日誌恢復出來的庫的狀態不一致。

  1. 先寫 redo log 後寫 binlog 假設在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 程式異常重啟。由於我們前面說過的，redo log 寫完之後，系統即使崩潰，仍然能夠把資料恢復回來，所以恢復後這一行 c 的值是 1。 但是由於 binlog 沒寫完就 crash 了，這時候 binlog 裡面就沒有記錄這個語句。因此，之後備份日誌的時候，存起來的 binlog 裡面就沒有這條語句。 然後你會發現，如果需要用這個 binlog 來恢復臨時庫的話，由於這個語句的 binlog 丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行 c 的值就是 0，與原庫的值不同。

  2. 先寫 binlog 後寫 redo log 如果在 binlog 寫完之後 crash，由於 redo log 還沒寫，崩潰恢復以後這個事務無效，所以這一行 c 的值是 0。但是 binlog 裡面已經記錄了“把 c 從 0 改成 1”這個日誌。所以，在之後用 binlog 來恢復的時候就多了一個事務出來，恢復出來的這一行 c 的值就是 1，與原庫的值不同。

引數推薦設定

  • innodb_flush_log_at_trx_commit 這個引數設定成 1 的時候，表示每次事務的 redo log 都直接持久化到磁碟。這個引數我建議你設定成 1，這樣可以保證 MySQL 異常重啟之後資料不丟失。

  • sync_binlog 這個引數設定成 1 的時候，表示每次事務的 binlog 都持久化到磁碟。這個引數我也建議你設定成 1，這樣可以保證 MySQL 異常重啟之後 binlog 不丟失。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31490526/viewspace-2768744/，如需轉載，請註明出處，否則將追究法律責任。

相關文章