MySQL的三大日志：redo log、undo log、 binlog

mysql的日志分為幾大類：錯誤日志、查詢日志、慢查詢日志、事務日志（redo log和undo log）、二進制日志（binlog）。

binlog

關于資料庫日志，舉個簡單的例子，我們在硬碟加載到記憶體之後，對資料進行一系列操作，在還未重新整理到硬碟之前，那就得在XXX位置先記錄下，然後再進行正常的增删改查操作，最後刷入硬碟。如果未刷入硬碟，在重新開機之後，先加載之前的記錄，那麼資料就回來了。

用于記錄資料庫執行的寫入性操作(不包括查詢)資訊，以二進制的形式儲存在磁盤中。binlog是mysql的邏輯日志(可了解為記錄的就是sql語句)，并且由Server層進行記錄，使用任何存儲引擎的mysql資料庫都會記錄binlog日志。

用途：

• 主從複制：MySQL Replication在Master端開啟binlog，Master把它的二進制日志傳遞給slaves并回放來達到master-slave資料一緻的目的
• 資料恢複：通過mysqlbinlog工具恢複資料
• 增量備份

檢視：

    1.  mysqlbinlog  mysql-bin.000007

    2. 指令行解析 SHOW BINLOG EVENTS  [IN 'log_name']  [FROM pos]    [LIMIT [offset,] row_count]

mysql> show binlog events in 'mysql-bin.000007' from 1190 limit 2\G
           

格式：STATMENT、ROW和MIXED

• 基于SQL語句的複制(statement-based replication, SBR)，每一條會修改資料的sql語句會記錄到binlog中。
• 基于行的複制(row-based replication, RBR)，不記錄每條sql語句的上下文資訊，記錄哪條資料被修改了。
• 基于上述兩種模式的混合複制(mixed-based replication, MBR)，一般的複制使用前一模式儲存binlog，無法複制的操作使用ROW模式儲存binlog。選取規則：如果是采用 INSERT，UPDATE，DELETE 直接操作表的情況，則日志格式根據 binlog_format 的設定而記錄；如果是采用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何都采用statement模式記錄

binlog_format=statment格式的日志内容：show binlog events in 'master.000001';

binlog_format=row格式的日志内容：

這張圖還是通過show指令檢視，但是還不能真正看到日志的詳細内容，需要使用指令：

mysqlbinlog  -vv data/master.000001 --start-position=8900

為什麼會有 mixed 這種 binlog 格式的存在場景？

• 有些 statement 格式的 binlog 可能會導緻主備不一緻，是以要使用 row 格式。
• row 格式的缺點是，很占空間。比如你用一個 delete 語句删掉 10 萬行資料，用 statement 的話就是一個 SQL 語句被記錄到 binlog 中，占用幾十個位元組的空間。但如果用 row 格式的 binlog，就要把這 10 萬條記錄都寫到 binlog 中。這樣做，不僅會占用更大的空間，同時寫 binlog 也要耗費 IO 資源，影響執行速度。
• MySQL 就取了個折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己會判斷這條 SQL 語句是否可能引起主備不一緻，如果有可能，就用 row 格式，否則就用 statement 格式。

為何現在越來越多的場景要求把 MySQL 的 binlog 格式設定成 row？

• delete 語句，row 格式的 binlog 會把被删掉的行的整行資訊儲存起來。如果發現删錯資料了，可以直接把 binlog 中記錄的 delete 語句轉成 insert，把被錯删的資料插入回去就可以恢複了。
• 如果你是執行錯了 insert 語句，insert 語句的 binlog 裡會記錄所有的字段資訊，這些資訊可以用來精确定位剛剛被插入的那一行。這時，你直接把 insert 語句轉成 delete 語句，删除掉這被誤插入的一行資料就可以了。
• 如果執行的是 update 語句的話，binlog 裡面會記錄修改前整行的資料和修改後的整行資料。是以，如果你誤執行了 update 語句的話，隻需要把這個 event 前後的兩行資訊對調一下，再去資料庫裡面執行。

用 binlog 來恢複資料的标準做法是，用 mysqlbinlog 工具解析出來，然後把解析結果整個發給 MySQL 執行。類似下面的指令：

将 master.000001 檔案裡面從第 2738 位元組到第 2973 位元組中間這段内容解析出來，放到 MySQL 去執行。
mysqlbinlog master.000001  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;
           

binlog寫入機制：

• binlog 的寫入邏輯比較簡單：事務執行過程中，先把日志寫到 binlog cache（write），事務送出的時候，再把 binlog cache 寫到 binlog 檔案（fsync）中。
• 一個事務的 binlog 是不能被拆開的，是以不論這個事務多大，也要確定一次性寫入。
• 系統給 binlog cache 配置設定了一片記憶體，每個線程一個，參數 binlog_cache_size 用于控制單個線程内 binlog cache 所占記憶體的大小。如果超過了這個參數規定的大小，就要暫存到磁盤。

注：把日志寫入到檔案系統的 page cache，并沒有把資料持久化到磁盤，是以速度比較快。一般情況下，我們認為 fsync 才占磁盤的 IOPS。

刷盤時機由參數 sync_binlog 控制：

• sync_binlog=0 的時候，表示每次送出事務都隻 write，不 fsync；
• sync_binlog=1 的時候，表示每次送出事務都會執行 fsync；
• sync_binlog=N(N>1) 的時候，表示每次送出事務都 write，但累積 N 個事務後才 fsync。

在出現 IO 瓶頸的場景裡，将 sync_binlog 設定成一個比較大的值，可以提升性能。在實際的業務場景中，考慮到丢失日志量的可控性，一般不建議将這個參數設成 0，比較常見的是将其設定為 100~1000 中的某個數值。MySQL 5.7.7之後版本的預設值為 1。

redo log

産生：

事務的四大特性裡面有一個是持久性，具體來說就是隻要事務送出成功，那麼對資料庫做的修改就被永久儲存下來了，不可能因為任何原因再回到原來的狀态。這一點，mysql是如何保證一緻性的呢？

最簡單的做法是在每次事務送出的時候，将該事務涉及修改的資料頁全部重新整理到磁盤中。但是這麼做會有嚴重的性能問題，主要展現在兩個方面：

• Innodb是以頁為機關進行磁盤互動的，而一個事務很可能隻修改一個資料頁裡面的幾個位元組，這個時候将完整的資料頁刷到磁盤的話，太浪費資源了！
• 一個事務可能涉及修改多個資料頁，并且這些資料頁在實體上并不連續，使用随機IO寫入性能太差！

如果MySQL當機，而Buffer Pool的資料沒有完全重新整理到磁盤，就會導緻資料丢失，無法保證持久性。是以，mysql設計了redo log，具體來說就是隻記錄事務對資料頁做了哪些修改，相對而言檔案更小并且是順序IO。

基本概念：

redo log包括兩部分：一個是記憶體中的日志緩沖(redo log buffer)，另一個是磁盤上的日志檔案(redo log file)。mysql每執行一條DML語句，先将記錄寫入redo log buffer，後續某個時間點再一次性将多個操作記錄寫到redo log file。這種先寫日志，再寫磁盤的技術就是MySQL裡經常說到的WAL(Write-Ahead Logging) 技術。

redo log寫入流程： A. redo log buffer -->  B. os buffer  -->  C. redo log file

刷盤時機：

有三種将redo log buffer寫入redo log file的時機，可以通過innodb_flush_log_at_trx_commit參數配置。

0：延遲寫，大約每秒重新整理寫入到磁盤資料 。如果出現系統崩潰，可能會出現丢失1秒資料，在流程中的A-B之間。

1：實時寫，實時刷，每次送出就寫入磁盤，IO 性能差。

2：實時寫，延遲刷，即每秒刷，在流程中的B-C之間。

一個沒有送出的事務的 redo log 也會被寫到磁盤中，三種時機：

• InnoDB 有一個背景線程，每隔 1 秒，就會把 redo log buffer 中的日志，調用 write 寫到檔案系統的 page cache，然後調用 fsync 持久化到磁盤。其中，事務執行中間過程的 redo log 也是直接寫在 redo log buffer 中。
• redo log buffer 占用的空間即将達到 innodb_log_buffer_size 一半的時候，背景線程會主動寫盤。注意，由于這個事務并沒有送出，是以這個寫盤動作隻是 write，而沒有調用 fsync，也就是隻留在了檔案系統的 page cache。
• 并行的事務送出的時候，順帶将這個事務的 redo log buffer 持久化到磁盤。

通常我們說 MySQL 的“雙 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都設定成 1。也就是說，一個事務完整送出前，需要等待兩次刷盤，一次是 redo log（prepare 階段），一次是 binlog。

記錄形式：

redo log實際上記錄資料頁的變更，而這種變更記錄是沒必要全部儲存，是以redo log實作上采用了大小固定，循環寫入的方式，當寫到結尾時，會回到開頭循環寫日志。

LSN （邏輯序列号) 是單調遞增的，用來對應 redo log 的一個個寫入點。每次寫入長度為 length 的 redo log， LSN 的值就會加上 length。LSN 也會寫到 InnoDB 的資料頁中，在每個頁的頭部，值

FIL_PAGE_LSN

記錄該頁的LSN，表示頁最後重新整理時LSN的大小。來確定資料頁不會被多次執行重複的 redo log。

write pos 是redo log目前記錄的LSN位置，checkpoint 是表示資料頁更改記錄刷盤後對應redo log所處的LSN位置，也是往後推移并且循環的，擦除記錄前要把記錄更新到資料檔案。write pos到check point之間的部分是redo log空着的部分，用于記錄新的記錄；check point到write pos之間是redo log待落盤的資料頁更改記錄，當write pos追上check point時，會先推動check point向前移動，空出位置再記錄新的日志。

crash-safe：

啟動innodb的時候，不管上次是正常關閉還是異常關閉，總是會進行恢複操作。因為redo log記錄的是資料頁的實體變化，是以恢複的時候速度比邏輯日志(如binlog)要快很多。重新開機innodb時，首先會檢查磁盤中資料頁的LSN，如果資料頁的LSN小于日志中的LSN，則會從checkpoint開始恢複。

還有一種情況，在當機前正處于checkpoint的刷盤過程，且資料頁的刷盤進度超過了日志頁的刷盤進度，此時會出現資料頁中記錄的LSN大于日志中的LSN，這時超出日志進度的部分将不會重做，因為這本身就表示已經做過的事情，無需再重做。

兩階段送出        

 看看下圖中update執行流程：

其中redo log 的寫入拆成了兩個步驟：prepare 和 commit，這就是"兩階段送出"，它是為了讓兩份日志之間的邏輯一緻。  

組送出（group commit）：

假設有個場景，多個并發事務在prepare階段，先寫的事務，會被選為這組的 leader，開始寫盤的時候，這個組裡面已經有了三個事務， LSN 也變成了組裡最後一個事務的LSN，三個事務同時寫入磁盤。

在并發更新場景下，第一個事務寫完 redo log buffer 以後，接下來這個 fsync 越晚調用，組員可能越多，節約 IOPS 的效果就越好。這裡，MySQL 有個優化機制：

之前說過，binlog分為兩步，write 和 fsync，MySQL 為了讓組送出的效果更好，把 redo log 做 fsync 的時間拖到了binlog 的 write  之後。

這樣兩階段送出就變成：

這麼一來，binlog 也可以組送出了。在執行圖中第 4 步把 binlog fsync 到磁盤時，如果有多個事務的 binlog 已經寫完了，也是一起持久化的，這樣也可以減少 IOPS 的消耗。不過通常情況下第 3 步執行得會很快，是以 binlog 的 write 和 fsync 間的間隔時間短，導緻能集合到一起持久化的 binlog 比較少，是以 binlog 的組送出的效果通常不如 redo log 的效果那麼好。

如果你想提升 binlog 組送出的效果，可以通過設定 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 來實作。

• binlog_group_commit_sync_delay 參數，表示延遲多少微秒後才調用 fsync;
• binlog_group_commit_sync_no_delay_count 參數，表示累積多少次以後才調用 fsync。

這兩個條件是或的關系，也就是說隻要有一個滿足條件就會調用 fsync。是以，當 binlog_group_commit_sync_delay 設定為 0 的時候，binlog_group_commit_sync_no_delay_count 也無效了。

到這裡有人會問了，WAL 機制是減少磁盤寫，可是每次送出事務都要寫 redo log 和 binlog，磁盤讀寫次數也沒變少呀？現在你就能了解，WAL 機制主要得益于兩個方面：

• redo log 和 binlog 都是順序寫，磁盤的順序寫比随機寫速度要快；
• 組送出機制，可以大幅度降低磁盤的 IOPS 消耗。

那麼，保持兩份日志之間邏輯一緻，有什麼用呢？簡單說，當你誤操作資料庫 或者 給資料庫擴容增加讀能力的時候，這種一緻性能保證資料庫資料恢複到誤操作之前，或者能達到線上主從一緻的目的。下面看看binlog恢複資料的流程：

binlog 會記錄所有的邏輯操作，并且是采用“追加寫”的形式。如果你的 DBA 承諾說半個月内的資料可以恢複，那麼備份系統中一定會儲存最近半個月的所有 binlog，同時系統會定期做整庫備份。這裡的“定期”取決于系統的重要性，可以是一天一備，也可以是一周一備。

當需要恢複到指定的某一秒時，比如某天下午兩點發現中午十二點有一次誤删表，需要找回資料，那你可以這麼做：

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

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

這兩歩可以參照：https://zhuanlan.zhihu.com/p/33504555

兩階段送出怎麼保證一緻的？或者說如果沒有兩階段送出，資料能保證一緻嗎？

再用上面流程圖的例子，假設目前 ID=2 的行，字段 c 的值是 0，再假設執行 update 語句過程中在寫完第一個日志後，第二個日志還沒有寫完期間發生了 crash，會出現什麼情況呢？

1. 要麼是先寫 redo log 後寫 binlog。redo log 寫完之後，系統即使崩潰，仍然能夠把資料恢複回來，是以恢複後這一行 c 的值是 1。但是由于 binlog 沒寫完就 crash 了，這時候 binlog 裡面就沒有記錄這個語句。是以，之後備份日志的時候，存起來的 binlog 裡面就沒有這條語句。然後你會發現，如果需要用這個 binlog 來恢複臨時庫的話，由于這個語句的 binlog 丢失，這個臨時庫就會少了這一次更新，恢複出來的這一行 c 的值就是 0，與原庫的值不同。

2. 要麼先寫 binlog 後寫 redo log。如果在 binlog 寫完之後 crash，由于 redo log 還沒寫，崩潰恢複以後這個事務無效，是以這一行 c 的值是 0。但是 binlog 裡面已經記錄了“把 c 從 0 改成 1”這個日志。是以，在之後用 binlog 來恢複的時候就多了一個事務出來，恢複出來的這一行 c 的值就是 1，與原庫的值不同。

現在，可以看看在兩階段送出的不同時刻，MySQL 異常重新開機會出現什麼現象？

如果是在寫入 redo log 處于 prepare 階段之後、寫 binlog 之前，發生了崩潰（crash），由于此時 binlog 還沒寫，redo log 也還沒送出，是以崩潰恢複的時候，這個事務會復原。這時候，binlog 還沒寫，是以也不會傳到備庫

如果binlog 寫完，redo log 還沒 commit 前發生 crash，那崩潰恢複的時候 MySQL 會怎麼處理？如果 redo log 裡面的事務隻有完整的 prepare，則判斷對應的事務 binlog 是否存在并完整，是則送出事務。

追問幾個問題：

1. 不引入兩個日志，也就沒有兩階段送出的必要了。隻用 binlog 來支援崩潰恢複，又能支援歸檔，不就可以了？

曆史原因，InnoDB 并不是 MySQL 的原生存儲引擎。MySQL 的原生引擎是 MyISAM，設計之初就沒有支援崩潰恢複。InnoDB 在作為 MySQL 的插件加入 MySQL 引擎家族之前，就已經是一個提供了崩潰恢複和事務支援的引擎了。

實作上的原因，那就是binlog沒有crash-safe能力。

2. 反過來，隻用redo log行不行？

一是 redo log沒有歸檔能力，他都是循環寫。一個是mysql系統依賴于binlog，MySQL 系統高可用的基礎，就是 binlog 複制。

3. 正常運作中的執行個體，資料寫入後的最終落盤，是從 redo log 更新過來的還是從 buffer pool 更新過來的呢？

實際上，redo log 并沒有記錄資料頁的完整資料，是以它并沒有能力自己去更新磁盤資料頁，也就不存在“資料最終落盤，是由 redo log 更新過去”的情況。

4. 為什麼 binlog cache 是每個線程自己維護的，而 redo log buffer 是全局共用的？

MySQL 這麼設計的主要原因是，binlog 是不能“被打斷的”。一個事務的 binlog 必須連續寫，是以要整個事務完成後，再一起寫到檔案裡。

而 redo log 并沒有這個要求，中間有生成的日志可以寫到 redo log buffer 中。redo log buffer 中的内容還能“搭便車”，其他事務送出的時候可以被一起寫到磁盤中。

5. 事務執行期間，還沒到送出階段，如果發生 crash 的話，redo log 肯定丢了，這會不會導緻主備不一緻呢？

不會。因為這時候 binlog 也還在 binlog cache 裡，沒發給備庫。crash 以後 redo log 和 binlog 都沒有了，從業務角度看這個事務也沒有送出，是以資料是一緻的。

6. 如果 binlog 寫完盤以後發生 crash，這時候還沒給用戶端答複就重新開機了。等用戶端再重連進來，發現事務已經送出成功了，這是不是 bug？

不是。你可以設想一下更極端的情況，整個事務都送出成功了，redo log commit 完成了，備庫也收到 binlog 并執行了。但是主庫和用戶端網絡斷開了，導緻事務成功的包傳回不回去，這時候用戶端也會收到“網絡斷開”的異常。這種也隻能算是事務成功的，不能認為是 bug。

實際上資料庫的 crash-safe 保證的是：

如果用戶端收到事務成功的消息，事務就一定持久化了；

如果用戶端收到事務失敗（比如主鍵沖突、復原等）的消息，事務就一定失敗了；

如果用戶端收到“執行異常”的消息，應用需要重連後通過查詢目前狀态來繼續後續的邏輯。此時資料庫隻需要保證内部（資料和日志之間，主庫和備庫之間）一緻就可以了。

undo log

資料庫事務四大特性中有一個是原子性，具體來說就是 原子性是指對資料庫的一系列操作，要麼全部成功，要麼全部失敗，不可能出現部分成功的情況。

實際上，原子性底層就是通過undo log實作的。undo log主要記錄了資料的邏輯變化，比如一條INSERT語句，對應一條DELETE的undo log，對于每個UPDATE語句，對應一條相反的UPDATE的undo log，這樣在發生錯誤時，就能復原到事務之前的資料狀态。同時，undo log也是MVCC(多版本并發控制)實作的關鍵。

• insert Undo Log是INSERT操作産生的undo log，由于是該資料的第一個記錄，對其他事務不可見，該Undo Log可以在事務送出後直接删除。
• update Undo Log記錄對DELETE和UPDATE操作産生的Undo Log，由于提供MVCC機制，是以不能在事務送出時就删除，而是放入undo log連結清單，等待purge線程進行最後的删除。

 存儲位置：

innodb存儲引擎對undo的管理采用段的方式。rollback segment稱為復原段，每個復原段中有1024個undo log segment。在以前老版本，隻支援1個rollback segment，這樣就隻能記錄1024個undo log segment。後來MySQL5.5可以支援128個rollback segment，即支援128*1024個undo操作，還可以通過變量 innodb_undo_logs (5.6版本以前該變量是 innodb_rollback_segments )自定義多少個rollback segment，預設值為128。

Rollback Segment預設存儲在共享表空間，即 ibdata檔案中，也可設定獨立UNDO表空間。當DB寫壓力較大時，可以設定獨立UNDO表空間，需要在初始化資料庫執行個體的時候，指定獨立表空間的數量。然後再把UNDO LOG從ibdata檔案中分離開來，指定 innodb_undo_directory目錄存放，可以制定到高速磁盤上，加快UNDO LOG 的讀寫性能。

undo及redo如何記錄事務

假設有A、B兩個資料，值分别為1,2，開始一個事務，事務的操作内容為：把1修改為3，2修改為4，那麼實際的記錄如下（簡化）：

  A.事務開始.

  B.記錄A=1到undo log.

  C.修改A=3.

  D.記錄A=3到redo log.

  E.記錄B=2到undo log.

  F.修改B=4.

  G.記錄B=4到redo log.

  H.将redo log寫入磁盤。

  I.事務送出

 Undo + Redo的設計主要考慮的是提升IO性能，增大資料庫吞吐量。可以看出，B D E G H，均是新增操作，但是B D E G 是緩沖到buffer區，隻有G是增加了IO操作，為了保證Redo Log能夠有比較好的IO性能，InnoDB 的 Redo Log的設計有以下幾個特點：

1. 盡量保持Redo Log存儲在一段連續的空間上，在系統第一次啟動時就會将日志檔案的空間完全配置設定， 以順序追加的方式記錄Redo Log,通過順序IO來改善性能。
2. 批量寫入日志。日志并不是直接寫入檔案，而是先寫入redo log buffer.當需要将日志重新整理到磁盤時 (如事務送出),将許多日志一起寫入磁盤。
3. 并發的事務共享Redo Log的存儲空間，它們的Redo Log按語句的執行順序，依次交替的記錄在一起，以減少日志占用的空間。這樣會造成将其他未送出的事務的日志寫入磁盤。
4. Redo Log上隻進行順序追加的操作，當一個事務需要復原時，它的Redo Log記錄也不會從Redo Log中删除掉。

怎麼進行的恢複？

上面說到未送出的事務和復原了的事務也會記錄Redo Log，是以在進行恢複時,這些事務要進行特殊的的處理。

由于redo log自身的特點，無法做到隻重做已經送出了的事務。但是可以做到，重做所有事務包括未送出的事務和復原了的事務。然後通過Undo Log復原那些未送出的事務。

InnoDB存儲引擎中的恢複機制有幾個特點：

在重做Redo Log時，并不關心事務性。 恢複時，沒有BEGIN，也沒有COMMIT,ROLLBACK的行為。也不關心每個日志是哪個事務的。盡管事務ID等事務相關的内容會記入Redo Log，這些内容隻是被當作要操作的資料的一部分。

要将Undo Log持久化，而且必須要在寫Redo Log之前将對應的Undo Log寫入磁盤。Undo和Redo Log的這種關聯，使得持久化變得複雜起來。為了降低複雜度，InnoDB将Undo Log看作資料，是以記錄Undo Log的操作也會記錄到redo log中。這樣undo log就可以象資料一樣緩存起來，而不用在redo log之前寫入磁盤了。

既然Redo沒有事務性，會重新執行被復原了的事務，同時Innodb也會将事務復原時的操作也記錄到redo log中。復原操作本質上也是對資料進行修改，是以復原時對資料的操作也會記錄到Redo Log中。一個被復原了的事務在恢複時的操作就是先redo再undo，是以不會破壞資料的一緻性。

來源參考：https://zhuanlan.zhihu.com/p/190886874、https://www.cnblogs.com/wyy123/p/7880077.html、https://www.cnblogs.com/drizzle-xu/p/9713513.html

以及 林曉斌《MySQL實戰45講》