binlog的作用及與redo log的區別

2020 年 4 月 13 日
筆記

區別

二進制日誌(bin log)會記錄所有與MySQL數據庫有關的日誌記錄，包括InnoDB、MyISAM、Heap等其他存儲引擎的日誌。而InnoDB存儲引擎的重做日誌只記錄有關該存儲引擎本身的事務日誌。
其次，記錄的內容不同，無論用戶將二進制日誌文件記錄的格式設為STATEMENT還是ROW，又或者是MIXED，其記錄的都是關於一個事務的具體操作內容，即該日誌是邏輯日誌。而InnoDB存儲引擎的重做日誌文件記錄的是關於每個頁（Page）的更改的物理情況。
此外，寫入的時間也不同，二進制日誌文件僅在事務提交前進行提交，即只寫磁盤一次，不論這時該事務多大。而在事務進行的過程中，卻不斷有重做日誌條目（redo entry）被寫入到重做日誌文件中。

作用

恢復（recovery）：某些數據的恢復需要二進制日誌，例如，在一個數據庫全備文件恢復後，用戶可以通過二進制日誌進行point-in-time的恢復。
複製（replication）：其原理與恢復類似，通過複製和執行二進制日誌使一台遠程的MySQL數據庫（一般稱為slave或standby）與一台MySQL數據庫（一般稱為master或primary）進行實時同步。
審計（audit）：用戶可以通過二進制日誌中的信息來進行審計，判斷是否有對數據庫進行注入的攻擊。

以下配置文件的參數影響着二進制日誌記錄的信息和行為：

sync_binlog

在默認情況下，二進制日誌並不是在每次寫的時候同步到磁盤（用戶可以理解為緩衝寫）。因此，當數據庫所在操作系統發生宕機時，可能會有最後一部分數據沒有寫入二進制日誌文件中，這會給恢復和複製帶來問題。參數sync_binlog=[N]表示每寫緩衝多少次就同步到磁盤。如果將N設為1，即sync_binlog=1表示採用同步寫磁盤的方式來寫二進制日誌，這時寫操作不使用操作系統的緩衝來寫二進制日誌。sync_binlog的默認值為0，如果使用InnoDB存儲引擎進行複製，並且想得到最大的高可用性，建議將該值設為ON。不過該值為ON時，確實會對數據庫的IO系統帶來一定的影響。

但是，即使將sync_binlog設為1，還是會有一種情況導致問題的發生。當使用InnoDB存儲引擎時，在一個事務發出COMMIT動作之前，由於sync_binlog為1，因此會將二進制日誌立即寫入磁盤。如果這時已經寫入了二進制日誌，但是提交還沒有發生，並且此時發生了宕機，那麼在MySQL數據庫下次啟動時，由於COMMIT操作並沒有發生，這個事務會被回滾掉。但是二進制日誌已經記錄了該事務信息，不能被回滾。這個問題可以通過將參數innodb_support_xa設為1來解決，雖然innodb_support_xa與XA事務有關，但它同時也確保了二進制日誌和InnoDB存儲引擎數據文件的同步。

max_binlog_size

指定了單個二進制日誌文件的最大值，如果超過該值，則產生新的二進制日誌文件，後綴名+1，並記錄到.index文件。從MySQL 5.0開始的默認值為1073 741824，代表1 G（在之前版本中max_binlog_size默認大小為1.1G）

binlog_cache_size

當使用事務的表存儲引擎（如InnoDB存儲引擎）時，所有未提交（uncommitted）的二進制日誌會被記錄到一個緩存中去，等該事務提交（committed）時直接將緩衝中的二進制日誌寫入二進制日誌文件，而該緩衝的大小由binlog_cache_size決定，默認大小為32K.如果如果超過此大小，會被定入臨時文件。
可以通過show variables like 'binlog_cache_use' 查看緩衝使用次數，通過show global status like 'binlog_cache_disk_use'\G 查看使用臨時文件次數.

binlog-do-db與binlog-ignore-db

參數binlog-do-db和binlog-ignore-db表示需要寫入或忽略寫入哪些庫的日誌。默認為空，表示需要同步所有庫的日誌到二進制日誌。

log-slave-update

如果當前數據庫是複製中的slave角色，則它不會將從master取得並執行的二進制日誌寫入自己的二進制日誌文件中去。如果需要寫入，要設置log-slave-update。如果需要搭建master=>slave=>slave架構的複製，則必須設置該參數。

binlog_format

參數可設的值有STATEMENT、ROW和MIXED。
binlog_format是動態參數，因此可以在數據庫運行環境下進行更改

將參數binlog_format設置為ROW，會對磁盤空間要求有一定的增加。這是因為這時MySQL數據庫不再將邏輯的SQL操作記錄到二進制日誌中，而是記錄對於每行的更改（如果修改10萬行數據，那麼也會記錄10萬行的變動）。而由於複製是採用傳輸二進制日誌方式實現的，因此複製的網絡開銷也有所增加。

（1）STATEMENT格式和之前的MySQL版本一樣，二進制日誌文件記錄的是日誌的邏輯SQL語句。

（2）在ROW格式下，二進制日誌記錄的不再是簡單的SQL語句了，而是記錄表的行更改情況。基於ROW格式的複製類似於Oracle的物理Standby（當然，還是有些區別）。同時，對上述提及的Statement格式下複製的問題予以解決。從MySQL 5.1版本開始，如果設置了binlog_format為ROW，可以將InnoDB的事務隔離基本設為READ COMMITTED，以獲得更好的並發性。

（3）在MIXED格式下，MySQL默認採用STATEMENT格式進行二進制日誌文件的記錄，但是在一些情況下會使用ROW格式，可能的情況有：

1）表的存儲引擎為NDB，這時對錶的DML操作都會以ROW格式記錄。

2）使用了UUID（）、USER（）、CURRENT_USER（）、FOUND_ROWS（）、ROW_COUNT（）等不確定函數。

3）使用了INSERT DELAY語句。

4）使用了用戶定義函數（UDF）。

5）使用了臨時表（temporary table）。