Mysql 误删数据进行恢复

前言

由于使用 Navicat 连接服务器的 MySQL，经常需要添加和删除一些测试数据，但是由于不仔细，不小心删除了不需要删除的数据，具体恢复流程如下

操作流畅

1、第一步

SHOW VARIABLES LIKE '%log_bin%';

如果 log_bin 为 ON 说明可以恢复，如果为 OFF 则没有开启 binlog，也没有预先生成回滚 SQL，那可能就无法快速回滚了，不过好像可以通过 ibd 恢复，具体可以参考 blog.csdn.net/hanjun0612/article/d...

2、查看数据文件存放路径

SHOW VARIABLES LIKE '%datadir%';

打开数据库所在路径查看有mysql-bin.****这样的文件，注意DELETE删除的时间,对比mysql-bin文件的修改时间

3、找到 mysql 安装路径

SHOW VARIABLES LIKE '%basedir%';

mysqlbinlog.exe --no-defaults  --start-datetime="2021-11-10 15:30:00" --stop-datetime="2019-11-26 18:10:00" -vv D:\mysql-8.0.26-winx64\data\binlog.000009 > D:\update\mysqllog.sql

mysqlbinlog 命令的参数说明
--base64-output=decode-rows //数据转换正常的字符，如果不设置这个参数将显示base64的数据
-vv 将二进制转换为可阅读文本
--database=DBName  //数据库名（一个mysql数据库比较多，指定方便恢复）
--start-datetime="2019-11-26 18:00:00"  //恢复起始时间
--stop-datetime="2019-11-26 18:10:00"  //恢复结束时间
D:\MySQL\Data\mysql-bin_copy.000028  //为数据恢复的日志文件
mysqllog.sql    //恢复以后我们需要的文件名

使用mysqlbinlog将binlog文件转换成可读的内容时可能会报错：

mysqlbinlog mysql-bin.000004 mysqlbinlog: unknown variable 'default-character-set=utf8'
原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。

两个方法可以解决这个问题

一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。

二是用mysqlbinlog –no-defaults mysql-bin.000004 命令打开

4、恢复 sql 语句

执行

$ cat mysqllog.sql | LC_CTYPE=C sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@6.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' > result.sql

结论

一定要Mysql开启binlog功能，开启binLog功能方法(windows),打开my.ini文件，添加如下配置,重启mysql即可开启

# log-bin
log-bin=mysql-bin 
binlog_format = ROW

还有就是在操作前一定要开启事务，等操作结果达到预期后再进行事务提交，一旦发生问题还可以进行回滚。在执行 DML 操作之前，使用begin 或者 START TRANSACTION 开始一个事务，如果需要回滚事务使用 rollback，确认提交事务使用 commit

本作品采用《CC 协议》，转载必须注明作者和本文链接