MySQL怎么恢復(fù)到任意一秒的狀態(tài)

本篇內(nèi)容介紹了“MySQL怎么恢復(fù)到任意一秒的狀態(tài)”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

看到這個題目是不是覺得數(shù)據(jù)庫再也不用擔心服務(wù)器 crash 了？

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

即為什么可以恢復(fù)到任意時間點？如何恢復(fù)到任意時間點？

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

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

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

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

其實執(zhí)行流程和查詢流程一致，只是最后執(zhí)行器執(zhí)行的是找到這條數(shù)據(jù)，并進行更新。

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

我個人是只聽過 binlog 的。

1 redo log

和大多數(shù)關(guān)系型數(shù)據(jù)庫一樣，InnoDB 記錄了對數(shù)據(jù)文件的物理更改，并保證總是日志先行。

也就是所謂的 WAL（Write-Ahead Logging），即在持久化數(shù)據(jù)文件前，保證之前的 redo log 已經(jīng)寫到磁盤。

MySQL 的每一次更新并沒有每次都寫入磁盤，InnoDB 引擎會先將記錄寫到 redo log 里，并更新到內(nèi)存中，然后再適當?shù)臅r候，再把這個記錄更新到磁盤。

提到了兩個重要的日志，我覺得這里有必要貼一下 InnoDB 的存儲結(jié)構(gòu)圖，對其有一個整體的認識：

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

1.1 redo log 是啥？

當數(shù)據(jù)庫對數(shù)據(jù)做修改的時候，需要把數(shù)據(jù)頁從磁盤讀到 buffer pool 中，然后在 buffer pool 中進行修改。

那么這個時候 buffer pool 中的數(shù)據(jù)頁就與磁盤上的數(shù)據(jù)頁內(nèi)容不一致，我們稱 buffer pool 的數(shù)據(jù)頁為 dirty page 臟數(shù)據(jù)。

感覺就像先拷貝一份數(shù)據(jù)，對拷貝的數(shù)據(jù)進行修改，修改完畢后再覆蓋到原數(shù)據(jù)。

這里也可以看出，所有的更新操作都是現(xiàn)在 dirty page 中進行的。

如果這個時候發(fā)生非正常的 DB 服務(wù)重啟，那么這些數(shù)據(jù)還沒在內(nèi)存，并沒有同步到磁盤文件中（注意，同步到磁盤文件是個隨機 IO），也就是會發(fā)生數(shù)據(jù)丟失。

如果這個時候，能夠在有一個文件，當 buffer pool 中的 dirty page 變更結(jié)束后，把相應(yīng)修改記錄記錄到這個文件（注意，記錄日志是順序 IO）。

那么當 DB 服務(wù)發(fā)生 crash 的情況，恢復(fù) DB 的時候，也可以根據(jù)這個文件的記錄內(nèi)容，重新應(yīng)用到磁盤文件，數(shù)據(jù)保持一致。

這個文件就是 redo log ，用于記錄數(shù)據(jù)修改后的記錄，順序記錄。

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

1.2 何時產(chǎn)生 & 釋放？

在事務(wù)開始之后就產(chǎn)生 redo log，redo log 的落盤并不是隨著事務(wù)的提交才寫入的，而是在事務(wù)的執(zhí)行過程中，便開始寫入 redo log 文件中。

當對應(yīng)事務(wù)的臟頁寫入到磁盤之后，redo log 的使命也就完成了，重做日志占用的空間就可以重用（被覆蓋）。

1.3 如何寫？

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

如圖所示：

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

• checkpoint 是當前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件；

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

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

redo log 文件是循環(huán)寫入的，在覆蓋寫之前，總是要保證對應(yīng)的臟頁已經(jīng)刷到了磁盤。

在非常大的負載下，redo log 可能產(chǎn)生的速度非?？欤瑢е骂l繁的刷臟操作，進而導致性能下降。

如果可預(yù)期會有這樣的場景，我們建議調(diào)大 redo log 文件的大小?？梢宰鲆淮胃蓛舻?shutdown，然后修改 redo log 配置，重啟實例。

參考：
http://mysql.taobao.org/monthly/2015/05/01/

1.4 相關(guān)配置

默認情況下，對應(yīng)的物理文件位于數(shù)據(jù)庫的 data 目錄下的 ib_logfile1、ib_logfile2。

innodb_log_group_home_dir 指定日志文件組所在的路徑，默認./ ，表示在數(shù)據(jù)庫的數(shù)據(jù)目錄下。
innodb_log_files_in_group 指定重做日志文件組中文件的數(shù)量，默認2
# 關(guān)于文件的大小和數(shù)量，由一下兩個參數(shù)配置
innodb_log_file_size 重做日志文件的大小。
innodb_mirrored_log_groups 指定了日志鏡像文件組的數(shù)量，默認1

1.5 其他

redo log 有一個緩存區(qū) Innodb_log_buffer，默認大小為 8M，Innodb 存儲引擎先將重做日志寫入 innodb_log_buffer 中。

然后會通過以下三種方式將 innodb 日志緩沖區(qū)的日志刷新到磁盤：

1、Master Thread 每秒一次執(zhí)行刷新 Innodb_log_buffer 到重做日志文件；
2、每個事務(wù)提交時會將重做日志刷新到重做日志文件；
3、當 redo log 緩存可用空間少于一半時，重做日志緩存被刷新到重做日志文件；

有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會丟失，這個能力稱為 crash-safe。

CrashSafe 能夠保證 MySQL 服務(wù)器宕機重啟后：

• 所有已經(jīng)提交的事務(wù)的數(shù)據(jù)仍然存在

• 所有沒有提交的事務(wù)的數(shù)據(jù)自動回滾

2 binlog

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

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

2.1 binlog 是什么？

邏輯格式的日志，可以簡單認為就是執(zhí)行過的事務(wù)中的 sql 語句。

但又不完全是 sql 語句這么簡單，而是包括了執(zhí)行的 sql 語句（增刪改）反向的信息。

也就意味著 delete 對應(yīng)著 delete 本身和其反向的 insert；update 對應(yīng)著 update 執(zhí)行前后的版本的信息；insert 對應(yīng)著 delete 和 insert 本身的信息。

2.2 何時產(chǎn)生 & 釋放？

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

binlog 的默認是保持時間由參數(shù) expire_logs_days 配置，也就是說對于非活動的日志文件，在生成時間超過配置的天數(shù)之后，會被自動刪除。

2.3 和 redo log 的區(qū)別

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

2、內(nèi)容不同：redo log 是物理日志，記錄的是在數(shù)據(jù)頁上做了什么修改,是正在執(zhí)行中的 dml 以及 ddl 語句；

而 binlog 是邏輯日志，記錄的是語句的原始邏輯，已經(jīng)提交完畢之后的 dml 以及 ddl sql 語句，如「給 ID=2 的這一行的 c 字段加 1」；

3、寫方式不同：redo log 是循環(huán)寫的，空間固定；binlog 是可以一直追加寫的，一個文件寫到一定大小后，會繼續(xù)寫下一個，之前寫的文件不會被覆蓋；

4、作用不同：redo log 主要用來保證事務(wù)安全，作為異常 down 機或者介質(zhì)故障后的數(shù)據(jù)恢復(fù)使用，binlog 主要用來做主從復(fù)制和即時點恢復(fù)時使用；

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

參考：
http://www.importnew.com/28039.html

3 數(shù)據(jù)更新事務(wù)流程

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

1、執(zhí)行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。

對應(yīng)到上面講的，就是將數(shù)據(jù)加載到臟數(shù)據(jù)中。

2、執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。

3、引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到 redo log 里面，此時 redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務(wù)。

4、執(zhí)行器生成這個操作的 binlog，并把 binlog 寫入磁盤；

5、執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。

兩階段提交

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

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

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

由上面的二階段提交流程可以看出，通過兩階段提交方式保證了無論在任何情況下，事務(wù)要么同時存在于 redo log 和 binlog 中，要么兩個里面都不存在。

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

此外需要注意的是，每個階段都需要進行一次 fsync 操作才能保證上下兩層數(shù)據(jù)的一致性。

PS：記錄 Binlog 是在 InnoDB 引擎 Prepare（即 Redo Log 寫入磁盤）之后，這點至關(guān)重要。

另外需要注意的一點就是，SQL 語句產(chǎn)生的 Redo 日志會一直刷新到磁盤（master thread 每秒 fsync redo log），而 Binlog 是事務(wù) commit 時才刷新到磁盤，如果 binlog 太大則 commit 時會慢。

參考：
http://www.ywnds.com/?p=7892

舉個例子

Bin log 用于記錄了完整的邏輯記錄，所有的邏輯記錄在 bin log 里都能找到，所以在備份恢復(fù)時，是以 bin log 為基礎(chǔ)，通過其記錄的完整邏輯操作，備份出一個和原庫完整的數(shù)據(jù)。 

如 redo log 執(zhí)行了 update t set status = 1，此時原庫的數(shù)據(jù) status 已更新為 1，而 bin log 寫入失敗，沒有記錄這一操作，后續(xù)備份恢復(fù)時，其 status = 0，導致數(shù)據(jù)不一致）。 

其核心就是， redo log 記錄的，即使異常重啟，都會刷新到磁盤，而 bin log 記錄的， 則主要數(shù)據(jù)庫邏輯操作，主要用于備份恢復(fù)。

一個完整的交易過程：

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

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

如果收了錢后被終止，然后回過頭發(fā)現(xiàn)賬本有記錄（prepare）而且錢箱有本次收入（bin log），則繼續(xù)完善賬本（commit），本次交易有效。

4 如何恢復(fù)任意時間的數(shù)據(jù)？

當需要恢復(fù)到指定的某一秒時，比如 2018.11.23 14.23.45 有一次數(shù)據(jù)庫誤操作，需要找回數(shù)據(jù)，那你可以這么做：

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

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

這樣你的臨時庫就跟誤刪之前的線上庫一樣了，然后你可以把表數(shù)據(jù)從臨時庫取出來，按需要恢復(fù)到線上庫去。

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

1、恢復(fù)過程中會掃描最后一個 binlog 文件，提取其中的 xid； 

2、重做檢查點以后的 redo 日志，搜集處于 prepare 階段的事務(wù)鏈表，將事務(wù)的 xid 與 binlog 中的 xid 對比。

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

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

總結(jié)一下，基本頂多會出現(xiàn)下面是幾種情況：

• 當事務(wù)在 prepare 階段 crash，數(shù)據(jù)庫 recovery 的時候該事務(wù)未寫入 binlog 并且 redo log 未提交，將該事務(wù) rollback。

• 當事務(wù)在 binlog 階段 crash，此時日志還沒有成功寫入到磁盤中，啟動時會 rollback 此事務(wù)。

• 當事務(wù)在 binlog 日志已經(jīng) fsync 到磁盤后 crash，但是 InnoDB 沒有來得及 commit，此時 MySQL 數(shù)據(jù)庫 recovery 的時候?qū)x出 binlog 中的 xid，然后告訴 InnoDB 提交這些 xid 的事務(wù)，InnoDB 提交完這些事務(wù)后會回滾其它的事務(wù)，使 redo log 和 binlog 始終保持一致。

我再來說下自己的理解

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

當在2之前崩潰時

重啟恢復(fù)：后發(fā)現(xiàn)沒有commit，回滾。

備份恢復(fù)：沒有 binlog 。一致。

當在3之前崩潰

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

備份：有binlog，一致。

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

總結(jié)

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

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

課后題目 & 評論區(qū)精華

這次評論區(qū)精彩有點多！

“MySQL怎么恢復(fù)到任意一秒的狀態(tài)”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！