硬核实战！mysql 错误操作整个表全部数据后如何恢复？附解决过程、思路（百万行SQL，通过binlog日志恢复）

2023-12-24 07:28
文章标签 sql 实战 数据 mysql 日志 恢复 解决 过程 全部 binlog 思路 database 整个 硬核 百万 错误操作

本文主要是介绍硬核实战！mysql 错误操作整个表全部数据后如何恢复？附解决过程、思路（百万行SQL，通过binlog日志恢复），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

mysql 错误操作整个表全部数据后如何恢复？（百万行SQL，通过binlog日志恢复）

事件起因

事情起因：以为某个表里的数据都是系统配置的数据，没有用户数据，一个字段需要覆盖替换为新的url链接，直接写下了update t_xxx set xxx = ‘https://xxxxxxxxx’ ，然后执行了，执行的时候IDEA还提示这是危险操作，我思考了下不危险，这个就是要全部覆盖，然后就点击了execute确认执行，于是翻车了，等我覆盖完去看效果的时候，惊奇的发现用户数据也被覆盖了，于是去看这个表里的数据，真有用户数据，但是值得高兴的是，全被覆盖了。。。。真是手贱啊，小心了一万年还是翻车了,全覆盖了跟删了没多大区别~~~~都是闯祸
既然事情已经发生，那就开始修复把~

在这里插入图片描述

找Binlog文件位置

binlog文件是mysql的更新记录，二进制形式。可以用于恢复数据。
这个文件每1个G为一个文件，满 了就新建一个，所以mysql运行久了这个binlog文件很多个，每个1G，建议跟我一样设置滚动删除，我是最多保留最近8个。（实验机器配置低~）

我的是mysql 8,ubuntu，我的binlog文件在这个目录

/var/lib/mysql

在这里插入图片描述

聪明的你一眼就能看出这个binlog 27号文件只有六百多兆，序号也是最新的，证明这个是最近的一个文件，就是他了，冲

读取指定时间范围内的数据操作DML

我直接查看了我这冤种操作的时间，发现大概是7分的时候操作的覆盖调数据，于是我把那一分钟里的SQL导出来（自行评估时间范围），导出为一个SQL文件
备注：mysqlbinlog工具是mysql自带的

mysqlbinlog --base64-output=DECODE-ROWS --verbose --start-datetime="2023-12-23 01:00:00" --stop-datetime="2023-12-23 01:10:00" /var/lib/mysql/binlog.000027 > restore.sql

把导出的SQL清理数据

导出的sql，我用IDEA打开的，随便你用哪个好用的文本处理工具，我们直接搜索set这个词，就能找到SQL的最开始位置如图


例如一条

### UPDATE `xx`.`t_xx`
### WHERE
###   @1=1
###   @2='xx'
###   @3=NULL
###   @4='默认'
###   @5='xx'
###   @6='xxxx'
###   @7='1'
###   @8=1
###   @9='2023-08-19 15:29:58'
###   @10='2023-08-19 18:41:06'
### SET
###   @1=1
###   @2='xx'
###   @3='xx'
###   @4='默认'
###   @5='xx'
###   @6='xx'
###   @7='1'
###   @8=1
###   @9='2023-08-19 15:29:58'
###   @10='2023-12-23 01:07:09'

这个数据的格式不难看出，记录了变更之前和之后的每个字段数据，xx为示意作用，在真实场景中就是你的真实数据，字段根据顺序@1一直到@10，证明我有10个字段，然后没个字段的值都记录了，整个结构为

update  表 
where 
每个字段的旧值
set 
每个字段的新值

那么我们现在开始处理数据
IDEA 对于这个26MB的文件直接变仅读模式，不给我编辑，于是打开了subline text这个工具处理文本

处理数据

删除update语句以外的所有无关行

找到这句错SQL的所有update语句，其它无关行全部删除

我们要的核心就是这个update语句，我们先掐头去尾，保留中间的update,我这个文件目前有100万行~
现在我这个文件只有那一个失误SQL执行的所有update语句，总共92万行~

去掉开头的###

直接搜索把###空格，这四个字符，直接替换为空字符，于是电脑卡了一会，，，电脑这会汗流浃背，风扇狂扇，过了会好像没响应，算了撸代码解决

/*** author: humorchen* date: 2023/12/23/023 1:57* desc:**/
public class RestoreData {/*** 移除头部标识** @throws Exception*/public static void removeHeadSignal() throws Exception {String file = "C:\\Users\\Administrator\\Desktop\\restore.sql";String newFile = "C:\\Users\\Administrator\\Desktop\\restore_1.sql";BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(newFile));BufferedReader bufferedReader = new BufferedReader(new FileReader(file));String line = null;while ((line = bufferedReader.readLine()) != null) {if (line.startsWith("### ")) {line = line.substring(4);}bufferedWriter.write(line);bufferedWriter.write("\n");}bufferedWriter.flush();bufferedWriter.close();bufferedReader.close();}public static void main(String[] args) throws Exception {removeHeadSignal();}
}

在这里插入图片描述

set和where互换

这个互换简直不要太简单，轻松的很

    /*** where和set互换** @throws Exception*/public static void reverseWhereAndSet() throws Exception {String file = "C:\\Users\\Administrator\\Desktop\\restore_1.sql";String newFile = "C:\\Users\\Administrator\\Desktop\\restore_2.sql";final String SET = "SET";final String WHERE = "WHERE";BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(newFile));BufferedReader bufferedReader = new BufferedReader(new FileReader(file));String line = null;while ((line = bufferedReader.readLine()) != null) {if (line.startsWith(WHERE)) {line = SET;} else if (line.startsWith(SET)) {line = WHERE;}bufferedWriter.write(line);bufferedWriter.write("\n");}bufferedWriter.flush();bufferedWriter.close();bufferedReader.close();}public static void main(String[] args) throws Exception {
//        removeHeadSignal();reverseWhereAndSet();}

在这里插入图片描述

字段替换

把字段数组作为参数数组传入函数，然后读取@多少，替换为对应下表的字段名

/*** 替换字段名** @param columnNames*/public static void replaceColumns(String[] columnNames) throws Exception {String file = "C:\\Users\\Administrator\\Desktop\\restore_2.sql";String newFile = "C:\\Users\\Administrator\\Desktop\\restore_3.sql";BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(newFile));BufferedReader bufferedReader = new BufferedReader(new FileReader(file));String line = null;final String PREFIX = "  @";final int PREFIX_LEN = PREFIX.length();while ((line = bufferedReader.readLine()) != null) {// line：  @1=1if (line.length() > PREFIX_LEN && line.startsWith(PREFIX)) {// 等于号位置int eqIndex = line.indexOf("=");String indexStr = line.substring(PREFIX_LEN, eqIndex);if (StrUtil.isNumeric(indexStr)) {int index = Integer.parseInt(indexStr) - 1;if (index >= 0 && index < columnNames.length) {// 替换字段名line = line.replace(PREFIX + indexStr, columnNames[index]);}}}bufferedWriter.write(line);bufferedWriter.write("\n");}bufferedWriter.flush();bufferedWriter.close();bufferedReader.close();}public static void main(String[] args) throws Exception {
//        removeHeadSignal();
//        reverseWhereAndSet();replaceColumns(new String[]{"id", "title", "icon", "type", "email", "prompt", "temperature", "keep_context", "create_time", "update_time"});}

在这里插入图片描述

加,和and

看到最后你就发现只缺标点符号,;了，那就加上，起初想去判断代码处于update、set、where三个区域里加不同的，又想到要判定最后一个不能加标点，要加空格，最后一个又是分号; 真麻烦！直接搞个后缀数组算了，反正我这个表结构是固定的于是用以下代码加上标点符号和换行。

/*** 添加,和and和;** @throws Exception*/public static void addPrefix(String[] addPrefix) throws Exception {String file = "C:\\Users\\Administrator\\Desktop\\restore_3.sql";String newFile = "C:\\Users\\Administrator\\Desktop\\restore_4.sql";BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(newFile));BufferedReader bufferedReader = new BufferedReader(new FileReader(file));String line = null;int index = -1;final String UPDATE = "UPDATE";while ((line = bufferedReader.readLine()) != null) {if (line.startsWith(UPDATE)) {index = 0;bufferedWriter.write("\n\n");;}if (index >= 0) {line = line + addPrefix[index];index++;}bufferedWriter.write(line);bufferedWriter.write("\n");}bufferedWriter.flush();bufferedWriter.close();bufferedReader.close();}public static void main(String[] args) throws Exception {
//        removeHeadSignal();
//        reverseWhereAndSet();
//        replaceColumns(new String[]{"id", "title", "icon", "type", "email", "prompt", "temperature", "keep_context", "create_time", "update_time"});addPrefix(new String[]{" ", " ", ",", ",", ",", ",", ",", ",", ",", ",", ",", " ", " ", " and ", " and ", " and ", " and ", " and ", " and ", " and ", " and ", " and ", " ;"});}

在这里插入图片描述

大功告成，拿去执行即可！

执行之前你先随机取个三条执行一下看看，确认自己的SQL生产对了。
我把sql上传到了ubuntu，进入mysql -uroot -p，然后use xxx;source xxx.sql;执行完数据恢复了，会有点久，如果数据量大，执行会更久。珍惜数据。。。
我这个一百万行的恢复数据SQL大约执行了3、5分钟执行完了。数据全部恢复到错误SQL前的数据了。

这篇关于硬核实战！mysql 错误操作整个表全部数据后如何恢复？附解决过程、思路（百万行SQL，通过binlog日志恢复）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/531002。

相关文章

Python获取中国节假日数据记录入JSON文件

Python获取中国节假日数据记录入JSON文件

《Python获取中国节假日数据记录入JSON文件》项目系统内置的日历应用为了提升用户体验,特别设置了在调休日期显示“休”的UI图标功能,那么问题是这些调休数据从哪里来呢？我尝试一种更为智能的方法：P... 目录节假日数据获取存入jsON文件节假日数据读取封装完整代码项目系统内置的日历应用为了提升用户体验，
阅读更多...
将Mybatis升级为Mybatis-Plus的详细过程

将Mybatis升级为Mybatis-Plus的详细过程

《将Mybatis升级为Mybatis-Plus的详细过程》本文详细介绍了在若依管理系统（v3.8.8）中将MyBatis升级为MyBatis-Plus的过程,旨在提升开发效率,通过本文,开发者可实现... 目录说明流程增加依赖修改配置文件注释掉MyBATisConfig里面的Bean代码生成使用IDEA生
阅读更多...
揭秘Python Socket网络编程的7种硬核用法

揭秘Python Socket网络编程的7种硬核用法

《揭秘PythonSocket网络编程的7种硬核用法》Socket不仅能做聊天室,还能干一大堆硬核操作,这篇文章就带大家看看Python网络编程的7种超实用玩法,感兴趣的小伙伴可以跟随小编一起... 目录1.端口扫描器：探测开放端口2.简易 HTTP 服务器：10 秒搭个网页3.局域网游戏：多人联机对战4.
阅读更多...
MySQL双主搭建+keepalived高可用的实现

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制，确认复制是否成功4.同
阅读更多...
Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战：从入门到进阶优化1. MyBatis
阅读更多...
MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL？1.2 动态SQL的优点1.3 动态S
阅读更多...
Mysql表的简单操作(基本技能)

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例：修改表在数据库中，表的操作主要
阅读更多...
C# WinForms存储过程操作数据库的实例讲解

C# WinForms存储过程操作数据库的实例讲解

《C#WinForms存储过程操作数据库的实例讲解》：本文主要介绍C#WinForms存储过程操作数据库的实例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、存储过程基础二、C# 调用流程1. 数据库连接配置2. 执行存储过程（增删改）3. 查询数据三、事务处
阅读更多...
Pandas使用SQLite3实战

Pandas使用SQLite3实战

《Pandas使用SQLite3实战》本文主要介绍了Pandas使用SQLite3实战,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学... 目录1 环境准备2 从 SQLite3VlfrWQzgt 读取数据到 DataFrame基础用法：读
阅读更多...
JSON Web Token在登陆中的使用过程

JSON Web Token在登陆中的使用过程

《JSONWebToken在登陆中的使用过程》：本文主要介绍JSONWebToken在登陆中的使用过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录JWT 介绍微服务架构中的 JWT 使用结合微服务网关的 JWT 验证1. 用户登录，生成 JWT2. 自定义过滤
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2