mysql数据库误删且未持久化_MySQL恢复误删数据解决方案

工作中难免会误删数据,下面说一下怎样从导出的备份数据和binlog日志中恢复数据。关于备份数据和binlog可以参考下面的文章:

一、恢复数据思路

总体思路是从备份文件中恢复已备份的数据,还有一些未备份的数据从binlog日志中恢复。当然,具体场景具体分析,否则没有意义。如下:

场景1:知道误删了一张表中ID是5的数据,有该数据的备份,且备份后该数据没有变过。此情况可以直接把备份的数据取出来,写入到该表中。

场景2:删除的数据没有备份,在binlog日志中找到该记录的所有操作,结合mysqlbinlog一步步分析执行,最终恢复该条数据。

从上面的两种场景来看,恢复数据都要找到该条数据的初始数据(即备份的数据,或者binlog日志中写入的数据),以及该条数据再后面是否经过变化。那么怎样知道该条数据后面是否经过变化?除了对业务的熟练,我就知道这个点儿数据不会被修改,更准确的方法还是查binlog日志。

在实际中我们遇到的情况往往要比上面的场景复杂的多,比如我一不小心删除了一部分数据,这部分数据有的备份了,有的没有备份,并且数据量很大或者误删了表。这种情况比较复杂,下面演示一下这种情况下的数据恢复。

二、演示恢复数据

1、模拟数据操作

1)准备数据

CREATE DATABASE IF NOT EXISTS mydb1 DEFAULT CHARACTER SET utf8;

USE mydb1;

DROP TABLE IF EXISTS student;

CREATE TABLE student (

idint(11) DEFAULT NULL,

name varchar(20) DEFAULT NULL,

ageint(11) DEFAULT NULL,

sex varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO student VALUES ('1', 'a1', '18', '男');

INSERT INTO student VALUES ('2', 'a2', '16', '女');

INSERT INTO student VALUES ('3', 'a3', '17', '男');

INSERT INTO student VALUES ('4', 'a4', '17', '女');

INSERT INTO student VALUES ('5', 'a5', '18', '男');

INSERT INTO student VALUES ('6', 'a6', '19', '女');

2)数据备份

mysqldump -uroot -p123456 mydb1 > /var/mysql/backup/mydb1_$(date +%Y%m%d_%H%M%S).sql

3)执行正常操作

INSERT INTO student VALUES ('7', 'a7', '17', '女');

INSERT INTO student VALUES ('8', 'a8', '17', '男');

INSERT INTO student VALUES ('9', 'a9', '20', '女');

UPDATE student SET name='a33' WHERE id=3;

备份和当前的情况分别是:

24ca5e5578fce2938af2321f43937b77.png

4)执行误删除操作

DELETE FROM student WHERE age=17;

此时数据如下:

12c435606f3ed42bf7b31242566f5f4c.png

5)执行正常操作

INSERT INTO student VALUES ('10', 'a10', '18', '女');

INSERT INTO student VALUES ('11', 'a11', '17', '男');

最终数据如下 :

54bbf48260c7c1921a9f7520f727084f.png

2、恢复数据分析

1)查找备份数据时的log日志文件,确定备份后,下一个次提交的偏移位置。

我的方法是先查找当前的日志文件,然后依次往前面的号的文件里找,直到发现与备份日期相近的日期为止。

7a83ff31080814a5249c19d826b345fe.png

当前的日志文件是2号文件,则先在2号文件中找日期与20200609_221029邻近的记录(备份日期是2020-06-09 22:10:29)

[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000002;

3d03ebcfc675e5ae759893183b5cb174.png

可以看到,备份后,下一个次提交的偏移位置是8805。

2)查找误删语句提交开始的偏移位置

e7ce9817c9118ec7f644cc0f369465ef.png

可以看到误删语句提交开始的偏移位置是9935

3)查找误删语句执行完之后的位置

4a283c31758923a9a8f13a951dd1bf06.png

可以看到误删语句执行完之后的位置是10262

4)执行导入备份sql语句,记住大致时间

mysql> source /var/mysql/backup/mydb1_20200609_221029.sql;

5)找到日志中开始导入备份sql的位置

dca1e823f299f2d03841c5328cc9df40.png

可以看到开始导入备份sql的位置是10755

3、恢复数据

1)导入备份sql已经恢复了部分数据

2)恢复备份后到误删之前的数据,起始位置:8805到9935

mysqlbinlog --start-position=8805 --stop-position=9935 mysql-bin.000002 |mysql -uroot -p123456;

3)恢复执行完误删语句之后到执行备份sql之前的数据,起始位置10262到10755

mysqlbinlog --start-position=10262 --stop-position=10755 mysql-bin.000002 |mysql -uroot -p123456;

4)恢复完成,查看现在数据

9511af004e7aa7e872bed46a88d3827d.png

5)验证是否成功

817ee092aa179f65dd8c1b766b8d44a8.png

备注:

1)本篇使用的MySQL版本是5.7.30,注意不同版本之间可能会有差异。

2)完成数据恢复以后最好再执行一下备份语句,避免以后从日志恢复的时候,日志中包含导入备份sql语句,语句比较庞大。

3)如果在导入备份sql的时候,在drop表之前,用户操作了表,而执行drop表后,用户操作信息被删除,可能导致数据丢失。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值