MySQL通過bin log日誌恢復資料｜手撕MySQL｜對線面試官

白澤來了發表於2022-03-04
MySql面試

關注微信公眾號【程式設計師白澤】，進入白澤的知識分享星球?

前言

作為《手撕MySQL》系列的第二篇文章，今天介紹一下MySQL的二進位制日誌（bin log），注意不要和MySQL的InnoDB儲存引擎特有的重寫日誌（redo log）混淆，bin log是記錄所有資料庫表資料及表結構變更的二進位制日誌（不會記錄查詢操作），藉助這個日誌可以實現：資料恢復和主從複製（不難理解，因為所有涉及變更的操作都記錄了下來，可以追溯）。

這篇文章側重於講解使用bin log進行資料恢復，下一篇文章講解主從複製。

預備知識

SSH工具推薦

接下來會頻繁在控制檯終端中輸入命令，因此推薦一款開源免費的ssh客戶端electerm：https://github.com/electerm/electerm，這是地址，可以直接下載安裝，非常好用！

image-20220303160935317

bin log 狀態管理

在開始講解bin log可以提供的兩個功能之前，先要學會管理自己MySQL服務的bin log狀態，並且通過修改引數對其進行控制。先來檢視一下自己MySQL服務是否已經開啟了bin log，可以看到我的二進位制日誌已經開啟。如果你的沒有，這裡建議通過修改MySQL配置檔案的方式將bin log宣告為開啟，然後重新啟動MySQL服務即可。

以Linux系統為例，MySQL資料庫是按照 /etc/my.cnf —— /etc/mysql/my.cnf —— /usr/local/mysql/etc/my.cnf —— ~/.my.cnf 的順序讀取配置檔案的，且如果出現引數重複設定則後一個配置檔案中引數會覆蓋前者。如果你的MySQL服務沒有配置檔案，那就直接自己建立一個，放在上面某個位置之一，然後在建立的配置檔案中輸入你從網上搜到的設定bin log開啟的配置程式碼，重啟MySQL服務即可。

現在假設你已經開啟了MySQL的二進位制日誌，如下：

mysql> show variables like '%log_bin%';

image-20220303172905803

bin log 資料檔案

觀察上面的查詢結果，可以看到兩個路徑變數：log_bin_basename和log_bin_index，分別表示bin log開啟後，資料檔案的生成位置（/usr/local/mysql/data/）和檔名規則（binlog.xxxxx1、binlog.xxxxx2以此類推），以及索引檔案binlog.index，其中存放著bin log資料檔案列表。

# 檢視MySQL資料檔案列表（大部分MySQL資料檔案都在這個路徑下，下面展示部分，主要是bin log相關的資料檔案和索引檔案）
lilithgamesdeMacBook-Pro-42:~ lilithgames$ sudo ls -al /usr/local/mysql/data

image-20220303172953921

# 檢視bin log索引檔案內容（完全對應上面列出的三個資料檔案）
lilithgamesdeMacBook-Pro-42:~ lilithgames$ sudo cat /usr/local/mysql/data/binlog.index

image-20220303173017882

既然上面說到，bin log記錄所有對資料庫的更改操作，那麼它是將SQL語句記錄在資料檔案中還是將改動之後的行結果記錄下來呢？這裡有三種記錄模式：

  • ROW：資料檔案中會記錄每一行資料被修改的情況，這樣就能保證在恢復資料或者主從複製時不會因為一些函式（如now()函式執行兩次獲取的時間是不一致的）導致資料不完全一致的情況，缺點是對於整張表的修改會導致大量資料插入到資料檔案中。
  • SRATEMENT：記錄修改資料的SQL語句，和ROW相反，在資料同步時，某些情況下會出現不完全一致的情況。
  • MIXED：混合使用上面兩種記錄模式，在一般情況下使用SRATEMENT，在特殊情況使用ROW。

但是，新版本MySQL的ROW模式已經進行了優化，對於表結構的修改會以STATEMENT模式記錄，而對於記錄的修改則會在資料檔案中記錄所有的行的變更。因此，ROW模式是bin log預設的工作模式。

mysql> show variables like 'binlog_format';

image-20220303173033257

資料恢復

準備資料

Talk is cheap，show me the code！我知道你已經迫不及待想體驗bin log的資料恢復了，那就讓我們開始吧～

為了方便展示，我們在MySQL登入態下執行flush logs命令，可以生成一個新的日誌檔案（為了將後面運算元據庫的命令單獨放在一個新的資料檔案中方便檢視）

# 生成新的二進位制資料檔案（序號增加）
mysql> flush logs;
# 檢視當前所有二進位制資料檔案
mysql> show binary logs;

因為我執行了兩次flush log命令，因此以此生成了兩個新的二進位制資料檔案，而且明顯可以看到這兩個資料檔案很小，因為還沒有新的修改表的操作被記錄下來。

image-20220303174818706

接下來我們建立一個測試資料庫：test_database，然後建立一張使用者表，並且為其插入幾條測試資料。

# 建立user表
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET=utf8;
# 插入測試使用者資料
INSERT INTO user (id, username) VALUES (null, 'AAA');
INSERT INTO user (id, username) VALUES (null, 'BBB');
INSERT INTO user (id, username) VALUES (null, 'CCC');
# 查詢測試
SELECT * FROM user

接下來我們通過mysqlbinlog命令來檢視bin log的資料檔案（猜測表的變更被記錄在binlog.000010二進位制檔案中），這裡展示一部分。

sudo mysqlbinlog /usr/local/mysql/data/binlog.000010

下面是binlog.000010檔案的部分內容，我們找到了建user表的語句（上面說了，新版MySQL的ROW資料記錄模式對於表結構的更改是STATEMENT形式的），下面是資料檔案中一些重要的欄位解釋：

  • 第一個at表示一個事件的起始位置pos，中間的是此次事件的二進位制資料，而末尾的at表示下一個事件開始位置pos（也就是當前時間的結束位置pos）
  • 220303 17:59:37 server id 1 表示server 1執行該事件的時間
  • exec_time 表示執行時間（具體時間在主從複製時master和slave有所不同，下篇文章講解）

image-20220303182831102

# 這個mysqlbinlog命令還可以新增引數，如指定查詢開始pos到結束pos之間的資料，Google一下～
sudo mysqlbinlog /usr/local/mysql/data/binlog.000010 
# 當然，不借助mysqlbinlog命令，在mysql登入狀態下也是可以直接查詢bin log資料檔案內容的，測試如下：
mysql> show binlog events in 'binlog.000010' from 447 limit 10;

image-20220303183743885

模擬失誤

# 失誤刪除id為1的使用者
DELETE FROM user where id=1
# 又插入兩個使用者
INSERT INTO user (id, username) VALUES (null, 'DDD');
INSERT INTO user (id, username) VALUES (null, 'EEE');

image-20220303193523466

資料恢復

要明確的是：藉助bin log二進位制日誌檔案進行資料恢復的本質，是重新執行兩個pos區間內的SQL（所以上面才花了較大篇幅講解檢視二進位制檔案，為的是學會定位pos點，也就是at後面那個數字）

首先通過mysql命令檢視binlog.000010資料檔案

mysql> show binlog events in 'binlog.000010';

這裡要找到兩個pos點，一個是user表建立的pos：447，另一個是執行delete操作之前的pos，這裡要求結束的pos不能直接選擇delete_rows操作的pos：1864，而是要選擇它前一個commit事件的下一個pos：1636（否則會出現警告⚠️）

image-20220303193018580

使用mysqlbinlog命令生成pos為447—1636之間的SQL檔案（上面說了資料恢復的本質是重新執行兩個pos區間內的SQL語句），通過下面的命令，生成了一個return.sql檔案。

sudo mysqlbinlog --start-position=447 --stop-position=1636 /usr/local/mysql/data/binlog.000010 > return.sql

執行return.sql檔案（相當於又執行了一遍這個區間的SQL語句），進行資料恢復！大功告成！刪除的AAA回來了！！不用被開除了！！

mysql> source ~/return.sql

image-20220303193820814

結束語

本篇文章簡單講述了利用bin log進行資料恢復的案例，並且花費了較大篇幅講解一些bin log的基礎知識，為的是為後續講解利用bin log進行主從複製打下基礎，希望閱讀本文之後，您感到對二進位制日誌的理解在八股文的基礎之上，更進一步了。

我是白澤，一名後端程式設計師/學生黨，建了一個春秋招備戰/內推/閒聊群，歡迎大家加入。

image-20220304093846935

下面是我的微信以及公眾號，關注公眾號【程式設計師白澤】，回覆簡歷可以獲取我正在使用的簡歷模板。

image-20220110183620835

相關文章