数据库update、delete误操作，使用binlog2sql手动回退

前言

当你看到了这篇文章，相信你现在可能非常着急。
如果你失误搞砸了线上数据，你暂时不适合看这篇文章，你要做的是抓紧向上反映，越快越好，及时止损。
如果你现在不太着急，比如误操作了测试环境或者一些相对不着急的冷数据，或者仅仅想做一些知识储备，那么接着往下看。

前提条件

先贴一下开源项目的地址, 如果有条件的话可以直接看官方文档
https://github.com/danfengcao/binlog2sql

MySQL必须开启了bin-log

注意: 必须是误操作之前就开启过了, 不是发现有问题了之后才去开启这些配置

1. 通过查看MySQL配置文件

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
1
2
3
4
5
6

2. 通过MySQL命令查看

show variables like '%log_bin%';
1

开启状态

未开启状态

3. 是否处于开启状态

如果没有开启bin-log功能, 那么这篇文章就帮不到你了.

如果处于开启状态, 就接着往下看吧.

操作环境

下面说一下我的测试环境

• MySQL5.7.38
• Linux+python2.7.17
• Windows+python3.7.9

模拟数据

随便搞一个表, 搞点数据进去

然后整一个全表更新

update test_roll_back set name = 'test';
1

使用方法

1. 找到相应的bin-log

首先你需要知道, 误操作的sql被记录在哪一个日志文件中. 一般如果发现及时的话, 就是在最新的日志文件中. 如果不是的话, 就需要知道sql执行的大致时间区间. 通过日志的更新时间确定它被记录在哪个日志文件里.

2. MySQL用户

准备建立连接的MySQL用户必须拥有以下权限

select, super/replication client, replication slave

建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 
1
2
3
4

权限说明

• select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
• super/replication client：两个权限都可以，需要执行’SHOW MASTER STATUS’, 获取server端的binlog列表
• replication slave：通过BINLOG_DUMP协议获取binlog内容的权限

安装

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

拉取项目完成后, 进入项目目录安装 requirements.txt

# Linux
pip install -r requirements.txt
# Windows
python -m  pip install -r requirements.txt
1
2
3
4

使用

基本使用

进入项目目录/binlog2sql/binlog2sql
执行以下语句, 可以从bin-log中解析出历史执行SQL语句
不懂参数的意思可以往下看, 下面有全部参数的注释
把相关参数改成自己需要的(需要注意单引号问题)

python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'
1

注意: 在Windows环境下，所有的单引号需要改为双引号

我的测试bin-log比较短小, 如果是较大的log文件则需要通过参数过滤

添加时间过滤

最常用的就是确定语句执行的时间，并添加过滤条件

python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -padmin -dtest -t test_roll_back --start-file=mysql-bin.000001 --start-datetime='2022-11-02 15:30:00' --stop-datetime='2022-11-02 15:40:00'
1

可以看到, 成功过滤出了参数时间内的sql

回滚

只需要在上面的基本命令上添加--flashback 或 -B参数即可

python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -padmin -dtest -t test_roll_back --start-file=mysql-bin.000001 --start-datetime='2022-11-02 15:30:00' --stop-datetime='2022-11-02 15:40:00' -B
1

成功生成了回退sql
接着执行回退语句就大功告成了

输出到文件

需要将生成的sql输出到文件需要添加> 文件路径

python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -padmin -dtest -t test_roll_back --start-file=mysql-bin.000001 --start-datetime='2022-11-02 15:30:00' --stop-datetime='2022-11-02 15:40:00' -B > rollback.sql
1

这样就OK了

参数说明

mysql连接配置

-h host; -P port; -u user; -p password

解析模式

–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。

解析范围控制

–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。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。