MySQL 透過 binlog 恢復資料

lufeijun1234發表於2018-12-04
MySql

透過了解 binlog 日誌的相關配置，簡單掌握透過 binlog 對資料庫進行資料恢復操作；

任何成熟軟體都會有一套成熟的日誌系統，當軟體出現問題時，這些日誌就是查詢問題來源的寶庫。同樣，mysql 也不例外，也會有一系列日誌記錄 mysql 的執行狀態。

mysql 主要有以下幾種日誌：

  • 錯誤日誌：記錄 mysql 執行過程中的錯誤資訊
  • 一般查詢日誌：記錄mysql正在執行的語句，包括查詢、修改、更新等的每條sql
  • 慢查詢日誌：記錄查詢比較耗時的SQL語句
  • binlog日誌：記錄資料修改記錄，包括建立表、資料更新等

這些日誌均需要在my.cnf檔案進行配置，如果不知道mysql的配置檔案路徑，可以使用mysql命令進行查詢，

mysql --verbose --help|grep -A 1 'Default options’ #該命令會羅列出my.cnf順序查詢的路徑。

binlog就是binary log，二進位制日誌檔案，記錄所有資料庫更新語句，包括表更新和記錄更新，即資料操縱語言(DML)，binlog主要用於資料恢復和配置主從複製等；

  • 資料恢復：當資料庫誤刪或者發生不可描述的事情時，可以透過binlog恢復到某個時間點的資料。
  • 主從複製：當有資料庫更新之後，主庫透過binlog記錄並通知從庫進行更新，從而保證主從資料庫資料一致；

mysql按照功能分為服務層模組和儲存引擎層模組，服務層負責客戶端連線、SQL語句處理最佳化等操作，儲存引擎層負責資料的儲存和查詢；binlog屬於服務層模組的日誌，即引擎無關性，所有資料引擎的資料更改都會記錄binlog日誌。當資料庫發生崩潰時，如果使用InnoDB引擎，binlog日誌還可以檢驗InnoDB的redo日誌的commit情況。

日誌開啟方式：

1、新增配置

log_bin=ON
log_bin_basename=/path/bin-log
log_bin_index=/path/bin-log.index

2、僅僅設定log-bin引數

 log-bin=/path/bin-log

當開啟binlog日誌之後，mysql會建立一個 log_bin_index指定的 .index 檔案和多個二進位制日誌檔案，index中按順序記錄了mysql使用的所有binlog檔案。binlog日誌則會以指定的名稱(或預設值) 加自增的數字作為字尾，ex：bin-log.000001，當發生下述三種情況時，binlog日誌便會進行重建:

檔案大小達到max_binlog_size引數的值
執行 flush logs命令
重啟mysql服務

透過引數binlog_format引數的值，可以設定binlog的格式，可選值有 statement、row、mixed

  • statement格式：記錄資料庫執行的原始SQL語句
  • row格式：記錄具體的行的修改，這個為目前預設值
  • mixed格式：因為上邊兩種格式各有優缺點，所以就出現了mixed格式

因為binlog是二進位制檔案，不能像其他檔案一樣，直接開啟檢視。但mysql提供了binlog檢視工具mysqlbinlog，可以解析二進位制檔案。當然不同格式的日誌解析結果是不一樣的；

  1. statement格式日誌，執行mysqlbinlog /path/bin-log.000001，可以直接看到原始執行的SQL語句
  2. row格式日誌，則可讀性沒有那麼好，但仍可透過引數使文件更加可讀 mysqlbinlog -v /path/bin-log.000001

mysqlbinlog兩對非常重要的引數

  1. --start-datetime --stop-datetime 解析某一個時間段內的binlog；
  2. --start-position --stop-position 解析在兩個position之間的binlog；

使用binlog恢復資料，本質上就是透過binlog找到所有DML操作，去掉錯誤的SQL語句，然後重走一遍長征路，就可以將資料恢復；

  1. 建立資料表並插入初始值

    CREATE TABLE `users` (
          `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
          `name` varchar(255) DEFAULT NULL,
          `age` int(8) DEFAULT NULL,
          PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 INSERT INTO `users` (`id`, `name`, `age`)
    VALUES
        (null, '姓名一', 5);

  2. 找到上一次全量備份的資料庫和binlog的position(ps：當然也可以透過時間進行恢復)。此處以目前狀態作為備份的初始值，

    mysqldump -uroot -p T > /path/xxx.sql;   # 備份資料庫
show master status;   # 檢視當前的position位置，此時值為154

  3. 插入多條記錄

    INSERT INTO `users` (`id`, `name`, `age`)
VALUES
 (null, '姓名二', 13),
 (null, '姓名三', 14),
 (null, '姓名四', 15),
 (null, '姓名五', 16),
 (null, '姓名六', 17);

  4. 進行誤操作，並且在誤操作之後又插入幾條資料

    update users set age = 5；
INSERT INTO `users` (`id`, `name`, `age`)
VALUES
(null, '姓名七', 16),
(null, '姓名八', 18);

  5. 發現誤操作之後，進行資料恢復，首先停止mysql對外的服務，利用備份資料恢復到上次資料；

  6. 透過mysqlbinlog命令對二進位制檔案進行分析，分析發現

    誤操作發生在position為706位置，且上次正常操作的結束位置在513
在1152到結尾位置有正常執行的SQL執行

  7. 透過mysqlbinlog命令從binlog日誌中匯出可執行的SQL檔案，並將資料匯入到mysql

    mysqlbinlog --start-position=154  --stop-position=513  bin-log.000001 > /path/bak.sql;
mysql -uroot -p < /path/bak.sql;

  8. 跳過錯誤的更新語句，再透過步驟7的邏輯把後續正常語句重新跑一遍，完成資料恢復工作

無論什麼時間，資料庫發生崩潰都會令人愁眉緊鎖，心煩意亂。binlog可以說是在各種情況下，資料庫崩潰、資料丟失之後的一粒後悔藥，本文透過線下環境，簡單的對資料庫進行了一次資料恢復實驗，如有不對，還請指教

http://www.ywnds.com/?p=12839
https://zhuanlan.zhihu.com/p/33504555

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章