關於MySQL日誌，我與阿里P9都聊了些啥？

冰河團隊發表於2021-04-14
MySql阿里

寫在前面

週末，我與阿里P9資深技術專家（這裡就不說名字了），聊起了MySQL這個話題，為啥會聊這個呢？因為他看到我出版了一部《MySQL技術大全：開發、優化與運維實戰》，對書籍的評價也是不錯的。隨後，我們聊了關於MySQL的幾個話題，其中一個就是MySQL的日誌機制。今天，我就把大概聊的一些內容以書面文章的形式分享給大家。希望能夠為小夥伴們帶來實質性的幫助！

文章已收錄到：

https://github.com/sunshinelyz/technology-binghe

https://gitee.com/binghe001/technology-binghe

MySQL日誌

說起MySQL的日誌，有三種型別的日誌對於MySQL來說是至關重要的，這三種日誌分別為：Binlog、Undo Log 和 Redo Log。

由於Binlog和UndoLog有類似的地方，所以，我們按照如下順序依次介紹MySQL中的三大日誌原理：Undo Log——> Redo Log ——> Binlog。

Undo Log日誌

什麼是Undo Log

顧名思義，Undo Log的字面意思就是撤銷操作的日誌，指的是使MySQL中的資料回到某個狀態。

在MySQL資料庫中，事務開始之前，MySQL會將待修改的記錄儲存到Undo Log中，如果資料庫崩潰或者事務需要回滾時，MySQL可以通過利用Undo Log日誌，將資料庫中的資料回滾到之前的狀態。

MySQL新增、修改和刪除資料時，在事務開始前，就會將資訊寫入Undo Log中。事務提交時，並不會立刻刪除Undo Log， InnoDB儲存引擎會將事務對應的Undo Log放入待刪除列表中，之後會通過後臺的purge thread對待刪除的列表進行刪除處理。這裡，值得注意的是：Undo Log是一種 邏輯日誌， 記錄的是一個變化過程。比如，MySQL執行一個delete操作，Undo Log就會記錄一個insert操作；MySQL執行一個insert操作，Undo Log就會記錄一個delete操作；MySQL執行一個update操作，Undo Log就會記錄一個相反的update操作。

Undo Log以段的方式來管理和記錄日誌資訊，在InnoDB儲存引擎的資料檔案中，包含了一種叫做rollback segment的回滾段，其內部包含了1024個undo log senment。

Undo Log作用

Undo Log對於MySQL實現事務來說，起著至關重要的作用，它實現了事務的原子性和多版本併發控制，也就是我們經常說的MVCC。

  • 實現事務的原子性

Undo Log能夠實現MySQL事務的原子性，在事務的處理過程中，如果MySQL出現了錯誤或者使用者手動執行了事務的回滾操作（執行了rollback操作），MySQL可以利用Undo Log日誌將資料庫中的資料恢復到之前的狀態。

  • 實現MVCC機制

Undo Log在MySQL的InnoDB儲存引擎中實現了多版本併發控制（MVCC）機制。事務未提交前，Undo Log儲存了未提交之前的版本資料，Undo Log中的資料可以作為舊版本資料的副本或者快照以便其他併發事務進行讀取操作。

事務A手動開啟事務後，對goods資料表中id為1的資料進行更新操作，首先會把更新命中的資料寫入到Undo Buffer中。在事務A未提交之前，此時，事務B手動開啟事務，對goods資料表中的id為1的資料進行查詢操作，此時的事務B會讀取Undo Log中的資料並返回給客戶端，這就是MySQL中的MVCC機制。

可以在MySQL中通過下面的命令來檢視控制Undo Log日誌的引數。

show variables like '%innodb_undo%';

Redo Log日誌

說了MySQL中的Undo Log，我們再來看看MySQL中的Redo Log日誌。

什麼是Redo Log

顧名思義Redo Log的字面意思就是重做日誌，指的是在資料庫出現意外情況時能夠對重新執行某種操作。在MySQL中，事務中修改的任何資料，都會將最新的資料寫入Redo Log中進行備份。

在MySQL中，隨著事務操作的執行，就會產生Redo Log日誌，在事務提交時會產生Redo Log並將其寫入Redo Buffer，Redo Buffer也並不是隨著事務的提交就會被立刻寫入到磁碟中，而是等事務操作的髒頁寫入到磁碟之後，Redo Log的使命也就完成了，此時，Redo Log日誌佔用的空間可以重新利用，會被後續產生的Redo Log日誌覆蓋。

Redo Log的原理

Redo Log 能夠實現事務的永續性，防止在發生故障的時間點，有髒頁未寫入表的 ibd 檔案中，在重啟 MySQL 服務的時候，根據 Redo Log 進行重做，從而將未提交的事務進行持久化。這個過程可以簡化為下圖所示。

Redo Log的寫機制

Redo Log檔案的內容是以順序迴圈的方式寫入檔案的，寫滿時就會回到第一個檔案，進行覆蓋寫。

  • Write Pos 是當前記錄的位置，一邊寫一邊後移，寫到最後一個檔案末尾後就回到 0 號檔案開頭；
  • CheckPoint是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄更新到數
    據檔案；

Write Pos 和 CheckPoint之間還空著的部分，可以用來記錄新的操作。如果 Write Pos 追上 CheckPoint，表示已經寫滿，此時就需要向後移動CheckPoint來擦除資料。

每個InnoDB儲存引擎至少有1個重做日誌檔案組（group），每個檔案組至少有2個重做日誌檔案，預設為ib_logfile0和ib_logfile1 。

可以在MySQL中通過如下命令來檢視控制Redo Log的引數。

show variables like '%innodb_log%';

Redo Log寫入機制

在Redo Log日誌資訊從Redo Buffer持久化到Redo Log時，具體的持久化策略可以通過innodb_flush_log_at_trx_commit 引數進行設定，具體策略如下所示。

  • 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丟失一秒內的事務資料。由後臺Master執行緒每隔 1秒執行一次操作。
  • 1（預設值）：每次事務提交執行 Redo Buffer -> OS cache -> flush cache to disk，這種方式最安全，效能最差。
  • 2：每次事務提交執行 Redo Buffer -> OS cache，然後由後臺Master執行緒再每隔1秒執行OS cache -> flush cache to disk 的操作。

一般建議選擇取值2，因為 MySQL 掛了資料沒有損失，整個伺服器掛了才會損失1秒的事務提交資料。

Binlog日誌

什麼是Binlog

Binlog記錄所有MySQL資料庫表結構變更以及表資料修改的二進位制日誌，不會記錄select和show這類查詢操作的日誌。Binlog日誌是以事件形式記錄，還包含語句所執行的消耗時間。開啟Binlog日誌有以下兩個最重要的使用場景。

  • 主從複製：在主庫中開啟Binlog功能，這樣主庫就可以把Binlog傳遞給從庫，從庫拿到Binlog後實現資料恢復達到主從資料一致性。
  • 資料恢復：通過mysqlbinlog等工具來恢復資料

關於Binlog的使用場景可以參見我出版的圖書《MySQL技術大全：開發、優化和運維實戰》一書。

Binlog檔案記錄模式

Binlog檔案記錄模式有STATEMENT、ROW和MIXED三種，具體含義如下。

ROW模式

ROW（row-based replication, RBR）：日誌中會記錄每一行資料被修改的情況，然後在slave端對相同的資料進行修改。

優點：能清楚記錄每一個行資料的修改細節，能完全實現主從資料同步和資料的恢復。

缺點：批量操作，會產生大量的日誌，尤其是alter table會讓日誌暴漲。

STATMENT模式

STATMENT（statement-based replication, SBR）：每一條被修改資料的SQL都會記錄到master的Binlog中，slave在複製的時候SQL程式會解析成和原來master端執行過的相同的SQL再次執行。簡稱SQL語句複製。

優點：日誌量小，減少磁碟IO，提升儲存和恢復速度

缺點：在某些情況下會導致主從資料不一致，比如last_insert_id()、now()等函式。

MIXED模式

MIXED（mixed-based replication, MBR）：以上兩種模式的混合使用，一般會使用STATEMENT模式儲存binlog，對於STATEMENT模式無法複製的操作使用ROW模式儲存binlog，MySQL會根據執行的SQL語句選擇寫入模式 。

Binlog檔案結構

對於MySQL的Binlog檔案結構有三種版本，見下圖。

關於Binlog檔案結構的具體資訊，小夥伴們可以參考MySQL的官方文件，具體連結為：https://dev.mysql.com/doc/internals/en/event-header-fields.html

Binlog寫機制

根據記錄模式和操作觸發event事件生成log event（事件觸發執行機制）。

將事務執行過程中產生的日誌時間（log event）寫入緩衝區，每個事務執行緒都有一個緩衝區。Log Event儲存在一個binlog_cache_mngr資料結構中，在該結構中有兩個緩衝區，一個是stmt_cache，用於存放不支援事務的資訊；另一個是trx_cache，用於存放支援事務的資訊。

事務在提交階段會將產生的log event寫入到外部binlog檔案中。不同事務以序列方式將log event寫入Binlog檔案中，所以一個事務包含的log event資訊在binlog檔案中是連續的，中間不會插入其他事務的log event。

Binlog檔案操作

Binlog狀態檢視

show variables like 'log_bin';

開啟Binlog功能，需要修改my.cnf或my.ini配置檔案，在[mysqld]下面增加log_bin=mysql_bin_log，重啟
MySQL服務。

binlog-format=ROW
log-bin=mysqlbinlog

使用show binlog events命令

show binary logs; //等價於show master logs;
show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001';

使用mysqlbinlog 命令

mysqlbinlog "檔名"
mysqlbinlog "檔名" > "test.sql"

使用 binlog 恢復資料

//按指定時間恢復
mysqlbinlog --start-datetime="2021-02-28 18:00:00" --stopdatetime="2021-03-01 00:00:00" mysqlbinlog.000001 | mysql -uroot -p123456
//按事件位置號恢復
mysqlbinlog --start-position=1789 --stop-position=2674 mysqlbinlog.000001
| mysql -uroot -p123456

刪除Binlog檔案

purge binary logs to 'mysqlbinlog.000001'; //刪除指定檔案
purge binary logs before '2021-03-01 00:00:00'; //刪除指定時間之前的檔案
reset master; //清除所有檔案

可以通過設定expire_logs_days引數來啟動自動清理功能。預設值為0表示沒啟用。設定為大於0的整數表示超出多少天binlog檔案會自動清除。

更多有關於Binlog日誌的資訊，可以參考我出版的《MySQL技術大全：開發、優化與運維實戰》一書。

好了，今天就到這兒吧，我是冰河，大家有啥問題可以在下方留言，也可以加我微信：sun_shine_lyz，我拉你進群，一起交流技術，一起進階，一起牛逼~~

相關文章