一句DELETE引发的加班(Mysql 恢复Delete删除的数据)

 本机用的Navicat连mysql测试DB又连了正式DB，因为本地与正式要频繁操作所以都打开了很多查询，本来要DELETE删除测试DB的数据，没看清在正式环境执行了。共删除了325条数据，然后在网上找恢复数据的办法，一定要是DELETE删除的，如果用的是drop table删除表是没办法恢复的，本次操作是Windows,如果是Liunx下的Mysql过程流程完全一样，也可以胡考的，具体恢复流程如下

第一步:先查看binlog功能是否开启

show variables like '%log_bin%';

如果为log_bin为ON说明可以恢复，如果为OFF说明没有开启binlog，也没有预先生成回滚SQL，那可能真的无法快速回滚了,GAMEOVER，下面的不用看了(好像可以通过ibd恢复，具体可以参考 https://blog.csdn.net/hanjun0612/article/details/102466509 )。

   第二步：查看数据文件存方路径

show variables like '%datadir%';

 打开数据库所在路径查看有mysql-bin.****这样的文件，注意DELETE删除的时间,对比mysql-bin文件的修改时间，我的是26号下午18点左右进行的删除数据，所以找mysql-bin.000028这个文件

 

  第三步：找到mysql安装目录

show variables like "%basedir%";

 CMD命令符进行Mysql安装目录下，找到mysqlbinlog.exe,如果没有说明你安装的是假mysql.

 

  第四步.通过mysqlbinlog 恢复删除的数据日志记录

mysqlbinlog --base64-output=decode-rows -v --database=DBName --start-datetime="2019-11-26 18:00:00" --stop-datetime="2019-11-26 18:10:00" D:\MySQL\Data\mysql-bin.000028 > mysqllog.sql

mysqlbinlog 命令的参数说明
--base64-output=decode-rows //数据转换正常的字符，如果不设置这个参数将显示base64的数据
--database=DBName  //数据库名（一个mysql数据库比较多，指定方便恢复）
--start-datetime="2019-11-26 18:00:00"  //恢复起始时间
--stop-datetime="2019-11-26 18:10:00"  //恢复结束时间
D:\MySQL\Data\mysql-bin.000028  //为数据恢复的日志文件
mysqllog.sql    //恢复以后我们需要的文件名

 执行以后如下图：

 

 

 打开mysqllog.sql文件，搜索 DELETE 关键字，找到被删除数据，看到这些数据以后总算放心了，如下图：

  第五步：把mysqllog的DELETE转换为Insert语句，这个在liunx下操作方便（有人用python转换也可以），把文件mysqllog.sql复制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

 

 转换以后打开mysqllogOK.sql文件,因为批量替换前面多个一个分号，去掉以后，把所有生成的Insert语句在navicat下执行即可恢复所有DELETE删除的数据。

如果没有liunx可以在windows下用vbs来实现，把下面代码复制保存为:delete2insert.vbs 文件（一定要是.vbs格式文件） 与mysqllog.sql在同一目录下，然后双击运行，会生成mysqllogOK.sql文件就是我们要的INSERT语句

'========================== 
'用VBS实现 MYSQL binglog DELETE转INSERT 
'========================== 
function replaceregex(patern,str,tagstr) 
    dim regex,matches 
    set regex=new regExp 
    regex.pattern=patern 
    regex.IgnoreCase=true 
    regex.global=true 
    matches=regex.replace(str,tagstr) 
    replaceregex=matches 
end function

'======Mysql binlog DELETE转INSERT================
'VBS打开文本文件
Set oldStream = CreateObject("ADODB.Stream")
oldStream.CharSet = "utf-8"
oldStream.Open
oldStream.LoadFromFile("mysqllog.sql") 'binLog生成的DELETE原日志文件
oldText = oldStream.ReadText()
    newText=replace(oldText,"### DELETE FROM", ";INSERT INTO")
    newText=replace(newText,"### WHERE", "SELECT")
    newText=replace(newText,"###", "")
    newText=replace(newText,"@1=", "")
    newText=replaceregex("\@[1-9]=",newText, ",")
    newText=replaceregex("\@[1-9][0-9]=",newText, ",")
oldStream.Close
'VBS保存文件
Set newStream = CreateObject("ADODB.Stream")
newStream.Type = 2 'Specify stream type - we want To save text/string data.
newStream.Charset = "utf-8" 'Specify charset For the source text data.
newStream.Open 'Open the stream And write binary data To the object
newStream.WriteText newText
newStream.SaveToFile "mysqllogOK.sql", 2 'DELETE转成INSERT以后的新的SQL文件名
newStream.Close

 

 总结：   

1.不要随便用DELETE，数据做用字段做删除标记，不用使用DELETE删除数据，如果非要删除，删除前一定先备份一下数据

2.数据库做好定时备份，如果数据库不大，可以一小时备份一次，再大也可以每天备份一次

3.做事细心，越熟练的事越要细心，本次DELETE导致的加班，就是以为自己对sql熟练就随意操作。

4.也是最重要的一点,一定要Mysql开启binlog功能

如果没开启binlog功能的赶紧开启一下吧，mysql开启binLog功能方法(windows),打开my.ini文件，添加如下配置,重启mysql即可开启

# log-bin
log-bin=mysql-bin 
binlog_format = ROW