MySQL的binlog、redo log和undo log怎麼使用

1、binlog

Binlog是用於記錄資料庫中執行的寫入操作的信息，它排除了查詢操作，並且以二進位格式保存在磁碟上。 binlog是mysql的邏輯日誌，並且由Server層進行記錄，使用任何儲存引擎的mysql資料庫都會記錄binlog日誌。

• 邏輯日誌：可以簡單得理解為sql語句；

• #物理日誌：MySQL中資料都是保存在資料頁中的，實體日誌記錄的是資料頁上的變更；在這裡插入程式碼片

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

binlog使用場景
專案 在實際應用中，binlog的主要使用場景有兩個，分別是主從複製和資料復原。

• 主從複製：在Master端開啟binlog，然後將binlog傳送到各個Slave端，Slave端重播binlog從而達到主從資料一致。

• 資料恢復：透過使用mysqlbinlog工具來恢復資料。

MySQL主從同步原理

• #主節點binlog dump 執行緒
  當從節點連接主節點時，主節點會建立一個log dump 線程，用於傳送binlog的內容。在讀取binlog中的動作時，此執行緒會對主節點上的binlog加鎖，當讀取完成，甚至在發動給從節點之前，鎖會被釋放；

• 從節點I/O執行緒
  當從節點上執行start slave指令之後，從節點會建立一個I/O執行緒用來連接主節點，請求主庫中更新的binlog。 I/O執行緒接收到主節點binlog dump程序發來的更新之後，保存在本地relaylog中；

• 從節點SQL執行緒
  SQL執行緒負責讀取relaylog中的內容，解析成具體的操作並執行，最終保證主從資料的一致性；
  MySQL 資料庫主從同步原理

##binlog的內容 上面說了，binlog是一種邏輯日誌，可以簡單得理解為sql語句，但其實還包含著執行的sql語句的反向邏輯。 delete對應delete本身以及反向的insert資訊；update包含對應的update執行前後資料行的相關資訊；insert包含自身的insert以及對應的delete資訊。

binlog的格式 binlog共有三種格式，分別是statement、row以及mixed。 MySQL 5.7.7版本之前預設使用的是statement，MySQL 5.7.7之後預設使用的是row。日誌的格式可以透過my.ini設定檔中的binlog-format來修改。
(1)statement：基於sql語句的複製（statement-based replication,SBR），每一個修改資料的sql語句都會記錄到binlog。

• 優點：不需要具體記錄某一行的變化，節省空間，減少io，提高效能；

• 缺點：在執行sysdate( )或sleep()等操作的時候，可能導致主從資料不一致的情況；

#(2)row：基於行記錄的複製（row-based replication,RBR），不記錄sql語句上下文相關訊息，而是記錄哪筆記錄被修改的細節。

• 優點：非常詳細地記錄每一行記錄修改的細節，因而不會出現資料無法被正確複製的情況；

• 缺點：由於會非常詳細地記錄每一筆記錄修改的細節，這樣會產生大量的日誌內容。假設現在有一條update語句，修改了許多筆記錄，則每筆修改記錄都會記錄到binlog。特別地，alter table這個操作，由於表結構的變化，每行記錄都會發生變化，導致日誌量暴增；

(3)mixed：根據上面所說的， statement和row各有優缺點，因此出現了mixed這個版本，將這二者混合。一般情況下使用statement格式來進行儲存，當遇到statement無法解決時，切換為row格式來進行儲存。

特別地，上面說了，新版本（MySQL 5.7.7之後）預設使用的row格式，這裡的row也做了相應的優化，在遇到alter table這個操作時採用statement格式進行記錄，其餘操作仍然使用row格式。

binlog刷盤時機

對於InnoDB儲存引擎來說，只有在交易提交的時候才會記錄binlog，此時記錄還在記憶體中，MySQL通過sync_binlog來控制binlog的刷盤時機，取值範圍為0-N：

• 0：不強製刷到磁碟，由系統自行判斷何時寫入磁碟中；

• 1：每次提交後都要將binlog寫入磁碟中；

• N：每N個事務，才會將binlog寫入磁碟中；

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

binlog的實體檔案大小

透過設定max_binlog_size參數，可以控制binlog的大小，這參數位於my.ini設定檔中。系統會建立一個新的檔案以繼續儲存日誌，當日誌的大小超過binlog檔案的容量限制時。當一個事務比較大時，或是日誌越來越多的時候，此時佔據的實體空間太大怎麼辦？ MySQL提供了一種自動刪除的機制，還是在my.ini設定檔中，可以透過設定expire_logs_days這個參數來解決，單位為天。當這個參數為0，表示永不刪除；為N時，表示第N天後自動刪除。

2、redo log

redolog是InnoDB引擎專有的日誌系統。主要是用來實現交易的持久性以及實現crash-safe功能。 redolog屬於實體日誌，記錄的是sql語句執行之後資料頁上的具體修改內容。
我們都知道，當MySQL運行的時候，會將資料從磁碟載入到記憶體當中。當執行sql語句對資料進行修改時，修改後的內容其實都只是暫時儲存到記憶體當中，如果此時斷電或出現其他情況，這些修改就會遺失。因而，當修改完資料之後，MySQL會尋找機會將這些記憶體中的記錄刷回磁碟當中。但這就出現一個效能問題，主要有兩個面向：

InnoDB中是以頁為資料單位與磁碟互動的，而一個事務很可能只是修改了一個頁上的幾個位元組，如果將一個完整的資料頁刷回磁碟當中，浪費資源；

一個事務可能涉及到多個資料頁，這些資料頁只是邏輯上連續，在物理上並不連續，使用隨機IO效能太差；

因此，MySQL設計了redolog來記錄交易對資料頁具體做了哪些修改，之後將redolog再刷回磁碟當中。你可能會有疑惑，本來就是想減少io，這不又加一次io麼？ InnoDB的設計者在設計之初就已經考慮到了這些。 Redo log 檔案通常都很小，而且在刷新磁碟的過程中採用的是順序 I/O。與隨機 I/O 相比，效能更好。

redo log基本概念
redolog由兩個部分組成，一個是記憶體中的日誌快取redo log buffer，一個是磁碟中的日誌檔案redo log file。當每次對資料記錄進行修改的時候，都會將這些修改內容先寫入redo log buffer中，後續等待適當的時機將記憶體中的修改刷回redo log file。這種先寫日誌，再寫磁碟的技術就是WAL(Write-Ahead Logging)技術。要注意的是redolog比資料頁先刷回磁碟，叢集索引，二級索引，undo頁面的修改，都需要記錄redolog。

在電腦作業系統中，用戶空間(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記錄形式
redolog採用固定大小，循環寫入的格式，當redolog寫滿之後，會重新從頭開始寫入。為什麼這麼設計呢？
redo log存在的意義主要是降低資料頁刷盤的要求。 redolog記錄了資料頁上的修改，但當資料頁也刷回到磁碟後，這些記錄就失去作用了。因此當MySQL判斷之前的redolog已經失去作用之後，新資料會將這些失效的資料進行覆寫。那如何判斷該不該進行覆蓋呢？

上圖是redo log file的示意圖，write pos表示redolog目前記錄的日誌序號LSN(log sequence number)。當資料頁也已經刷回磁碟之後，會更新redo log file中的LSN，表示到這個LSN之前的資料已經落盤，這個LSN就是check point。 write pos到check point之間的部分是redolog空餘的部分，用來記錄新的記錄；check point到write pos之間是redolog已經記錄的資料頁修改部分，但此時資料頁還未刷回磁碟的部分。當write pos追上check point時，會先推check point向前移動，空出位置再記錄新的日誌。

啟動innodb的時候，不管上次是正常關閉還是異常關閉，總是會進行恢復操作。恢復時，會先檢查資料頁中的LSN，如果這個LSN小於redolog中的LSN，也就是write pos位置，說明在redolog上記錄著資料頁上尚未完成的動作，接著就會從最近的一個check point出發，開始同步資料。

那有沒有可能資料頁中的LSN大於redolog中的LSN呢？答案是當然可能。當這種情況出現時，這時超出redolog的部分將不會重做，因為這本身就表示已經做過的事情，無需再重做。
redo log與binlog區別

##檔案大小redo log的大小是固定的。 binlog可透過設定參數max_binlog_size設定每個binlog檔案的大小。 實作方式redo log是InnoDB引擎層實作的，並不是所有引擎都有。 binlog是Server層實現的，所有引擎都可以使用binlog日誌

	#redo log	binlog

####記錄方式######redo log 採用循環寫的方式記錄，當寫到結尾時，會回到開頭循環寫日誌。 ######binlog 透過追加的方式記錄，當檔案大小大於給定值後，後續的日誌會記錄到新的檔案上############適用場景#### ##redo log適用於崩潰復原(crash-safe)######binlog適用於主從複製和資料復原############

由binlog和redo log的區別可知：binlog日誌只用於歸檔，只依賴binlog是沒有crash-safe能力的。但只有redo log也不行，因為redo log是InnoDB特有的，且日誌上的記錄落盤後會被覆掉。因此需要binlog和redo log二者同時記錄，才能確保當資料庫發生宕機重新啟動時，資料不會遺失。
兩階段提交
上面簡單介紹了redolog和binlog，在對資料進行修改時，他們都會對這些修改進行保存落地，只是一個是實體日誌，一個是邏輯日誌。那他倆具體在修改過程中是如何執行的呢？

假設現在有一個update語句要執行，update from table_name set c=c 1 where id=2，執行流程如下：

• ##先定位到id=2這一筆記錄；

• 執行器拿到引擎給的行數據，把這個值加上1，得到新的一行數據，再呼叫引擎介面寫入這行新數據；

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

• 執行器產生這個操作的binlog，並把binlog寫入磁碟;

• 執行器呼叫引擎的提交事務接口，引擎把剛剛寫入的redo log 改成提交（commit）狀態，更新完成；

示意圖如下：

這種將redolog的寫入拆分成prepare和commit兩個步驟的過程稱之為兩階段提交。

redolog 和binlog都可以用來表示交易的提交狀態，而兩階段提交就是讓這兩個狀態保持邏輯上的一致。如果不使用兩階段提交，而是先寫其中一個再寫另外一個可能會帶來一些問題。

此時還是使用update來舉例。假設目前id=2，有一個欄位c=0，分別分析以下情況：

先寫redolog再寫binlog 假設先寫redolog，當redolog寫完，但是binlog還未寫完的時候，此時MySQL突然出現異常導致重新啟動。由於之前redolog已經寫完，系統重新啟動後，修改後的記錄仍然存在，所以恢復後這一行 c 的值是 1。但由於系統重啟，binlog中並未有這項紀錄。之後備份日誌的時候，存起來的binlog裡面就沒有這條語句。然後你會發現，如果需要用這個binlog 來恢復臨時庫的話，由於這個語句的binlog丟失，這個臨時庫就會少了這次更新，恢復出來的這一行c 的值就是0，與原庫的值不同。

先寫binlog再寫redolog 假如先寫binlog，然後寫redolog的時候系統重新啟動。重啟之後，redolog中沒有對c進行修改的記錄，此時c的值還是0。但 binlog裡面已經記錄了「把 c 從 0 改成 1」這個日誌。所以，之後用 binlog來恢復的時候就多了一個事務出來，恢復出來的這一行 c 的值就是 1，與原始庫的值不同。

因此，綜上所述，如果是先寫某一個日誌再寫另一個日誌，就會出現資料庫的狀態與使用binlog恢復出來的函式庫的狀態不一致的情況。

3、undo log

undolog主要用來記錄某行記錄被修改之前的狀態，記錄的是修改前的資料。使用undolog可以在交易回溯時將記錄恢復到交易開始前的狀態。事務的原子性和持久性也是依賴undolog來實現的。 undo log主要記錄了資料的邏輯變化，例如一條INSERT語句，對應一條DELETE的undo log，對於每個UPDATE語句，對應一條相反的UPDATE的undo log，這樣在發生錯誤時，就能回滾到事務之前的數據狀態。在資料復原過程中，使用binlog和redolog結合，可確保資料復原的準確性。

undolog的作用流程如下所示：

• 在交易開始前將修改前的版本寫入undo log；

• 開始進行修改，將修改過的資料儲存到記憶體當中；

• #將undolog持久化到磁碟當中；

• 將資料頁刷回磁碟當中；

• 交易提交；

和redolog一樣，undolog也需要先於資料頁刷回磁碟。如果undolog完整，可以使用其中記錄的資訊來回溯事務，以恢復資料。

在一個事務當中，可能會對同一資料進行多次修改，那麼是不是每一次修改前的記錄都要記錄到undolog中呢？這樣的話，會導致undolog日誌量太大，此時redolog就要上場了。在一個事務當中，如果是對同一筆記錄進行修改，undolog只會記錄交易開始前的原始記錄，當再次對這條記錄進行修改時，redolog會記錄後續的變更。在資料復原過程中，透過協調redolog和undolog的作用，前滾和回溯操作來完成資料的復原。過程如下圖所示：

#

以上是MySQL的binlog、redo log和undo log怎麼使用的詳細內容。更多資訊請關注PHP中文網其他相關文章！