程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 爛泥:通過binlog恢復mysql數據庫,binlogmysql

爛泥:通過binlog恢復mysql數據庫,binlogmysql

編輯:MySQL綜合教程

爛泥:通過binlog恢復mysql數據庫,binlogmysql


本文由秀依林楓提供友情贊助,首發於爛泥行天下。

在上一篇文章,我們講解了有關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數據庫告一段落。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved