前情提要
今天是2020年8月初,黑格比台风刚过上海,一波未平一波又起。。。我竟然丢数据了。。。事情的缘由很简单,就是本来需要的数据被我覆盖了。但是不慌,我们有法子把删除的数据找回来。
MysqlBinlog介绍
MysqlBinlog是MySQL数据库中的二进制日志,主要用来记录用户更改数据时的历史语句。所以此日志不会记录包含select/show等在内的查询语句。主要记录包括create/insert/update等在内的数据更改语句。Binlog有很多高级用法,但在此篇文章我就介绍它较为简单的一个用法——恢复数据!
有的小伙伴这里肯定有些疑问,为啥我恢复数据要去查日志?问得好,因为数据是我们用insert 语句一条一条插进去的呀,所以每一条insert语句的value值就是我们表中的数据!
MysqlBinlog重要语句及步骤
根据下面的语句一条一条往下看,很简单的,你就明白数据咋恢复了。不要跳看哦
1、查看Binlog是否启用
show
如果你的Binlog开启则会像上图一样,Value值会是ON。如果没有启动则是OFF。只有启动了Binlog服务你才有拯救数据的机会,如果没开启的就请出门左转传送门。
mysql开启binlog日志方法jingyan.baidu.com2、查看Binlog储存的日志文件路径
show
那么如何找到我们的日志文件呢?如图红框的路径就是你的日志文件的存储路径,打开此路径后,可能是这样的景象:
上图红色框内的文件就是你的日志文件啦~但是不要高兴的太早,这个日志文件你只能用mysqlbinlog来打开,所以现在你看到它并没有什么卵用。。。
3、查看Binlog文件的名称及大小
show binary logs;
你可以通过语句2的路径找到log文件名,也可以通过这句找到log文件名,知道了log文件名和文件的大小(见上图的File_size),再根据文件名由旧到新的增序命名,就大致可以猜出自己的数据可能会在哪几个log文件中。敲黑板,文件名(Log_name)很重要!
4、查看Binlog文件中的内容简介
-- 查看日志文件的前两条记录
show binlog events in 'DESKTOP-R34UERN-bin.000008' limit 2G;
-- 查看日志文件指定位置后的两条记录
show binlog events in 'DESKTOP-R34UERN-bin.000008' from 78032910 limit 2G;
知道了文件名,下一步就是打开来一睹芳容。上面两条语句就是用来查询日志文件记录的简介,请注意events in 后的文件名请填写自己【show binary logs;】出来的文件名。
这里有小伙伴肯定要问那个from后的数字哪来的?from后的那个数字其实就是上图红框标出来的Pos的值,你可以理解为记录的锚点,方便你进行分段查找与执行的,不然这成千上万的语句该怎么定位!那我们该怎么去找到我们所需要的Pos值呢?请继续往下看。
这一步虽然我们看不到日志中记录的SQL语句,但是我们可以根据Pos值去分段验证记录,且Info中会提供一些有用的信息(比如此记录操作的数据库和表名是什么,上二图马赛克部分)
5、导出记录中的SQL语句
请注意此步骤需要离开MySQL环境,到系统命令行中进行操作
mysqlbinlog
在系统中进行此操作前,你需要先进到Binlog路径中,路径见语句2。
上图为执行语句,执行完毕会在Binlog路径下生成一个文件名为a.sql的文件,这就是你需要的日志内容文件。这行语句的意思如下:
【--base64-output=decode-rows -v】这一部分可以理解为格式化将二进制文件可视化为正常可以阅读的SQL代码
【--start-datetime=】这是查询日志的开始时间
【--stop-datetime=】这是查询日志的结束时间
【--database=】这是你要查询相关数据库的筛选条件
【> a.sql】这是你最终要保存的SQL文件的文件命名(此处为a.sql)
这一步可以让日志中的记录直接导出为sql文件,里面会呈现你历史写的SQL语句。打开a.sql后如下图:
红色框:此条记录的初始Pos值;蓝色框:此条记录的最终Pos值
绿色框:此条记录当时执行的日期时间;黄色框:SQL语句
通过这些你就可以定位到你需要恢复数据的初始Pos值和最终Pos值了。
6、数据恢复
-- 格式说明
mysqlbinlog --start-position=初始Pos --stop-position=最终Pos -d 数据库名称 Binlog文件名|mysql -uMySQL用户名 -pMySQL密码 数据库名称
-- 示例代码
mysqlbinlog --start-position=10000 --stop-position=10086 -d db_test DESKTOP-R34UERN-bin.000008|mysql -uroot -p012345 db_test
执行结果如上图,它会出现一个友情提示。到这里就恢复完毕了。是不是很简单?
因本人非科班出身,如有错误欢迎指正,还请理解~、
欢迎关注~一个医学生(预防医学)的数据成长之路。。。