1、要想通过binlog日志恢复数据,首先要检查是否开启binlog。
show VARIABLES like '%log_bin%';
如果未开启binlog,该方法就不适用了。
2、查找binlog日志所在位置,一般放在mysql安装目录下,可以查看/etc/my.cnf文件。
3、记住误操作的大概时间,找到该时间对应的binlog日志文件(一般为.0000**结尾的),一般binlog日志文件都很大,不方便查看,所以可先根据时间区间截取文件为sql文本,方便查看。
mysqlbinlog --no-defaults --start-datetime='2022-10-12 15:00:00' --stop-datetime='2022-10-12 17:00:00' /var/lib/mysql/mysql-bin.000031 >/var/lib/mysql/mysql_delete20221012.sql
4、打开截取的sql文件,找到执行删除的节点位置,记录下节点开始和结束节点,这两个节点之间就是我们待恢复的数据。
5、通过节点精确导出误操作的sql。
mysqlbinlog --no-defaults -vv --start-position=953682477 --stop-position=954306016 /var/lib/mysql/mysql-bin.000031 >/var/lib/mysql/bin_data.sql
6、将delete语句转换为insert语句。
cat /var/lib/mysql/bin_data.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-9]=//g' | sed 's/@[1-9][0-9]=//g' > /var/lib/mysql/delete2insert.sql
这是转换后的数据,转换语句为网络搜索的,尝试了几种都存在一些小问题,需要在文件中手动改一些,;之类的符号。
7、新建临时库,并把删除的表创建出来,在新库中先执行转换后的sql语句,确定没问题后再导入真实数据库,至此,数据已完全恢复。