本机用的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:MySQLDatamysql-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:MySQLDatamysql-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