mysql删库后恢复数据流程

前言

本文将介绍当我们使用mysql不小心删库之后，或者恶意被删库后，如何进行数据恢复

binlog

我们进行数据恢复的前提是：

• 1、必须开启binlog日志功能
• 2、最好是会对数据进行定期备份（否则数据量太大的情况下很难通过binlog进行恢复）

我们先来了解下binlog

binlog是mysql server层提供的功能，和存储引擎无关，binlog保存了所有服务端执行的DDL和DML语句。

所以在binlog完整的情况下，可以理解为当被删库后可以把所有DML语句重新执行一遍来达到数据恢复

binlog的作用:

• 1、做日志恢复
• 2、主从架构时做数据同步

准备

首先我们自己先部署一个mysql，部署的流程这里不介绍。

然后修改配置文件，笔者这里是使用docker启动的mysql，修改容器内部配置文件：
/etc/mysql/mysql.conf.d/mysqld.cnf，如果不是容器的话目录应该是/etc/my.cnf文件，如图

log-bin：指定binlog的目录
expire_logs_day：保留多少天内的日志，如果希望全量保存则可以不设置
server_id：主从架构时使用，这里使用binlog也需要设置，否则可能报错

我们先创建了一些初始化数据

创建了db1、db2数据库以及一些数据

然后我们看下当前binlog的状态：

使用mysql连接：mysql -uroot -p123456;

执行：show master status;

查看下当前binlog的最后一个文件以及position

position：可以理解为当前写到该binlog文件的哪个位置

备份

接下来我们开始备份数据库

使用mysql的mysqldump工具进行备份所有数据库

执行：mysqldump -F -R -x -uroot -p123456 --all-databases >/db_all_20210202_19_21.sql

这里解释下：

-F：等同于–flush-logs: 在执行导出前先刷新binlog日志文件，一般来说，如果是全库导出，建议先刷新日志文件
-R,： 导出存储过程以及自定义函数
-x, 等同于–lock-all-tables: 在导出任务执行期间锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局锁定，并且自动关闭–single-transaction 和–lock-tables 选项。这个参数副作用比较大，这是全库锁定，备份执行过程中，该库无法进行读写操作，不是所有业务场景都能接受的。请慎用。（读写操作无法执行）

此时的binlog应该是一个新的文件了，我们连接后再看下

也就是说我们备份的文件db_all_20210202_19_21.sql包含了binlog文件mysql-bin.000014之前的所有数据

然后我们又写了一些数据

现在有人恶意删库跑路了！！！

我们的所有数据库都被删除了，我们尝试进行恢复数据

我们首先需要找到binlog文件删库的日志是记录在哪里的，这里主要根据时间来依次查找下

先看下当前有哪些binlog日志文件，直接在binlog日志目录下查看

我们在mysql-bin.000014-mysql-bin.000017之间查找删库语句

执行：mysqlbinlog -v /var/lib/binlog/mysql-bin.000014； 可以查看binlog详细日志，以及操作的时间。先定位到删库的时间，然后寻找对应的删库操作，我们这里是手动删除db1和db2的，根据时间先定位到了是在日志文件mysql-bin.000014

然后执行：show binlog events in ‘mysql-bin.000014’;

可以看到position 984到1231为我们的删库语句日志，所以我们需要先恢复到position 984之前

此时如果整个mysql的的存储目录都被删掉了，则需要重新安装下mysql或者想办法生成一份初始化的存储目录文件

笔者是使用docker安装的，所以重启了docker即重新生成了，重启后的文件如下

1、我们先恢复到我们上述的备份包

执行：mysql -uroot -p123456 </db_all_20210202_19_21.sql

执行后查看数据，发现db1和db2都已经恢复了，但是后续增加的name=7、8、9三条数据丢失了

2、我们基于binlog恢复备份包到删库之前的数据

执行：mysqlbinlog --start-position=1 --stop-position 984 /var/lib/binlog/mysql-bin.000014 | mysql -uroot -p;

然后查看数据库表，发现数据已经完整了，如果此时还需要后面的数据，那么依次恢复mysql-bin.000015、mysql-bin.000016、mysql-bin.000017的数据即可

总结

本文介绍的只是基础的数据恢复流程，仅限学习。真正的DBA维护数据库肯定没有这么简单。

注意点：

• 1、必须定期备份完整数据库以及开启binlog日志，binlog日志的目录必须放好，如果binlog的日志也被删除了，那么数据就很难全量恢复了
• 2、需要考虑备份数据库时的数据一致性，因为当数据量大时备份比较耗时，而这个时候肯定不能把库表全部锁住，本文为了演示才进行锁表备份（可以考虑停掉一个从库，在从库上备份数据，完成后再连接到主库），另外mysqldump据说在数据量大时比较耗时，DBA可能会采取其他的备份手段