长话短说：binlog2sql工具实际就是一个python脚本；利用这个脚本，可以将mysql的binlog日志翻译成对应的sql语句，并且可以生成对应的回滚sql；

当然，mysql自带的mysqlbinlog也可以翻译binlog日志，但只是翻译成了易于可读的日志，并不能用于直接执行，或者回滚；

一、用途数据快速回滚(闪回)主从切换后新master丢数据的修复从binlog生成标准SQL，带来的衍生功能

github.com：

二、安装

shell> git clone  && cd binlog2sqlshell> pip install -r requirements.txt

直接从github上拉取，或者下载压缩包；

三、使用前置条件

1、需要安装python环境和pip命令；

本环境安装的是python3.8；

2、mysql服务必须设置以下参数:

[mysqld]server_id = 100log_bin = /usr/local/mysql/binlog/master-bin	#开启binlog日志max_binlog_size = 1Gbinlog_format = rowbinlog_row_image = full

参数解读：

max_binlog_size 参数：

binlog日志的大小上限，文件达到1G的时候会自动切割；

binlog_format 参数：

binlog的日志格式（row记录每一行的变化）；

binlog_row_image 参数：

binlog_row_image ：前提是binlog_format 必须为row；

binlog_row_image参数可以设置三个合法值: FULL、MINIMAL、NOBLOB

FULL：binlog日志记录所有前镜像和后镜像

MINIMAL：binlog日志的前镜像只记录唯一识别列(唯一索引列、主键列)，后镜像只记录修改列。

NOBLOB：binlog记录所有的列，就像full格式一样。但对于BLOB或TEXT格式的列，如果他不是唯一识别列(唯一索引列、主键列)，或者没有修改，那就不记录。

四、binlog2sql 工具使用

1、提前创建好db_python库；

2、在db_python库中创建tb_test、tb_pycharm表

1、在表中插入数据

首先在 db_python库 的 tb_test表 中插入了4条数据；

在 db_python库的 tb_pycharm表 中也插入了1条数据；后又更新了一条数据；

mysql> show master logs;	#查看最新的binlog日志

[root@mysql2 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' --start-file='master-bin.000003'INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (24, '大黄蜂', '中国科幻大学', '机械设计'); #start 4 end 445 time 2020-12-01 11:34:43INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (25, '林玟书', '中南大学', '军事战略学'); #start 476 end 764 time 2020-12-01 11:35:01INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (26, '林国瑞', '中国农业大学', '人机与环境工程'); #start 795 end 1095 time 2020-12-01 11:35:13INSERT INTO `db_python`.`tb_pycharm`(`id`, `name`, `school`, `profession`) VALUES (7009, '徐小天', '复旦大学', '计算机应用'); #start 1126 end 1417 time 2020-12-01 11:37:27UPDATE `db_python`.`tb_pycharm` SET `id`=7008, `name`='苏大大', `school`='南京理工大学', `profession`='农业资源利用' WHERE `id`=7008 AND `name`='王明恭' AND `school`='南京理工大学' AND `profession`='农业资源利用' LIMIT 1; #start 1448 end 1805 time 2020-12-01 11:38:08

可以看到，使用binlog2sql翻译后，变成了可直接执行的sql语句；对，这些就对应了1和2中的操作；

并且sql语句后附带了事务的起始值和终止值，还有起始时间和终止时间；（可以用于过滤）

5、刷新binlog日志

在mysql命令行中 flush logs;

mysql> flush logs;

该命令相当于手动切割binlog日志，会生成一个新的binlog日志文件。当然，旧的binlog也会保留；此处是为了方便测试、查看，提升效果，所以手动切割了日志；

6、清空一个表

mysql> delete from db_python.tb_test;

清空db_python库tb_test表中的数据；

7、继续使用工具查看

[root@mysql2 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' -ddb_python -t tb_test --start-file='master-bin.000004' DELETE FROM `db_python`.`tb_test` WHERE `id`=23 AND `name`='刘华强' AND `school`='清华大学' AND `profession`='机械设计' LIMIT 1; #start 482 end 923 time 2020-12-01 11:50:52DELETE FROM `db_python`.`tb_test` WHERE `id`=24 AND `name`='大黄蜂' AND `school`='中国科幻大学' AND `profession`='机械设计' LIMIT 1; #start 482 end 923 time 2020-12-01 11:50:52DELETE FROM `db_python`.`tb_test` WHERE `id`=25 AND `name`='林玟书' AND `school`='中南大学' AND `profession`='军事战略学' LIMIT 1; #start 482 end 923 time 2020-12-01 11:50:52DELETE FROM `db_python`.`tb_test` WHERE `id`=26 AND `name`='林国瑞' AND `school`='中国农业大学' AND `profession`='人机与环境工程' LIMIT 1; #start 482 end 923 time 2020-12-01 11:50:52

翻译后的binlog，可以清楚的看到执行的delete语句；

9、执行回滚sql恢复数据

[root@mysql2 binlog2sql]# mysql -uroot -p < tb_test.sqlmysql> SELECT * FROM db_python.tb_test;+----+-----------+--------------------+-----------------------+| id | name      | school             | profession            |+----+-----------+--------------------+-----------------------+| 23 | 刘华强    | 清华大学           | 机械设计              || 24 | 大黄蜂    | 中国科幻大学       | 机械设计              || 25 | 林玟书    | 中南大学           | 军事战略学            || 26 | 林国瑞    | 中国农业大学       | 人机与环境工程        |+----+-----------+--------------------+-----------------------+4 rows in set (0.00 sec)

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。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

1、解析某库某表的binlog日志

python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' -ddb_python -t tb_test --start-file='master-bin.000002'

2、指定事务的位置

[root@mysql2 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' -ddb_python --start-file='master-bin.000004' --start-position=3043 --stop-position=3861INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (1, '张佳莹'); #start 3043 end 3294 time 2020-12-01 16:01:21INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (2, '大盖伦'); #start 3325 end 3576 time 2020-12-01 16:01:31INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (3, '虎式坦克'); #start 3607 end 3861 time 2020-12-01 16:01:52

3、指定时间范围

[root@mysql2 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' -ddb_python --start-file='master-bin.000004' --start-datetime='2020-12-01 16:01:00' --stop-datetime='2020-12-01 16:02:00'USE b'db_python';CREATE TABLE `tb_name` (`id`  int NOT NULL AUTO_INCREMENT ,`name`  varchar(255) NOT NULL ,PRIMARY KEY (`id`));INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (1, '张佳莹'); #start 3043 end 3294 time 2020-12-01 16:01:21INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (2, '大盖伦'); #start 3325 end 3576 time 2020-12-01 16:01:31INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (3, '虎式坦克'); #start 3607 end 3861 time 2020-12-01 16:01:52USE b'db_python';TRUNCATE TABLE tb_name;

binlog2sql工具只能用于 insert、update、delete 类型的操作，其他操作可以记录、翻译，但是无法生成回滚sql；

下期前言

生产环境中还是会出现误删库、表的操作。比如，使用navicat这种客户端连接后，以为操作的是测试数据库，删除库、表后才发现操作的是生产库。

那么该如何避免？

架构只能解决数据库单点故障和容灾切换的问题；定时备份确实可以保全数据，但无法肯定不是最新数据；（除非实时备份）通过binlog日志虽然可以恢复删库删表的操作，但是人为操作，难度较大，且容易出错；