使用MySQL binlog进行不完全恢复

创建一张表，对其插入数据，假设数据被误删除

mysql> create database zwc;
Query OK, 1 row affected (0.02 sec)

mysql> use zwc
Database changed
mysql> create table t_recover(id int auto_increment primary key,val int,data varchar(50));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_recover(val,data) values (10,'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_recover(val,data) values (20,'bbb');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_recover(val,data) values (30,'ccc');
Query OK, 1 row affected (0.00 sec)

产生第二个binlog

mysql> flush logs;      
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_recover(val,data) values (40,'ccc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_recover(val,data) values (50,'ddd');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_recover(val,data) values (60,'eee');
Query OK, 1 row affected (0.00 sec)

mysql> delete from t_recover where id between 4 and 5;
Query OK, 2 rows affected (0.02 sec)

mysql> insert into t_recover(val,data) values (70,'fff');
Query OK, 1 row affected (0.00 sec)

产生第三个binlog

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_recover(val,data) values (80,'ggg');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_recover(val,data) values (90,'hhh');
Query OK, 1 row affected (0.00 sec)

mysql> drop table t_recover;
Query OK, 0 rows affected (0.00 sec)

分析3个binlog日志

[root@dbsql mysql_3307]# /service/mysql_3307/bin/mysqlbinlog 3307.000009 > 09.log
[root@dbsql mysql_3307]# /service/mysql_3307/bin/mysqlbinlog 3307.000010 > 10.log
[root@dbsql mysql_3307]# /service/mysql_3307/bin/mysqlbinlog 3307.000011 > 11.log

找到相应的position，对t_recover表进行全数据恢复

[root@dbsql mysql_3307]# /service/mysql_3307/bin/mysqlbinlog --start-position=287 --stop-position=848 3307.000009 | /service/mysql_3307/bin/mysql
[root@dbsql mysql_3307]# /service/mysql_3307/bin/mysqlbinlog --start-position=134 --stop-position=523 3307.000010 | /service/mysql_3307/bin/mysql
[root@dbsql mysql_3307]# /service/mysql_3307/bin/mysqlbinlog --start-position=659 3307.000010 | /service/mysql_3307/bin/mysql
[root@dbsql mysql_3307]# /service/mysql_3307/bin/mysqlbinlog --start-position=134 --stop-position=384 3307.000011 | /service/mysql_3307/bin/mysql

验证t_recover表

mysql> select * from t_recover;
+----+------+------+
| id | val  | data |
+----+------+------+
|  1 |   10 | aaa  |
|  2 |   20 | bbb  |
|  3 |   30 | ccc  |
|  4 |   40 | ccc  |
|  5 |   50 | ddd  |
|  6 |   60 | eee  |
|  7 |   70 | fff  |
|  8 |   80 | ggg  |
|  9 |   90 | hhh  |
+----+------+------+
9 rows in set (0.00 sec)