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;

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

4)执行误删除操作

DELETE FROM student WHERE age=17;

此时数据如下：

5)执行正常操作

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

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

最终数据如下 ：

2、恢复数据分析

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

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

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

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

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

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

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

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

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

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

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

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

可以看到开始导入备份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)恢复完成，查看现在数据

5)验证是否成功

备注：

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

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

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