mysql误删数据后,从binlog中进行恢复删除数据

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

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';
mysql误删数据后,从binlog中进行恢复删除数据
mysql误删数据后,从binlog中进行恢复删除数据

如图所示,找到起始位置

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

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

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

6.3 替换“### ”

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

6.4 删除每行后面的注释

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

这个命令的作用是将文件c.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中。

6.5 WHERE替换为SELECT

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

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

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

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

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

文章来源:https://www.cnaaa.net,转载请注明出处:https://www.cnaaa.net/archives/9240

(0)
杰斯的头像杰斯
上一篇 2023年7月29日 下午5:15
下一篇 2023年7月31日 下午4:38

相关推荐

  • 索引–加快MySQL查询

    1.索引的描述 索引在MySQL中也叫做”键”,保存着数据位置的信息; 其作用是为了加快数据库的查询速度; 实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。 2.索引的使用 添加索引 #案例 主键会自动创建索引,外键约束也会自动创建索引 删除索引 #案例 添加联合索引 语法 联合索引又叫复合索引,是MySQL的I…

    2022年8月17日
    37000
  • Navicat161 数据库文件修复

    错误分析 此错误一般为表损坏,修复即可。可能造成原因为服务器突然断电,而有程序还在往表里写数据或者表的数据很大或者数据搬迁的时候编码格式有误,避免浪费时间去修表,可采用Navicat去快速修复表!!! 登录Navicat数据库,找到报错的数据表,右键维护->修复表->拓展或者快速

    2022年11月17日
    59800
  • 数据库报错:Cause com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure

    原因1.因为SSL连接原因(大部分人的原因)因为MySQL在高版本需要指明是否进行SSL连接。有可能你 pom 文件引入的 MySQL 依赖版本是MySQL5.7及以上 这些的时候,你就需要指定SSL连接,如果你不知道,默认就是开启,所以就会出现上面的错误。 2.因为数据库连接超时原因当数据库重启或数据库空闲连接超过设置的最大timemout时间,数据库会强…

    2023年8月1日
    27300
  • MySQL性能优化,MySQL索引优化,order by优化,explain优化

    建表 优化一:全部用到索引 介绍 建立的复合索引包含了几个字段,查询的时候最好能全部用到,而且严格按照索引顺序,这样查询效率是最高的。(最理想情况,具体情况具体分析) SQL 案例 优化二:最左前缀法则 介绍 如果建立的是复合索引,索引的顺序要按照建立时的顺序,即从左到右,如:a->b->c(和 B+树的数据结构有关) 无效索引举例 SQL 案例…

    2023年8月28日
    27600
  • 解决远程连接MySQL报错:2003 – Can‘t connect to MySQL server on ‘X.X.X.X‘ (10060 “Unknown error“)问题

    问题先看报错: 2003 – Can’t connect to MySQL server on ‘X.X.X’ (10060 “Unknown error”)。 意思是服务器远程连接出错:未知错误。 解决方案:遇到这个问题,首先想到的是权限不够。那咱就给它赋权。 首先登录MySQL数据库:mysql -u用户名 -p密码; 然后:use mysq…

    2023年7月27日
    30300

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

在线咨询: QQ交谈

邮件:712342017@qq.com

工作时间:周一至周五,8:30-17:30,节假日休息

关注微信