【BUG 记录】史诗级 BUG - MYSQL 删库删表却没有备份如何恢复数据

2024-02-27 18:44
文章标签 数据 mysql 恢复 记录 bug 没有 备份 database 删库 史诗 删表

本文主要是介绍【BUG 记录】史诗级 BUG - MYSQL 删库删表却没有备份如何恢复数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

【BUG 记录】史诗级 BUG - MYSQL 删库删表却没有备份如何恢复数据

  • 1. 问题描述
  • 2. 解决方案（binlog）
    • 2.1 构造测试环境
    • 2.2 查看 MySQL 环境是否开启 binlog
    • 2.3 查看所有的 binlog 日志记录
    • 2.4 查看当前正在使用的是哪一个 binlog 文件
    • 2.5 查看此时的 binlog 日志记录
    • 2.6 删除记录（这里模拟删除表操作）
    • 2.7 mysqlbinlog 恢复数据
    • 2.8 binlog2sql 数据回滚
  • 3. binlog2sql 详细介绍
    • 3.1 概念
    • 3.2 安装 binlog2sql
    • 3.3 更改mysql配置文件
    • 3.4 运行脚本获取数据库里的内容
    • 3.5 删除数据
    • 3.6 回滚数据

在这里插入图片描述

1. 问题描述

  在实际开发过程中，不小心把测试环境的三张业务表（user、comany、user_dept_rel）数据删除了，并且当时没有备份这三张表的数据，导致测试环境瘫痪，其中最关键的user表给删了，导致很多测试账号登录不上系统，搞了一下午，最终通过binlog日志 恢复了数据。在此记录一下当时的解决过程。

环境：Python 3.11
mysql：8.0+

2. 解决方案（binlog）

2.1 构造测试环境

  1. 库（test）

  2. 建表（user、company、user_dept_rel），面下测试中主要使用user表来演示

    create table user(id int AUTO_INCREMENT primary key,name varchar(50),phone varchar(50)
);insert into user(id,name,phone) value(1,"zhangsan",17756566565);
insert into user(id,name,phone) value(2,"lisi",17712345687);
insert into user(id,name,phone) value(3,"wangwu",17756562333);
insert into user(id,name,phone) value(4,"zhaoliu",17756564444);create table company(id int AUTO_INCREMENT primary key,name varchar(50),code varchar(50)
);insert into company(id,name,code) value(1,"测试公司1","ceshigongsi1");
insert into company(id,name,code) value(2,"测试公司2","ceshigongsi2");
insert into company(id,name,code) value(3,"测试公司3","ceshigongsi3");
insert into company(id,name,code) value(4,"测试公司4","ceshigongsi4");create table user_dept_rel(id int AUTO_INCREMENT primary key,user_id int,dept_id int
);insert into user_dept_rel(id,user_id,dept_id) value(1,111,1001);
insert into user_dept_rel(id,user_id,dept_id) value(2,222,1002);
insert into user_dept_rel(id,user_id,dept_id) value(3,333,1003);
insert into user_dept_rel(id,user_id,dept_id) value(4,444,1004);

  3. 查看数据
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

2.2 查看 MySQL 环境是否开启 binlog

  通过命令查看是否开启 binlog 记录功能（该功能，默认是开启的）

show variables like '%log_bin%';

在这里插入图片描述

2.3 查看所有的 binlog 日志记录

  1. 命令
    show binary logs;
  2. 结果
    在这里插入图片描述

2.4 查看当前正在使用的是哪一个 binlog 文件

  1. 命令
    -- 查看当前的 binlog 文件
show master status;
  2. 结果
    在这里插入图片描述
    当使用的是MYSQL-bin.000042这个日志文件，在前面 binlog 日志文件路径 （我的是D:\Settings\MySQL\DATA\Data ）下查看
    在这里插入图片描述

2.5 查看此时的 binlog 日志记录

  1. 命令

    show binlog events in 'MYSQL-bin.000042';

  2. 结果

    在这里插入图片描述

2.6 删除记录（这里模拟删除表操作）

  1. 删除
    delete from test.user;
delete from test.company;
delete from test.user_dept_rel;
  2. 查看日志
    在这里插入图片描述
    可以看到我们三个删除事件，注意binlog 中每个事件都有一个 begin 和 commit，我们后面进行恢复或回滚的时候开始和结束的 pos 都是取的事件整体的开始点和结束点。比如上面第一个删除事件的开始点(Pos)其实是7868，结束点（End_log_pos）则是8167。

2.7 mysqlbinlog 恢复数据

注意： 这里是恢复数据，不是回滚数据

  • 恢复的本质：将原有的插入语句再执行一遍
  • 回滚的本质：回退到删除之前的状态

  mysqlbinlog是mysql自带的命令，一般是在mysql安装目录下的bin目录里。因为我们是恢复数据，所以要找到已经删除语句的对应写入事件，将该事件再重新执行一遍即可。

  1. 通过命令确认插入语句的事件位置

    show binlog events in 'MYSQL-bin.000042';

    在这里插入图片描述
    确定了 user 表的插入事件起始位置是3677 ，结果位置是4819

  2. 使用 mysqlbinlog 命令恢复

    D:\Settings\MySQL\DATA\Data>mysqlbinlog.exe --start-position=3677 --stop-position=4819 mysql-bin.000042 | mysql -uroot -p123456

    常用参数解释：
    D:\Settings\MySQL\DATA\Data> ： mysqlbinlog 的路径，就上面通过语句查出来的日志路径
    --start-position：起始位置
    --stop-position：结束位置
    --start-datetime：起始日期
    --stop-datetime：结束日期
    mysql-bin.000042：日志文件

  3. 查看结果
    在这里插入图片描述

    • 查看表：在这里插入图片描述
      可以看到数据已经回来了

    • 查看 binlog 日志：
      在这里插入图片描述
      可以看出，日志中也多了四条写入事件。这里只恢复了user表，company 和 user_dept 同理

注意：如果说找不到 insert 语句了，或者insert 语句 在很久之前执行的，找不到，我们可以通过在日志中找出它的 delete 语句，然后手动将 delete from 语句转换成 insert 语句（可以写一个python 脚本），重新执行一下就可以了。（我当时就是这么干的）

小结：

  • mysqlbinlog命令只是用于恢复，不能用于回滚。如果数据进行update操作，则很难通过该命令恢复。所以该命令比较适用一些数据迁移，数据同步的场景。

  • mysqlbinlog 运行过程中如果出现unknown variable 'default-character-set=utf8mb4'异常，可以再该命令后加--no-defaults参数解决：mysqlbinlog --no-defaults

2.8 binlog2sql 数据回滚

  binlog2sql是一个第三方的工具，binlog2sql回滚的原理是生成要回滚事件对应的sql语句，我们最后只需要拷贝该语句实现即可。

3. binlog2sql 详细介绍

3.1 概念

  binlog2sql是一个用于解析二进制日志的开源工具。它具有从二进制日志中提取原始 SQL 语句的功能。它具有从二进制日志生成回滚 SQL 以进行时间点恢复的功能。

3.2 安装 binlog2sql

前提条件：安装这个工具，需要电脑具备python环境，如果没有还请自行安装python 3.11 (这块我的python版本是3.11)

cd D:\Workspace\PyCharm\
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

3.3 更改mysql配置文件

  在 my.ini 中添加，找到log-bin="MYSQL-bin"，然后在下一行添加

log-bin
server_id = 1
binlog_format = row
binlog_row_image = full

在这里插入图片描述

~修改完之后重启 mysql 服务

3.4 运行脚本获取数据库里的内容

D:\Workspace\PyCharm\binlog2sql>cd binlog2sql
D:\Workspace\PyCharm\binlog2sql\binlog2sql>python binlog2sql.py -uroot -p123456 -P3306 -d test -t user --start-file=D:\Settings\MySQL\DATA\Data\  --stop-file=MYSQL-bin.000042
Traceback (most recent call last):File "D:\Workspace\PyCharm\binlog2sql\binlog2sql\binlog2sql.py", line 145, in <module>binlog2sql = Binlog2sql(connection_settings=conn_setting, start_file=args.start_file, start_pos=args.start_pos,^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^File "D:\Workspace\PyCharm\binlog2sql\binlog2sql\binlog2sql.py", line 46, in __init__self.connection = pymysql.connect(**self.conn_setting)^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^File "D:\Settings\Python\Python311\Lib\site-packages\pymysql\__init__.py", line 90, in Connectreturn Connection(*args, **kwargs)^^^^^^^^^^^^^^^^^^^^^^^^^^^File "D:\Settings\Python\Python311\Lib\site-packages\pymysql\connections.py", line 706, in __init__self.connect()File "D:\Settings\Python\Python311\Lib\site-packages\pymysql\connections.py", line 931, in connectself._get_server_information()File "D:\Settings\Python\Python311\Lib\site-packages\pymysql\connections.py", line 1269, in _get_server_informationself.server_charset = charset_by_id(lang).name^^^^^^^^^^^^^^^^^^^File "D:\Settings\Python\Python311\Lib\site-packages\pymysql\charset.py", line 38, in by_idreturn self._by_id[id]~~~~~~~~~~~^^^^
KeyError: 255

  如果出现以上报错，更新PyMySQL 即可解决，mysql8的版本问题，安装对应的binlog2sql工具版本

pip uninstall PyMySQL
pip install PyMySQL==0.9.3

再次运行

D:\Workspace\PyCharm\binlog2sql\binlog2sql>python binlog2sql.py -uroot -p123456 -P3306 -d test -t user --start-file=MYSQL-bin.000042  --stop-file=MYSQL-bin.000042
D:\Settings\Python\Python311\Lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 1")result = self._query(query)
USE b'test';
create table t1 (title varchar(100) default null,price int not null) engine=innodb;
USE b'test';
create table user(id int AUTO_INCREMENT primary key,name varchar(50),phone varchar(50)
);
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (1, 'zhangsan', '17756566565'); #start 3598 end 3874 time 2024-02-26 10:55:04
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (2, 'lisi', '17712345687'); #start 3905 end 4177 time 2024-02-26 10:55:04
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (3, 'wangwu', '17756562333'); #start 4208 end 4482 time 2024-02-26 10:55:04
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (4, 'zhaoliu', '17756564444'); #start 4513 end 4788 time 2024-02-26 10:55:04
USE b'test';
create table company(id int AUTO_INCREMENT primary key,name varchar(50),code varchar(50)
);
USE b'test';
create table user_dept_rel(id int AUTO_INCREMENT primary key,user_id int,dept_id int
);
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='zhangsan' AND `phone`='17756566565' LIMIT 1; #start 7789 end 8136 time 2024-02-26 11:39:35
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='lisi' AND `phone`='17712345687' LIMIT 1; #start 7789 end 8136 time 2024-02-26 11:39:35
DELETE FROM `test`.`user` WHERE `id`=3 AND `name`='wangwu' AND `phone`='17756562333' LIMIT 1; #start 7789 end 8136 time 2024-02-26 11:39:35
DELETE FROM `test`.`user` WHERE `id`=4 AND `name`='zhaoliu' AND `phone`='17756564444' LIMIT 1; #start 7789 end 8136 time 2024-02-26 11:39:35
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (1, 'zhangsan', '17756566565'); #start 9210 end 9497 time 2024-02-26 10:55:04
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (2, 'lisi', '17712345687'); #start 9528 end 9807 time 2024-02-26 10:55:04
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (3, 'wangwu', '17756562333'); #start 9838 end 10119 time 2024-02-26 10:55:04
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (4, 'zhaoliu', '17756564444'); #start 10150 end 10432 time 2024-02-26 10:55:04

从以上内容能看出来我们刚才所执行的语句，该命令只是将二进制文件解析成我们可读的sql文件。

常用参数解释：

-h 主机
-P 端口 
-u 用户名
-p 密码
-d 指定库名
-t 指定表--start-file   起始解析文件，只需文件名，无需全路径
--start-datetime 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
--stop-datetime 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。--only-dml 只解析dml，忽略ddl。可选。默认False。
--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

其他参数大家可以自己网上搜索

3.5 删除数据

  由于我们刚才重启了mysql服务，所以当前的binlog日志文件变了
在这里插入图片描述
  现在日志文件是MYSQL-bin.000044

  我们删除数据(这块只删除user表)

delete from user;

  查看日志文件，会多一条delete事件

在这里插入图片描述

3.6 回滚数据

  添加参数 --flashback，生成rollback语句

# 这是没有--flashback 的执行结果，将delete from 语句打印出来
D:\Settings\Python\Python311\Lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 1")result = self._query(query)
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='zhangsan' AND `phone`='17756566565' LIMIT 1; #start 4 end 504 time 2024-02-26 14:22:35
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='lisi' AND `phone`='17712345687' LIMIT 1; #start 4 end 504 time 2024-02-26 14:22:35
DELETE FROM `test`.`user` WHERE `id`=3 AND `name`='wangwu' AND `phone`='17756562333' LIMIT 1; #start 4 end 504 time 2024-02-26 14:22:35
DELETE FROM `test`.`user` WHERE `id`=4 AND `name`='zhaoliu' AND `phone`='17756564444' LIMIT 1; #start 4 end 504 time 2024-02-26 14:22:35# 这是添加--flashback 的执行结果，将delete from 语句转换成了insert into 语句 打印出来
D:\Workspace\PyCharm\binlog2sql\binlog2sql>python binlog2sql.py -uroot -p123456 -P3306 -d test -t user --start-file=MYSQL-bin.000044 --flashback
D:\Settings\Python\Python311\Lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 1")result = self._query(query)
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (4, 'zhaoliu', '17756564444'); #start 4 end 504 time 2024-02-26 14:22:35
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (3, 'wangwu', '17756562333'); #start 4 end 504 time 2024-02-26 14:22:35
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (2, 'lisi', '17712345687'); #start 4 end 504 time 2024-02-26 14:22:35
INSERT INTO `test`.`user`(`id`, `name`, `phone`) VALUES (1, 'zhangsan', '17756566565'); #start 4 end 504 time 2024-02-26 14:22:35

  我们复制insert into 语句执行就可以了！！！

这篇关于【BUG 记录】史诗级 BUG - MYSQL 删库删表却没有备份如何恢复数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


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

相关文章

大模型研发全揭秘：客服工单数据标注的完整攻略

大模型研发全揭秘：客服工单数据标注的完整攻略

在人工智能（AI）领域，数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者，掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中，工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注，不仅能够帮助提升客服自动化系统的智能化水平，还能优化客户服务流程，提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
关于数据埋点，你需要了解这些基本知识

关于数据埋点，你需要了解这些基本知识

产品汪每天都在和数据打交道，你知道数据来自哪里吗？ 移动app端内的用户行为数据大多来自埋点，了解一些埋点知识，能和数据分析师、技术侃大山，参与到前期的数据采集，更重要是让最终的埋点数据能为我所用，否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式，可以区分为： 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理：自动程度高的，能解决通用统计，便于统一化管理，但个性化定
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
豆包 MarsCode 不允许你还没有女朋友

豆包 MarsCode 不允许你还没有女朋友

在这个喧嚣的世界里，爱意需要被温柔地唤醒。为心爱的她制作每日一句小工具，就像是一场永不落幕的浪漫仪式，每天都在她的心田播撒爱的种子，让她的每一天都充满甜蜜与期待。 背景 在这个瞬息万变的时代，我们都在寻找那些能让我们慢下来，感受生活美好的瞬间。为了让这份浪漫持久而深刻，我们决定为女朋友定制一个每日一句小工具。这个工具会在她意想不到的时刻，为她呈现一句充满爱意的话语，让她的每一天都充满惊喜和感动
阅读更多...
使用SecondaryNameNode恢复NameNode的数据

使用SecondaryNameNode恢复NameNode的数据

1）需求： NameNode进程挂了并且存储的数据也丢失了，如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2）故障模拟 （1）kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 （2）删除NameNode存储的数据（/opt/module/hadoop-3.1.4/data/tmp/dfs/na
阅读更多...
异构存储（冷热数据分离）

异构存储（冷热数据分离）

异构存储主要解决不同的数据，存储在不同类型的硬盘中，达到最佳性能的问题。 异构存储Shell操作 （1）查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies （2）为指定路径（数据存储目录）设置指定的存储策略 hdfs storagepolicies -setStoragePo
阅读更多...
Hadoop集群数据均衡之磁盘间数据均衡

Hadoop集群数据均衡之磁盘间数据均衡

生产环境，由于硬盘空间不足，往往需要增加一块硬盘。刚加载的硬盘没有数据时，可以执行磁盘数据均衡命令。（Hadoop3.x新特性） plan后面带的节点的名字必须是已经存在的，并且是需要均衡的节点。 如果节点不存在，会报如下错误： 如果节点只有一个硬盘的话，不会创建均衡计划： （1）生成均衡计划 hdfs diskbalancer -plan hadoop102 （2）执行均衡计划 hd
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2