利用Mysqlbinlog日志恢復(fù)數(shù)據(jù)庫(kù)數(shù)據(jù)的操作指引

下文給大家?guī)?lái)關(guān)于利用Mysqlbinlog日志恢復(fù)數(shù)據(jù)庫(kù)數(shù)據(jù)的操作指引，感興趣的話(huà)就一起來(lái)看看這篇文章吧，相信看完利用Mysqlbinlog日志恢復(fù)數(shù)據(jù)庫(kù)數(shù)據(jù)的操作指引對(duì)大家多少有點(diǎn)幫助吧。

一、開(kāi)啟mysql-binlog日志

在mysql配置文件my.cnf加上如下配置

[mysqld]

log-bin=mysql-bin

binlog_do_db=bin_test

重啟mysql

service mysqld restart

二、備份數(shù)據(jù)庫(kù)

1）先查看一下當(dāng)前數(shù)據(jù)庫(kù)情況及binlog日志情況：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

3 rows in set (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 106 | bin_test | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

2）創(chuàng)建庫(kù)及數(shù)據(jù)：bin_test

mysql> create database bin_test;

Query OK, 1 row affected (0.00 sec)

mysql> use bin_test;

Database changed

mysql> create table abc(id int(10) primary key auto_increment,name varchar(255));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into abc(name) value('zhangsan');

Query OK, 1 row affected (0.00 sec)

mysql> insert into abc(name) value('lisi');

Query OK, 1 row affected (0.00 sec)

mysql> insert into abc(name) value('wangwu');

Query OK, 1 row affected (0.00 sec)

mysql> select * from abc;

+----+----------+

| id | name |

+----+----------+

| 1 | zhangsan |

| 2 | lisi |

| 3 | wangwu |

+----+----------+

3 rows in set (0.00 sec)

3）備份數(shù)據(jù)到/tmp/test.sql

[root@localhost ~]# whereis mysqldump

mysqldump: /usr/bin/mysqldump /usr/share/man/man1/mysqldump.1.gz

[root@localhost ~]# /usr/bin/mysqldump -uroot -p123456 bin_test > /tmp/test.sql

4）查看下binlog日志情況：

mysql> show binlog events in 'mysql-bin.000001';

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+

| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |

| mysql-bin.000001 | 106 | Query | 1 | 197 | create database bin_test |

| mysql-bin.000001 | 197 | Query | 1 | 337 | use `bin_test`; create table abc(id int(10) primary key auto_increment,name varchar(255)) |

| mysql-bin.000001 | 337 | Intvar | 1 | 365 | INSERT_ID=1 |

| mysql-bin.000001 | 365 | Query | 1 | 471 | use `bin_test`; insert into abc(name) value('zhangsan') |

| mysql-bin.000001 | 471 | Intvar | 1 | 499 | INSERT_ID=2 |

| mysql-bin.000001 | 499 | Query | 1 | 601 | use `bin_test`; insert into abc(name) value('lisi') |

| mysql-bin.000001 | 601 | Intvar | 1 | 629 | INSERT_ID=3 |

| mysql-bin.000001 | 629 | Query | 1 | 733 | use `bin_test`; insert into abc(name) value('wangwu') |

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+

9 rows in set (0.00 sec)

三、這時(shí)模擬誤操作（刪除數(shù)據(jù)庫(kù)）

此時(shí)突然數(shù)據(jù)庫(kù)損壞或者人為刪除

mysql> drop database bin_test;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

3 rows in set (0.00 sec)

再查看下binlog日志有沒(méi)有記錄刪庫(kù)的操作：

mysql> show binlog events in "mysql-bin.000001";

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+

| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |

| mysql-bin.000001 | 106 | Query | 1 | 197 | create database bin_test |

| mysql-bin.000001 | 197 | Query | 1 | 337 | use `bin_test`; create table abc(id int(10) primary key auto_increment,name varchar(255)) |

| mysql-bin.000001 | 337 | Intvar | 1 | 365 | INSERT_ID=1 |

| mysql-bin.000001 | 365 | Query | 1 | 471 | use `bin_test`; insert into abc(name) value('zhangsan') |

| mysql-bin.000001 | 471 | Intvar | 1 | 499 | INSERT_ID=2 |

| mysql-bin.000001 | 499 | Query | 1 | 601 | use `bin_test`; insert into abc(name) value('lisi') |

| mysql-bin.000001 | 601 | Intvar | 1 | 629 | INSERT_ID=3 |

| mysql-bin.000001 | 629 | Query | 1 | 733 | use `bin_test`; insert into abc(name) value('wangwu') |

| mysql-bin.000001 | 733 | Query | 1 | 822 | drop database bin_test |

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+

10 rows in set (0.00 sec)

可以看到第20條的操作為刪除數(shù)據(jù)庫(kù)的操作。

四、此時(shí)數(shù)據(jù)庫(kù)已經(jīng)被完全破壞

1）使用mysqlbinlog命令進(jìn)行恢復(fù)；首先在恢復(fù)前要確認(rèn)正確的position起始值與終止值：

mysql> show binlog events in "mysql-bin.000001";

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+

| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |

| mysql-bin.000001 | 106 | Query | 1 | 197 | create database bin_test |

| mysql-bin.000001 | 197 | Query | 1 | 337 | use `bin_test`; create table abc(id int(10) primary key auto_increment,name varchar(255)) |

| mysql-bin.000001 | 337 | Intvar | 1 | 365 | INSERT_ID=1 |

| mysql-bin.000001 | 365 | Query | 1 | 471 | use `bin_test`; insert into abc(name) value('zhangsan') |

| mysql-bin.000001 | 471 | Intvar | 1 | 499 | INSERT_ID=2 |

| mysql-bin.000001 | 499 | Query | 1 | 601 | use `bin_test`; insert into abc(name) value('lisi') |

| mysql-bin.000001 | 601 | Intvar | 1 | 629 | INSERT_ID=3 |

| mysql-bin.000001 | 629 | Query | 1 | 733 | use `bin_test`; insert into abc(name) value('wangwu') |

| mysql-bin.000001 | 733 | Query | 1 | 822 | drop database bin_test |

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+

10 rows in set (0.00 sec)

此時(shí)我們已經(jīng)確認(rèn)“起始值”為創(chuàng)建庫(kù)時(shí)的起始：106；"終止值"為drop庫(kù)時(shí)的前一句的結(jié)束：733

2）使用mysqlbinlog進(jìn)行恢復(fù)：

mysqlbinlog --no-defaults --start-position="106" --stop-position="733" /var/lib/mysql/mysql-bin.000001 |mysql -uroot -p

Enter password:

3）查看下數(shù)據(jù)是否恢復(fù)成功：

11mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| bin_test |

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

11mysql> select * from bin_test.abc;

+----+----------+

| id | name |

+----+----------+

| 1 | zhangsan |

| 2 | lisi |

| 3 | wangwu |

+----+----------+

3 rows in set (0.00 sec)

3）恢復(fù)成功

【總結(jié)】：mysql備份和bin-log日志

備份數(shù)據(jù)：

mysqldump -uroot -p123456 bin_test -l -F '/tmp/test.sql'

-l:讀鎖（只能讀取，不能更新）

-F:即flush logs，可以重新生成新的日志文件，當(dāng)然包括log-bin日志

查看binlog日志：

mysql>show master status

如果數(shù)據(jù)較多，先導(dǎo)入之前備份數(shù)據(jù)：

mysql -uroot -p123456 bin_test -v -f </tmp/test.sql

-v查看導(dǎo)入的詳細(xì)信息

-f是當(dāng)中間遇到錯(cuò)誤時(shí)，可以skip過(guò)去，繼續(xù)執(zhí)行下面的語(yǔ)句

恢復(fù)binlog-file二進(jìn)制日志文件：

mysqlbinlog --no-defaults binlog-file | mysql -uroot -p123456

從某一(567)點(diǎn)開(kāi)始恢復(fù)：

mysqlbinlog --no-defaults --start-position="567" mysql-bin.000001| mysql -uroot -p123456 test

先查好那一點(diǎn)（用more來(lái)查看）

[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000001 --start-position="794" --stop-position="1055" | more

然后恢復(fù)：

[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000001 --start-position="794" --stop-position="1055" | /usr/bin/mysql -uroot -p123456 test

重置binlog日志

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 106 | | |

+------------------+----------+--------------+------------------+

mysql> flush logs;#關(guān)閉當(dāng)前的二進(jìn)制日志文件并創(chuàng)建一個(gè)新文件，新的二進(jìn)制日志文件的名字在當(dāng)前的二進(jìn)制文件的編號(hào)上加1。 

看了以上關(guān)于利用Mysqlbinlog日志恢復(fù)數(shù)據(jù)庫(kù)數(shù)據(jù)的操作指引詳細(xì)內(nèi)容，是否有所收獲。如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。