MySQL误删怎么办

比眉伴天荒 2023-10-08 16:55 52阅读 0赞

## 使用delete误删 ##

### 处理 ###

使用flashback工具，原理是修改binlog的内容，拿回原库重放。需要binlog格式为row格式，并且binlog\_row\_image=FULL

执行流程：  
● 对于Inser语句，对应的binlog event类型是write\_rows\_event，把它改写成delete\_rows\_event即可  
● 对于delete语句，是将delete\_rows\_event改为write\_rows\_event  
● 如果是update\_rows，binlog里面记录了数据行修改前和修改后的值，对调这两行的位置即可

**不建议直接在主库上执行这些操作**，恢复数据比较安全的做法是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。因为如果在主库上操作的话，可能由于发现数据问题的时间晚了一点儿，导致已经在之前误操作的基础上，业务到吗又继续修改了其他数据，如果单独恢复这几行数据，而又未经确认的话，会造成数据的二次破坏。

### 预防 ###

1.  把sql\_sage\_update设置为on，如果我们忘记在delete或者update语句中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错，如果真的需要全删一个小标，可以在条件中增加where id>=0，但是delete全表是很慢的，需要生成回滚日志，写redo、写binlog，所以从性能上来说应该优先考虑使用truncate table或者drop table命令（这两个命令删除，binlog中就只会有truncate/drop语句）
2.  代码上线前，必须经过SQL审计

## 误删库/表 ##

### 方法一： ###

如果想恢复数据，就需要有全量备份+增量日志的方式，要求线上有定期的全量备份，并且实时备份binlog。

#### 举例 ####

假设当前库的备份策略是一天一备，假如有人中午12点误删了一个库，恢复流程如下：

1.  取最近一次的全备，也就是当天的0点
2.  用备份恢复出一个临时库
3.  从日志备份里，取出凌晨0点之后的日志
4.  把这些日志除了误删数据的语句外，全部应用到临时库

#### 注意事项 ####

##### 加速数据恢复. #####

如果临时库上有多个数据库，可以在使用mysqlbinlog时，加上一个-database参数，用来指定误删表所在的库，这样就避免了在恢复数据时还要应用其他库日志的情况（读取无用日志）

##### 跳过误操作的binlog #####

● 如果原实例没有使用GTID模式，只能在应用到包含指定错误的binlog的时候，先应用-stop-position参数执行到误操作之前的日志，然后再用-start-position从误操作之后的日志继续执行  
● 如果实例使用了GTID模式，可以将错误操作的binlog gtid跳过

##### mysqlbinlog恢复数据慢怎么解决 #####

**主要原因**：  
● 如果是误删表，最好就是只恢复出这张表，但是mysqlbinlog工具并不能指定只解析一个表的日志  
● 用mysqlbinlog解析出日志应用，应用日志的过程只能是单线程，MySQL的并行复制在这里用不上

**加速的方法**：在用备份恢复出临时实例后，将这个实例设置成备库的从库，这样：

1.  在start slave之前，先通过执行change replication filter replicate\_do\_table=(tbl\_name)命令。就可以让临时库只同步误操作的表
2.  也可以用上并行复制来加速整个数据恢复过程

##### 如果备库上已经删除了临时实例需要的binlog怎么办？ #####

可以从binlog备份系统中找到需要的binlog，再放回备库中，比如临时实例需要的binlog从binlog\_0005开始，而备库只有binlog\_0007开始，可以找到binlog\_0005和binlog\_0006手动放回备库，  
● 将两个binlog文件放到备库的日志目录下  
● 打开日志目录的master.index文件，在文件开头加入缺少的这两个binlog名词  
● 重启备库，让备库重新认识这两个日志文件

### 方法二： ###

使用延迟复制备库，设置从库同步binlog的延迟时间，一般主从存在的问题是，如果主库上有个表被删除了，这个命令很快就会被发送给所有的从库，导致所有的从库数据表也都被一起误删了。  
通过 CHANGE MASTER TO MASTER\_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟，比如你把 N 设置为 3600，这就代表了如果主库上有数据被误删了，并且在 1 小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。这样的话，你就随时可以得到一个，只需要最多再追 1 小时，就可以恢复出数据的临时实例，也就缩短了整个数据恢复需要的时间。

### 预防误删库/表 ###

● 账号分离  
○ 只给业务开发童鞋DML权限，不给truncate/drop权限  
○ 即使是DBA团队成员，日志也都只能使用只读账号，必要的时候才是用有更新权限的账号  
● 制定操作规范  
○ 删表之前，必须先对表做改名操作， 观察一段时间，确保业务无影响以后再删  
○ 改名的时候，要求给表名加固定的后缀（比如\_to\_be\_deleted），然后删除表的动作必须通过管理系统执行，并且管理系删除表的时候，之恩给你删除固定后缀的表

## 使用rm命令误删整个MySQL实例 ##

对于一个有高可用机制的MySQL集群来说，rm删除数据后，只要不是恶意的把整个集群删除，而只是删除了其中一个节点的数据的话，HA就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。

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

[ebf67ba23ab74592962dedf3620fbb8f.png]: https://img-blog.csdnimg.cn/ebf67ba23ab74592962dedf3620fbb8f.png