前言

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

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

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

方法如下

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

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

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

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

5，查看最后一個(gè)binlog日志的編號名稱及其最后一個(gè)操作事件pos結(jié)束點(diǎn)的值：show master status;

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

每當(dāng)mysqld服務(wù)重啟時(shí)，會自動(dòng)執(zhí)行刷新binlog日志命令，mysqldump備份數(shù)據(jù)時(shí)加-F選項(xiàng)也會刷新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點(diǎn)406開始查詢，如下：

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

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

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

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

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

6的年齡修改成了30

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

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

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

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

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

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

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

發(fā)現(xiàn)執(zhí)行更新操作的事務(wù)區(qū)間為573到718，所以可以執(zhí)行以下命令來恢復(fù)這段數(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)下(只恢復(fù)這段事務(wù)區(qū)間的數(shù)據(jù)也就是更新的數(shù)據(jù))，恢復(fù)結(jié)果如下：

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

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

修改后將當(dāng)前數(shù)據(jù)庫刪除，(刪除庫之后再執(zhí)行一下flush logs創(chuàng)建一個(gè)新的日志文件，這樣新的操作都寫入到這個(gè)文件中，上個(gè)日志文件只用來恢復(fù)數(shù)據(jù)，不會有其余數(shù)據(jù)混入)現(xiàn)在我們通過時(shí)間點(diǎn)來恢復(fù)從備份數(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ù)得到了恢復(fù)

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

執(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ù)得到了恢復(fù)，如圖，

總結(jié)

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