【MySQL】删库别着急跑路（三）--binlog2sql闪回工具的使用

原创

mb5fd86d34c044c 2021-03-11 16:21:57 ©著作权

文章标签 java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者mb5fd86d34c044c的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.先聊聊Binlog

再说binlog2sql闪回工具之前，我们先聊下binlog。Binlog记录了MySQL数据库所有的DDL和DML操作。它在MySQL数据库里起着至关重要的作用。

Binlog的主要作用有以下几个：

1. 完成主从复制功能。主服务器的所有修改操作都会通过binlog发给从服务器，从而到达主从同步。

2. 数据修复。数据可以通过binlog进行基于时间点和位置的恢复操作，binlog2sql工具也是依赖于binlog来实现的。

3. 审计工作。可以通过binlog进行变更sql的审计工作，由于查询操作不记录在binlog中，所以无法完成查询操作的审计。

2.MySQL闪回工具--binlog2sql介绍

binlog2sql是大众点评开源的一款用于解析binlog的工具，可以从MySQL binlog解析出你要的SQL，根据不同选项可以得到原始SQL、回滚SQL、去除主键的INSERTSQL等。

主要用途：

1. 数据快速回滚（闪回）

2. 主从切换后master丢数据的修复。

3. 从binlog生成标SQL，带来的衍生功能。

Binlog2sql工具的下载地址：https://github.com/danfengcao/binlog2sql。

环境安装需要的依赖包—PyMySQL、python-pip、python-mysql-replication、wheel argpare。（具体安装包可联系作者索要）。

安装依赖包：

(1) PyMySQL安装shell>tar -zxvf PyMySQL-0.8.0.tar.gzshell>cd PyMySQL-0.8.0shell>python setup.py install(2) wheel安装shell>tar -xzvf wheel-0.31.0.tar.gzshell>cd wheel-0.31.0shell>python setup.py install(3) python-mysql-replication安装shell>unzip python-mysql-replication-master.zipshell>cd python-mysql-replication-mastershell>python setup.py install(4) 通过pip安装相应的依赖包shell>tar -xzvf pip-10.0.1.tar.gzshell>cd pip-10.0.1shell>python setup.py install(5) 解压binlog2sql软件shell>unzip binlog2sql-master.zipshell>cd /soft/binlog2sql-mastershell>pip install -r requirements.txt（前面包都安装成功后，这句就可以不用了）(6) user需要的最小权限mysql>GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

3.binlog2sql参数

(1) mysql连接配置：-h host; -P port; -u user; -p password(2) 解析模式--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。(3) 解析范围控制--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。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

4.应用案例

误删整张表数据，需要紧急回滚

1. 首先我们先看下test库中t表的数据

mysql> select * from t;+----+------+------+| id | name | age  |+----+------+------+|  1 | ss   |    5 ||  4 | jack |   16 ||  5 | tom  |   24 ||  6 | baby |   39 || 22 | lucy |    1 || 23 | zm   |    5 || 24 | wzm  |    5 || 25 | xc   |   23 |+----+------+------+8 rows in set (0.00 sec)

2.删除t表数据

delete from t; #删除时间是20:13:41。

3. 再次查询验证数据已经被删除。

mysql> select * from t;Empty set (0.00 sec)

恢复数据步骤

1. 首先查看当前的binlog文件

mysql> show master status;+------------+----------+--------------+------------------+-------------------+| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------+----------+--------------+------------------+-------------------+| efs.000003 |     1420 |              |                  |                   |+------------+----------+--------------+------------------+-------------------+

2. 查看binlog位置

最新的binlog文件是mysql-bin.000052，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。

[root@node1 binlog2sql]# python binlog2sql.py -uadmin -pAdmin@123 -d test -t t --start-file='efs.000003' --start-datetime='2020-05-08 20:10:00' --stop-datetime='2020-05-08 20:20:00'

【MySQL】删库别着急跑路（三）--binlog2sql闪回工具的使用_java

3. 生成回滚SQL

[root@node1 binlog2sql]# python binlog2sql.py -uadmin -pAdmin@123 -d test -t t --start-file='efs.000003' --start-position=1028 --stop-position=1389 -B > rollback.sql

【MySQL】删库别着急跑路（三）--binlog2sql闪回工具的使用_java_02

4. 执行语句回滚

[root@node1 binlog2sql]# mysql -uroot -p test <rollback.sql

5. 查看结果

mysql> select * from t;+----+------+------+| id | name | age  |+----+------+------+|  1 | ss   |    5 ||  4 | jack |   16 ||  5 | tom  |   24 ||  6 | baby |   39 || 22 | lucy |    1 || 23 | zm   |    5 || 24 | wzm  |    5 || 25 | xc   |   23 |+----+------+------+8 rows in set (0.00 sec)

5.总结与建议

我们用三篇文章来一起聊了数据库的备份和恢复，如果出现问题我们应该选用哪种更高效的手段来解决数据丢失的问题呢？

首先我们对数据丢失的场景进行分类：

1. 误删除某些行。

针对误删某行的场景，建议用闪回工具恢复数据，这时候binlog2sql工具就能派上用场了。

2. 误删库

这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份 binlog。

3. 误删表

如果表的数据量较小并且是通过delete删除的情况下，我们可以通过闪回工具恢复。如果数据量较大的话或者是通过drop删除的，可以先通过全备把这张表恢复到临时库，然后通过线上库的binlog进行恢复。

4. rm删除

这时候最好祈祷数据库不是单机了，这种情况最快恢复业务的办法就是把流量切到备库去了。

5. 总结建议