前言

MySQL的binlog日志是MySQL日志中非常重要的一種日志，記錄了數(shù)據(jù)庫所有的DML操作。通過binlog日志我們可以進行數(shù)據(jù)庫的讀寫分離、數(shù)據(jù)增量備份以及服務器宕機時的數(shù)據(jù)恢復。

定期備份固然可以在服務器發(fā)生宕機的時候快速的恢復數(shù)據(jù)，但傳統(tǒng)的全量備份不可能做到實時，所以在發(fā)生宕機的時候，也會損傷一部分數(shù)據(jù)，如果這個時候開啟了binlog日志，那么可以通過binlog來對沒有做備份的這一階段損失的數(shù)據(jù)進行恢復

Binlog日志，即binary log，是二進制日志文件，有兩個作用，一個是增量備份，另一個是主從復制，即主節(jié)點維護一個binlog日志文件，從節(jié)點從binlog中同步數(shù)據(jù)，也可以通過binlog日志來恢復數(shù)據(jù)

方法如下

1，登錄mysql查看binlog日志的狀態(tài)，輸入show variables like ‘%log_bin%’;查看binlog為off關閉狀態(tài)

2，開啟mysql binlog日志，進入mysql配置文件(vi /etc/my.cnf) 在mysqld區(qū)域內(nèi)添加如下內(nèi)容，①server-id = 1(單個節(jié)點id) ②log-bin= /var/lib/mysql/mysql-bin(位置一般和mysql庫文件所在位置一樣) ③expire_logs_days = 10(表示此日志保存時間為10天),重啟mysqld,再次查看binlog日志開啟狀態(tài)為ON

3，Binlog日志包括兩類文件；第一個是二進制索引文件(后綴名為.index),第二個為日志文件(后綴名為.00000*),記錄數(shù)據(jù)庫所有的DDL和DML(除了查詢語句select)語句事件

4，查看所有binlog日志文件列表:show master logs;

5，查看最后一個binlog日志的編號名稱及其最后一個操作事件pos結束點的值：show master status;

6，F(xiàn)lush logs 刷新日志，此刻開始產(chǎn)生一個新編號的binlog文件，例如：

每當mysqld服務重啟時，會自動執(zhí)行刷新binlog日志命令，mysqldump備份數(shù)據(jù)時加-F選項也會刷新binlog日志

7，清空所有binlog日志命令:reset master;

8，查看binlog文件內(nèi)容，使用查看工具mysqlbinlog來查看(cat/vi/more都是無法打開的)

9，上面的方法讀取內(nèi)容較多不易觀察，以下命令更為方便查看命令：show binlog events in ‘mysql-bin.000003’;

10，指定查詢,從pos點406開始查詢，如下：

11，指定查詢，從pos點154開始查詢，中間跳過2行，查詢4條數(shù)據(jù)，如圖：

12，利用binlog日志恢復mysql數(shù)據(jù)，例如，現(xiàn)有一張數(shù)據(jù)表如圖：此表位于hello數(shù)據(jù)庫

現(xiàn)在將此數(shù)據(jù)庫備份到本地(模擬每周的備份情況)，備份命令如下

可以在數(shù)據(jù)備份之前或者之后執(zhí)行flush logs重新生成一個binlog日志用來記錄備份之后的所有增刪改操作(重新生成日志更好找pos點)，由于業(yè)務需求，現(xiàn)在對表進行插入

數(shù)據(jù)，修改數(shù)據(jù)，如圖新增了兩條數(shù)據(jù)id 為5和6，修改后數(shù)據(jù)如圖，

6的年齡修改成了30

13，由于操作失誤，誤刪除了數(shù)據(jù)庫，所有數(shù)據(jù)都不見了,此時可以通過binlog日志恢復數(shù)據(jù)，由于之前有做了數(shù)據(jù)庫備份，所以可以先將備份的數(shù)據(jù)導入進去，剩下缺少的就是備份之后操作所產(chǎn)生的內(nèi)容(備份之后執(zhí)行了插入內(nèi)容以及更改內(nèi)容)，先恢復備份的數(shù)據(jù)：創(chuàng)建庫hello并選擇庫(use hello)，通過命令source /root/hello.sql將數(shù)據(jù)庫內(nèi)容導入，然后執(zhí)行

14，恢復的內(nèi)容如下所示：

15，上面恢復的數(shù)據(jù)只是截止到備份時間的數(shù)據(jù)，剩下缺少的數(shù)據(jù)可以通過binlog日志來恢復，由于我們備份數(shù)據(jù)庫之前重新創(chuàng)建了mysql-bin.000006日志，所以備份后的所有操作都保存在這個日志中，可以先備份下這個日志文件，cp mysql-bin.000006 /root/ 接著執(zhí)行flush logs 刷新日志，重新創(chuàng)建了一個binlog日志7

16，重新創(chuàng)建一個日志7的目的：接下來所有操作的數(shù)據(jù)都會寫入到日志7中，日志6中不會在寫入任何數(shù)據(jù)(方便根據(jù)日志6的內(nèi)容恢復數(shù)據(jù)，因為日志6的數(shù)據(jù)就是備份之后到刪庫之前的所有操作日志，重建日志7不會有過多的數(shù)據(jù)影響恢復)

17，登錄mysql 查看日志6: show binlog events in ‘mysql-bin.000006’;結果如下：

查看日志發(fā)現(xiàn)，在備份數(shù)據(jù)后首先執(zhí)行的是插入數(shù)據(jù)操作(在一個事務內(nèi))，此時可以通過指定pos結束點恢復(部分恢復)，如圖，pos結束點位435(事務已提交表示結束)，執(zhí)行命令如下, /usr/bin/mysqlbinlog --stop-position=435 --database=hello /var/lib/mysql/mysql-bin.000006 | /usr/bin/mysql -uroot -p密碼 -v hello (其中整個命令的含義是通過mysqlbinlog讀取日志內(nèi)容并通過管道傳給mysql命令，-v表示執(zhí)行此mysql命令)執(zhí)行后查詢表發(fā)現(xiàn)如下：

備份之后插入的數(shù)據(jù)都出現(xiàn)了，目前還差一次修改的數(shù)據(jù)沒有找回來，接著看日志6如圖，

發(fā)現(xiàn)執(zhí)行更新操作的事務區(qū)間為573到718，所以可以執(zhí)行以下命令來恢復這段數(shù)據(jù)，/usr/bin/mysqlbinlog --start-position=573 --stop-position=718 --database=hello /var/lib/mysql/mysql-bin.000006 | /usr/bin/mysql -uroot -p密碼 -v hello注意其中的符號都是英文狀態(tài)下(只恢復這段事務區(qū)間的數(shù)據(jù)也就是更新的數(shù)據(jù))，恢復結果如下：

更改的數(shù)據(jù)回來了，也可以最開始的時候直接通過最終的pos結束點718來恢復

18，還有一種通過時間來恢復，還是先備份當前數(shù)據(jù)庫(備份之前先flush logs創(chuàng)建一個新的binlog日志文件9)，然后修改數(shù)據(jù)插入數(shù)據(jù)后如圖，

修改后將當前數(shù)據(jù)庫刪除，(刪除庫之后再執(zhí)行一下flush logs創(chuàng)建一個新的日志文件，這樣新的操作都寫入到這個文件中，上個日志文件只用來恢復數(shù)據(jù)，不會有其余數(shù)據(jù)混入)現(xiàn)在我們通過時間點來恢復從備份數(shù)據(jù)后到刪除數(shù)據(jù)庫這期間所有操作的數(shù)據(jù)，首先通過mysqlbinlog來了查看日志文件9如圖所示，

19，執(zhí)行命令：/usr/bin/mysqlbinlog --start-datetime="2018-04-27 20:57:55" --stop-datetime="2018-04-27 20:58:18" --database=hello /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p8856769abcd -v hello 更改的數(shù)據(jù)得到了恢復

20，插入數(shù)據(jù)的日志時間如圖：

執(zhí)行命令/usr/bin/mysqlbinlog --start-datetime="2018-04-27 20:58:18" --stop-datetime="2018-04-27 20:58:35" --database=hello /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p8856769abcd -v hello 插入的數(shù)據(jù)得到了恢復，如圖，

總結

到此這篇關于Mysql如何通過binlog日志恢復數(shù)據(jù)的文章就介紹到這了,更多相關Mysql binlog日志恢復數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！