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

Q：binlog寫入時(shí)機(jī)

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

什么是binlog cache？

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

如果binlog cache空間足夠，在事務(wù)提交的時(shí)候，cache中的內(nèi)容會(huì)被清空，同時(shí)這些數(shù)據(jù)會(huì)被寫入到 binlog files 中；因?yàn)?bin log內(nèi)容無論多大在事務(wù)提交時(shí)都需要一次性寫入，所以當(dāng) bin log cache放不下的時(shí)候，就需要暫存到磁盤，然后提交被寫入到 binlog files。

參數(shù) binlog_cache_size:控制單個(gè)線程內(nèi) binlog cache 所占內(nèi)存的大小

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

• write首先會(huì)寫入 page cache 中的 binlog files 中，page cache 就是一塊內(nèi)存。（不占用磁盤IOPS）
• fsync然后操作系統(tǒng)執(zhí)行 fsync 時(shí) bin log 才會(huì)從 page cache 中真正持久化到磁盤。（占用磁盤IOPS）

write 和 fsync寫入時(shí)機(jī)

關(guān)于write和fsync 的時(shí)機(jī)，是通過參數(shù) sync_binlog 控制：

• sync_binlog=0：表示每次提交事務(wù)只是 write，不執(zhí)行 fsync，也就是binlog不做持久化。（不建議）
• sync_binlog=1：表示每次提交事務(wù)都要發(fā)生 fsync。
• sync_binlog=N：表示每次事務(wù)都會(huì)write，但是N次事務(wù)提交會(huì)執(zhí)行fsync進(jìn)行持久化。

通常來說，為了提高IOPS，會(huì)將這個(gè)參數(shù)設(shè)為100-1000。缺點(diǎn)是如果還沒有執(zhí)行fsync就宕機(jī)，最多會(huì)丟失最近N個(gè)事務(wù)的binlog日志。如果為了保證數(shù)據(jù)安全，就設(shè)為1。

如果binlog內(nèi)容缺失怎么辦？

對(duì)于通過 redo log 可以恢復(fù)數(shù)據(jù)，我們都已經(jīng)知道了，但是如果 binlog 內(nèi)容缺失呢？

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

這就產(chǎn)生了一種可能，如果發(fā)生mysql服務(wù)器了crash，此時(shí)redolog已經(jīng)做持久化，但是binlog還有事務(wù)仍保存在page cache中，沒有來得及執(zhí)行fsync。宕機(jī)后重啟服務(wù)，依賴redolog恢復(fù)數(shù)據(jù)，就會(huì)出現(xiàn)binlog 長(zhǎng)度比真實(shí)數(shù)據(jù)所應(yīng)該需要的長(zhǎng)度短的情況。

面對(duì)數(shù)據(jù)完整但是binlog缺失，這種情況下會(huì)出現(xiàn)什么異樣呢？

如果 MySQL 服務(wù)器在崩潰恢復(fù)時(shí)發(fā)現(xiàn)二進(jìn)制日志比應(yīng)有的短，則它至少缺少一個(gè)成功提交的 InnoDB 事務(wù)。如果 sync_binlog=1 并且磁盤/文件系統(tǒng)在請(qǐng)求時(shí)進(jìn)行實(shí)際同步（有些則沒有），則不會(huì)發(fā)生這種情況，所以服務(wù)器打印一條錯(cuò)誤消息日志。在這種情況下，此二進(jìn)制日志不正確，應(yīng)從源數(shù)據(jù)的新快照重新啟動(dòng)復(fù)制。

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

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

如何使用binlog恢復(fù)歷史數(shù)據(jù)？

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

眾所周知，我們可以通過 binlog 恢復(fù)任意時(shí)間點(diǎn)的數(shù)據(jù)，如何做到呢？

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

假定場(chǎng)景：比如今天中午12點(diǎn)有一次誤刪表數(shù)據(jù)，需要找回?cái)?shù)據(jù)，如何做？

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

這樣臨時(shí)庫就到達(dá)了誤刪數(shù)據(jù)之前的狀態(tài)，然后可以把表數(shù)據(jù)從臨時(shí)庫取出來，按需求恢復(fù)到線上正式數(shù)據(jù)庫。