<Test>[xuanzhi]> select * from tb2;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+------+------+
3 rows in set (0.04 sec)
<Test>[xuanzhi]> delete from xuanzhi.tb2 where id <2;
Query OK, 1 row affected (0.02 sec)
<Test>[xuanzhi]>
这个时候应该如果只指定xuanzhi库,那么tb1和tb2的DML操作的反向操作都会记录下来：
[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.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `xuanzhi`.`tb2` SET `id`=1, `name`='aa';
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]#
指定单个表tb2：
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456' -d 'xuanzhi' -T 'tb2'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `xuanzhi`.`tb2` SET `id`=1, `name`='aa';
[root@localhost mysql3306]#
因为上面删除了一条tb2的数据，所有���个文件就对应生成一条tb2的INSERT记录
下面进行POS点生成反向SQL：（--start-position= --stop-position=）
从上面的binlog可以看到开始的--start-position=1557 结束的--stop-position=1981，这一段binlog里做了UPDATE `test`.`user` ... 和 DELETE FROM `xuanzhi`.`tb2` ... 的操作，那么用binlog-rollback.pl应该会生成一个UPDATE和一个INSERT语句
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456' --start-position=1557 --stop-position=1981
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]# cat /data/t.sql
INSERT INTO `xuanzhi`.`tb2` SET `id`=1, `name`='aa';
UPDATE `test`.`user` SET `id`=2, `name`='user2', `age`=30 WHERE `id`=2 AND `name`='user2' AND `age`=20;
[root@localhost mysql3306]#
更多的测试，就看同学们了，有测试不当的地方请告诉我，大家一起学习。
总结：一、感谢那些有分享精神的大神们，让我们学到了更多的东西，但开源的脚本需要多测试。
二、误操的情况，时有发生，所以我们要做好备份，做好一些数据恢复的测试。