每次在测试环境或者开发环境恢复数据都是一件让人崩溃的事情，前期，不是没开binlog就是完全没备份，后期规定binlog必须开，而且备份每天都要做。今天我们要说的不是全备恢复，也不是增量恢复，说的是数据误删除恢复。开发和测试经常会找来说，帮我恢复一条数聚，刚改错了，得怎么办：
一般情况下: 恢复全库-->增量恢复到删除时间前: 这有时候真的很浪费时间。曾经git上有大神写了一个shell脚本，专门针对此类恢复，用起来很不错的，只是他那里只是列出来执行过的语句和值，恢复的语句需要自己手工去改。今天我们使用一个perl版本的恢复工具，并且会自动列出恢复语句。
1.首先查看mysql配置
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
-----如果以上两个没开，兄弟可以休息去了，别往下看了，真的。
2.工具如下：
[root@db3 tmp]# perl binlog-rollback.pl
==========================================================================================
Command line options :
--help # OUT : print help info
-f, --srcfile # IN : binlog file. [required]
-o, --outfile # OUT : output sql file. [required]
-h, --host # IN : host. default '127.0.0.1'
-u, --user # IN : user. [required]
-p, --password # IN : password. [required]
-P, --port # IN : port. default '3306'
--start-datetime # IN : start datetime
--stop-datetime # IN : stop datetime
--start-position # IN : start position
--stop-position # IN : stop position
-d, --database # IN : database, split comma
-T, --table # IN : table, split comma. [required] set -d
-i, --ignore # IN : ignore binlog check contain DDL(CREATE|ALTER|DROP|RENAME)
--debug # IN : print debug information
Sample :
shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd'
shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' -i
shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --debug
shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -h '192.168.1.2' -u 'user' -p 'pwd' -P 3307
shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107
shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107 --stop-position=10000
shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' -d 'db1,db2'
shell> perl binlog-rollback.pl -f 'mysql-bin.0000*' -o '/tmp/t.sql' -u 'user' -p 'pwd' -d 'db1,db2' -T 'tb1,tb2'
==========================================================================================
[root@db3 tmp]#
3.实战来了:
1.查看当前binlog：
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| log_bin | ON |
| log_bin_basename | /u01/logbin3/db3 |
| log_bin_index | /u01/logbin3/db3.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------+
[root@db1 logbin3]# more /u01/logbin3/db3.index
/u01/logbin3/db3.000001
/u01/logbin3/db3.000002
/u01/logbin3/db3.000003
/u01/logbin3/db3.000004
==flush 一下，搞一个干净的binlog;
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
[root@db1 logbin3]# more /u01/logbin3/db3.index
/u01/logbin3/db3.000001
/u01/logbin3/db3.000002
/u01/logbin3/db3.000003
/u01/logbin3/db3.000004
/u01/logbin3/db3.000005
2.删除数聚：
mysql> show tables;
+----------------+
| Tables_in_db03 |
+----------------+
| t01 |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> select * from t01;
+------+------+
| id | name |
+------+------+
| 100 | abc |
+------+------+
1 row in set (0.00 sec)
mysql> delete from t01 where id=100;
Query OK, 1 row affected (0.00 sec)
3.恢复数据：
perl binlog-rollback.pl -f 'db3.000005' -o '/u01/logbin3/db3.sql' -u 'root' -p '123456'
[root@db1 logbin3]# perl binlog-rollback.pl -f 'db3.000005' -o '/u01/logbin3/db3.sql' -u 'root' -p '123456' -i
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@db1 logbin3]# more db3.sql
INSERT INTO `db03`.`t01` SET `id`=100, `name`='abc';
4.多操作几遍：
[root@db1 logbin3]# perl binlog-rollback.pl -f 'db3.000005' -o '/u01/logbin3/db3.sql' -u 'root' -p '123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@db1 logbin3]#
[root@db1 logbin3]#
[root@db1 logbin3]# more db3.sql
INSERT INTO `db03`.`t01` SET `id`=300, `name`='ccc';
INSERT INTO `db03`.`t01` SET `id`=200, `name`='bbb';
DELETE FROM `db03`.`t01` WHERE `id`=300 AND `name`='ccc';
DELETE FROM `db03`.`t01` WHERE `id`=200 AND `name`='bbb';
INSERT INTO `db03`.`t01` SET `id`=100, `name`='abc';
是不是很清晰很明了。
因为不能上传文件，如有需要，可以添加公众号：源数聚