MySQL透過binlog恢復資料的方法介紹（程式碼範例）

這篇文章帶給大家的內容是關於MySQL透過binlog恢復資料的方法介紹（程式碼範例），有一定的參考價值，有需要的朋友可以參考一下，希望對你有所幫助。

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

mysql 日誌檔案

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

mysql 主要有以下幾種日誌：

• 錯誤日誌：記錄mysql 運行過程中的錯誤訊息

• ##一般查詢日誌：記錄mysql正在執行的語句，包括查詢、修改、更新等的每個sql

• #慢查詢日誌：記錄查詢比較耗時的SQL 語句

• binlog 日誌：記錄資料修改記錄，包括建立表格、資料更新等

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

mysql --verbose --help|grep -A 1 'Default options’ #该命令会罗列出my.cnf 顺序查找的路径。

binlog 日誌

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

• 資料恢復：當資料庫誤刪或發生不可描述的事情時，可以透過binlog 恢復到某個時間點的資料。

• 主從複製：當有資料庫更新之後，主庫透過binlog 記錄並通知從庫進行更新，從而保證主從資料庫資料一致；

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

binlog 日誌開啟

日誌開啟方式：

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 日誌格式

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

  * statement 格式：记录数据库执行的原始 SQL 语句
  * row 格式：记录具体的行的修改，这个为目前默认值
  * mixed 格式：因为上边两种格式各有优缺点，所以就出现了 mixed 格式

binlog 日誌檢視工具：mysqlbinlog

因為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 恢復數據，本質上就是透過binlog 找到所有DML 操作，去掉錯誤的SQL 語句，然後重走一遍長徵路，就可以將數據恢復；

離線實操：

建立資料表並插入初始值

    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);

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

    mysqldump -uroot -p T > /path/xxx.sql;   # 备份数据库
    show master status;   # 查看当前的position位置，此时值为154

插入多筆記錄

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

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

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

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

透過mysqlbinlog 指令對二進位檔案進行分析，分析發現

    误操作发生在position为706位置，且上次正常操作的结束位置在513
    在1152到结尾位置有正常执行的SQL执行

透過mysqlbinlog指令從binlog 日誌匯出可執行的SQL 文件，並將資料匯入到mysql

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

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

小結

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

這篇文章到這裡就全部結束了，關於MySQL的更多知識大家可以關注php中文網的

MySQL教學欄位！ ！ ！

以上是MySQL透過binlog恢復資料的方法介紹（程式碼範例）的詳細內容。更多資訊請關注PHP中文網其他相關文章！