暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL使用binlog日志恢复误删的数据

MySQL使用binlog日志恢复误删的数据

IT那活儿 2024-04-24
4555

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！  



误删除数据解决方案
方案一：使用备份
如果有定期备份数据库的习惯，并且最近的备份包含了误删除的数据，可以通过还原备份来恢复数据。
请注意，恢复数据将覆盖当前的数据库状态，所以在执行恢复前请确保备份是最新的。
方案二：使用日志文件
MySQL数据库可以开启binlog功能，记录数据库中每个操作的日志。可以通过解析binlog日志来找回误删除的操作。可以使用"mysqlbinlog"命令来解析binlog文件，并通过该命令生成的SQL语句进行数据恢复。
今天举例说明使用备份文件和binlog日志结合来实现MySQL的误删除数据的恢复。此方法要求数据库必须打开二进制日志，在生产环境中强烈建议打开binlog。虽然占用了一定的存储资源，带来的收益是巨大的。当数据库被误操作删除了之后，要想恢复出所有的数据，需要备份数据和binlog的结合，备份数据只能恢复出备份之前的数据，而数据库不会是静止的，这就产生了增量数据，这些增量数据该如何恢复，这就需要binlog日志，通过这些binlog日志将增量数据恢复。

恢复流程：

  • 1）先使用备份数据恢复出全部备份的数据

  • 2）使用binlog日志恢复出增量数据到误操作之前


具体过程

2.1  创建表t2
插入4条数据：
再次插入4条数据：
2.2 进行数据库的全备份
注：本例中以test库为例。
mysqldump -p -d test --single-transaction --set-gtid
-purged=off --master-data=2 --flush-logs --routines --
triggers --events --extended-insert=true> test.sql
参数说明：

  • single-transaction

    此选项会将隔离级别设置为：REPEATABLE READ。并且随后再执行一条START TRANSACTION语句，让整个数据在dump过程中保证数据的一致性，这个选项对InnoDB的数据表很有用，且不会锁表。但是这个不能保证MyISAM表和MEMORY表的数据一致性。

  • master-data=2  

    在bin-log日志里记录备份到了哪个位置，比如说binlog名称，binlog位置。

  • set-gtid-purged=off

    表示在备份时不输出跟 GTID 相关的信息。

  • flush-logs

    命令的作用就是关闭当前使用的binary log,然后打开新的binary log文件,文件的序号加1。

  • extended-insert 

    代表长INSERT，多row在一起批量INSERT提高导入效率。

  • routines，events，triggers

    备份的时候将过程，函数，触发器，事件进行备份。

2.3 进行增量数据插入

分3次，每次4条。
2.4 检查插入的数据
备份之前的8条数据和备份之后的8条数据。
2.5 删除test库
当前看到test库已被删除，已经不能查询。
2.6 进行恢复，进行全备恢复
查看全库恢复的内容：
可以看到已经恢复出全备之前的所有数据，下一步进行增量数据恢复，首先解析binlog文件，找到执行误操作的那条命令，他的事物号为787.记住该事物号，很重要！，很重要！很重要！
后面会使用到。
mysqlbinlog -uroot -p --start-position=154
/var/lib/mysql/mysql-bin.000002 --base64-output=decode-rows -vv
参数解释：
  • start-position:
    日志开始位点，mysqlbinlog从哪个位点开始解析binlog日志.
  • stop-position
    日志结束位点，mysqlbinlog到哪个位点结束解析binlog日志.
看到在mysql-bin.000002日志里面出现了drop database的操作：
当前需要的是注释掉解析出的drop database日志语句，或者使用stop-position来限定binlog结束位置，下面使用stop-position参数限定：
mysqlbinlog -uroot -p --start-position=154
--stop-position=787 var/lib/mysql/mysql-bin.000002>000003binlog.sql
执行增量的binlog恢复：
检查增量恢复的数据：

END


本文作者：张 建(上海新炬中北团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论