一、是基于最新的完整备份+binlog进行数据恢复了，这时需要把备份导回去，还要找出Binlog DELETE前的pos位置，再进行binlog恢复，恢复完后再把记录恢复到误操的环境上。如果表很大，这时间要很久。
二、因为Binlog格式为ROW时，记录了行的修改，所以DELETE是可以看到所有列的值的，把binlog解析出来，找到被DELETE的记录，通过各种处理再恢复回去，但binlog不能基于一个库或表级别的解析，只能整个binlog解析再进行操作。
以上的方法都比较消耗时间，当然使用binlog-rollback.pl脚本有点类似第二种方法，但是binlog-rollback.pl可以指定库或表进行反向解析，还可以指定POS点，效率相当更高一些。
下面我们运行 binlog-rollback.pl 脚本，生成删除数据语句的反向SQL：
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]#
再次查看输出文件：
[root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `test`.`user` SET `id`=1, `name`='user1', `age`=20;
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='user2' AND `age`=30;
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='user1' AND `age`=20;
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`='aa';
[root@localhost mysql3306]#
刚刚DELETE的2条记录已经生成了反向INSERT语句，这样恢复就简单多啦：
INSERT INTO `test`.`user` SET `id`=1, `name`='user1', `age`=20;
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';
下面我们模拟修改数据的时候，误修改了，如下：
<Test>[xuanzhi]> select * from xuanzhi.tb1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
+------+------+
1 row in set (0.00 sec)
<Test>[xuanzhi]> select * from test.user;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 2 | user2 | 30 |
+------+-------+------+
1 row in set (0.00 sec)
<Test>[xuanzhi]> update xuanzhi.tb1 set name = 'MySQL' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
<Test>[xuanzhi]> update test.user set age = 20 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
<Test>[xuanzhi]>
这个时候发现修改错数据了，需要还原，同样可以使用脚本binlog-rollback.pl 进行对所在Binlog的DML生成反向的SQL，进行恢复：
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]#
再查看输出文件：
[root@localhost mysql3306]# cat /data/t.sql
UPDATE `test`.`user` SET `id`=2, `name`='user2', `age`=30 WHERE `id`=2 AND `name`='user2' AND `age`=20;
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='aa' WHERE `id`=1 AND `name`='MySQL';
INSERT INTO `test`.`user` SET `id`=1, `name`='user1', `age`=20;
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='user2' AND `age`=30;
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='user1' AND `age`=20;
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`='aa';
[root@localhost mysql3306]#
可以看到生成了反向的UPDATE语句：
UPDATE `test`.`user` SET `id`=2, `name`='user2', `age`=30 WHERE `id`=2 AND `name`='user2' AND `age`=20;
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='aa' WHERE `id`=1 AND `name`='MySQL';
下面进行指定库的反向解析，参数为（-d）
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456' -d 'xuanzhi'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='aa' WHERE `id`=1 AND `name`='MySQL';
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`='aa';
[root@localhost mysql3306]#
可以看到输入的文件只含xuanzhi库的所有DML的反向SQL。
下面进行指定库下某个表的反向解析，参数为：-T （为了看到效果在xuanzhi库下的tb2表删除一些记录）：