妈妈再也不用担心我把数据弄丢了

数据是现代大小厂的重要资产，保护和恢复数据成为了重要的技能，

最近几年，常有一些无良程序员删库跑路的情况，不仅给所在企业更是给自己造成重大的损失，

另外，即使不是故意的情况下，也会因为疏忽造成数据误操作，是一件及麻烦又头疼的事情……

神器出场

最近的一个项目里，客户数据因为维护不当，导致数据丢失，为了挽回数据，并建立一个跨网闸（内部组网之间不通，无法使用 MySql 主从同步）的数据备份机制，发现了一个神器 binlog2sql。

研究了一番之后，不仅恢复了误操作丢失的数据，还通过 binlog2sql 将主服务器上的 binlog 转化为 SQL 语句，存入文件，实现了数据同步！

安装

binlog2sql 使用 Python 开发，所以需要 Python 环境，可参考 Python 环境搭建

将 binlog2sql 用 git 克隆的本地，GitHub 上的地址是: https://github.com/danfengcao/binlog2sql.git

git clone https://github.com/danfengcao/binlog2sql.git

通过 binlog2sql 目标下的 requirements.txt 安装依赖包

提示：推荐在 Python 虚拟环境中安装，创建虚拟环境可参考 Python 虚拟环境 看这一篇就够了

pip install -r requirements.txt

一切顺利的话，很快就可完成安装。

命令行进入 binlog2sql 代码目录下测试一下

> python binlog2sql.py

usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD ...]] [-P PORT] [--start-file START_FILE] [--start-position START_POS] [--stop-file END_FILE] [--stop-position END_POS]
                     [--start-datetime START_TIME] [--stop-datetime STOP_TIME] [--save-as SAVE_AS] [--stop-never] [--help] [-d [DATABASES ...]] [-t [TABLES ...]] [--only-dml]
                     [--sql-type [SQL_TYPE ...]] [-K] [-B] [--back-interval BACK_INTERVAL]

Parse MySQL binlog to SQL you want

...<省略>...

由于没加任何参数，所以打印出使用说明，那说明安装正常了。

简介

binlog2sql 是通过分析 MySql 数据库的 binlog 文件，从中解析出需要执行的 sql 语句的。

那么使用时需要提供一些必要的参数，其中重要的有数据库服务器链接信息，需要分析的 binlog 文件名等，

还可以指定解析的起始和结束位置，以及开始和结束时间。

身手不凡

是骡子是马拉出来溜溜。

恢复被删数据

假如库表 tb_user 中的数据如下：

+----+--------+---------------------+
| id | name   | createtime          |
+----+--------+---------------------+
|  1 | 张三   | 2021-01-10 00:04:33 |
|  2 | 李四   | 2021-01-10 00:04:48 |
|  3 | 王五   | 2021-04-23 20:25:00 |
|  4 | 赵六   | 2021-06-04 11:21:23 |
+----+--------+---------------------+

这时不小心执行了一个删操作，将数据误删了

delete from tb_user

如何恢复呢？

我们看一下数据库的日志情况

show master status;

会看到类似这样的结果

+------------------+-----------+
| File             | Position  |
+------------------+-----------+
| mysql-bin.000002 |     13136 |
+------------------+-----------+

注意：只有 MySql 数据库打开了日志记录功能，才能查询到，打开日志功能请参考 binlog日志开启和使用

可以看出，目前日志记录在文件 mysql-bin.000002 中，当前最新的记录位置是 12546 行

假如当时误操作的时间是上午 11点半左右（可能着急吃饭，没注意），那么预估一个时间范围，比如 11点25 到 11点35，看看一下当时的操作：

python binlog2sql -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t tb_user --start-file='mysql-bin.000002' --start-datetime='2021-06-04 11:25:00' --stop-datetime='2021-06-04 11:35:00'

输出为：

INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-06-04 11:21:23', 4, '李四'); #start 12317 end 12487 time 2021-06-04 11:21:23
DELETE FROM `test`.`tb_user` WHERE `createtime`='2021-01-10 00:04:33' AND `id`=1 AND `name`='张三' LIMIT 1; #start 12728 end 12829 time 2021-06-04 11:27:32
DELETE FROM `test`.`tb_user` WHERE `createtime`='2021-01-10 00:04:48' AND `id`=2 AND `name`='李四' LIMIT 1; #start 12728 end 12829 time 2021-06-04 11:27:32
DELETE FROM `test`.`tb_user` WHERE `createtime`='2021-04-23 20:25:00' AND `id`=3 AND `name`='王五' LIMIT 1; #start 12728 end 12829 time 2021-06-04 11:27:32
DELETE FROM `test`.`tb_user` WHERE `createtime`='2021-06-04 11:21:23' AND `id`=4 AND `name`='赵六' LIMIT 1; #start 12728 end 12829 time 2021-06-04 11:27:32

可以看出，第二行开始到第五行为删除语句，查看语句最后的起始和结束位置 start 12728 end 12829

即 binlog 中，删除执行的位置在 12728-12829 之间，于是锁定精确位置，生成回滚语句：

python binlog2sql -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t tb_user --start-file='mysql-bin.000002' --start-position=12728 --stop-position=12829 -B

注意参数 -B，意思是生成回滚 SQL，即生成的是撤销之前操作的语句

输出为：

INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-06-04 11:21:23', 4, '赵六'); #start 12728 end 12829 time 2016-12-13 20:28:05
INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-04-23 20:25:00', 3, '王五'); #start 12728 end 12829 time 2016-12-13 20:28:05
INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-01-10 00:04:48', 2, '李四'); #start 12728 end 12829 time 2016-12-13 20:28:05
INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-01-10 00:04:33', 1, '张三'); #start 12728 end 12829 time 2016-12-13 20:28:05

从输出的语句来看，顺序是删除的倒序，而且已经将原来的 delete 语句改为了 insert 语句，也就是原来操作的逆操作

如果确认语句没问题，执行生成的语句就可以了

是不是既方便又高效呢？

解析 SQL

binlog2sql 功能强大，使用起来也很方便，看看其他功能吧。

作为一个命令行工具，功能都体现在参数里，可分为 解析模式、解析目标、解析范围三部分。

解析模式

binlog2sql 支持两个解析模式，默认的是单次解析，即运行一次解析一次，

还可以支持持续解析，即不间断地从目标数据库地 binlog 中解析出 sql 来，持续解析通过参数 --never-stop 开启，

开启之后，线程不会退出，一直处于运行状态，会自动判断 binlog 的变化，对变化部分增量式解析。

这种模式可以用于数据库同步，不过生产上使用前，最好考虑各种异常情况，比如重启，网络中断等情况。

参数 -K 或 --no-primany-key 表示的去除 INSERT 语句中的主键，这个在数据汇总的场景下很方便，可以避免多个数据源中主键冲突的问题。

参数 -B 或 --flashback，表示回滚模式，在上面的例子中展示过，即会解析成逆操作的 sql 语句。

在回滚模式下，每生成一千条 SQL 语句会加一个 SLEEP 语句，是为以免数据执行时产生拥堵，默认为 1 秒，可以通过 --back-interval 参数来设置，

例如 --back-interval 2 表示暂停 2 秒。

解析目标

MySql 设置 binlog 时可以指定记录哪个库，以及哪些表，即目标。

那么用 binlog2sql 也可以指定解析目标。

参数 -d 或 --databases 用于指定数据库，如果多个库，用空格分隔，例如 -d db1 db2；

参数 -t 或 --tables 用于指定库表，多个库表用空格分隔，例如 -t tb1 tb2。

如果指定解析目标不仅效率更高，而且分析和执行解析的结果也更方便。

解析范围

范围包括 binlog 文件范围，时间范围 以及 行范围，例如前面例子中用到了 时间范围 和 行范围。

文件范围 用 --start-file 和 --stop-file 参数来指定，只需要提供 binlog 文件名即可，不需要写全路径，这是因为，binlog2sql 会自动根据目标服务器配置读取 binlog 文件；

时间范围 用 --start-datetime 和 --stop-datetime 参数来指定，时间格式为 %Y-%m-%d %H:%M:%S；

行范围 用 --start-position 和 --stop-position 参数来指定，也可以简写为 --start-pos 和 --end-pos。

深入了解

binlog2sql 不仅是一个实用的工具，而且也是个研究和学习的好例子。

只有不到 500 行代码，很容易阅读；

阅读源码，不仅能深入了解其实现原理，而且还可以学习到很多好用法。

实现原理

binlog2sql 的原理是，利用 pymysql 从目标服务器上获取 binlog 信息，然后锁定范围，使用 pymysqlreplication 解析 binlog 文件，最后，得到需要解析出的 sql 语句。

在这基础上，做了一些功能性扩展，比如解析范围，解析模式等，相对来说比较简单，很容易看懂。

命令行参数

编程时处理命令行参数是机械而繁琐的，特别是有不同性质的性质和别名的参数

binlog2sql 中利用了 argparse 模块，

argparse 模块可以让人轻松编写用户友好的命令行接口。程序定义它需要的参数，argparse 可以从 sys.argv 解析出提供的命令行参数。而且 argparse 模块还会自动生成帮助和使用手册，并在用户给程序传入无效参数时报出错误信息。

很容易就能编程高大上的命令行程序接口，再也不用为很 low 的程序接口发愁了。

文件处理上下文（context）

binlog2sql 在回滚模式（即提供了参数 -B）中，使用了一个临时文件记录解析出来的 SQL 语句，并且在完成之后删除。

一般来说，在完成后主动删除文件即可，不过如果能利用 with 块的资源回收功能就更好了。

查看源码，会看到一个创建文件写法：

@contextmanager
def temp_open(filename, mode):
    f = open(filename, mode)
    try:
        yield f
    finally:
        f.close()
        os.remove(filename)

@contextmanager 指示器可以将一个生成器，作为一个上下文管理器，

那么：

with 声明部分，会执行前会执行 yield 语句之前的部分

with 范围内，会执行 yield 语句，即返回一个需要后续处理的对象，比如文件，后续处理是关闭

with 执行完成前，会执行 yield 语句之后的代码

那么这段代码的意义就是，当文件使用完成后，关闭文件，并且删除掉。

使用方式为：

with temp_open(tmp_file, "w") as f_tmp
    ...
    f_tmp.write(sql + '\n')
    ...

这样无论如何只要 with 块执行完，文件就会被删除，不用担心忘记，是不是很优雅？

除了这两点，还有很多值得把玩的地方，有兴趣的话可以读读源码。

总结

无论是什么工具，都需要有一定的基础和良好的习惯上才会发挥作用，比如得开启 MySql 的 binlog 日志，并由记录工作的习惯。

同时，任何工具方法都有它的特点，可以在了解功能的同时，研究一下其使用原理，是一个很好的技能提升机会。

很多人在抱怨，没有应用场景，没有实际项目，其实研究这些工具，就会有事半功倍的效果。

比心