创建一张表,对其插入数据,假设数据被误删除
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)