mysql数据被误删的恢复方案以及预防措施_Mysql

前言

数据误删是一个严重的数据库管理问题，但通过合理的备份策略和使用适当的恢复工具，可以有效地减少数据丢失的风险。

几种常见的数据恢复方法：包括使用备份、二进制日志、表空间文件以及第三方工具

一、使用备份恢复

备份是最常见的数据恢复方法。通过定期备份数据库，可以在数据丢失时快速恢复到最近的备份状态。

停止mysql服务
```
sudo systemctl stop mysql
```
恢复备份
```
mysql -u root -p < /path/to/backup.sql
```
重启mysql服务
```
sudo systemctl start mysql
```

优点：

简单易行：只需执行几条简单的命令即可完成恢复。
可靠性高：只要备份文件完整且未损坏，可以完全恢复到备份时的状态。

缺点：

数据丢失：只能恢复到最后一次备份的时间点，之后的数据无法恢复。
依赖备份策略：需要有定期的备份计划，否则可能没有可用的备份文件。

二、使用二进制日志（binary log）

二进制日志记录了所有对数据库进行的更改操作。通过这些日志，可以回滚到特定时间点或重放某些操作。

查询binlog开启状态

首先要保证binlog是开启的，不然数据肯定是没办法恢复回来的

show variables like 'log_bin';

mysql> show variables like 'log_bin';
+---------------+-------+
| variable_name | value |
+---------------+-------+
| log_bin       | on    |
+---------------+-------+
1 row in set (0.01 sec)

如果log_bin的值为on，则表示binlog已经开启；如果值为off，则表示binlog没有开启

查询binlog模式
```
show variables like 'binlog_format';
```
将返回一个结果集，其中包含当前的binlog格式。可能的值有：
- row：表示使用行模式（row-based replication），这是推荐的设置，因为它提供了更好的数据一致性。
- statement：表示使用语句模式（statement-based replication），在这种模式下，可能会丢失一些数据，因为它仅记录执行的sql语句。
- mixed：表示混合模式（mixed-based replication），在这种模式下，mysql会根据需要自动切换行模式和语句模式

查询当前使用的binlog文件

mysql> show master status\g
*************************** 1. row ***************************
 file: mysql-bin.000217
 position: 668127868
 binlog_do_db: 
 binlog_ignore_db: 
executed_gtid_set: 29dc2bf9-f657-11ee-b369-08c0eb829a3c:1-291852745,
744ca9cd-5f86-11ef-98d6-0c42a131d16f:1-5374311
1 row in set (0.00 sec)

查找二进制日志文件：找到包含删除操作的二进制日志文件

通过mysqlbinlog工具将binlog文件解析成可读的sql文件
```
mysqlbinlog --start-datetime="yyyy-mm-dd hh:mm:ss" --stop-datetime="yyyy-mm-dd hh:mm:ss" /path/to/binlog | mysql -u root -p
```
其中：–start-datetime 和 --stop-datetime 用于指定时间范围，以精确定位到误删操作之前的状态。
重放数据

解析的这个文件就是一个sql脚本文件，通过往常的方式执行sql脚本即可
```
mysql -uroot -proot < binlog.sql
```
优点：
- 精确恢复：可以根据具体的时间点进行恢复，减少数据丢失。
- 灵活性高：适用于各种复杂的恢复场景。
缺点：
- 复杂性较高：需要了解二进制日志的结构和使用方法。
- 依赖日志完整性：如果二进制日志文件不完整或损坏，可能无法成功恢复。

通过 mysql 的二进制日志（binlog）恢复被删除的表，步骤

# 查看 binlog 已启用
show variables like 'log_bin';
如果返回值为 on，则已启用。

# 查找 binlog 文件
show binary logs;
# 使用 mysqlbinlog 工具读取 binlog 文件
mysqlbinlog --start-datetime="2023-10-01 00:00:00" --stop-datetime="2023-10-01 23:59:59" binlog.000001

# 查找删除表的操作
# 使用 grep 来筛选出 drop table 语句：
mysqlbinlog binlog.000001 | grep 'drop table'

# 重放删除之前的操作
#确认了删除表之前的状态后，提取出在删除之前的 create table 语句，然后手动重新创建该表。

# 恢复数据
# 如果在 binlog 中找到插入数据的操作，可以通过相应的 sql 语句恢复数据。

#注意事项
#进行此操作时请确保停止对数据库的写入，以避免数据不一致。
#操作前最好备份当前数据库状态，以防万一。

三、使用innodb表空间恢复

对于innodb存储引擎，可以通过复制表空间文件（.ibd文件）来进行恢复。这种方法适用于物理文件级别的恢复。

停止mysql服务
```
sudo systemctl stop mysql
```
复制ibd文件：将误删表的ibd文件从备份或旧版本中复制回来
修改表结构：根据需要修改表结构，使其与当前数据库一致。
启动mysql服务
```
sudo systemctl start mysql
```
导入表空间
```
alter table your_table_name import tablespace;
```
优点：
- 快速恢复：适用于大数据集，因为不需要重新加载整个表的数据。
- 物理级别恢复：可以直接从文件系统中恢复表空间文件。
缺点：
- 风险较高：如果表结构不一致，可能会导致数据损坏。
- 依赖文件系统：需要访问底层的文件系统，操作较为复杂。