MySQL 透過 binlog 恢復資料

lufeijun1234發表於2018-12-04

透過了解 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 協議》,轉載必須註明作者和本文連結

相關文章