MySQL通過binlog恢復數(shù)據(jù)

mysql 日志文件

任何成熟軟件都會有一套成熟的日志系統(tǒng)，當軟件出現(xiàn)問題時，這些日志就是查詢問題來源的寶庫。同樣，mysql也不例外，也會有一系列日志記錄mysql的運行狀態(tài)。

mysql主要有以下幾種日志：

• 錯誤日志：記錄mysql運行過程中的錯誤信息
• 一般查詢日志：記錄mysql正在運行的語句，包括查詢、修改、更新等的每條sql
• 慢查詢日志：記錄查詢比較耗時的SQL語句
• binlog日志：記錄數(shù)據(jù)修改記錄，包括創(chuàng)建表、數(shù)據(jù)更新等
  

這些日志均需要在my.cnf文件進行配置，如果不知道m(xù)ysql的配置文件路徑，可以使用mysql命令進行查找，

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

binlog日志

binlog就是binary log，二進制日志文件，記錄所有數(shù)據(jù)庫更新語句，包括表更新和記錄更新，即數(shù)據(jù)操縱語言(DML)，binlog主要用于數(shù)據(jù)恢復和配置主從復制等；

數(shù)據(jù)恢復：當數(shù)據(jù)庫誤刪或者發(fā)生不可描述的事情時，可以通過binlog恢復到某個時間點的數(shù)據(jù)。主從復制：當有數(shù)據(jù)庫更新之后，主庫通過binlog記錄并通知從庫進行更新，從而保證主從數(shù)據(jù)庫數(shù)據(jù)一致；

mysql按照功能分為服務層模塊和存儲引擎層模塊，服務層負責客戶端連接、SQL語句處理優(yōu)化等操作，存儲引擎層負責數(shù)據(jù)的存儲和查詢；binlog屬于服務層模塊的日志，即引擎無關性，所有數(shù)據(jù)引擎的數(shù)據(jù)更改都會記錄binlog日志。當數(shù)據(jù)庫發(fā)生崩潰時，如果使用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參數(shù)

 log-bin=/path/bin-log

當開啟binlog日志之后，mysql會創(chuàng)建一個 log_bin_index指定的 .index 文件和多個二進制日志文件，index中按順序記錄了mysql使用的所有binlog文件。binlog日志則會以指定的名稱(或默認值) 加自增的數(shù)字作為后綴，ex：bin-log.000001，當發(fā)生下述三種情況時，binlog日志便會進行重建:

文件大小達到max_binlog_size參數(shù)的值 執(zhí)行 flush logs命令 重啟mysql服務

binlog 日志格式

通過參數(shù)binlog_format參數(shù)的值，可以設置binlog的格式，可選值有 statement、row、mixed * statement格式：記錄數(shù)據(jù)庫執(zhí)行的原始SQL語句 * row格式：記錄具體的行的修改，這個為目前默認值 * mixed格式：因為上邊兩種格式各有優(yōu)缺點，所以就出現(xiàn)了mixed格式

binlog日志查看工具：mysqlbinlog

因為binlog是二進制文件，不能像其他文件一樣，直接打開查看。但mysql提供了binlog查看工具mysqlbinlog，可以解析二進制文件。當然不同格式的日志解析結果是不一樣的； 1. statement格式日志，執(zhí)行mysqlbinlog /path/bin-log.000001，可以直接看到原始執(zhí)行的SQL語句 2. row格式日志，則可讀性沒有那么好，但仍可通過參數(shù)使文檔更加可讀 mysqlbinlog -v /path/bin-log.000001

mysqlbinlog兩對非常重要的參數(shù) 1. --start-datetime --stop-datetime 解析某一個時間段內的binlog； 2. --start-position --stop-position 解析在兩個position之間的binlog；

使用binlog恢復數(shù)據(jù)

使用binlog恢復數(shù)據(jù)，本質上就是通過binlog找到所有DML操作，去掉錯誤的SQL語句，然后重走一遍長征路，就可以將數(shù)據(jù)恢復；

線下實操

1.創(chuàng)建數(shù)據(jù)表并插入初始值

    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.找到上一次全量備份的數(shù)據(jù)庫和binlog的position(ps：當然也可以通過時間進行恢復)。此處以目前狀態(tài)作為備份的初始值，

    mysqldump -uroot -p T > /path/xxx.sql;   # 備份數(shù)據(jù)庫
    show master status;   # 查看當前的position位置，此時值為154

3.插入多條記錄

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

4.進行誤操作，并且在誤操作之后又插入幾條數(shù)據(jù)

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

5.發(fā)現(xiàn)誤操作之后，進行數(shù)據(jù)恢復，首先停止mysql對外的服務，利用備份數(shù)據(jù)恢復到上次數(shù)據(jù)；

6.通過mysqlbinlog命令對二進制文件進行分析，分析發(fā)現(xiàn)

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

7.通過mysqlbinlog命令從binlog日志中導出可執(zhí)行的SQL文件，并將數(shù)據(jù)導入到mysql

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

8.跳過錯誤的更新語句，再通過步驟7的邏輯把后續(xù)正常語句重新跑一遍，完成數(shù)據(jù)恢復工作

小結

無論什么時間，數(shù)據(jù)庫發(fā)生崩潰都會令人愁眉緊鎖，心煩意亂。binlog可以說是在各種情況下，數(shù)據(jù)庫崩潰、數(shù)據(jù)丟失之后的一粒后悔藥，本文通過線下環(huán)境，簡單的對數(shù)據(jù)庫進行了一次數(shù)據(jù)恢復實驗，如有不對，還請指教

以上就是MySQL通過 binlog 恢復數(shù)據(jù)的詳細內容，更多關于MySQL binlog 恢復數(shù)據(jù)的資料請關注腳本之家其它相關文章！