误删mysql数据的一次辛酸史

生产数据库delete垃圾数据忘记添加where，导致整个表清空，瞬间血槽满了。。。。

解决方案如下

1.查看是否启用binlog，并找到日志存储路径

log_bin为ON表示开启了日志记录，否则该文章的解决方案不适用

mysql> show variables like'log_bin%';
#展示结果
+---------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------+
| log_bin | ON |
| log_bin_basename | /home/programs/mysql-5.6.26/data/mysql-bin |
| log_bin_index | /home/programs/mysql-5.6.26/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------------------------

datadir为日志文件的存储路径

show variables like '%datadir%';
#展示结果

+---------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------+
| datadir | /var/lib/mysql/ |
+---------------------------------+--------------------------------------------------+

 

2.抓取需要恢复的日志

进入日志存储路径，日志文件的命名格式一般为mysql-bin.*  *为版本。根据delete执行的时间查询日志

mysqlbinlog --no-defaults --base64-output=decode-rows -v --database=demo-data --start-datetime="2021-12-07 17:48:54" --stop-datetime="2021-12-07 17:48:55" mysql-bin.000023>mysqllogdata.sql
#--database 指定数据名称

 

其它文章大部分没使用--no-defaults，若mysql指定的默认编码不匹配时，执行命令会出现

 

  如上命令会抓取日志到mysqllogdata.sql中，抓取的内容如下：

 

 这个地方我花费的时间比较多，因为按照时间段抓取难免会抓取到其它线程的执行语句；有查到可以通过日志位置抓取的命令，但抓取日志并没有抓全，命令如下：

mysqlbinlog --no-defaults --base64-output=decode-rows -v --database=demo-data --start-position="145894884" mysql-bin.000023

 

最终解决方案只能是先按照时间段抓取日志，然后再删除其中其它执行命令，如有其它更好的方式，欢迎评论区留言。

3.日志文件替换

抓取出的日志文件为DELETE命令，下一步需要将命令替换成INSERT命令，linux替换指令

cat mysqllogdata_n.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_n.sql

 

替换完后的日志文件

 请注意，第一条INSERT命令前会多一个';'，需要删除。

4.执行insert脚本

经过一两个小时的折腾后，终于来到了最关键的地方，执行insert脚本恢复数据，方法有很多，就不详细说了；我是直接将脚本内容粘贴到Navicat执行，linux可进入数据库后执行如下命令

source /var/lib/maysql/mysqllogOK_n.sql

 

检查无误后下班回家。

 

 本文参考文章https://www.cnblogs.com/-mrl/p/9959365.html