MySQL误删数据或者误清空表恢复

时间:2024-02-15 15:13:45

前提:

确认MySQL server开启了binlog,设置了以下参数:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog-format = row

没有开启的话,那真的无法快速回滚,以下方法不适用。

一、 情形1: 使用第三方工具

这里以第三方工具Navicat为例,注意,请查看第三方客户端是否有日志功能

1. 误删几条记录

可以直接从日志读取相关记录,手动转化为insert语句

2. 误删整张表表

也就是点击了清空表,这也是本次遇到的问题,在日志里只有DELLECT from TableName日志,没有详细的行记录,需要使用情形2的方法,日志如图:

如图,误删了一张表,然后刷新发现里边啥都没有了。一脸懵逼o((⊙﹏⊙))o

二、 情形2: 没有客户端,直接命令行删除了整张表

如果是直接使用了命令,或者其他语言接口,难以找到操作日志,那么情形1的两种方法都需要利用binlog2sql快速闪回
Github链接:binlog2sql,具体使用方法在readme中已经有了很详细的说明,以下试我的使用过程:

步骤一:查找binlog

show master status;


找到binlog路径,默认在这个文职,否则看数据库配置的里手动设定的存储路径:

步骤二:安装binlog2sql数据恢复工具

binlog2sql是一款简单易用的binlog解析工具,其中一个功能就是生成回滚SQL。

git clone https://github.com/danfengcao/binlog2sql.git
cd binlog2sql
pip install -r requirements.txt

我测试2.7后续操作会报错,后来换成了python3.7,测试通过。python环境建议直接用python3.
环境准备好后,把上一步查到的最新binlog复制到项目目录下,如图:

到此准备工作完毕

步骤三: 开始模糊查询,并定位范围

想办法回想误删的时间点,开始用起始时间和结束时间模糊查询,找到误删的位置
如果是用了第三方工具,可以直接从日志里找删除时间,如图我的误删时间是[2021-01-07 15:11:49.054]

然后运行命令,解析binlog,定位并确认误删的位置。

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p\'admin\' -dDatabaseName -tTableNameDeleted --start-file=\'binlog.000007\' --start-datetime=\'2021-01-07 15:11:48\' --stop-datetime=\'2021-01-07 15:11:50\'\' 

这里注意替换相应的数据库配置和数据库名称、待查询的表名称

操作的结果是详细的操作每条记录的语句。这一步可以修改起始结束时间重复操作,直到只有我们误删的记录。 然后随便找一条误删的记录,注意后面的注释:

最终,我们可以得到误操作的sql语句序号是图中的start(24908391) 到 end(24920250 )的范围。

步骤四:将误删的SQL语句转化为insert语句

-python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p\'admin\' dDatabaseName -tTableNameDeleted --start-file=\'binlog.000007\' --start-position=24908391 --stop-position=24920250 -B > rollback.sql | cat

其中数据库等配置同上,start-positionstop-position替换成刚刚我们查出的start(24908391) 和 end(24920250 )值.
然后我们会得到插入语句rollback.sql,如图:

之后执行这些插入语句就可以恢复啦。结束