利用mysql-binlog日志恢复数据

当我们不小心清空或删除了表里的数据后,恰巧这时的数据还没有进行备份, 这时候想恢复数据的话, 就要用到mysql-binlog日志了,但前提是要先开启binlog日志。

1、开启binlog日志,编辑 /etc/my.cnf(本案例的配置文件所在的位置)

[mysqld]

log-bin=mysql-bin

重启mysql:

service mysql restart

2、查看binlog日志文件位置

[root@Aaron var]# ll
-rw-rw—-. 1 mysql mysql 27687 10月 20 12:51 mysql-bin.000001
-rw-rw—-. 1 mysql mysql 1131872 10月 20 12:51 mysql-bin.000002
-rw-rw—-. 1 mysql mysql 264 10月 20 12:51 mysql-bin.000003
-rw-rw—-. 1 mysql mysql 1116 10月 20 12:51 mysql-bin.000004
-rw-rw—-. 1 mysql mysql 126 10月 20 12:52 mysql-bin.000005
-rw-rw—-. 1 mysql mysql 3864276 10月 20 16:19 mysql-bin.000006
-rw-rw—- 1 mysql mysql 1808 10月 27 22:27 mysql-bin.000016
[root@Aaron var]# pwd
/usr/local/mysql/var

案例中binlog日志文件位置在  /usr/local/mysql/var

3、新建一个测试表,并插入数据:

CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test (id, title)INSERT INTO test (id, title)VALUES (NULL, ‘title1’), (NULL, ‘title2’), (NULL, ‘title3’);

mysql> select * from test;
+—-+——–+
| id | title |
+—-+——–+
| 1 | title1 |
| 2 | title2 |
| 3 | title3 |
+—-+——–+

这时进行数据备份,备份test库下test表到 /root/test.sql

[root@Aaron var]# mysqldump -uroot -p123123 test test > /root/test.sql
[root@Aaron var]# ls /root/test.sql
/root/test.sql
[root@Aaron var]#

4、插入新的记录, 并清空test表

mysql> INSERT INTO test (id, title)mysql> INSERT INTO test (id, title)VALUES (NULL, ‘title4’), (NULL, ‘title5’), (NULL, ‘title6’);

mysql> select * from test;
+—-+——–+
| id | title |
+—-+——–+
| 1 | title1 |
| 2 | title2 |
| 3 | title3 |
| 4 | title4 |
| 5 | title5 |
| 6 | title6 |
+—-+——–+

mysql> truncate table test;

mysql> select * from test;
Empty set

5、开始逐步恢复数据, 先恢复备份的数据

[root@Aaron var]# mysql -uroot -p123123 test < /root/test.sql

mysql> select * from test;
+—-+——–+
| id | title |
+—-+——–+
| 1 | title1 |
| 2 | title2 |
| 3 | title3 |
+—-+——–+

之前的数据已经恢复过来, 下面通过binlog日志来恢复4,5,6 三条数据,先来查看下,当前mysql最新的二进制文件是哪个

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000016 | 1531 | | |
+——————+———-+————–+——————+

最新的二进制日志文件为:mysql-bin.000016,所以, 我们从这个日志恢复数据(注意:因为没有对二进制文件进行刷新操作, 所以这个文件里面会有其它一些DELETE,TRUNCATE操作), 这个时候我们恢复的时候会用到两个参数,分别是:–start-position(从哪个位置恢复)和–stop-position(到哪个位置结束)

6、开始恢复,在本案例中,要恢复的数据开始位置是474,结束位置是632。  632位置以后, 有truncate操作, 所以这个操作是不能进行恢复的。

[root@Aaron var]# mysqlbinlog  ./mysql-bin.000016 –start-position=474  –stop-position=632 | mysql -uroot -p123123

mysql> select * from test;
+—-+——–+
| id | title |
+—-+——–+
| 1 | title1 |
| 2 | title2 |
| 3 | title3 |
| 4 | title4 |
| 5 | title5 |
| 6 | title6 |
+—-+——–+

7、 数据(4,5,6)已经恢复成功

数据备份
mysqldump -uroot -p123456 test -l -F ‘/tmp/test.sql’
-l:读锁(只能读取,不能更新)
-F:即flush logs,可以重新生成新的日志文件,当然包括log-bin日志

查看最新的binlog日志
show master status;

刷新binlog日志

flush logs; #关闭当前的二进制日志文件并创建一个新文件,新的二进制日志文件的名字在当前的二进制文件的编号上加1。

0

发表评论

电子邮件地址不会被公开。