转载本文请联系Python技术公众号。数据是现代大大小小的工厂的重要资产。保护和恢复数据已成为一项重要技能。近年来，一些无良程序员经常删库跑路，不仅给公司造成很大损失，也给自己造成很大损失。另外，即使不是故意的，也会因为疏忽导致数据误操作，这是一件麻烦又麻烦的事情……神器外观最近的一个项目中，因为维护不当导致客户数据丢失。为了恢复数据，建立了跨网守的数据备份机制（内网不通，无法使用MySql主从同步），发现了一个神器binlog2sql[1]。经过一番研究，不仅恢复了误操作丢失的数据，还通过binlog2sql将主服务器上的binlog[2]转化为SQL语句，存入文件，实现了数据同步！安装binlog2sql是使用Python开发的，所以需要Python环境，可以参考Python环境用git在本地克隆一个binlog2sql。GitHub上的地址是：https://github.com/danfengcao/binlog2sql.gitgitclonehttps://github.com/danfengcao/binlog2sql.gitbinlog2sql目标下的Requirements.txt安装依赖包提示：建议安装在一个Python虚拟环境。创建虚拟环境，参考Python虚拟环境阅读本文。pipinstall-rrequirements.txt如果一切顺利，很快就会安装完成。命令行进入binlog2sql代码目录并测试>pythonbinlog2sql.pyusage:binlog2sql.py[-hHOST][-uUSER][-p[PASSWORD...]][-PPORT][--start-fileSTART_FILE][--start-positionSTART_POS][--stop-fileEND_FILE][--stop-positionEND_POS][--start-datetimeSTART_TIME][--stop-datetimeSTOP_TIME][--save-asSAVE_AS][--stop-never][--help][-d[DATABASES...]][-t[TABLES...]][--only-dml][--sql-type[SQL_TYPE...]][-K][-B][--back-intervalBACK_INTERVAL]ParseMySQLbinlogtoSQLyouwant...<省略>...因为没有加参数，所以打印出说明，说明安装正常。简介binlog2sql分析MySql数据库的binlog文件，解析出需要执行的sql语句。那么在使用的时候需要提供一些必要的参数，其中比较重要的有数据库服务器链接信息，需要分析的binlog文件名等，还可以指定分析的起止位置，以及开始和结束时间。不寻常的技能就像骡子和马。恢复删除的数据如果数据库表tb_user中的数据如下：+----+--------+--------------------+|id|name|createtime|+----+--------+--------------------+|1|张三|2021-01-1000:04:33||2|李斯|2021-01-1000:04:48||3|王武|2021-04-2320:25:00||4|赵刘|2021-06-0411:21:23|+----+--------+--------------------+此时不小心执行一次删除操作，误删了数据deletefromtb_user如何恢复？我们看一下数据库的日志状态showmasterstatus；你会看到这样的结果+----------------+------------+|File|Position|+----------------+------------+|mysql-bin.000002|13136|+-----------------+------------+注意：只有MySql数据库有启用日志记录的功能，你可以找到它。开启日志功能请参考binlog日志的开启与使用[3]。可以看到当前的日志记录在文件mysql-bin.000002中，当前最新的记录位置是12546行。上午11点30分左右（可能是赶时间吃饭，没注意），然后预估一个时间范围，比如11点25分到11点35分，看看当时的运行：pythonbinlog2sql-h127.0.0.1-P3306-uadmin-p'admin'-dtest-ttb_user--start-file='mysql-bin.000002'--start-datetime='2021-06-0411:25:00'--stop-datetime='2021-06-0411:35:00'输出为：INSERTINTO`test`.`tb_user`(`createtime`,`id`,`name`)VALUES('2021-06-0411:21:23',4,'Lisi');#start12317end12487time2021-06-0411:21:23DELETEFROM`test`.`tb_user`WHERE`createtime`='2021-01-1000:04:33'AND`id`=1AND`name`='张三'LIMIT1;#start12728end12829time2021-06-0411:27:32DELETEFROM`test`.`tb_user`WHERE`createtime`='2021-01-1000:04:48'AND`id`=2AND`name`='李四'LIMIT1;#start12728end12829time2021-06-0411:27:32DELETEFROM`test`.`tb_user`WHERE`createtime`='2021-04-2320:25:00'AND`id`=3AND`name`='WangWu'LIMIT1;#start12728end12829time2021-06-0411:27:32DELETEFROM`test`.`tb_user`WHERE`createtime`='2021-06-0411:21:23'AND`id`=4AND`name`='赵刘'LIMIT1;#start12728end12829time2021-06-0411:27:32可以看到，从第二行到第五行删除语句，查看语句的最终起止位置start12728end12829，也就是binlog中delete执行位置在12728-12829之间，所以精确位置被锁定，生成回滚语句：pythonbinlog2sql-h127.0.0.1-P3306-uadmin-p'admin'-dtest-ttb_user--start-file='mysql-bin.000002'--start-position=12728--停止位置tion=12829-B注意参数-B表示生成回滚SQL，即生成撤销之前操作的语句的输出：INSERTINTO`test`.`tb_user`(`createtime`,`id`,`name`)VALUES('2021-06-0411:21:23',4,'赵刘');#start12728end12829time2016-12-1320:28:05INSERTINTO`test`.`tb_user`(`createtime`,`id`,`name`)VALUES('2021-04-2320:25:00',3,'王武');#start12728end12829time2016-12-1320:28:05INSERTINTO`test`.`tb_user`(`createtime`,`id`,`name`)VALUES('2021-01-1000:04:48',2,'李四');#start12728end12829time2016-12-1320:28:05INSERTINTO`test`.`tb_user`(`createtime`,`id`,`name`)VALUES('2021-01-1000:04:33',1,'张三');#start12728end12829time2016-12-1320:28:05从输出语句来看，顺序是删除的逆序，将原来的delete语句改成了insert语句，是原来操作的逆操作。如果确认语句没问题，则执行生成的语句。是不是方便又高效？解析SQLbinlog2sql功能强大，简单易用。让我们看看其他功能。作为一个命令行工具，其功能都体现在参数上，分为三种：解析方式、解析目标、解析范围部分。解析模式binlog2sql支持两种解析模式。默认是单次解析，即运行一次解析一次，也可以支持连续解析，即不间断地从目标数据库的binlog中解析sql，通过参数--never-stop连续解析打开。开启后线程不会退出，一直处于运行状态。会自动判断binlog的变化，增量分析变化的部分。该模式可用于数据库同步，但在生产中使用前，最好考虑各种异常情况，如重启、网络中断等，参数-K或--no-primany-key表示primaryINSERT语句中的key去掉了，在数据聚合场景下非常方便，可以避免多数据源主键冲突的问题。参数-B或--flashback表示回滚方式，如上例所示，会被解析成逆向的sql语句。在回滚模式下，每产生1000条SQL语句就会添加一条SLEEP语句，避免数据执行过程中出现拥堵。默认1秒，可以通过--back-interval参数设置，比如--back-interval2表示暂停2秒。ParsetargetMySql在设置binlog时，可以指定记录哪些库和表，即target。那么binlog2sql也可以指定分析目标。参数-d或--databases用于指定数据库，如果有多个数据库，以空格分隔，如-ddb1db2；参数-t或--tables用于指定数据库表，多个数据库表之间用空格隔开，如-ttb1tb2。如果指定解析目标，不仅效率更高，而且解析执行结果也更方便。解析范围包括binlog文件范围、时间范围和行范围，如上例中使用的时间范围和行范围。文件范围由--start-file和--stop-file参数指定，只需要binlog文件名，不需要完整路径，因为binlog2sql会根据目标服务器配置自动读取binlog文件;time范围由--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语句。在此基础上做了一些功能扩展，比如分析范围、分析模式等，比较简单易懂。对命令行参数进行编程时，处理命令行参数比较机械和繁琐，尤其是不同属性和别名的参数。argparse模块[4]在binlog2sql中使用。argparse模块允许人们轻松编写用户友好的命令行界面。该程序定义了它接受的参数，argparse可以解析来自sys.argv的提供的命令行参数。此外，argparse模块还会自动生成帮助和用户手册，并在用户向程序传递无效参数时报告错误信息。轻松编写高端的命令行程序界面，再也不用为很低级的程序界面而烦恼了。文件处理上下文（context）binlog2sql在回滚方式（即提供参数-B）下使用一个临时文件记录解析出的SQL语句，完成后删除。一般来说，完成后主动删除文件就可以了，但如果能利用with块的资源回收功能就更好了。查看源码，会看到创建文件的方法：@contextmanagerdeftemp_open(filename,mode):f=open(filename,mode)try:yieldffinally:f.close()os.remove(filename)@contextmanager[5]指示器可以使用生成器[6]作为上下文管理器[7]，那么：with语句的部分将在执行yield语句之前执行。在with的范围内，会执行yield语句，即返回一个需要处理的对象，比如文件，后续处理是在执行前关闭yield语句后的代码完成了。这段代码的意思是当文件被使用时，文件被关闭并删除。使用方法是：withtemp_open(tmp_file,"w")asf_tmp...f_tmp.write(sql+'\n')...这样不管怎样只要执行with块就会删除文件，不用担心忘记吧？十分优雅？除了这两点，还有很多值得玩的地方。有兴趣的可以阅读源码。综上所述，无论是什么工具，都需要一定的基础和良好的习惯才能发挥作用。比如需要打开MySql的binlog日志，记录工作习惯。同时，任何工具方法都有自己的特点。在了解其功能的同时研究其使用原理，是提高技能的好机会。很多人都在抱怨没有应用场景，没有实际项目。事实上，研究这些工具会事半功倍。比心参考资料[1]binlog2sql:https://github.com/danfengcao/binlog2sql[2]binlog:https://laijianfeng.org/2019/03/MySQL-Binlog-%E4%BB%8B%E7%BB%8D/[3]启用并使用binlog日志：https://juejin.cn/post/6854573218485944333[4]argparse模块：https://docs.python.org/zh-cn/3/library/argparse。html[5]@contextmanager:https://www.liaoxuefeng.com/wiki/1016959663602400/1115615597164000[6]生成器:https://www.programiz.com/python-programming/generator[7]语境经理：https://www.geeksforgeeks.org/context-manager-in-python/
