通常情况下对于mysql innodb误删除导致记录丢失的恢复方案有三种,分别是备份还原、binlog还原和记录深度解析,今天我们就binlog还原来聊一聊
实现原理:mysql服务器通过记录sql执行记录到二进制日志文件,我们可以导出成可视化的txt格式的,里面就是对数据库各种操作的sql语句,我们对这些sql语句做处理,得到自己想要的格式,来做数据的恢复。
1.先查看binlog功能是否开启
show variables like ‘%log_bin%’;
windows系统开启方法:
打开my.ini文件,添加如下配置,重启mysql即可开启
my.ini配置文件在数据文件存放路径Data的上一层目录
#log-bin
log-bin=mysql-bin
binlog_format=ROW
2.查看数据文件存放路径
show variables like '%datadir%';
3.找到mysql安装目录,mysqlbinlog.exe工具就在/bin目录下
show variables like "%basedir%";
4.使用mysqlbinlog命令,cmd管理员运行或者PowerShell(管理员)
mysqlbinlog --base64-output=decode-rows -vv --database=springboot --start-datetime="2022-01-10 16:50:00" --stop-datetime="2022-01-10 16:51:00" --set-charset="utf8" C:\ProgramData\MySQL\"MySQL Server 5.7"\Data\mysql-bin.000010 > D:\original.txt
参数说明:
–base64-output=decode-rows:base64编码格式输出
-v用行事件重构伪sql语句
-v在列数据类型上添加注释
–database数据库
–start-datetime指定日志记录解析开始时间
–stop-datetime指定日志记录解析结束时间
–set-charset设置输出编码
mysql-bin*:二进制日志输入文件
original.txt:解析后的输出文件,名称自定义
–start-datetime和–stop-datetime两个参数可以帮助我们在日志量比较大时,快速定位要解析的日志,时间是您执行delete语句的时间
navicat工具查看sql执行日志
工具-历史日志-搜索delete(只能查到当日sql执行日志)
5.通过cat和sed来重构我们想要的sql语句
cat original.txt | sed -n '/### /p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@2.*),/\1;/g' | sed 's/@[1-9]=//g' > result.txt
命令说明:
sed -n ‘/### /p’:以### 开头的行
sed ‘s/### //g;s//*./,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;’:删除### ,/**/,DELETE FROM替换成INSERT INTO,WHERE替换成SELECT
sed -r 's/(@2.),/\1;/g’:把@2行最后一行,替换成; 根据自己表的字段个数改下就可以了
sed ‘s/@[1-9]=//g’:删除以@1-9=
result.txt:把处理后的内容写入result.txt文件