MySQL数据误删恢复

一、查看MySQL的binlog是否开启

mysql> show variables like '%log_bin%';

二、查看binlog存放路径

mysql> show variables like '%datadir%';

三、根据误删的时间找到相对应的binlog日志

四、转换binlog为正常sql

mysqlbinlog --base64-output=decode-rows -v --database=DBName --start-datetime="2020-08-15 13:00:00" --stop-datetime="2020-08-15 14:00:00" mysql-bin.055119 > binlog.sql

五、将sql中的delete语句转换成inster语句

cat binlog.sql | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/;INSERT INTO/g;s/WHERE/VALUE/g;' |sed -r 's/(@17.*),/\1;/g' | sed 's/@1=//g'| sed 's/@[1-9]=/,/g' | sed 's/@[1-9][0-9]=/,/g' > inster.sql

六、找到对应的inster语句，在MySQL中执行就行了

七、MySQL开启binlog

vim /etc/my.cnf

[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin