文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

mysql通过binlog恢复或回滚数据

2023-08-31 18:37

关注

前言

近期项目开发中出现了一次误删的操作,于是在网上查找了mysql的回滚操作,这里记录一下。

简单说下binlog,binlog是mysql中的二进制日志,其记录了数据库发生更改的各种变化。所以通过binlog可以回滚或者恢复失误的操作。

恢复一般使用mysqlbinlog命令,该命令是mysql自带的,使用简单。其运行的本质是将日志记录中的事件再次执行一遍。回滚一般要借助第三方工具binlog2sql,其回滚的本质是解析日志文件生成要回滚的sql,我们拷贝执行该sql即可。

创建一个test数据库,准备一张空表t1,包含两个字段id、name,如下:

 3、通过如下命令查看是否开启binlog记录功能(如果没开则上网搜一下开启教程,这里不过多介绍):

show variables like 'log_bin';

为了便于观察测试,使用如下一系列命令产生一个新的binlog日志文件,使得我们后续的操作都记录在新的binlog中。

flush logs //产生一个新的日志文件show variables like 'log_bin_basename' //查看日志存储地址show master status; //查看最新日志文件名称

插入三条数据,如下:

 6、查看此时的binlog日志记录

show binlog events in 'binlog.000068';

 这里可以看到我们的三次插入事件

删除一条数据,此时的表数据和binlog如下:

 

 可以看到多了一个delete事件,这里注意下binlog中每个事件都有一个begin和commit,我们后面进行恢复或回滚的时候开始和结束的pos都是取的事件整体的开始点和结束点。比如上面删除事件的开始点其实是1105,结束点则是1316

8、mysqlbinlog恢复数据

注意这里是恢复数据,不是回滚数据,恢复的本质是将原有的插入语句再执行一遍,而回滚则是回退到删除之前的状态
mysqlbinlog是mysql自带的命令,一般是在mysql安装目录下的bin目录里。因为我们是恢复数据,所以要找到已经删除语句的对应写入事件,将该事件再重新执行一遍即可。
先通过show binlog events in 'binlog.000068'确认下插入语句的事件位置

可以看到开始位置为815,结束位置为1026,接着用mysqlbinlog命令恢复:

mysqlbinlog --no-defaults   ..\data\binlog.000068 --start-position=815 --stop-position=1026  | mysql -uroot -p123456 test

 下面来看一下表数据和binlog信息:

 

可以看到表中数据已经恢复,binlog中也多了一次写入事件。

小结:
mysqlbinlog命令只是用于恢复,不能用于回滚。如果数据进行update操作,则很难通过该命令恢复。所以该命令比较适用一些数据迁移,数据同步的场景

mysqlbinlog 运行过程中如果出现unknown variable 'default-character-set=utf8mb4'异常,可以再该命令后加--no-defaults参数解决:mysqlbinlog --no-defaults

mysqlbinlog命令的详细用法这里没有介绍,需要的可以上官网或者百度搜索。

binlog2sql数据回滚

binlog2sql是一个第三方的工具,其安装过程中调试了很久,为了不让该文章的逻辑变的杂乱,这里我新开了一篇文章讲解(binlog2sql 工具安装使用及问题汇总_Interest1_wyt的博客-CSDN博客),不了解该工具的可以参考下。

binlog2sql回滚的原理是生成要回滚事件对应的sql语句,我们最后只需要拷贝该语句实现即可。这里为了便于观察,我们先清空t1表,然后再重新开启一个binlog记录,开启和查看新binlog命令前面介绍过,这里仅截图展示下:

 向t1表再次插入三条数据:

 修改最后一条数据的name字段,此时表和binlog的记录情况如下:

 通过binlog2sql生成回滚sql

E:\Program Files\PYTHON3.9.5\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 1")  result = self._query(query)UPDATE `test`.`t1` SET `id`=3, `name`='回滚3' WHERE `id`=3 AND `name`='更新' LIMIT 1; #start 1105 end 1312 time 2022-04-16 11:20:00

将回滚语句拷贝出来执行后再观察t1表和binlog日志:

 可以看到日志中多了一条更新事件,表中数据也被还原。

来源地址:https://blog.csdn.net/Interest1_wyt/article/details/115028429

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯