完全掌握MySQL三大日志binlog、redo log和undo log

本篇文章給大家?guī)?lái)了關(guān)于mysql日志的相關(guān)知識(shí)，我們重點(diǎn)需要關(guān)注的是二進(jìn)制日志(binlog)和事務(wù)日志(包括redo log和undo log)，希望對(duì)大家有幫助。

1、binlog

binlog用于記錄數(shù)據(jù)庫(kù)執(zhí)行的寫入性操作(不包括查詢)信息，以二進(jìn)制的形式保存在磁盤中。binlog是mysql的邏輯日志，并且由Server層進(jìn)行記錄，使用任何存儲(chǔ)引擎的mysql數(shù)據(jù)庫(kù)都會(huì)記錄binlog日志。

• 邏輯日志：可以簡(jiǎn)單得理解為sql語(yǔ)句；
• 物理日志：MySQL中數(shù)據(jù)都是保存在數(shù)據(jù)頁(yè)中的，物理日志記錄的是數(shù)據(jù)頁(yè)上的變更；在這里插入代碼片

binlog是通過(guò)追加的方式進(jìn)行寫入的，可以通過(guò)max_binlog_size參數(shù)設(shè)置每個(gè)binlog文件的大小，當(dāng)文件大小達(dá)到給定值之后，會(huì)生成新的文件來(lái)保存日志。

binlog使用場(chǎng)景
項(xiàng)目 在實(shí)際應(yīng)用中，binlog的主要使用場(chǎng)景有兩個(gè)，分別是主從復(fù)制和數(shù)據(jù)恢復(fù)。

• 主從復(fù)制：在Master端開啟binlog，然后將binlog發(fā)送到各個(gè)Slave端，Slave端重放binlog從而達(dá)到主從數(shù)據(jù)一致。
• 數(shù)據(jù)恢復(fù)：通過(guò)使用mysqlbinlog工具來(lái)恢復(fù)數(shù)據(jù)。

MySQL主從同步原理

• 主節(jié)點(diǎn) binlog dump 線程
  當(dāng)從節(jié)點(diǎn)連接主節(jié)點(diǎn)時(shí)，主節(jié)點(diǎn)會(huì)創(chuàng)建一個(gè)log dump 線程，用于發(fā)送binlog的內(nèi)容。在讀取binlog中的操作時(shí)，此線程會(huì)對(duì)主節(jié)點(diǎn)上的binlog加鎖，當(dāng)讀取完成，甚至在發(fā)動(dòng)給從節(jié)點(diǎn)之前，鎖會(huì)被釋放；
• 從節(jié)點(diǎn)I/O線程
  當(dāng)從節(jié)點(diǎn)上執(zhí)行start slave命令之后，從節(jié)點(diǎn)會(huì)創(chuàng)建一個(gè)I/O線程用來(lái)連接主節(jié)點(diǎn)，請(qǐng)求主庫(kù)中更新的binlog。I/O線程接收到主節(jié)點(diǎn)binlog dump進(jìn)程發(fā)來(lái)的更新之后，保存在本地relaylog中；
• 從節(jié)點(diǎn)SQL線程
  SQL線程負(fù)責(zé)讀取relaylog中的內(nèi)容，解析成具體的操作并執(zhí)行，最終保證主從數(shù)據(jù)的一致性；
  MySQL 數(shù)據(jù)庫(kù)主從同步原理

binlog的內(nèi)容
上面說(shuō)了，binlog是一種邏輯日志，可以簡(jiǎn)單得理解為sql語(yǔ)句，但是實(shí)際上還包含著執(zhí)行的sql語(yǔ)句的反向邏輯。delete對(duì)應(yīng)著delete本身以及反向的insert信息；update包含著對(duì)應(yīng)的update執(zhí)行前后數(shù)據(jù)行的相關(guān)信息；insert包含自身的insert以及對(duì)應(yīng)的delete信息。

binlog的格式
binlog共有三種格式，分別是statement、row以及mixed。MySQL 5.7.7版本之前默認(rèn)使用的是statement，MySQL 5.7.7之后默認(rèn)使用的是row。日志的格式可以通過(guò)my.ini配置文件中的binlog-format來(lái)修改。
(1)statement：基于sql語(yǔ)句的復(fù)制（statement-based replication,SBR），每一條修改數(shù)據(jù)的sql語(yǔ)句都會(huì)記錄到binlog中。

• 優(yōu)點(diǎn)：不需要具體記錄某一行的變化，節(jié)約空間，減少io，提高性能；
• 缺點(diǎn)：在執(zhí)行sysdate()或者sleep()等操作的時(shí)候，可能導(dǎo)致主從數(shù)據(jù)不一致的情況；

(2)row：基于行記錄的復(fù)制（row-based replication,RBR），不記錄sql語(yǔ)句上下文相關(guān)信息，而是記錄哪條記錄被修改的細(xì)節(jié)。

• 優(yōu)點(diǎn)：非常詳細(xì)地記錄每一行記錄修改的細(xì)節(jié)，因而不會(huì)出現(xiàn)數(shù)據(jù)無(wú)法被正確復(fù)制的情況；
• 缺點(diǎn)：由于會(huì)非常詳細(xì)地記錄每一條記錄修改的細(xì)節(jié)，這樣會(huì)產(chǎn)生大量的日志內(nèi)容。假設(shè)現(xiàn)在有一條update語(yǔ)句，修改了很多條記錄，則每條修改記錄都會(huì)記錄到binlog中。特別地，alter table這個(gè)操作，由于表結(jié)構(gòu)的變化，每行記錄都會(huì)發(fā)生變化，導(dǎo)致日志量暴增；

(3)mixed：根據(jù)上面所說(shuō)的，statement和row各有優(yōu)缺點(diǎn)，因此出現(xiàn)了mixed這個(gè)版本，將這二者進(jìn)行混合。一般情況下使用statement格式來(lái)進(jìn)行保存，當(dāng)遇到statement無(wú)法解決時(shí)，切換為row格式來(lái)進(jìn)行保存。
特別地，上面說(shuō)了，新版本（MySQL 5.7.7之后）默認(rèn)使用的row格式，這里的row也做了相應(yīng)的優(yōu)化，在遇到alter table這個(gè)操作時(shí)采用statement格式進(jìn)行記錄，其余操作仍然使用row格式。

binlog刷盤時(shí)機(jī)

對(duì)于InnoDB存儲(chǔ)引擎來(lái)說(shuō)，只有在事務(wù)提交的時(shí)候才會(huì)記錄binlog，此時(shí)記錄還在內(nèi)存中，MySQL通過(guò)sync_binlog來(lái)控制binlog的刷盤時(shí)機(jī)，取值范圍為0-N：

• 0：不強(qiáng)制刷到磁盤，由系統(tǒng)自行判斷何時(shí)寫入磁盤中；
• 1：每次提交后都要將binlog寫入磁盤中；
• N：每N個(gè)事務(wù)，才會(huì)將binlog寫入磁盤中；

從上面可以看出，sync_binlog最安全的是設(shè)置是1，這也是MySQL 5.7.7之后版本的默認(rèn)值。但是設(shè)置一個(gè)大一些的值可以提升數(shù)據(jù)庫(kù)性能，因此實(shí)際情況下也可以將值適當(dāng)調(diào)大，犧牲一定的一致性來(lái)獲取更好的性能。

binlog的物理文件大小

在my.ini配置文件中，可以通過(guò)max_binlog_size來(lái)配置binlog的大小。當(dāng)日志量超過(guò)binlog文件的大小時(shí)，系統(tǒng)會(huì)重新生成一個(gè)新的文件來(lái)繼續(xù)保存文件。當(dāng)一個(gè)事務(wù)比較大時(shí)，或者是當(dāng)日志越來(lái)越多的時(shí)候，此時(shí)占據(jù)的物理空間太大怎么辦？MySQL提供了一種自動(dòng)刪除的機(jī)制，還是在my.ini配置文件中，可以通過(guò)配置expire_logs_days這個(gè)參數(shù)來(lái)解決，單位為天。當(dāng)這個(gè)參數(shù)為0，表示永不刪除；為N時(shí)，表示第N天后自動(dòng)刪除。

2、redo log

redolog是InnoDB引擎專有的日志系統(tǒng)。主要是用來(lái)實(shí)現(xiàn)事務(wù)的持久性以及實(shí)現(xiàn)crash-safe功能。redolog屬于物理日志，記錄的是sql語(yǔ)句執(zhí)行之后數(shù)據(jù)頁(yè)上的具體修改內(nèi)容。
我們都知道，當(dāng)MySQL運(yùn)行的時(shí)候，會(huì)將數(shù)據(jù)從磁盤中加載到內(nèi)存當(dāng)中。當(dāng)執(zhí)行sql語(yǔ)句對(duì)數(shù)據(jù)進(jìn)行修改時(shí)，修改后的內(nèi)容其實(shí)都只是暫時(shí)保存到內(nèi)存當(dāng)中，如果此時(shí)斷電或者出現(xiàn)其他情況，這些修改就會(huì)丟失。因而，當(dāng)修改完數(shù)據(jù)之后，MySQL會(huì)尋找機(jī)會(huì)將這些內(nèi)存中的記錄刷回到磁盤當(dāng)中。但這就出現(xiàn)一個(gè)性能問題，主要有兩個(gè)方面：

InnoDB中是以頁(yè)為數(shù)據(jù)單位與磁盤進(jìn)行交互的，而一個(gè)事務(wù)很可能只是修改了一個(gè)頁(yè)上的幾個(gè)字節(jié)，如果將一個(gè)完整的數(shù)據(jù)頁(yè)刷回磁盤當(dāng)中，浪費(fèi)資源；

一個(gè)事務(wù)可能涉及到多個(gè)數(shù)據(jù)頁(yè)，這些數(shù)據(jù)頁(yè)只是邏輯上連續(xù)，在物理上并不連續(xù)，使用隨機(jī)IO性能太差；

因此，MySQL設(shè)計(jì)了redolog來(lái)記錄事務(wù)對(duì)數(shù)據(jù)頁(yè)具體做了哪些修改，之后將redolog再刷回磁盤當(dāng)中。你可能會(huì)有疑惑，本來(lái)就是想減少io，這不又加上一次io么？InnoDB的設(shè)計(jì)者在設(shè)計(jì)之初就已經(jīng)考慮到了這些。redolog文件一般都比較小，且在刷回磁盤的過(guò)程中是順序io，相比于隨機(jī)io來(lái)說(shuō)，性能更好。

redo log基本概念
redolog由兩部分組成，一個(gè)是內(nèi)存中的日志緩存redo log buffer，一個(gè)是磁盤中的日志文件redo log file。當(dāng)每次對(duì)數(shù)據(jù)記錄進(jìn)行修改的時(shí)候，都會(huì)將這些修改內(nèi)容先寫入redo log buffer中，后續(xù)等待合適的時(shí)機(jī)將內(nèi)存中的修改刷回到redo log file中。這種先寫日志，再寫磁盤的技術(shù)就是WAL(Write-Ahead Logging)技術(shù)。需要注意的是redolog比數(shù)據(jù)頁(yè)先刷回磁盤，聚簇索引，二級(jí)索引，undo頁(yè)面的修改，均需要記錄redolog。

在計(jì)算機(jī)操作系統(tǒng)中，用戶空間(user space)下的緩沖區(qū)數(shù)據(jù)一般情況下是無(wú)法直接寫入磁盤的，中間必須經(jīng)過(guò)操作系統(tǒng)內(nèi)核空間(kernel space)緩沖區(qū)(OS Buffer)。因此，redo log buffer寫入redo log file實(shí)際上是先寫入OS Buffer，然后再通過(guò)系統(tǒng)調(diào)用fsync()將其刷到redo log file中，過(guò)程如下：

mysql支持三種將redo log buffer寫入redo log file的時(shí)機(jī)，可以通過(guò)innodb_flush_log_at_trx_commit參數(shù)配置，各參數(shù)值含義如下：

redo log記錄形式
redolog采用固定大小，循環(huán)寫入的格式，當(dāng)redolog寫滿之后，會(huì)重新從頭開始寫。為什么這么設(shè)計(jì)呢？
redo log存在的意義主要就是降低對(duì)數(shù)據(jù)頁(yè)刷盤的要求。redolog記錄了數(shù)據(jù)頁(yè)上的修改，但是當(dāng)數(shù)據(jù)頁(yè)也刷回到磁盤后，這些記錄就失去作用了。因此當(dāng)MySQL判斷之前的redolog已經(jīng)失去作用之后，新數(shù)據(jù)會(huì)將這些失效的數(shù)據(jù)進(jìn)行覆蓋。那如何判斷該不該進(jìn)行覆蓋呢？

上圖是redo log file的示意圖，write pos表示redolog當(dāng)前記錄的日志序列號(hào)LSN(log sequence number)。當(dāng)數(shù)據(jù)頁(yè)也已經(jīng)刷回磁盤之后，會(huì)更新redo log file中的LSN，表示到這個(gè)LSN之前的數(shù)據(jù)已經(jīng)落盤，這個(gè)LSN就是check point。write pos到check point之間的部分是redolog空余的部分，用于記錄新的記錄；check point到write pos之間是redolog已經(jīng)記錄的數(shù)據(jù)頁(yè)修改部分，但此時(shí)數(shù)據(jù)頁(yè)還未刷回磁盤的部分。當(dāng)write pos追上check point時(shí)，會(huì)先推動(dòng)check point向前移動(dòng)，空出位置再記錄新的日志。

啟動(dòng)innodb的時(shí)候，不管上次是正常關(guān)閉還是異常關(guān)閉，總是會(huì)進(jìn)行恢復(fù)操作?；謴?fù)時(shí)，會(huì)先檢查數(shù)據(jù)頁(yè)中的LSN，如果這個(gè)LSN小于redolog中的LSN，即write pos位置，說(shuō)明在redolog上記錄著數(shù)據(jù)頁(yè)上尚未完成的操作，接著就會(huì)從最近的一個(gè)check point出發(fā)，開始同步數(shù)據(jù)。

那有沒有可能數(shù)據(jù)頁(yè)中的LSN大于redolog中的LSN呢？答案是當(dāng)然可能。出現(xiàn)這種情況時(shí)，這時(shí)超出redolog的部分將不會(huì)重做，因?yàn)檫@本身就表示已經(jīng)做過(guò)的事情，無(wú)需再重做。
redo log與binlog區(qū)別

由binlog和redo log的區(qū)別可知：binlog日志只用于歸檔，只依靠binlog是沒有crash-safe能力的。但只有redo log也不行，因?yàn)閞edo log是InnoDB特有的，且日志上的記錄落盤后會(huì)被覆蓋掉。因此需要binlog和redo log二者同時(shí)記錄，才能保證當(dāng)數(shù)據(jù)庫(kù)發(fā)生宕機(jī)重啟時(shí)，數(shù)據(jù)不會(huì)丟失。
兩階段提交
上面簡(jiǎn)單介紹了redolog和binlog，在對(duì)數(shù)據(jù)進(jìn)行修改時(shí)，他們都會(huì)對(duì)這些修改進(jìn)行保存落地，只是一個(gè)是物理日志，一個(gè)是邏輯日志。那他倆具體在修改過(guò)程中是如何執(zhí)行的呢？

假設(shè)現(xiàn)在有一條update語(yǔ)句要執(zhí)行，update from table_name set c=c+1 where id=2，執(zhí)行流程如下：

• 先定位到id=2這一條記錄；
• 執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上 1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)；
• 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到redolog里面，此時(shí) redolog 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)；
• 執(zhí)行器生成這個(gè)操作的 binlog，并把binlog寫入磁盤；
• 執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成；

示意圖如下所示：

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

redolog 和binlog都可以用于表示事務(wù)的提交狀態(tài)，而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致。如果不使用兩階段提交，而是先寫其中一個(gè)再寫另外一個(gè)可能會(huì)帶來(lái)一些問題。

此時(shí)還是使用update來(lái)舉例。假設(shè)當(dāng)前id=2，有一個(gè)字段c=0，分別分析以下情況：
先寫redolog再寫binlog
假設(shè)先寫redolog，當(dāng)redolog寫完，但是binlog還未寫完的時(shí)候，此時(shí)MySQL突然出現(xiàn)異常導(dǎo)致重啟。由于之前redolog已經(jīng)寫完，系統(tǒng)重啟后，修改的記錄仍然存在，所以恢復(fù)后這一行 c 的值是 1。但由于系統(tǒng)重啟，binlog中并未有這條記錄。之后備份日志的時(shí)候，存起來(lái)的binlog里面就沒有這條語(yǔ)句。然后你會(huì)發(fā)現(xiàn)，如果需要用這個(gè) binlog 來(lái)恢復(fù)臨時(shí)庫(kù)的話，由于這個(gè)語(yǔ)句的binlog丟失，這個(gè)臨時(shí)庫(kù)就會(huì)少了這一次更新，恢復(fù)出來(lái)的這一行 c 的值就是 0，與原庫(kù)的值不同。
先寫binlog再寫redolog
假如先寫binlog，然后寫redolog的時(shí)候系統(tǒng)重啟。重啟之后，redolog中沒有對(duì)c進(jìn)行修改的記錄，此時(shí)c的值還是0。但是 binlog里面已經(jīng)記錄了“把 c 從 0 改成 1”這個(gè)日志。所以，在之后用 binlog來(lái)恢復(fù)的時(shí)候就多了一個(gè)事務(wù)出來(lái)，恢復(fù)出來(lái)的這一行 c 的值就是 1，與原庫(kù)的值不同。

因此，綜上所述，如果是先寫某一個(gè)日志再寫另一個(gè)日志，就會(huì)出現(xiàn)數(shù)據(jù)庫(kù)的狀態(tài)與使用binlog恢復(fù)出來(lái)的庫(kù)的狀態(tài)不一致的情況。

3、undo log

undolog主要用來(lái)記錄某條行記錄被修改之前的狀態(tài)，記錄的是修改前的數(shù)據(jù)。這樣的話，當(dāng)事務(wù)進(jìn)行回滾時(shí)，就可以通過(guò)undolog將記錄恢復(fù)到事務(wù)開始前的樣子。事務(wù)的原子性和持久性也是依靠undolog來(lái)實(shí)現(xiàn)的。undo log主要記錄了數(shù)據(jù)的邏輯變化，比如一條INSERT語(yǔ)句，對(duì)應(yīng)一條DELETE的undo log，對(duì)于每個(gè)UPDATE語(yǔ)句，對(duì)應(yīng)一條相反的UPDATE的undo log，這樣在發(fā)生錯(cuò)誤時(shí)，就能回滾到事務(wù)之前的數(shù)據(jù)狀態(tài)。同時(shí)，在進(jìn)行數(shù)據(jù)恢復(fù)的時(shí)候，與binlog，redolog結(jié)合使用，保證了數(shù)據(jù)恢復(fù)的正確性。

undolog的作用流程如下所示：

• 在事務(wù)開始之前將修改前的版本寫入到undo log中；
• 開始進(jìn)行修改，將修改過(guò)的數(shù)據(jù)保存到內(nèi)存當(dāng)中；
• 將undolog持久化到磁盤當(dāng)中；
• 將數(shù)據(jù)頁(yè)刷回到磁盤當(dāng)中；
• 事務(wù)提交；

需要注意的是，與redolog一樣，undolog也是要先于數(shù)據(jù)頁(yè)刷回到磁盤當(dāng)中。在恢復(fù)數(shù)據(jù)時(shí)，如果undolog是完整的，可以根據(jù)undolog來(lái)回滾事務(wù)。

在一個(gè)事務(wù)當(dāng)中，可能會(huì)對(duì)同一條數(shù)據(jù)進(jìn)行多次修改，那么是不是每一次修改前的記錄都要記錄到undolog中呢？這樣的話，會(huì)導(dǎo)致undolog日志量太大，此時(shí)redolog就要上場(chǎng)了。在一個(gè)事務(wù)當(dāng)中，如果是對(duì)同一條記錄進(jìn)行修改，undolog只會(huì)記錄事務(wù)開始前的原始記錄，當(dāng)再次對(duì)這條記錄進(jìn)行修改時(shí)，redolog會(huì)記錄后續(xù)的變化。在數(shù)據(jù)恢復(fù)時(shí)，redolog完成前滾，undolog完成回滾，二者相互協(xié)調(diào)完成數(shù)據(jù)的恢復(fù)。過(guò)程如下所示：

還有一個(gè)功能就是MVCC多版本控制鏈了，這個(gè)請(qǐng)參考這篇文章
MySQL之MVCC實(shí)現(xiàn)原理

binlog，redolog和undolog是MySQL中最重要的三個(gè)日志，在進(jìn)行數(shù)據(jù)恢復(fù)時(shí)，三者進(jìn)行協(xié)調(diào)合作，保證數(shù)據(jù)恢復(fù)的正確性。

推薦學(xué)習(xí)：mysql視頻教程