在上一篇文章,我們講解了有關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資料庫指定的部分
<b>一、 </b><b>建立新的資料庫ailanni</b>
使用以下指令建立資料庫,如下:
mysql –h 192.168.1.213 -uroot -p123456
create database ailanni;
show databases;
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yMi1WdoR3XxADMldWYtl2XwlGbj9CXxEzLcRTMwIzLcNHZh9GbwV3LcRnblRnbvNWLwd3Lc12bj5Sau5WYslmL3d3dvw1LcpDc0RHaiojIsJye.png)
<b>二、 </b><b>建立新的表ilannitable</b>
使用以下指令建立表,如下:
use ailanni;
create table ilannitable(id int(4));
show tables;
<b>三、 </b><b>向表ilannitable插入資料</b>
使用以下指令向表ilannitable中插入資料1、2,如下:
insert into ilannitable values(1);
insert into ilannitable values(2);
select id from ilannitable;
<b>四、 </b><b>重新整理mysqlbin日志</b>
此時mysql的binlog日志檔案隻有一個mysql-bin.000001,如下:
show master logs;
注意:此時mysql的binlog檔案為mysql-bin.000001,并且資料庫ailanni中隻有1、2兩條資料。如下:
現在我們來重新整理binlog日志,生成新的binlog日志檔案mysql-bin.000002,如下:
flush 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
<b>五、 </b><b>完整備份ailanni資料庫</b>
現在我們來完整的備份ailanni資料庫,使用mysqldump指令,如下:
/usr/local/mysql/bin/mysqldump -uroot -p123456 ailanni >/root/ailanni.sql
檢視備份檔案是否與ailanni資料庫的内容一緻,如下:
cat ailanni.sql
通過上圖可知,備份的資料庫檔案與ailanni資料庫的内容是一緻的。
<b>六、 </b><b>删除表ilannitable插入的部分資料</b>
在第三、四步中,我們向表ilannitable中插入了1、2、3、4四個資料。為了模拟在ailanni資料庫被破壞後的實際情形,我們現在把ailanni資料庫中的3這條資料給删除,如下:
delete from ilannitable where id=3;
此時我們再次重新整理binlog日志,生成新的binlog日志檔案為mysql-bin.000004,如下:
<b>七、 </b><b>删除資料庫ailanni</b>
在第六步中,我們删除了資料記錄3,現在我們來删除ailanni資料庫。如下:
drop database ailanni;
删除完畢後,我們再次重新整理binlog日志,生成新的binlog日志檔案為mysql-bin.000005,如下:
<b>八、 </b><b>各個binlog日志的内容解析</b>
現在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.000003
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000004
<b>九、 </b><b>恢複ailanni全部資料</b>
現在我們要恢複ailanni資料庫,在此首先有關ailanni資料庫是否存在的情況要說明下。
如果目前mysql資料庫伺服器上有ailanni這個資料庫,那麼我們在恢複資料時,就不需要重新建立ailanni資料庫。如果目前mysql資料庫伺服器上沒有ailanni這個資料庫,那麼我們就需要重新建立一個空的ailanni資料庫。即該資料庫隻是一個空的庫,裡面沒有任何表或者其他的元素。
如果不建立ailanni資料庫的話,我們在通過mysql指令恢複資料庫時,系統就會報錯,如下:
在第五步時,我們備份的資料庫是ailanni資料庫全部的資料。如果要恢複ailanni資料庫全部資料的話,我們隻需要執行mysql指令導入備份的sql檔案即可。如下:
/usr/local/mysql/bin/mysql -uroot -p123456 ailanni</root/ailanni.sql
檢視恢複後的ailanni資料庫是否和被删除之前的資料一樣,如下:
mysql -uroot -p123456
通過上圖,我們可以很明顯的看到目前ailanni資料庫已經被全部恢複,而且資料一個也沒有丢失。
<b>十、 </b><b>恢複ailanni資料庫指定的部分</b>
在通過mysql的binlog日志恢複資料庫時,我們可以指定資料庫恢複的位置、恢複的時間,同時也可以不指定資料庫恢複的位置和時間。
有關mysqlbinlog指令的使用方法,我們可以通過mysqlbinlog的幫助指令進行檢視,如下:
/usr/local/mysql/bin/mysqlbinlog –help
<b>注意圖中标記出來的數字,其中1和2是表示通過binlog恢複資料的開始和結束時間,3和4表示的binlog恢複資料的開始和結束位置。</b>
<b>其中時間我們都知道,看下binlog的日志檔案内容就知道了。而位置就是我們在前面檢視各個binlog日志内容時标記出來的數字部分。如下:</b>
<b>圖中被标出來的黃色部分數字5表示時間,6表示位置的節點,位置節點我們也稱為binlog的pos點。</b>
<b>10.1</b><b>不指定位置和時間來恢複資料庫</b>
我們先來測試不指定時間和位置來恢複資料庫。
在第九步中我們恢複了ailanni全部的資料庫,現在我們要求把資料庫恢複到删除資料記錄3時的狀态。即ailanni資料庫中隻有1、2、4,這三條資料。這個時候我們就要根據mysql的binlog日志進行恢複,要不然達不到這個要求。
在第八步中我們已經分析了各個binlog日志中執行的sql語句。根據分析,我們需要使用mysql-bin.000003這個binlog日志來恢複資料庫。現在我們再次檢視該binlog日志,如下:
删除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,這三條資料。這樣也達到我們的要求了。
<b>重要說明:</b>
<b>通過上面的文章,我們知道現在ailanni資料庫的情況是,已經恢複全部資料。</b>
<b>為了做下面的實驗,我們就要把ailanni資料庫恢複到還沒有進行完全備份之前的狀态,即ailanni資料庫還沒有資料的狀态。我們需要做的是清空現在的ailanni資料庫,保持ailanni是一個空的資料庫。但是ailanni資料庫的表結構、主鍵、外鍵等等資訊都是完整的。</b>
<b>同時還要說明下:如果在同一個時間點,binlog日志中有執行過多條sql語句的話,那麼我們在恢複資料庫時,一定要根據pos點的位置來恢複資料,切記切記。因為此時在binlog日志中,時間節點是一樣的,但是pos位置節點是唯一的。</b>
<b>是以在通過binlog恢複資料庫時,強烈建議使用pos位置節點的方法來恢複資料。</b>
清空ailanni資料庫使用以下指令,如下:
delete from ilannitable;
有關這個情況,我會再寫一篇文章。
現在我們得到的是一個沒有資料的ailanni資料庫,這樣我們下面的實驗就可以繼續進行。
<b>10.2</b><b>指定時間來恢複資料庫</b>
要求恢複到删除資料記錄3時的資料庫。即資料庫中隻有1、2、4,這三條資料。
還是和10.1要求是一樣的,但是這次我們根據時間點來恢複資料。我們也需要一個空的ailanni資料庫,這個我們可以通過前面的說明可以得到。
現在我們再次檢視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
通過上圖,我們可以看到通過時間點來恢複資料庫也達到我們的要求,并且ailanni資料庫中确實隻有1、2、4,這三條資料。
<b>10.3</b><b>指定位置來恢複資料庫</b>
要求把資料庫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日志檔案,如下:
注意圖中标記出來的數字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資料庫告一段落。