|
|
|
|
公众号矩阵

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

数据是现代大小厂的重要资产,保护和恢复数据成为了重要的技能,最近几年,常有一些无良程序员删库跑路的情况,不仅给所在企业更是给自己造成重大的损失。

作者: 派森酱 来源:Python技术|2021-06-08 07:48

本文转载自微信公众号「Python技术」,作者派森酱。转载本文请联系Python技术公众号。

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

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

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

神器出场

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

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

安装

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

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

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

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

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

  1. pip install -r requirements.txt 

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

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

  1. > python binlog2sql.py 
  2.  
  3. 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] 
  4.                      [--start-datetime START_TIME] [--stop-datetime STOP_TIME] [--save-as SAVE_AS] [--stop-never] [--help] [-d [DATABASES ...]] [-t [TABLES ...]] [--only-dml] 
  5.                      [--sql-type [SQL_TYPE ...]] [-K] [-B] [--back-interval BACK_INTERVAL] 
  6.  
  7. Parse MySQL binlog to SQL you want 
  8.  
  9. ...<省略>... 

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

简介

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

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

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

身手不凡

是骡子是马拉出来溜溜。

恢复被删数据

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

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

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

  1. delete from tb_user 

如何恢复呢?

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

  1. show master status; 

会看到类似这样的结果

  1. +------------------+-----------+ 
  2. | File             | Position  | 
  3. +------------------+-----------+ 
  4. | mysql-bin.000002 |     13136 | 
  5. +------------------+-----------+ 

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

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

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

  1. 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' 

输出为:

  1. 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 
  2. 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 
  3. 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 
  4. 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 
  5. 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 之间,于是锁定精确位置,生成回滚语句:

  1. 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,即生成的是撤销之前操作的语句

输出为:

  1. 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 
  2. 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 
  3. 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 
  4. 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 模块[4],

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

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

文件处理上下文(context)

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

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

查看源码,会看到一个创建文件写法:

  1. @contextmanager 
  2. def temp_open(filename, mode): 
  3.     f = open(filename, mode) 
  4.     try: 
  5.         yield f 
  6.     finally: 
  7.         f.close() 
  8.         os.remove(filename) 

@contextmanager[5] 指示器可以将一个生成器[6],作为一个上下文管理器[7],

那么:

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

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

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

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

使用方式为:

  1. with temp_open(tmp_file, "w"as f_tmp 
  2.     ... 
  3.     f_tmp.write(sql + '\n'
  4.     ... 

这样无论如何只要 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/

【编辑推荐】

  1. 鸿蒙官方战略合作共建——HarmonyOS技术社区
  2. MySql+SSCMS系统
  3. MySQL 加锁范围三——普通索引和普通字段
  4. MySQL中记得用not null,不然就滚蛋!
  5. Veeam 备份还原MySQL
  6. 用实例带你了解 MySQL 全局锁
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

带你轻松入门 RabbitMQ

带你轻松入门 RabbitMQ

轻松入门RabbitMQ
共4章 | loong576

9人订阅学习

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

9人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

40人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微