mysql恢复应急预案,【MySQL】删库别着急跑路（三）--binlog2sql闪回工具的使用

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'

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

4.执行语句回滚

[root@node1 binlog2sql]# mysql -uroot -p test 

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. 总结建议

在我们的实际运维工作中，提前预防要比处理问题的意义要更大。对此，总结了以下几点预防建议供大家参考：

1. 备份！备份！一定要做好备份！

2. 收缩权限，权限最小化，尽量给研发或者DBA分配权限低的用户。

3. 搭建延迟复制的备库。

4. 针对不同场景提前制定应急预案。

5. 把 sql_safe_updates 参数设置为 on。没有 where 条件或者where条件中没有包含索引字段的delete 、 update操作，在执行时就会报错。

6. 制定好开发规范和运维操作规范， SQL语句要经过严格评审才能投产上线。

7. 能在备库执行的操作尽量在备库操作