MYSQL误删除DELETE数据找回

查看是否开启日志

show variables like’%log_bin%';

查看日志存储文件名

show master status;

查看日志文件路径

show variables like “%basedir%”;

将DESKTOP-P651KVA-bin.000021文件转换成mysqllog.sql
mysqlbinlog --base64-output=decode-rows -v --database=wanghui --start-datetime=“2022-02-15 13:00:00” --stop-datetime=“2022-02-15 14:10:00” C:\Program Files\MySQL\MySQL Server 8.0\DESKTOP-P651KVA-bin.000021 > mysqllog.sql
mysqllog.sql文件内容如下所示
删除的语句在里面
在linux环境里面将mysqllog.sql转换成mysqllogOK.sql文件
打开mysqllogOK.sql文件，删除的数据就找回来了

mysqlbinlog --base64-output=decode-rows -v --database=bigdata_park --start-datetime=“2022-06-14 18:14:00” --stop-datetime=“2022-06-14 18:19:00” /home/mysql/mysql3306/logs/mysql-bin.001288 > mysqllog.sql

找出binglog文件的起始位置 position 值

mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position “699782059” --stop-position “790262625” --database bigdata_park /home/mysql/mysql3306/logs/mysql-bin.001288 > mysql2.sql

mysqlbinlog --no-defaults /home/mysql/mysql3306/logs/mysql-bin.001288 --start-position=699782059 --stop-position=790262625 | mysql -uroot -p123456 test

mysqlbinlog --no-defaults /home/mysql/mysql3306/logs/mysql-bin.001288 --start-position=699782059 --stop-position=790262625 >mysqllogOK.sql

mysqlbinlog --no-defaults --database=db --base64-output=decode-rows -v --start-position=699782059 --stop-position=790262625 /home/mysql/mysql3306/logs/mysql-bin.001288 > mysqllogOK.sql

把binglog中的delete 替换成insert
cat mysql2.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

cat mysql2.sql | sed -n ‘/###/p’ | se ‘s/### //g;s//*./,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/VALUES(/g;’ |sed -r ‘s/(@9.),/\1);/g’ | sed ‘s/@[1-9]=//g’ >data_reload.sql