文章详情

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

请输入下面的图形验证码

提交验证

短信预约提醒成功

mysql误删数据后,从binlog中进行恢复删除数据(拯救手残,不跑路)

2023-08-16 18:31

关注

在一次数据维护过程中,对数据删除时没有提前备份数据,导致数据被删除后无法通过备份文件直接恢复。

数据恢复思路

Created with Raphaël 2.3.0 数据被删除 数据是否有备份 从备份中恢复 binlog是否打开 查看binlog状态 刷新binlog状态 查找删除语句 mysqllogbin恢复数据 GG,数据丢失,卷铺盖跑路 yes no yes no


  1. 数据如果在删除前提前备份好,那么直接从备份文件中恢复。
  2. 如果没有备份文件,则需要查看mysql数据库是否打开logbin日志。如果没有打开直接GG。如果恰好打开了的,则需要如下几步进行:
1. 查看binlog状态,确认binlog文件2. 刷新binlog状态,重新生成新binlog文件,确保原文件不被修改3. 查找删除语句,确定好起始位置、删除时间等信息4. 通过mysqllogbin恢复数据

操作明细

本文以下内容为mysql开启binlog的前提下进行数据恢复的步骤。希望以后都不用到!!!

1.查找当前操作的binlog状态

在myql命令行或者链接工具中,命令查看binlog当前的位置状态和文件信息。

show master status;

2. flush logs刷新状态

在myql命令行或者链接工具中,使用flush logs命令重新生成新的binlog命令。

flush logs;

3.备份第一步中查出来的binlog文件

在Linux下,使用cp命令将文件复制一份到/home/recover/下,后续操作都是在这个备份文件基础上进行操作的。
cp -a /var/lib/mysql/mysql-bin.000002 /home/recover/

4.查找删除数据的起始位置、时间等关键信息

这一步如果binlog文件比较大的话,建议在Navicat等连接工具中进行查询,使用命令查询后,找到删除命令的起始位置

show binlog events in 'mysql-bin.000002';

在这里插入图片描述
在这里插入图片描述

如图所示,找到起始位置

5.恢复数据

根据第四步找到的起始位置,使用mysqlbinlog命令,在系统中恢复数据库数据。

/usr/bin/mysqlbinlog --start-position=245 --stop-position=2997666 --database=test1 /var/lib/mysql/mysql-bin.000002 | /usr/bin/mysql -uroot -p123456 -v test1

/usr/bin/mysqlbinlog --start-position=245 --stop-position=2997666 --database=test1 /home/recover/mysql-bin.000002 | /usr/bin/mysql -uroot -p123456 -v test1

在实际执行上面命令的时候,一直提示出错

ERROR 1032 (HY000) at line 8299: Can’t find record in ‘table_name’

暂时不知道什么原因,可能和数据库版本有关?

因为这一步直接使用mysqlbinlog没有恢复成功,所以就只想到用到下面这一种方法:使用sed将binlog文件中的删除sql命令清洗成insert语句,通过拼接出来的sql去恢复数据

6.使用sed清洗数据

6.1 从binlog文件中提取删除信息

根据第四步中找到的起始位置,使用mysqllogbin命令提取对应的删除信息

mysqlbinlog --base64-output=decode-rows -vv \ --start-position=245 --stop-position=2997666 /home/recover/mysql-bin.000002 >a.txt

6.2 提取对应的删除sql

sed -n '/^###/'p a.txt >b.txt

这个命令的作用是将文件a.txt中以###开头的行复制到文件b.txt中。

sed是一个流编辑器,-n选项表示关闭默认的输出,只输出经过处理的行。

  • '/^###/'是一个正则表达式,表示匹配以###开头的行。
  • p命令表示输出匹配的行。

a.txt是要处理的文件。
>b.txt表示将输出结果重定向到文件b.txt中。

6.3 替换“### ”

sed 's/### //g' b.txt >c.txt

s/### //g是一个替换命令,表示将所有的### (三个井号后跟一个空格)替换为空。

  • s表示执行替换操作。
  • /### /是一个正则表达式,表示匹配所有的### 。
  • /g表示全局替换,即将所有匹配的字符串都进行替换。

b.txt是要处理的文件。
>c.txt表示将替换后的内容输出到文件c.txt中。

6.4 删除每行后面的注释

sed 's#/.*#,#g' c.txt >d.txt

这个命令的作用是将文件c.txt中每一行第一个/及其后面的内容替换为一个逗号,并将替换后的内容输出到文件d.txt中。

s#/.*#,#g是一个替换命令,表示将每一行第一个/及其后面的内容替换为一个逗号。

  • s表示执行替换操作。
  • /#/是一个正则表达式,表示匹配每一行中第一个/。
  • .*表示匹配/后面的任意字符串。
  • /#/,表示替换为一个逗号。
  • /g表示全局替换,即将每一行中第一个匹配的字符串都进行替换。

c.txt是要处理的文件。
>d.txt表示将替换后的内容输出到文件d.txt中。

6.5 DELETE FROM替换为INSERT INTO

sed 's#DELETE FROM#INSERT INTO#g' d.txt >e.txt

这个命令的作用是将文件d.txt中所有的DELETE FROM替换为INSERT INTO,然后将替换后的内容输出到文件e.txt中。

s#DELETE FROM#INSERT INTO#g是一个替换命令,表示将所有的DELETE FROM替换为INSERT INTO。

  • s表示执行替换操作。
  • #DELETE FROM#是一个正则表达式,表示匹配所有的DELETE FROM。
  • #INSERT INTO#表示替换为INSERT INTO。
  • /g表示全局替换,即将所有匹配的字符串都进行替换。

d.txt是要处理的文件。
>e.txt表示将替换后的内容输出到文件e.txt中。

6.5 WHERE替换为SELECT

sed 's#WHERE#SELECT#g' e.txt >f.txt

这个命令的作用是将文件e.txt中所有的WHERE替换为SELECT,然后将替换后的内容输出到文件f.txt中。

s#WHERE#SELECT#g是一个替换命令,表示将所有的WHERE替换为SELECT。

  • s表示执行替换操作。
  • #WHERE#是一个正则表达式,表示匹配所有的WHERE。
  • #SELECT#表示替换为SELECT。
  • /g表示全局替换,即将所有匹配的字符串都进行替换。

e.txt是要处理的文件。
>f.txt表示将替换后的内容输出到文件f.txt中。

6.5 @53= 替换成’,‘ 语句结尾添加’;‘号

sed -r 's#(@53=.*)(,)#\1;#g' f.txt >h.txt

这个命令的作用是将文件f.txt中所有的以@11=开头、以,结尾的字符串替换为这个字符串加上;,然后将替换后的内容输出到文件h.txt中。

-r选项表示使用扩展正则表达式。
s#(@53=.*)(,)#\1;#g是一个替换命令,表示将所有的以@11=开头、以,结尾的字符串替换为这个字符串加上;。

  • s表示执行替换操作。
  • #(@11=.*)(,)#是一个正则表达式,匹配所有以@11=开头、以,结尾的字符串。
  • \1;表示将匹配到的字符串替换为匹配到的内容加上;。
  • /g表示全局替换,即将所有匹配的字符串都进行替换。

f.txt是要处理的文件。
>h.txt表示将替换后的内容输出到文件h.txt中。

6.5 生成可执行的sql

sed -r 's#(@.*=)(.*)#\2#g' h.txt >aa.sql

6.6 添加commit

sed -i '$a commit;' aa.sql

至此,已经从导出binlog文件中清洗除了需要恢复的sql语句(insert语句),可以通过命令或者桌面工具执行sql,恢复删除的数据了。

6.7 执行sql

mysql -uroot -p123456 test < aa.sql

7. 问题总结

7.1 日期格式问题

在实际操作过程中,发现因为从binlog中导出的数据包含日期格式有问题,没有带单引号,所以造成sed清洗出来的sql执行不成功。问题解决方法有两个:

使用sed 直接匹配时间格式后对时间格式直接进行添加单引号,如下命令:

sed -E "s/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/'&'/g" filename

优化:对时间格式进行判断其前后是否有单引号,没有则进行添加
sed -E "s/'?[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}'?/'&'/g" filename

7.2 sed中> 和>> 的区别

在使用过程中,有一步使用了>>且重复执行后,导致最后生成的sql数据重复

7.3 在删除数据一定要谨慎

对大量数据进行删除过程中,一定要谨慎。

8 参考文章

通过binlog恢复误delete的数据(一)
使用binlog日志恢复MySQL数据库删除数据的方法

来源地址:https://blog.csdn.net/mu_yu_xi/article/details/129818766

阅读原文内容投诉

免责声明:

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

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

软考中级精品资料免费领

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

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

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

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

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

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

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