mysql误执行删除操作恢复数据

1.先查看binlog功能是否开启
show variables like ‘%log_bin%’;(查看log_bin字段是否为on)
2.查看数据文件存放路径
show variables like ‘%datadir%’;
3.找到mysql安装目录
show variables like “%basedir%”;
4.通过mysqlbinlog恢复删除的数据日志记录
mysqlbinlog --base64-output=decode-rows -v --database=databaseName --start-datetime=“2020-12-04 16:00:00” --stop-datetime=“2020-12-04 17:00:00” /var/lib/mysql/binlog.000061 > mysqllog.sql
5.把mysqllog的DELETE转换为Insert语句，这个在liunx下操作方便
cat mysqllog.sql | sed -n ‘/###/p’ | sed ‘s/### //g;s//*./,/g;s/DELETE FROM/;INSERT INTO/g;s/WHERE/SELECT/g;’ |sed -r 's/(@17.),/\1;/g’ | sed ‘s/@1=//g’| sed ‘s/@[1-9]=/,/g’ | sed ‘s/@[1-9][0-9]=/,/g’ > mysqllogOK.sql