使用MySQL binlog进行不完全恢复

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

mysql> create database zwc;Query OK, 1 row affected (0.02 sec)mysql> use zwcDatabase changedmysql> 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)