通過binlog恢復mysql資料庫

在上一篇文章，我們講解了有關mysql的binlog日誌的基礎知識。這篇文章，我們來講解如何通過mysql的binlog日誌來恢復資料庫。

在使用binlog日誌來恢復資料庫之前，我們有一些前提工作需要做。步驟如下：

1、建立新的資料庫ailanni

2、建立新的表ilannitable

3、向表ilannitable插入資料

4、重新整理mysqlbin日誌

5、完整備份ailanni資料庫

6、刪除表ilannitable插入的部分資料

7、刪除資料庫ailanni

8、各個binlog日誌的內容解析

9、恢復ailanni全部資料

10、恢復ailanni資料庫指定的部分

一、 建立新的資料庫ailanni

使用以下命令建立資料庫，如下：

mysql –h 192.168.1.213 -uroot -p123456

create database ailanni;

show databases;

二、 建立新的表ilannitable

使用以下命令建立表，如下：

use ailanni;

create table ilannitable(id int(4));

show tables;

三、 向表ilannitable插入資料

使用以下命令向表ilannitable中插入資料1、2，如下：

insert into ilannitable values(1);

insert into ilannitable values(2);

select id from ilannitable;

四、 重新整理mysqlbin日誌

此時mysql的binlog日誌檔案只有一個mysql-bin.000001，如下：

show master logs;

注意:此時mysql的binlog檔案為mysql-bin.000001，並且資料庫ailanni中只有1、2兩條資料。如下：

select id from ilannitable;

現在我們來重新整理binlog日誌，生成新的binlog日誌檔案mysql-bin.000002，如下：

flush logs;

show master logs;

現在我們再次向ailanni資料庫中插入新的資料3、4，如下：

insert into ilannitable values(3);

insert into ilannitable values(4);

資料3、4插入完畢後，我們再次重新整理binlog日誌，生成新的binlog日誌檔案為mysql-bin.000003，如下：

現在我們總共有三個binlog日誌檔案：mysql-bin.000001、mysql-bin.000002、mysql-bin.000003。其中在mysql-bin.000001中，我們向ilannitable表中插入兩個資料1、2。在mysql-bin.000002中我們向ilannitable表中插入兩個資料3、4。

我們可以檢視binlog中的內容如下：

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

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

五、 完整備份ailanni資料庫

現在我們來完整的備份ailanni資料庫，使用mysqldump命令，如下：

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

檢視備份檔案是否與ailanni資料庫的內容一致，如下：

cat ailanni.sql

通過上圖可知，備份的資料庫檔案與ailanni資料庫的內容是一致的。

六、 刪除表ilannitable插入的部分資料

在第三、四步中，我們向表ilannitable中插入了1、2、3、4四個資料。為了模擬在ailanni資料庫被破壞後的實際情形，我們現在把ailanni資料庫中的3這條資料給刪除，如下：

delete from ilannitable where id=3;

此時我們再次重新整理binlog日誌，生成新的binlog日誌檔案為mysql-bin.000004，如下：

flush logs;

show master logs;

七、 刪除資料庫ailanni

在第六步中，我們刪除了資料記錄3，現在我們來刪除ailanni資料庫。如下：

drop database ailanni;

show databases;

刪除完畢後，我們再次重新整理binlog日誌，生成新的binlog日誌檔案為mysql-bin.000005，如下：

flush logs;

show master logs;

八、 各個binlog日誌的內容解析

現在mysql的binlog日誌檔案有5個，從mysql-bin.000001到mysql-bin.000005，其中在mysql-bin.000001中我們向ilannitable表中插入了1、2兩條資料。

在mysql-bin.000002中我們向ilannitable表中插入了3、4兩條資料，在mysql-bin.000003中我們從表ilannitable刪除了3這條資料。在mysql-bin.000004中我們把ailanni資料庫給刪除了。

各個binlog日誌的SQL操作語句，如下：

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

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

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

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

九、 恢復ailanni全部資料

現在我們要恢復ailanni資料庫，在此首先有關ailanni資料庫是否存在的情況要說明下。

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

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

在第五步時，我們備份的資料庫是ailanni資料庫全部的資料。如果要恢復ailanni資料庫全部資料的話，我們只需要執行mysql命令匯入備份的sql檔案即可。如下：

create database ailanni;

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

檢視恢復後的ailanni資料庫是否和被刪除之前的資料一樣，如下：

mysql -uroot -p123456

use ailanni;

select id from ilannitable;

通過上圖，我們可以很明顯的看到目前ailanni資料庫已經被全部恢復，而且資料一個也沒有丟失。

十、 恢復ailanni資料庫指定的部分

在通過mysql的binlog日誌恢復資料庫時，我們可以指定資料庫恢復的位置、恢復的時間，同時也可以不指定資料庫恢復的位置和時間。

有關mysqlbinlog命令的使用方法，我們可以通過mysqlbinlog的幫助命令進行檢視，如下：

/usr/local/mysql/bin/mysqlbinlog –help

注意圖中標記出來的數字，其中1和2是表示通過binlog恢復資料的開始和結束時間，3和4表示的binlog恢復資料的開始和結束位置。

其中時間我們都知道，看下binlog的日誌檔案內容就知道了。而位置就是我們在前面檢視各個binlog日誌內容時標記出來的數字部分。如下：

圖中被標出來的黃色部分數字5表示時間，6表示位置的節點，位置節點我們也稱為binlog的pos點。

10.1不指定位置和時間來恢復資料庫

我們先來測試不指定時間和位置來恢復資料庫。

在第九步中我們恢復了ailanni全部的資料庫，現在我們要求把資料庫恢復到刪除資料記錄3時的狀態。即ailanni資料庫中只有1、2、4，這三條資料。這個時候我們就要根據mysql的binlog日誌進行恢復，要不然達不到這個要求。

在第八步中我們已經分析了各個binlog日誌中執行的SQL語句。根據分析，我們需要使用mysql-bin.000003這個binlog日誌來恢復資料庫。現在我們再次檢視該binlog日誌，如下：

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

刪除3這條資料後，ailanni資料庫中的資料記錄情況也就是我們恢復資料庫後所要的狀態。如下：

現在我們來通過mysqlbin來恢復刪除3這條資料時的資料庫，使用如下命令：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000003 |mysql -uroot -p123456

通過上圖我們可以看到此時資料庫ailanni已經恢復到刪除資料3時的狀態了，ailanni資料庫中確實只有1、2、4，這三條資料。這樣也達到我們的要求了。

重要說明：

通過上面的文章，我們知道現在ailanni資料庫的情況是，已經恢復全部資料。

為了做下面的實驗，我們就要把ailanni資料庫恢復到還沒有進行完全備份之前的狀態，即ailanni資料庫還沒有資料的狀態。我們需要做的是清空現在的ailanni資料庫，保持ailanni是一個空的資料庫。但是ailanni資料庫的表結構、主鍵、外來鍵等等資訊都是完整的。

同時還要說明下：如果在同一個時間點，binlog日誌中有執行過多條sql語句的話，那麼我們在恢復資料庫時，一定要根據pos點的位置來恢復資料，切記切記。因為此時在binlog日誌中，時間節點是一樣的，但是pos位置節點是唯一的。

因此在通過binlog恢復資料庫時，強烈建議使用pos位置節點的方法來恢復資料。

清空ailanni資料庫使用以下命令，如下：

delete from ilannitable;

select id from ilannitable;

有關這個情況，我會再寫一篇文章。

現在我們得到的是一個沒有資料的ailanni資料庫，這樣我們下面的實驗就可以繼續進行。

10.2指定時間來恢復資料庫

要求恢復到刪除資料記錄3時的資料庫。即資料庫中只有1、2、4，這三條資料。

還是和10.1要求是一樣的，但是這次我們根據時間點來恢復資料。我們也需要一個空的ailanni資料庫，這個我們可以通過前面的說明可以得到。

現在我們再次檢視mysql-bin.000003檔案，如下：

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

注意圖中黃色部分標記出來的時間點，我們可以看到在這個時間點之後，資料3就被刪除了。我們恢復資料，就恢復到這個時間點即可達到要求。使用以下命令進行恢復，如下：

/usr/local/mysql/bin/mysqlbinlog –stop-datetime=’2014-11-17 11:30:43′ /usr/local/mysql/data/mysql-bin.000003 |mysql -uroot -p123456

mysql -uroot -p123456

use ailanni;

select id from ilannitable;

通過上圖，我們可以看到通過時間點來恢復資料庫也達到我們的要求，並且ailanni資料庫中確實只有1、2、4，這三條資料。

10.3指定位置來恢復資料庫

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

分析要達到此要求，我們只能通過binlog日誌的方式來恢復資料。根據第八步的binlog日誌可以得知，要想恢復到此時的資料庫，那麼我們就要使用mysql-bin.000002這個日誌檔案。

要到達上述的目的，我們需要先來恢復資料1、2，使用mysql-bin.000001檔案，如下：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 |mysql -uroot -p123456

資料1、2恢復後，我們現在正式開始恢復插入資料3後，未插入資料4時的資料庫。

再次檢視mysql-bin.000002日誌檔案，如下：

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

注意圖中標記出來的數字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

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

到此我們有關通過binlog日誌恢復mysql資料庫告一段落。