MySQL数据库数据恢复方案应对没有where误操作导致的大量数据更新或删除

最新推荐文章于 2025-03-05 19:10:24 发布

Micro麦可乐

最新推荐文章于 2025-03-05 19:10:24 发布

阅读量1.3w

点赞数 44

分类专栏：数据库文章标签：数据库 mysql sql 数据恢复 binlog

本文链接：https://blog.csdn.net/lhmyy521125/article/details/139511699

版权

数据库专栏收录该内容

6 篇文章

订阅专栏

在这里插入图片描述

😄 19年之后由于某些原因断更了三年，23年重新扬帆起航，推出更多优质博文，希望大家多多支持～
🌷 古之立大事者，不惟有超世之才，亦必有坚忍不拔之志
🎐 个人CSND主页——Micro麦可乐的博客
🐥《Docker实操教程》专栏以最新的Centos版本为基础进行Docker实操教程，入门到实战
🌺《RabbitMQ》专栏主要介绍使用JAVA开发RabbitMQ的系列教程，从基础知识到项目实战
🌸《设计模式》专栏以实际的生活场景为案例进行讲解，让大家对设计模式有一个更清晰的理解
💕《Jenkins实战》专栏主要介绍Jenkins+Docker的实战教程，让你快速掌握项目CI/CD，是2024年最新的实战教程
🌞《Spring Boot》专栏主要介绍我们日常工作项目中经常应用到的功能以及技巧，代码样例完整
如果文章能够给大家带来一定的帮助！欢迎关注、评论互动～

你是否也遇到这种问题？

平时我们在系统开发过程中操作数据库的时候，在执行 Update 或 Delete 语句时因为自己的疏忽忘记传递 Where 条件，导致数据库中的数据大量的被更新或删除，你是不是准备跑路？

不要慌，借助 SQL 正是当前CSDN热门话题的机会，博主今天来详细讲解如何应对这种情况并提供数据恢复方案。

前提条件

如果你公司中有专门运维人员或专职的 DBA（全称Database Administrator），又或者你就是公司里那个从前端到后端再到运维都是你负责的全能型人才，在安装MySQL 数据库的时候一般都会开启binlog日志。

首先本次博主以 MySQL 数据库使用的是MySQL8，且开启了binlog，执行以下命令查看是否开启了binlog

SHOW VARIABLES = 'log_bin';

输出结果如果是ON，则代表已经开启
在这里插入图片描述

binlog作用

binlog 一般是做为数据库主从同步时候从库的数据的来源，另外一方面就可以用于恢复数据。
针对 MYSQL主从同步 可以参考博主的这篇教程：Docker上实现MYSQL实现主从复制

本次我们就是利用binlog来实现恢复数据！

开始演示

创建测试数据

首先我们创建 recovery-test 数据库，并创建一个 student 学生表，并插入测试数据

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学生ID',
  `name` varchar(50) NOT NULL COMMENT '学生姓名',
  `gender` varchar(10) NOT NULL COMMENT '学生性别',
  `birthday` date NOT NULL COMMENT '学生生日',
  `address` varchar(100) NOT NULL COMMENT '学生住址',
  `phone` varchar(20) NOT NULL COMMENT '学生联系方式',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生信息表';


INSERT INTO `student` VALUES (1, '小明', '男', '2023-06-16', '广州', '13700137000');
INSERT INTO `student` VALUES (2, '小羊', '女', '2023-06-16', '广州', '13800126000');

模拟删除

模拟我们因为疏忽没有拼接 WHERE 条件的情况

DELETE FROM student

获取binlog

如果你不清楚binlog的存储位置可以执行

SHOW VARIABLES LIKE 'log_bin_basename';

可以看到binlog的命名以及前缀的路径
在这里插入图片描述
以博主mac系统进入对应目录查看，可以看到binlog的相关文件，可以根据时间获取binlog当前的文件名

或者你也可以执行下面的语句，获取当前当前binlog文件名

show master status;

开始恢复

在已经确认了 binlog 文件路径以及当前 binlog 文件名后，你可能会有以下两种情况：

情况一

如果你明确误操作的时间，可以执行根据删除前的时间来恢复数据，

mysqlbinlog --start-datetime="开始时间" \
--stop-datetime="结束时间" \
  --database="recovery-test" \
  /usr/local/var/mysql/binlog.016924 > binlog_before_delete.sql

情况二

如果你已经忘记了误操作的时间，那么就可以使用查询指定 SQL 的语句来获取语句执行在日志中的 position

mysqlbinlog --no-defaults -vv \
 /usr/local/var/mysql/binlog.016924 \
 --database="recovery-test" | grep -iE "(update | delete)";

其中 grep -iE "(update | delete)" 你可以替换匹配你的误操作语句，由于是新表且只删除了一次，这里博主就模糊匹配 update 或 delete

最终会获得操作语句的position值，如博主的输出如下
在这里插入图片描述

提示
#240607 就是博主获取删除时候的 position 值
如果你删除非当前当前binlog文件，可以依次查询其它 binlog 文件

最后根据 position 的值执行

mysqlbinlog --start-position=240600 \
--stop-position=240607 \
  --database="recovery-test" \
  /usr/local/var/mysql/binlog.016924 > binlog_before_delete.sql

最后执行还原操作，大家自行替换自己对应参数即可

mysql -u root -p recovery-test  < binlog_before_delete.sql

验证结果

恢复数据后，必须进行数据验证，确保数据的完整性和正确性。

结语

日常工作中处理出了使用 mysqldump 来定时备份数据用以还原外，你还可以通过以上述讲解步骤，有效应对由于 UPDATE 和 DELETE 语句未加条件导致的数据大量更新或删除的问题。希望本文能为小伙伴们提供有价值的参考，提高数据管理的安全性和可靠性！

如果你在学习过程中如有疑问欢迎留言探讨，博主将在闲暇时间及时进行答复！如果本文有帮助到你，希望一键三连多多支持博主！

在这里插入图片描述