MySQL中binlog2sql实现数据误删恢复工具

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

#安装pip
curl https://bootstrap.pypa.io/pip/2.7/get-pip.py -o get-pip.py

#mysql server必须设置以下参数(值不需要和下面相同，只要开启即可):
[mysqld] 
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1g
binlog_format = row 

#回滚user需要的最小权限集合：
select, super/replication client, replication slave 
建议授权 grant select, replication slave, replication client on *.* to

#权限说明
select：需要读取server端information_schema.columns表，获取表结构的元信息，拼接成可视化的sql语句
super/replication client：两个权限都可以，需要执行'show master status', 获取server端的binlog列表
replication slave：通过binlog_dump协议获取binlog内容的权限

#解析出标准sql
shell> python binlog2sql.py -h127.0.0.1 -p3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'
输出：
insert into `test`.`test3`(`addtime`, `data`, `id`) values ('2016-12-10 13:03:38', 'english', 4); 
update `test`.`test3` set `addtime`='2016-12-10 12:00:00', `data`='中文', `id`=3 where `addtime`='2016-12-10 13:03:22' and `data`='中文' and `id`=3 limit 1;
delete from `test`.`test3` where `addtime`='2016-12-10 13:03:38' and `data`='english' and `id`=4 limit 1; #start 981 end 1147
#解析出回滚sql
shell> python binlog2sql.py --flashback -h127.0.0.1 -p3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147
输出：
insert into `test`.`test3`(`addtime`, `data`, `id`) values ('2016-12-10 13:03:38', 'english', 4); 
update `test`.`test3` set `addtime`='2016-12-10 13:03:22', `data`='中文', `id`=3 where `addtime`='2016-12-10 12:00:00' and `data`='中文' and `id`=3 limit 1; 

#mysql连接配置
-h host; -p port; -u user; -p password
#解析模式
--stop-never 持续解析binlog。可选。默认false，同步至执行命令时最新的binlog位置。
-k, --no-primary-key 对insert语句去除主键。可选。默认false
-b, --flashback 生成回滚sql，可解析大文件，不受内存限制。可选。默认false。与stop-never或no-primary-key不能同时添加。
--back-interval -b模式下，每打印一千行回滚sql，加一句sleep多少秒，如不想加sleep，请设为0。可选。默认1.0。

#解析范围控制
--start-file 起始解析文件，只需文件名，无需全路径 。必须。
--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime 起始解析时间，格式'%y-%m-%d %h:%m:%s'。可选。默认不过滤。
--stop-datetime 终止解析时间，格式'%y-%m-%d %h:%m:%s'。可选。默认不过滤。

#对象过滤
-d, --databases 只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。
-t, --tables 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。
--only-dml 只解析dml，忽略ddl。可选。默认false。
--sql-type 只解析指定类型，支持insert, update, delete。多个类型用空格隔开，如--sql-type insert delete。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

mysql> select * from db_status;
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
| id   | status | location_id | external_ip   | dbname               | created_date        | modified_date       |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
|    0 |      0 | ind01       | 1292 		  | gachar               | 2020-11-24 17:15:16 | 1997-01-01 00:00:00 |
| 1808 |      1 | ind01       | 1293          | null                 | 2020-11-25 15:34:09 | 1997-01-01 00:00:00 |
| 1809 |      1 | us01        | 1294 		  | change				 | 1970-01-02 00:00:00 | 1997-01-01 00:00:00 |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
3 rows in set (0.08 sec)

#误删除全表
mysql> delete from web_db_status;
query ok, 3 rows affected (0.20 sec)

mysql> select * from web_db_status;
empty set (0.00 sec)
可以看到表中数据已经被删除

#查看当前binlog的位点
mysql> show master status\g;
*************************** 1. row ***************************
             file: mysql_bin.000020
         position: 94967010
     binlog_do_db:
 binlog_ignore_db:
executed_gtid_set:
1 row in set (0.00 sec)

#最新的binlog文件是mysql_bin.000020，我们再定位误操作sql的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据
#注意这里可以根据位点和时间判断，因为binlog文件都会有写入时间
#如果知道位点直接位点方式过滤，如果不知道可以用大概时间解析出标准sql
shell>python binlog2sql/binlog2sql.py -h127.0.0.1 -p 3306 -u root -p -dtest -tweb_db_status --start-file='mysql_bin.000020' --start-datetime='2021-01-05 16:00:00' --stop-datetime='2021-01-05 16:22:00'
输出:
delete from `test`.`web_db_status` where `status`=0 and `modified_date`='1997-01-01 00:00:00' and `external_ip`='1292' and `dbname`='gachar' and `created_date`='2020-11-24 17:15:16' and `location_id`='ind01' and `id`=0 limit 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04
delete from `test`.`web_db_status` where `status`=1 and `modified_date`='1997-01-01 00:00:00' and `external_ip`='1293' and `dbname` is null and `created_date`='2020-11-25 15:34:09' and `location_id`='ind01' and `id`=1808 limit 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04
delete from `test`.`web_db_status` where `status`=1 and `modified_date`='1997-01-01 00:00:00' and `external_ip`='1294' and `dbname`='change' and `created_date`='1970-01-02 00:00:00' and `location_id`='us01' and `id`=1809 limit 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04
#可以看到上面解析出来了我们刚才删除的三条sql语句（注意这里命令行没有加-b参数即解析出标准sql）

#接下来我们直接解析回滚sql（命令中加入-b参数 即--flashback）
shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -p 3306 -u root -p -b -dtest -tweb_db_status --start-file='mysql_bin.000020' --start-datetime='2021-01-05 16:00:00' --stop-datetime='2021-01-05 16:22:00' > rollback.sql
shell> cat rollback.sql
insert into `test`.`web_db_status`(`status`, `modified_date`, `external_ip`, `dbname`, `created_date`, `location_id`, `id`) values (1, '1997-01-01 00:00:00', '1294', 'change', '1970-01-02 00:00:00', 'us01', 1809); #start 94966615 end 94966979 time 2021-01-05 16:05:04
insert into `test`.`web_db_status`(`status`, `modified_date`, `external_ip`, `dbname`, `created_date`, `location_id`, `id`) values (1, '1997-01-01 00:00:00', '1293', null, '2020-11-25 15:34:09', 'ind01', 1808); #start 94966615 end 94966979 time 2021-01-05 16:05:04
insert into `test`.`web_db_status`(`status`, `modified_date`, `external_ip`, `dbname`, `created_date`, `location_id`, `id`) values (0, '1997-01-01 00:00:00', '1292', 'gachar', '2020-11-24 17:15:16', 'ind01', 0); #start 94966615 end 94966979 time 2021-01-05 16:05:04
#可以看到已经生成了反向插入sql，直接将这些sql执行到数据库中

#确认一下生成的回滚sql使我们对应的表的反向sql，是否正确，确认无误导入数据库
mysql> source /root/binlog2sql/rollback.sql;
query ok, 1 row affected (0.04 sec)

query ok, 1 row affected (0.00 sec)

query ok, 1 row affected (0.01 sec)

mysql> select * from db_status;
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
| id   | status | location_id | external_ip   | dbname               | created_date        | modified_date       |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
|    0 |      0 | ind01       | 1292 		  | gachar               | 2020-11-24 17:15:16 | 1997-01-01 00:00:00 |
| 1808 |      1 | ind01       | 1293          | null                 | 2020-11-25 15:34:09 | 1997-01-01 00:00:00 |
| 1809 |      1 | us01        | 1294 		  | change				 | 1970-01-02 00:00:00 | 1997-01-01 00:00:00 |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
3 rows in set (0.08 sec)

#对比前后的数据
#数据已经成功恢复