昨天在使用mysql的时候,大脑一热,一条本来带where子句的delete被我把where条件删除掉之后当成select给执行了,结果就是把整个表给清空了,4000条数据瞬间蒸发了,当时真想把手指头切了算了,然后在各种百度+谷歌的帮助下花了将近4个小时找方法成功恢复,真是4秒钟run一个sql,删了4000条数据,最后花了4个小时才找回来,大脑发热害人不浅啊。下面来说说方法,思路是利用mysqlbinlog把操作日志导出成sql命令,然后找到删除操作之前的命令,重新执行一次,这样就恢复到删除操作之前的状态了。
mysqlbinlog是一个查看mysql二进制日志的工具,可以把mysql上面的所有操作记录从日志里导出,这个工具在CentOS下的路径是:
/usr/local/mysql/bin/mysqlbinlog
小提示: 如果你找不到mysqlbinlog的路径,可以使用find命令,格式是(其他用法请参考help): find [path] -name "Name" 例如: find / -name "mysqlbinlog" 这个命令的意思是从根目录起查找名字为"mysqlbinlog"的文件。
我们需要先找到当前所用的mysql日志,使用如下命令进入mysql:
mysql -p
之后输入密码,进入mysql后使用命令查看当前所用的日志:
show master status;
我当前的log文件名叫mysql-bin.000007,记下这个文件名,一会会用到。然后按一下Ctrl+C退出mysql,回到命令行。
日志文件在CentOS下位于:
/usr/local/mysql/var/mysql-bin.000007
现在我们来用mysqlbinlog把操作日志导出:
命令格式如下:
mysqlbinlog --start-datetime="" --stop-datetime="" logfilename -r outputfilename
例如:
/usr/local/mysql/bin/mysqlbinlog --start-datetime="2013-03-01 00:00:00" --stop-datetime="2014-03-21 23:59:59" /usr/local/mysql/var/mysql-bin.000007 -r test2.sql
这条命令是吧从2013-03-01 00:00:00到2014-03-21 23:59:59的操作日志导出到test2.sql文件中。
然后打开test2.sql,找到那条delete命令,查看之前的数据,然后根据时间点进一步缩小sql范围,这是一个漫长枯燥的过程,没办法,谁叫自己脑袋不清醒,创下的祸呢。。这样最后就能得到所有插入数据的记录了,然后把这些记录保存到一个新的sql里面,打开mysql,然后运行吧。
昨天数据恢复完之后发现有些问题,新插入的数据提示插入成功但是在数据表里看不见,这让我很纳闷,我突然看到日志里的每一条记录后面都跟了一个Commit,我试着在mysql运行了一下commit,发现新插入的数据能在数据表里看到了,然后在网上搜索发现有一个叫做autocommit的变量,用来设置是否自动提交,为0则需要执行commit提交,应该就是这个变量的问题,运行:
set autocommit = 1
问题搞定。以后run sql命令的时候一定要看准再run,还有就是最好把safe update打开,然它在执行update或delete使强制使用where语句,这样就能避免很多悲剧的发生。