通過binlog恢復mysql備份之前的資料

爛泥行天下發表於2016-05-12

上一篇文章,我們講解了如何通過mysql的binlog日誌恢復mysql資料庫,文章連線為《爛泥:通過binlog恢復mysql資料庫》。其中我們提到了的備份資料庫要晚與要恢復的資料庫時間,即要恢復的資料庫在前,而備份的資料庫在後。

當時我提到說會單獨寫一篇文章講解這個情況,本篇文章我就來介紹如何通過binlog日誌恢復比備份資料庫早時的資料庫狀態。我們還是以上篇文章的資料及備份檔案為基礎,來進行本篇文章的講解。

ailanni資料庫完整備份時,其資料庫中有1、2、3、4,這四條資料。而此時mysql總共有三個binlog日誌檔案:mysql-bin.000001、mysql-bin.000002、mysql-bin.000003。其中在mysql-bin.000001中,我們向ilannitable表中插入兩個資料1、2。在mysql-bin.000002中我們向ilannitable表中插入兩個資料3、4。

現在我們要求把ailanni資料庫恢復到插入資料3後,未插入資料4時的狀態。即資料庫ailanni中只有資料1、2、3,這三條資料。

分析:要達到上述的要求,我們需要使用到mysql-bin.000002,這個binlog日誌檔案。然後根據binlog日誌恢復資料庫使,可以指定pos位置節點的方法,來恢復ailanni資料庫。

除此非常重要的是,我們還要先把ailanni資料庫先恢復到完整備份時的狀態,如果不先把ailanni資料庫先恢復到完整備份時的狀態,我們在使用binlog日誌進行恢復時,系統會報錯。

我們需要分以下幾個步驟,可以恢復題目要求的ailanni資料庫狀態。如下:

1、 完整恢復ailanni資料庫

2、 刪除恢復後ailanni資料庫中的全部資料

3、 通過binlog日誌恢復ailanni資料庫

一、完整恢復ailanni資料庫

要完整恢復ailanni資料庫,我們可以通過mysql命令來實現。但是在這之前,我們還要就有關ailanni資料庫是否存在的情況要說明下。

如果目前mysql資料庫伺服器上有ailanni這個資料庫,那麼我們在恢復資料時,就不需要重新建立ailanni資料庫。如果目前mysql資料庫伺服器上沒有ailanni這個資料庫,那麼我們就需要重新建立一個空的ailanni資料庫。即該資料庫只是一個空的庫,裡面沒有任何表或者其他的元素。

如果不建立ailanni資料庫的話,我們在通過mysql命令恢復資料庫時,系統就會報錯,如下:

clip_image001

現在我們來執行mysql命令匯入備份的sql檔案,如下:

create database ailanni;

/usr/local/mysql/bin/mysql -uroot -p123456 ailanni</root/ailanni.sql

mysql -uroot -p123456

use ailanni;

select id from ilannitable;

clip_image002

通過上圖,我們可以看到ailanni資料庫已經完全恢復。

二、刪除恢復後ailanni資料庫中的全部資料

在第一步中我們已經把ailanni資料庫全部恢復,現在我們需要清空ailanni資料庫。如下:

delete from ilannitable;

select id from ilannitable;

clip_image003

通過上圖,我們可以看到目前ailanni資料庫中已經沒有資料,是一個空的資料庫。

三、通過binlog日誌恢復ailanni資料庫

第二步已經清空ailanni資料庫,這樣我們就可以通過mysql的binlog日誌來恢復ailanni資料庫。

也許你會問我們為什麼要先完整恢復ailanni資料庫,然後再把ailanni資料庫清空?

這個是因為如果我們不先完整恢復ailanni資料庫的話,我們在使用binlog進行恢復ailanni資料庫時,系統會報錯的。如下:

clip_image004

如果我們不清空ailanni資料庫的話,你會發下恢復後的ailanni資料庫,還是沒有達到我們的要求。如下:

clip_image005

所以我們要進行第一、二步的操作。

現在我們來檢視mysql-bin.000002檔案,以確定ailanni資料庫恢復的pos位置節點,如下:

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000002

clip_image006

注意圖中標記出來的數字304,這個就是binlog的pos節點位置。我們可以看到在該pos節點後mysql才執行插入資料4的SQL語句。那麼我們只需要把資料恢復這個節點即可。

使用如下命令進行恢復資料,如下:

/usr/local/mysql/bin/mysqlbinlog –stop-position=304 /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p123456

clip_image007

通過上圖,我們現在可以看到ailanni資料庫已經恢復到插入資料3之後,未插入4的狀態。現在ailanni資料庫中確實只有1、2、3,這三條資料,已經達到我們的要求。

其實我們還可以這樣說,目前只有mysql的所有binlog日誌,而且還有一個比較完整的資料庫備份,那麼該如何恢復資料庫呢?

按照上述方法也是可以恢復的。


相關文章