深入講解MySQL中的重要日志 binlog 的寫入機制以及影響IO性能的關鍵配置，并且介紹了如何利用binlog去恢復數據，保證MySQL的可靠性。

Q：binlog寫入時機

binlog 的寫入邏輯并不復雜：事務執行過程中，先把日志寫到 binlog cache，事務提交的時候，再把 binlog cache 寫到 binlog 文件中。

什么是binlog cache？

binlog cache 在事務的運行期間暫存著binlog的數據，binlog cache 是在每個線程內空間獨立的。如果啟用了bin log日志，MySQL 會為每個客戶端分配一個二進制日志緩存。如果您經常使用大型事務，則可以增加此緩存大小以獲得更好的性能，可通過 binlog_cache_size 配置其大小，默認 32768 bytes。

如果binlog cache空間足夠，在事務提交的時候，cache中的內容會被清空，同時這些數據會被寫入到 binlog files 中；因為 bin log內容無論多大在事務提交時都需要一次性寫入，所以當 bin log cache放不下的時候，就需要暫存到磁盤，然后提交被寫入到 binlog files。

參數 binlog_cache_size:控制單個線程內 binlog cache 所占內存的大小

上面說的 寫入到 bin log 中其實又拆為兩部分：

• write首先會寫入 page cache 中的 binlog files 中，page cache 就是一塊內存。（不占用磁盤IOPS）
• fsync然后操作系統執行 fsync 時 bin log 才會從 page cache 中真正持久化到磁盤。（占用磁盤IOPS）

write 和 fsync寫入時機

關于write和fsync 的時機，是通過參數 sync_binlog 控制：

• sync_binlog=0：表示每次提交事務只是 write，不執行 fsync，也就是binlog不做持久化。（不建議）
• sync_binlog=1：表示每次提交事務都要發生 fsync。
• sync_binlog=N：表示每次事務都會write，但是N次事務提交會執行fsync進行持久化。

通常來說，為了提高IOPS，會將這個參數設為100-1000。缺點是如果還沒有執行fsync就宕機，最多會丟失最近N個事務的binlog日志。如果為了保證數據安全，就設為1。

如果binlog內容缺失怎么辦？

對于通過 redo log 可以恢復數據，我們都已經知道了，但是如果 binlog 內容缺失呢？

根據 sync_binlog 參數描述，如果設置為大于一，就代表N次事務才會將log持久化到磁盤中。而與此同時，redo log 的 innodb_flush_log_at_trx_commit 參數設置為1，表示每次事務提交都會執行 fsync。

這就產生了一種可能，如果發生mysql服務器了crash，此時redolog已經做持久化，但是binlog還有事務仍保存在page cache中，沒有來得及執行fsync。宕機后重啟服務，依賴redolog恢復數據，就會出現binlog 長度比真實數據所應該需要的長度短的情況。

面對數據完整但是binlog缺失，這種情況下會出現什么異樣呢？

如果 MySQL 服務器在崩潰恢復時發現二進制日志比應有的短，則它至少缺少一個成功提交的 InnoDB 事務。如果 sync_binlog=1 并且磁盤/文件系統在請求時進行實際同步（有些則沒有），則不會發生這種情況，所以服務器打印一條錯誤消息日志。在這種情況下，此二進制日志不正確，應從源數據的新快照重新啟動復制。

按照官方描述，如果將 sync_binlog設為1不會出現這種情況，如果出現了binlog比預期少，則會在服務器打印一條binlog日志的異常log：The binary log xxx is shorter than its expected size.

所以這種情況需要去人為干預處理，從最新的快照數據源重新復制。

如何使用binlog恢復歷史數據？

binlog 會記錄所有的邏輯操作，并且是采用“追加寫”的形式，不會像redolog一樣去覆蓋日志文件。binlog 里面的數據是可靠的，一定是事務提交后的數據，這就全靠 redolog 來保證。

眾所周知，我們可以通過 binlog 恢復任意時間點的數據，如何做到呢？

其實這是有前提的，我們必須要有定期備份全量數據的機制，比如半個月、每周、或者每天。

假定場景：比如今天中午12點有一次誤刪表數據，需要找回數據，如何做？

• 首先，找到最近的一次全量備份，比如昨天凌晨1點，那就從這個備份恢復到臨時庫；
• 然后，從備份的時間昨天凌晨1點開始，將備份的 binlog 依次取出來重放，直到今天中午12點的數據。

這樣臨時庫就到達了誤刪數據之前的狀態，然后可以把表數據從臨時庫取出來，按需求恢復到線上正式數據庫。