技術文章 » 一起分析MySQL的binlog怎麼恢復資料

一起分析MySQL的binlog怎麼恢復資料

2022-03-21 19:01:02
本篇文章給大家帶來了關於的相關知識，其中主要介紹了binlog的相關問題，binlog一般稱作歸檔紀錄檔，下面就來看一下怎麼利用binlog來恢復mysql中的資料，希望對大家有幫助。

推薦學習：

我們常常聽人說，只要你願意，MySQL 可以恢復至半個月甚至一個月以內的任何一個狀態。網上也有很多刪庫跑路的段子。。。

那麼今天鬆哥想和大家來聊一聊 MySQL 中的 binlog，來手把手教大家如何利用 binlog 來恢復 MySQL 中的資料，這樣，以後要是不小心刪庫了，那也不用跑路了。

MySQL 中的紀錄檔比較重要的有 binlog（歸檔紀錄檔）、redo log（重做紀錄檔）以及 undo log，那麼跟我們本文相關的主要是 binlog，另外兩個紀錄檔鬆哥將來有空了再和大家詳細介紹。

1. binlog

binlog 我們中文一般稱作歸檔紀錄檔，如果大家看過鬆哥之前發的 MySQL 主從搭建，應該對這個紀錄檔有印象，當我們搭建 MySQL 主從的時候就離不開 binlog（傳送門：MySQL8 主從複製踩坑指南）。

binlog 是 MySQL Server 層的紀錄檔，而不是儲存引擎自帶的紀錄檔，它記錄了所有的 DDL 和 DML(不包含資料查詢語句)語句，而且是以事件形式記錄，還包含語句所執行的消耗的時間等，需要注意的是：

  • binlog 是一種邏輯紀錄檔，他裡邊所記錄的是一條 SQL 語句的原始邏輯，例如給某一個欄位 +1，注意這個區別於 redo log 的物理紀錄檔（在某個資料頁上做了什麼修改）。
  • binlog 檔案寫滿後，會自動切換到下一個紀錄檔檔案繼續寫，而不會覆蓋以前的紀錄檔，這個也區別於 redo log，redo log 是迴圈寫入的，即後面寫入的可能會覆蓋前面寫入的。
  • 一般來說，我們在設定 binlog 的時候，可以指定 binlog 檔案的有效期，這樣在到期後，紀錄檔檔案會自動刪除，這樣避免佔用較多儲存空間。

根據 MySQL 官方檔案的介紹，開啟 binlog 之後，大概會有 1% 的效能損耗，不過這還是可以接受的，一般來說，binlog 有兩個重要的使用場景：

  • MySQL 主從複製時：在主機上開啟 binlog，主機將 binlog 同步給從機，從機通過 binlog 來同步資料，進而實現主機和從機的資料同步。
  • MySQL 資料恢復，通過使用 mysqlbinlog 工具再結合 binlog 檔案，可以將資料恢復到過去的某一時刻。

2. 開啟 binlog

為了演示方便，鬆哥這裡在 Docker 中安裝了 MySQL，我們以此為例來開始今天的演示。如果小夥伴們還不懂 docker 的使用，可以在公眾號後臺回覆 docker，有鬆哥寫的教學。

首先我們在 docker 中安裝好 MySQL，然後進入到容器中，通過如下命令可以檢視 binlog 是否開啟：

這個 OFF 就表示 binlog 是一個關閉狀態，沒有開啟，接下來我們來開啟 binlog。

開啟 binlog 主要是修改 MySQL 的組態檔 mysqld.cnf，該檔案在容器的 /etc/mysql/mysql.conf.d 目錄下。

針對該組態檔，我們做如下修改：

# 這個參數列示啟用 binlog 功能，並指定 binlog 的儲存目錄
log-bin=javaboy_logbin

# 設定一個 binlog 檔案的最大位元組
# 設定最大 100MB
max_binlog_size=104857600

# 設定了 binlog 檔案的有效期（單位：天）
expire_logs_days = 7

# binlog 紀錄檔只記錄指定庫的更新（設定主從複製的時候會用到）
#binlog-do-db=javaboy_db

# binlog 紀錄檔不記錄指定庫的更新（設定主從複製的時候會用到）
#binlog-ignore-db=javaboy_no_db

# 寫快取多少次，刷一次磁碟，預設 0 表示這個操作由作業系統根據自身負載自行決定多久寫一次磁碟
# 1 表示每一條事務提交都會立即寫磁碟，n 則表示 n 個事務提交才會寫磁碟
sync_binlog=0

# 為當前服務取一個唯一的 id（MySQL5.7 之後需要設定）
server-id=1

各項設定的含義鬆哥已經在注視中說明了。截圖如下：

設定完成後，執行如下命令重新啟動 mysql 容器（mysql1 是我這裡容器的名字）：

docker restart mysql1

重新啟動之後，再次執行 show variables like 'log_bin%'; 即可看到 binlog 已經開啟了。

這裡除了 log_bin 變數外，還有兩個變數名也值得我們關注：

  • log_bin_basename：這個是將來產生的 binlog 紀錄檔檔案的名稱字首，換句話說，根據大家目前所看到的設定，將來產生的 binlog 紀錄檔檔名為 javaboy_logbin.xxx，這個檔案中將會用來記錄所有的 DDL 和 DML 語句事件。
  • log_bin_index：這個是 binlog 的索引檔案，儲存了所有 binlog 的目錄，因為 binlog 可能會有多個。我們可以來檢視一下現在的 javaboy_logbin.index 檔案：

可以看到，目前只有一個 logbin 檔案。

3. 常見 binlog 操作

接下來我們再來介紹幾個常見的 binlog 操作命令。

  1. 檢視所有 binlog 紀錄檔

通過如下方式我們可以檢視 binlog 紀錄檔列表：

show master logs;

可以看到，我這裡目前只有一個紀錄檔檔案，檔名為 javaboy_logbin.000001，File_size 表示這個檔案佔用的位元組大小是 154。

  1. 檢視 master 狀態

這個命令我們在搭建 MySQL 主從的時候經常會用到，如下：

這個時候可以看到最新的 binlog 紀錄檔檔名稱以及最後一個操作事件的 Position 值（這個值有啥用，我們後面會給大家詳細介紹）。

  1. 重新整理 binlog

正常來說，一個 binlog 寫滿之後，會自動切換到下一個 binlog 開始寫，不過我們也可以執行一個 flush logs 命令來手動重新整理 binlog，手動重新整理 binlog 之後，就會產生一個新的 binlog 紀錄檔檔案，接下來所有的 binlog 紀錄檔都將記錄到新的檔案中。如下：

由上圖可以看到，我們重新整理紀錄檔之後，再通過 show master logs 去檢視紀錄檔，發現紀錄檔檔案已經多了一個新產生的了，然後再通過 show master status 去檢視最新的紀錄檔檔案資訊，發現也已經變為 javaboy_logbin.000002。

  1. 重置 binlog

reset master 可以重置 binlog 紀錄檔檔案，讓紀錄檔重新從 000001 開始記錄，不過如果當前主機有一個或者多個從機在執行，那麼該命令就執行不了（因為從機是通過 binlog 來實現資料庫同步的，主機把 binlog 清空了，從機會報找不到 binlog 的錯誤）。

  1. 檢視 binlog

由於 binlog 是二進位制紀錄檔檔案，所以要是直接開啟，那肯定是看不了的：

沒有看到任何有用的資訊。

為了檢視 binlog，MySQL 為我們提供了兩個官方工具，我們一個一個來看，首先是 mysqlbinlog 命令，如下：

雖然看起來亂糟糟的，不過仔細看著其實都有跡可循。因為我這裡是一個新安裝的資料庫，裡邊只是建立了一個名為 javaboy 的庫，然後建立了一個名為 user 的表加了兩條資料，其他什麼事情都沒做，所以建立庫的指令碼我們其實能夠從紛雜的檔案中找到。

產生的紀錄檔檔案中有一個 end_log_pos 是紀錄檔檔案的 pos 點，這個將來在資料恢復的時候有用。

不過這種檢視方式不夠人性化，我們說 binlog 是按照事件來記錄紀錄檔的，所以如果我們能夠按照事件的方式檢視紀錄檔，就會好很多，我們再來看看如下一個命令：

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

這個表示以事件的方式來檢視 binlog，這裡涉及到幾個引數：

  • log_name：可以指定要檢視的 binlog 紀錄檔檔名，如果不指定的話，表示檢視最早的 binlog 檔案。
  • pos：從哪個 pos 點開始檢視，凡是 binlog 記錄下來的操作都有一個 pos 點，這個其實就是相當於我們可以指定從哪個操作開始檢視紀錄檔，如果不指定的話，就是從該 binlog 的開頭開始檢視。
  • offset：這是是偏移量，不指定預設就是 0。
  • row_count：檢視多少行記錄，不指定就是檢視所有。

我們來看一個簡單的例子：

show binlog events in 'javaboy_logbin.000001';

這下就清晰多了，我們可以看到之前的所有操作，例如：

  • 在 Pos 219-322 之間建立了一個庫。
  • 在 Pos 387-537 之間建立了一張表。
  • 在 Pos 677-780 之間新增了一條記錄。
  • …

4. 資料恢復實戰

好啦，有了前面的基礎知識準備，接下來鬆哥來給大家手把手演示一個刪庫/恢復的場景。

我先來說說我這個資料庫目前的情況。

這是一個新安裝的資料庫，裡邊我新建了一個資料庫名為 javaboy，javaboy 庫中新建了一張表名為 user，user 中有兩條記錄，如下：

現在假設我們定期（每週三凌晨三點）對資料庫進行備份。

現在凌晨三點了，資料庫自動備份開始了，我們通過如下命令將資料庫備份成 SQL 指令碼，如下：

mysqldump -uroot -p --flush-logs --lock-tables -B javaboy>/root/javaboy.bak.sql

這裡有幾個引數跟大家解釋下：

  • -u、-p 這兩個就不用說了。
  • –flush-logs：這個表示在匯出之前先重新整理 binlog，重新整理 binlog 之後將會產生新的 binlog 檔案，後續的操作都存在新的 binlog 中。
  • –lock-tables：這個表示開始匯出前，鎖定所有表。需要注意的是當匯出多個資料庫時，–lock-tables 分別為每個資料庫鎖定表，因此這個選項不能保證匯出檔案中的表在資料庫之間的邏輯一致性，不同資料庫表的匯出狀態可以完全不同。
  • -B：這個表示指定匯出的資料庫名稱，如果使用 --all-databases 或者 -A 代替 -B 表示匯出所有的資料庫。

以上命令執行完成後，會在 /root 目錄下生成一個 javaboy.bak.sql 檔案，該檔案就是備份的 sql 檔案了。

這是星期三凌晨三點發生的事情。

接下來到了星期四早上，來上班了，一頓操作後，往資料庫中又新增了兩條操作，如下：

接下來，小 X 今天跟領導吵架了很不爽，決定刪除跑路：

領導發現了大驚，當即要求立馬恢復資料。這時候該你表現了。

首先，我們有星期三凌晨的備份檔案，先用那個檔案進行資料恢復：

恢復之後，現在到星期三早上凌晨三點的資料有了。

從星期三早上凌晨三點到星期四的資料現在沒了。

這個時候我們就要藉助於 binlog 來恢復了。大家還記得，我們星期三凌晨三點執行備份的時候，用了一個引數叫做 --flush-logs，使用了該參數列示從備份那一刻起，新的 binlog 將產生在一個新的紀錄檔檔案中，對於我們這裡來說，新的 binlog 檔案當然就是 javaboy_logbin.000002 了，我們去檢視一下該檔案：

show binlog events in 'javaboy_logbin.000002';

我這裡生成的該檔案比較長，我擷取其中一部分：

可以看到，在 764-865 這個 Pos 中發生了刪庫跑路事件，那麼我們只需要回放該檔案將資料恢復到 764 這個位置即可。

由於 javaboy_logbin.000002 檔案是在星期三凌晨三點備份之後產生的新檔案，因此這個檔案從起始到 764 這個 Pos 之間的操作，就是星期三凌晨三點到刪庫之前的操作了。

那麼我們來看下通過 binlog 來恢復資料的命令：

mysqlbinlog /var/lib/mysql/javaboy_logbin.000002 --stop-position=764 --database=javaboy | mysql -uroot -p

那麼這裡涉及到兩個引數：

  • –stop-position=764 表示恢復到 764 這個 Pos，不指定的話就把按整個檔案恢復了，如果按當前檔案恢復的話，由於這個 binlog 檔案中有刪除資料庫的語句，那麼就會導致執行完該 binlog 之後，javaboy 庫又被刪除了。
  • –database=javaboy 表示恢復 javaboy 這個庫。

另外還有一個我們這裡沒用到的引數叫做 --start-position，這個表示起始的 Pos，不指定的話表示從頭開始資料恢復。

好啦，弄完之後，再來檢視資料庫：

資料恢復啦～

注意：所有操作之前，記得該備份就備份（防止你操作錯了又回不去），鬆哥為了省事上面省略了一些備份操作。

5. 小結

好啦，今天這篇文章主要是和小夥伴們分享了 MySQL 的 binlog 紀錄檔，並通過一個小案例來演示如何通過 binlog 實現資料庫的刪庫恢復。好啦，感興趣的小夥伴可以試試哦（別在生產庫上試哦）～

推薦學習：

以上就是一起分析MySQL的binlog怎麼恢復資料的詳細內容，更多請關注TW511.COM其它相關文章！