MySQL專欄學習系列，這個專欄我不會奢望多少人會看，只是記錄下來，希望可以幫到那些需要的人。

看到這個題目是不是覺得資料庫再也不用擔心伺服器 crash 了？

那我們需要學習為什麼可以這麼做？以及如何做？

即為什麼可以恢復到任意時間點？如何恢復到任意時間點？

為什麼有了 binlog 還需要 redo log？

事務是如何提交的？事務提交先寫 binlog 還是 redo log？如何保證這兩部分的日誌做到順序一致性？

上一次課我們學習了一條 select 語句的全部執行過程，那麼今天我們就從一條 update 語句開始。

mysql> update T set c=c+1 where ID=2;

其實執行流程和查詢流程一致，只是最後執行器執行的是找到這條資料，並進行更新。

另外，更新過程還涉及到一個重要的日誌模組，即 redo log（重做日誌）和 binlog（歸檔日誌）。

我個人是隻聽過 binlog 的。

1 redo log

和大多數關係型資料庫一樣，InnoDB 記錄了對資料檔案的物理更改，並保證總是日誌先行。

也就是所謂的 WAL（Write-Ahead Logging），即在持久化資料檔案前，保證之前的 redo log 已經寫到磁碟。

MySQL 的每一次更新並沒有每次都寫入磁碟，InnoDB 引擎會先將記錄寫到 redo log 裡，並更新到記憶體中，然後再適當的時候，再把這個記錄更新到磁碟。

提到了兩個重要的日誌，我覺得這裡有必要貼一下 InnoDB 的儲存結構圖，對其有一個整體的認識：

如果下面看的各種空間懵逼了，建議回來看一眼這個圖。

1.1 redo log 是啥？

當資料庫對資料做修改的時候，需要把資料頁從磁碟讀到 buffer pool 中，然後在 buffer pool 中進行修改。

那麼這個時候 buffer pool 中的資料頁就與磁碟上的資料頁內容不一致，我們稱 buffer pool 的資料頁為 dirty page 髒資料。

感覺就像先複製一份資料，對複製的資料進行修改，修改完畢後再覆蓋到原資料。

這裡也可以看出，所有的更新操作都是現在 dirty page 中進行的。

如果這個時候發生非正常的 DB 服務重啟，那麼這些資料還沒在記憶體，並沒有同步到磁碟檔案中（注意，同步到磁碟檔案是個隨機 IO），也就是會發生資料丟失。

如果這個時候，能夠在有一個檔案，當 buffer pool 中的 dirty page 變更結束後，把相應修改記錄記錄到這個檔案（注意，記錄日誌是順序 IO）。

那麼當 DB 服務發生 crash 的情況，恢復 DB 的時候，也可以根據這個檔案的記錄內容，重新應用到磁碟檔案，資料保持一致。

這個檔案就是 redo log ，用於記錄資料修改後的記錄，順序記錄。

我理解的，redo log 就是存放 dirty page 的物理空間。

1.2 何時產生 & 釋放？

在事務開始之後就產生 redo log，redo log 的落盤並不是隨著事務的提交才寫入的，而是在事務的執行過程中，便開始寫入 redo log 檔案中。

當對應事務的髒頁寫入到磁碟之後，redo log 的使命也就完成了，重做日誌佔用的空間就可以重用（被覆蓋）。

1.3 如何寫？

redo log 檔案以 ib_logfile[number] 命名，並以順序的方式寫入檔案檔案，寫滿時則回溯到第一個檔案，進行覆蓋寫。

如圖所示：

• write pos 是當前記錄的位置，一邊寫一邊後移，寫到最後一個檔案末尾後就回到 0 號檔案開頭；

• checkpoint 是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄更新到資料檔案；

write pos 和 checkpoint 之間還空著的部分，可以用來記錄新的操作。

如果 write pos 追上 checkpoint，表示寫滿，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進一下。

redo log 檔案是迴圈寫入的，在覆蓋寫之前，總是要保證對應的髒頁已經刷到了磁碟。

在非常大的負載下，redo log 可能產生的速度非常快，導致頻繁的刷髒操作，進而導致效能下降。

如果可預期會有這樣的場景，我們建議調大 redo log 檔案的大小。可以做一次乾淨的 shutdown，然後修改 redo log 配置，重啟例項。

參考：

1.4 相關配置

預設情況下，對應的物理檔案位於資料庫的 data 目錄下的 ib_logfile1、ib_logfile2。

innodb_log_group_home_dir 指定日誌檔案組所在的路徑，預設./ ，表示在資料庫的資料目錄下。
innodb_log_files_in_group 指定重做日誌檔案組中檔案的數量，預設2
# 關於檔案的大小和數量，由一下兩個引數配置
innodb_log_file_size 重做日誌檔案的大小。
innodb_mirrored_log_groups 指定了日誌映象檔案組的數量，預設1

1.5 其他

redo log 有一個快取區 Innodb_log_buffer，預設大小為 8M，Innodb 儲存引擎先將重做日誌寫入 innodb_log_buffer 中。

然後會透過以下三種方式將 innodb 日誌緩衝區的日誌重新整理到磁碟：

1、Master Thread 每秒一次執行重新整理 Innodb_log_buffer 到重做日誌檔案；
2、每個事務提交時會將重做日誌重新整理到重做日誌檔案；
3、當 redo log 快取可用空間少於一半時，重做日誌快取被重新整理到重做日誌檔案；

有了 redo log，InnoDB 就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為 crash-safe。

CrashSafe 能夠保證 MySQL 伺服器當機重啟後：

• 所有已經提交的事務的資料仍然存在

• 所有沒有提交的事務的資料自動回滾

---

2 binlog

如前文所講，MySQL 整體可以分為 Server 層和引擎層。

其實，redo log 是屬於引擎層的 InnoDB 所特有的日誌，而 Server 層也有自己的日誌，即 binlog（歸檔日誌）。

2.1 binlog 是什麼？

邏輯格式的日誌，可以簡單認為就是執行過的事務中的 sql 語句。

但又不完全是 sql 語句這麼簡單，而是包括了執行的 sql 語句（增刪改）反向的資訊。

也就意味著 delete 對應著 delete 本身和其反向的 insert；update 對應著 update 執行前後的版本的資訊；insert 對應著 delete 和 insert 本身的資訊。

2.2 何時產生 & 釋放？

事務提交的時候，一次性將事務中的 sql 語句按照一定的格式記錄到 binlog 中。因此，對於較大事務的提交，可能會變得比較慢一些。

binlog 的預設是保持時間由引數 expire_logs_days 配置，也就是說對於非活動的日誌檔案，在生成時間超過配置的天數之後，會被自動刪除。

2.3 和 redo log 的區別

1、redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 層實現，所有引擎都可以使用；

2、內容不同：redo log 是物理日誌，記錄的是在資料頁上做了什麼修改,是正在執行中的 dml 以及 ddl 語句；

而 binlog 是邏輯日誌，記錄的是語句的原始邏輯，已經提交完畢之後的 dml 以及 ddl sql 語句，如「給 ID=2 的這一行的 c 欄位加 1」；

3、寫方式不同：redo log 是迴圈寫的，空間固定；binlog 是可以一直追加寫的，一個檔案寫到一定大小後，會繼續寫下一個，之前寫的檔案不會被覆蓋；

4、作用不同：redo log 主要用來保證事務安全，作為異常 down 機或者介質故障後的資料恢復使用，binlog 主要用來做主從複製和即時點恢復時使用；

5、另外，兩者日誌產生的時間，可以釋放的時間，在可釋放的情況下清理機制，都是完全不同的。

參考：

---

3 資料更新事務流程

有了對這兩個日誌的概念性理解，我們再來看執行器和 InnoDB 引擎在執行這個簡單的 update 語句時的內部流程。

1、執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜尋找到這一行。如果 ID=2 這一行所在的資料頁本來就在記憶體中，就直接返回給執行器；否則，需要先從磁碟讀入記憶體，然後再返回。

對應到上面講的，就是將資料載入到髒資料中。

2、執行器拿到引擎給的行資料，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行資料，再呼叫引擎介面寫入這行新資料。

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

4、執行器生成這個操作的 binlog，並把 binlog 寫入磁碟；

5、執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的 redo log 改成提交（commit）狀態，更新完成。

兩階段提交

上面處理 redo log 和 binlog 看著是不是有點懵逼？

其實這就是所謂的兩階段提交，即 COMMIT 會被自動的分成 prepare 和 commit 兩個階段。

MySQL 在 prepare 階段會生成 xid，然後會在 commit 階段寫入到 binlog 中。在進行恢復時事務要提交還是回滾，是由 Binlog 來決定的。

由上面的二階段提交流程可以看出，透過兩階段提交方式保證了無論在任何情況下，事務要麼同時存在於 redo log 和 binlog 中，要麼兩個裡面都不存在。

這樣就可以保證事務的 binlog 和 redo log 順序一致性。一旦階段 2 中持久化 binlog 完成，就確保了事務的提交。

此外需要注意的是，每個階段都需要進行一次 fsync 操作才能保證上下兩層資料的一致性。

PS：記錄 Binlog 是在 InnoDB 引擎 Prepare（即 Redo Log 寫入磁碟）之後，這點至關重要。

另外需要注意的一點就是，SQL 語句產生的 Redo 日誌會一直重新整理到磁碟（master thread 每秒 fsync redo log），而 Binlog 是事務 commit 時才重新整理到磁碟，如果 binlog 太大則 commit 時會慢。

參考：

舉個例子

Bin log 用於記錄了完整的邏輯記錄，所有的邏輯記錄在 bin log 裡都能找到，所以在備份恢復時，是以 bin log 為基礎，透過其記錄的完整邏輯操作，備份出一個和原庫完整的資料。 

如 redo log 執行了 update t set status = 1，此時原庫的資料 status 已更新為 1，而 bin log 寫入失敗，沒有記錄這一操作，後續備份恢復時，其 status = 0，導致資料不一致）。 

其核心就是， redo log 記錄的，即使異常重啟，都會重新整理到磁碟，而 bin log 記錄的， 則主要資料庫邏輯操作，主要用於備份恢復。

一個完整的交易過程：

賬本記上賣一瓶可樂（redo log 為 prepare 狀態），然後收錢放入錢箱（bin log 記錄）然後回過頭在賬本上打個勾（redo log 置為commit），給人可樂，表示一筆交易結束。 

如果收錢時交易被打斷，回過頭來整理此次交易，發現只有記賬沒有收錢，則交易失敗，刪掉賬本上的記錄（回滾）；

如果收了錢後被終止，然後回過頭發現賬本有記錄（prepare）而且錢箱有本次收入（bin log），則繼續完善賬本（commit），本次交易有效。

4 如何恢復任意時間的資料？

當需要恢復到指定的某一秒時，比如 2018.11.23 14.23.45 有一次資料庫誤操作，需要找回資料，那你可以這麼做：

1、首先，找到最近的一次全量備份，如果你運氣好，可能就是昨天晚上 11.22 日的一個備份，從這個備份恢復到臨時庫；

2、然後，從備份的時間點開始，將備份的 binlog 依次取出來，重放到中午誤刪表之前的那個時刻。

這樣你的臨時庫就跟誤刪之前的線上庫一樣了，然後你可以把表資料從臨時庫取出來，按需要恢復到線上庫去。

當遇到 crash 時，恢復的過程也非常簡單：

1、恢復過程中會掃描最後一個 binlog 檔案，提取其中的 xid； 

2、重做檢查點以後的 redo 日誌，蒐集處於 prepare 階段的事務連結串列，將事務的 xid 與 binlog 中的 xid 對比。

若存在，說明事務記錄到 binlog 成功，但是最終未 commit 成功，則提交，否則就回滾；

這裡要結合上面的兩段提交一起看，才能理解得比較透徹。

總結一下，基本頂多會出現下面是幾種情況：

• 當事務在 prepare 階段 crash，資料庫 recovery 的時候該事務未寫入 binlog 並且 redo log 未提交，將該事務 rollback。

• 當事務在 binlog 階段 crash，此時日誌還沒有成功寫入到磁碟中，啟動時會 rollback 此事務。

• 當事務在 binlog 日誌已經 fsync 到磁碟後 crash，但是 InnoDB 沒有來得及 commit，此時 MySQL 資料庫 recovery 的時候將會讀出 binlog 中的 xid，然後告訴 InnoDB 提交這些 xid 的事務，InnoDB 提交完這些事務後會回滾其它的事務，使 redo log 和 binlog 始終保持一致。

我再來說下自己的理解

1、prepare 階段； 2、寫binlog 階段；3、commit 階段；

當在2之前崩潰時

重啟恢復：後發現沒有commit，回滾。

備份恢復：沒有 binlog 。一致。

當在3之前崩潰

重啟恢復：雖沒有commit，但滿足prepare和binlog完整，所以重啟後會自動commit。

備份：有binlog，一致。

總結起來說就是如果一個事務在 prepare 階段中落盤成功，並在 MySQL Server 層中的 binlog 也寫入成功，那這個事務必定 commit 成功。

總結

介紹了 MySQL 裡面最重要的兩個日誌，即物理日誌 redo log 和邏輯日誌 binlog。

最好能夠理解這兩種日誌的作用分別是什麼，自己能夠理清楚事物的提交流程。

課後題目 & 評論區精華

這次評論區精彩有點多！

問

課後題目

前面我說到定期全量備份的週期“取決於系統重要性，有的是一天一備，有的是一週一備”。那麼在什麼場景下，一天一備會比一週一備更有優勢呢？或者說，它影響了這個資料庫系統的哪個指標？

答1

備份時間週期的長短，感覺有2個方面

首先，是恢復資料丟失的時間，既然需要恢復，肯定是資料丟失了。如果一天一備份的話，只要找到這天的全備，加入這天某段時間的binlog來恢復，如果一週一備份，假設是週一，而你要恢復的資料是週日某個時間點，那就，需要全備+週一到週日某個時間點的全部binlog用來恢復，時間相比前者需要增加很多；看業務能忍受的程度

其次，是資料庫丟失，如果一週一備份的話，需要確保整個一週的binlog都完好無損，否則將無法恢復；而一天一備，只要保證這天的binlog都完好無損；當然這個可以透過校驗，或者冗餘等技術來實現，相比之下，上面那點更重要

答2

備份資料庫的週期直接影響到了恢復的速度，一天一備的話，恢復時只需要重新執行最近一天的資料庫修改操作。而一週一備則需要做很多。所以在對於系統恢復速度很敏感的系統，最好使用一天一備，甚至一小時一備等等。

答3

我理解備份就是救命藥加後悔藥，災難發生的時候備份能救命，出現錯誤的時候備份能後悔。事情都有兩面性，沒有誰比誰好，只有誰比誰合適，完全看業務情況和需求而定。一天一備恢復時間更短，binlog更少，救命時候更快，但是後悔時間更短，而一週一備正好相反。我自己的備份策略是設定一個16小時延遲複製的從庫，充當後悔藥，恢復時間也較快。再兩天一個全備庫和binlog，作為救命藥,最後時刻用。這樣就比較兼顧了。

答4

1如果沒有主從，無Binlog Server，建議至少每天一備份，庫很小併發少，可以縮短備份週期，例如每小時備份一次。

2如果有主從，有Binlog Server，建議至少每週備份一次，庫較小併發不算高，可以縮短備份週期，例如每天備份一次。

這裡不區分有主從無Binlog Server的情況，是由於重要系統建議至少搭建主從複製，儘可能搭建Binlog Server(金融環境尤為重要)。

問

1.首先客戶端透過tcp/ip傳送一條sql語句到server層的SQL interface

2.SQL interface接到該請求後，先對該條語句進行解析，驗證許可權是否匹配

3.驗證透過以後，分析器會對該語句分析,是否語法有錯誤等

4.接下來是最佳化器器生成相應的執行計劃，選擇最優的執行計劃

5.之後會是執行器根據執行計劃執行這條語句。在這一步會去open table,如果該table上有MDL，則等待。

如果沒有，則加在該表上加短暫的MDL(S)

(如果opend_table太大,表明open_table_cache太小。需要不停的去開啟frm檔案)

6.進入到引擎層，首先會去innodb_buffer_pool裡的data dictionary(後設資料資訊)得到表資訊

7.透過後設資料資訊,去lock info裡查出是否會有相關的鎖資訊，並把這條update語句需要的鎖資訊寫入到lock info裡(鎖這裡還有待補充)

8.然後涉及到的老資料透過快照的方式儲存到innodb_buffer_pool裡的undo page裡,並且記錄undo log修改的redo

(如果data page裡有就直接載入到undo page裡，如果沒有，則需要去磁碟裡取出相應page的資料，載入到undo page裡)

9.在innodb_buffer_pool的data page做update操作。並把操作的物理資料頁修改記錄到redo log buffer裡

由於update這個事務會涉及到多個頁面的修改，所以redo log buffer裡會記錄多條頁面的修改資訊。

因為group commit的原因，這次事務所產生的redo log buffer可能會跟隨其它事務一同flush並且sync到磁碟上

10.同時修改的資訊，會按照event的格式,記錄到binlog_cache中。(這裡注意binlog_cache_size是transaction級別的,不是session級別的引數,

一旦commit之後，dump執行緒會從binlog_cache裡把event主動傳送給slave的I/O執行緒)

11.之後把這條sql,需要在二級索引上做的修改，寫入到change buffer page，等到下次有其他sql需要讀取該二級索引時，再去與二級索引做merge

(隨機I/O變為順序I/O,但是由於現在的磁碟都是SSD,所以對於定址來說,隨機I/O和順序I/O差距不大)

12.此時update語句已經完成，需要commit或者rollback。這裡討論commit的情況，並且雙1

13.commit操作，由於儲存引擎層與server層之間採用的是內部XA(保證兩個事務的一致性,這裡主要保證redo log和binlog的原子性),

所以提交分為prepare階段與commit階段

14.prepare階段,將事務的xid寫入，將binlog_cache裡的進行flush以及sync操作(大事務的話這步非常耗時)

15.commit階段，由於之前該事務產生的redo log已經sync到磁碟了。所以這步只是在redo log裡標記commit

16.當binlog和redo log都已經落盤以後，如果觸發了重新整理髒頁的操作，先把該髒頁複製到doublewrite buffer裡，把doublewrite buffer裡的重新整理到共享表空間，然後才是透過page cleaner執行緒把髒頁寫入到磁碟中

老師，你看我的步驟中有什麼問題嘛？我感覺第6步那裡有點問題,因為第5步已經去open table了，第6步還有沒有必要去buffer裡查詢後設資料呢?這後設資料是表示的系統的後設資料嘛,還是所有表的？謝謝老師指正

答

其實在實現上5是呼叫了6的過程了的，所以是一回事。MySQL server 層和InnoDB層都儲存了表結構，所以有書上描述時會拆開說。

這個描述很詳細，同時還有點到我們後面要講的內通

問

你好，關於提到的'資料頁'這個詞我沒有太理解，是一種儲存方式麼？

答

MySQL的記錄是以“頁”為單位存取的，預設大小16K。也就是說，你要訪問磁碟中一個記錄，不會只讀這個記錄，而會把它所在的16K資料一起讀入記憶體

問

請問用redolog恢復時還寫binlog嗎？反之呢？

答

崩潰恢復過程不寫binlog了，用binlog恢復例項（或搭建備庫）的時候，是會寫redolog的

原文連結：https://mp.weixin.qq.com/s/epFUcwfzt8rWu7Mk1ItZgg