mysql误删除数据恢复（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-winx64databinlog.000009 > D:updatemysqllog.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:MySQLDatamysql-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