MySQL数据被误删怎么办？

忘是亡心i 2024-02-05 23:43 63阅读 0赞

#### 文章目录 ####

*  前言
 *  数据备份恢复工具
 *  数据备份策略
 *  数据备份恢复演示
 *   *  备份数据
     *  模拟数据误删
     *  恢复备份的数据
     *  恢复未备份的数据
 *  总结

## 前言 ##

很多年前，被公司外派到一家单位驻场开发一个OA项目，两个开发对接各部门的需求，需求还要及时生效（一边开发一边使用）。有一次生产环境的一个bug本地没办法复现，由于没有测试人员，也就不存在测试环境，所以本地连了生产库去调试。不出意外的话要出意外了：在调试的过程中，我俩当作开发环境很自然的把数据给删了。

作为一名只会CRUD的小白怎么会恢复数据这么高级的操作，不过还好，经过我俩一小时的百度，在各种ctrl+c、ctrl+v的命令操作下，最终成功的把数据恢复了。

如果我当时了解数据备份恢复，也不至于这么手忙脚乱的，所以程序员掌握数据的备份恢复操作还是很重要的。最近正好在输出MySQL系列文章，所以在这里记录一下MySQL数据备份和恢复的方法及操作，希望可以帮助到跟我一样的小伙伴。

## 数据备份恢复工具 ##

MySQL自带了一个数据备份的客户端mysqldump，使用mysqldump可以基于现状生成一组SQL语句（建表语句、insert语句），在数据丢失时可以通过执行这些SQL语句恢复到原始状态，从而达到备份恢复效果。但是，当数据量很大的时候，这种方式就不是很适合了，因为mysqldump是单线程执行，过多的SQL执行会使整个恢复过程过于缓慢。

所以，基于此痛点，就诞生了一款开源的多线程备份恢复工具 mydumper，其特点就是多线程、快， 具体可以前往[博客][Link 1]进行了解，这篇博客介绍的非常的详细，这里就不多赘述。

以上两种工具都属于逻辑备份，何为逻辑备份？就是数据通过SQL语句的形式进行备份和恢复，总的来说执行速度会很慢。

还有一种物理备份方式，简单来说就是直接将表数据.ibd文件、binlog、redolog等物理文件直接copy备份，相对逻辑备份来讲物理备份速度会快很多，目前常用的物理备份工具有[PXB(Percona XtraBackup)][PXB_Percona XtraBackup] 以及MySQL8.0推出的新特性 [Clone Plugin][] ，感兴趣的可以自行前往了解。

## 数据备份策略 ##

为了避免误操作导致数据被删除，通常在生产环境中会制定数据备份策略，比如用什么工具，备份周期是一天一次还是一周一次，每次备份是全量还是增量等，这个取决于数据的重要性、数据的变动频率、备份成本等方面的需求。

下面将基于MySQL自带的mysqldump进行数据备份，并演示一下数据被误删后的恢复操作。

## 数据备份恢复演示 ##

备份前先看一下当前的数据情况。

![在这里插入图片描述][64a9c256e7c14c2389d27bff6e6dae7c.png]

### 备份数据 ###

在使用mysqldump的时候根据自己的备份需求加一堆参数，比如下面这条命令：

mysqldump -uroot -pLeYk2qwd -h 127.0.0.1 -P3306 -A -R  --triggers --master-data=2  --single-transaction  > /backup/full.sql

*  `-u -p -h -P`就不用说了，毕竟作为一个客户端，连接MySQL服务还是需要用户名密码验证的。
 *  `-A` 是用来备份这个MySQL实例所有的库，如果要备份单个库，参数为 ‘-B db1 db2’
 *  `-R` 是用来备份存储过程及函数。
 *  `--triggers` 用来备份触发器。
 *  `--master-data=2` 的作用是：在备份时记录binlog的状态信息，这个后面会用到。
 *  `--single-transaction` 的作用是：直接备份可能会因为时间过长而导致锁等待问题。为了避免这种情况，该参数对InnoDB引擎的表数据进行快照备份，减少锁等待的同时也保证了数据一致性。

更多的参数使用请参考[官方文档][Link 2]。

执行上面的命令后就会得到一份sql备份文件。

> 一般数据量级在100G左右，备份时间大约在30分钟左右，所以数据量很大的情况下建议物理备份。

### 模拟数据误删 ###

执行备份命令成功后进行删库或删表操作，**模拟误删场景**

drop database test;

![在这里插入图片描述][bf86cee4aada4e91bb4b7e84b92e3d1f.png]

可以看到test库已经被删除。

### 恢复备份的数据 ###

接下来就可以执行恢复数据命令，将刚才备份的`/backup/full.sql`进行恢复，命令如下：

set sql_log_bin=0;
    source /backup/full.sql;

> `set sql_log_bin=0;`是将binlog日志记录进行关闭，否则数据恢复时所执行的sql语句也会被记录到binlog中，binlog是不需要记录恢复的操作。

命令执行成功后，刚才被删的库以及表数据就被恢复了。

### 恢复未备份的数据 ###

在实际应用中，恢复数据不是这么简单的，因为备份操作基本上不会是实时的，如果昨天备份数据，今天误删了数据，那么在这之间的数据如何恢复？

这个时候就体现出binlog的作用了，之前的文章介绍过，binlog会记录所有的增删改操作，所以，未备份的数据就可以通过binlog进行恢复。如何恢复呢？

上面说到，mysqldump命令中有一个参数：`--master-data=2`，加上这个参数后，会在备份的sql文件中记录此次备份的数据位于binlog的位置，如下图

![在这里插入图片描述][1f87065b4fc44fe3a9d6020e150de2ff.png]

`MASTER_LOG_FILE` 的意思是此次的备份已经到‘mysql-bin.000004’这个文件了，备份最末端的数据在文件中的偏移量为`MASTER_LOG_POS=2548`。

基于这个信息，我们可以知道：**未备份的数据位于binlog偏移量为MASTER\_LOG\_POS至误删操作的偏移量。**

通过命令`mysqlbinlog /data/mysql/mysql-bin.000004` 或者 `show binlog events in 'mysql-bin.000004'`可以看到未备份数据的偏移量。如下图

![在这里插入图片描述][9693d578a842468b90e843cf5f440f8a.png]

为了演示“**恢复未备份的数据**”，我在account表中添加几条数据，然后再进行「删库->恢复备份的数据->恢复未备份的数据」的操作。备份状态如下图

![在这里插入图片描述][7efdf6d782db42adb97e47bd87cd3e56.png]

再次执行恢复命令后，会发现新添加的这两条数据不存在。

![在这里插入图片描述][84befc4e8104409e9f93cf35a9c8ed8a.png]

此时，备份的数据和binlog的状态对应如下图

![在这里插入图片描述][377a0c9eb8504ece908db951d7f7835a.png]

然后先执行以下命令将未备份的数据SQL语句导出来

mysqlbinlog --start-position=2770 --stop-position=3327 /data/mysql/mysql-bin.000004 >/backup/bin.sql

再登录到mysql服务执行以下命令即可恢复到删库前的状态。

set sql_log_bin=0;
    source /backup/bin.sql
    set sql_log_bin=1;

至此，在误删操作后，数据就恢复成功了。

可能会有人问“binlog也被删除了呢？怎么恢复？”，这个就涉及到主从复制、高可用模式了。下篇文章会介绍，感兴趣的话点点关注吧。

在这要说明一下，MySQL5.7后默认开启了GTID（全局事务标识符）特性，用于简化 MySQL 主从复制和故障恢复，也可以应用到刚才的恢复未备份的数据中。跟基于偏移量导出binlog相比，执行基于gtid的sql可以保证唯一性、幂等性，功能更丰富。操作与偏移量相似，这里就不演示了，贴一个相关的命令作为参考

-- 导出gtid为1至10，不包括6和9的sql语句，
    mysqlbinlog --skip-gtids --include-gtids='xxxxxxx-1xxxx-xxxx-0xxxxxx:1-10' --exclude-gtids='xxxxxxx5-axxxx-1xxx-8xxx-0xxxx:6','48xxxx5-axxx-1xxa-xxxxxx:9' mysql-bin.000004 >/backup/bin.sql

## 总结 ##

mysqldump只是进行了数据的备份，无法做到完全的恢复，在恢复数据时还要借助binlog对未及时备份的数据进行恢复。

虽然现在许多公司倾向于使用云端的高可用性集群数据库，忽略了对备份恢复操作的关注，但为了安全起见，仍需掌握数据备份与恢复的操作。这样可以在突发情况下，可以采取应对措施，减少事故带来的损失。

[Link 1]: https://www.cnblogs.com/gered/p/15381240.html
[PXB_Percona XtraBackup]: https://www.cnblogs.com/xulinforDB/p/17496195.html
[Clone Plugin]: https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html
[64a9c256e7c14c2389d27bff6e6dae7c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/8654db47a2c8495ab80f34118a907c14.png
[Link 2]: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
[bf86cee4aada4e91bb4b7e84b92e3d1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c20cb323369441a9b18f90622e9a96b9.png
[1f87065b4fc44fe3a9d6020e150de2ff.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b0bc12c22444453f9aef9ae3030b5bfd.png
[9693d578a842468b90e843cf5f440f8a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/adc51b76746e4c4bb1bbe874ad1cab76.png
[7efdf6d782db42adb97e47bd87cd3e56.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/f8c40234f776497284169a2def18b22f.png
[84befc4e8104409e9f93cf35a9c8ed8a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b5534931eeed40dfbf2bc8984b8f83ec.png
[377a0c9eb8504ece908db951d7f7835a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/988638b8ffb84ece8ef38e46b936e449.png