MySQL误删恢复方法2

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 实际工作中总会发生数据误删除的场景,在没有备份情况下,如何快速恢复误删数据就显得非常重要。本文基于MySQL的binlog日志机制,当日志格式设置为“binlog_format=ROW”时,记录一步一步手动解析binlog、恢复误删数据的全过程,供大家参考使用。

实际工作中总会发生数据误删除的场景,在没有备份情况下,如何快速恢复误删数据就显得非常重要。
本文基于MySQL的binlog日志机制,当日志格式设置为“binlog_format=ROW”时,记录一步一步手动解析binlog、恢复误删数据的全过程,供大家参考使用。

一、确定删除时间段

查看数据库日志名字,查看操作事件,确认删除的具体时间,以定位删除的日志内容。
show master logs;
show binlog events in 'binlog.000067';
show binlog events in 'binlog.000067' FROM 1293303 LIMIT 0, 20;
通过类似如上的语句可确定具体的日志名字,具体的操作时间点或position

mysql> show binlog events in 'binlog.000002' FROM 2853160 LIMIT 0, 20;
+---------------+---------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos     | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+---------+----------------+-----------+-------------+--------------------------------------+
| binlog.000002 | 2853160 | Query          |         1 |     2853246 | BEGIN                                |
| binlog.000002 | 2853246 | Table_map      |         1 |     2853315 | table_id: 94 (liking.wp_options)     |
| binlog.000002 | 2853315 | Update_rows    |         1 |     2859755 | table_id: 94 flags: STMT_END_F       |
| binlog.000002 | 2859755 | Query          |         1 |     2859842 | COMMIT                               |
| binlog.000002 | 2859842 | Anonymous_Gtid |         1 |     2859921 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 2859921 | Query          |         1 |     2860007 | BEGIN                                |
| binlog.000002 | 2860007 | Table_map      |         1 |     2860076 | table_id: 94 (liking.wp_options)     |
| binlog.000002 | 2860076 | Update_rows    |         1 |     2866516 | table_id: 94 flags: STMT_END_F       |
| binlog.000002 | 2866516 | Query          |         1 |     2866603 | COMMIT                               |
| binlog.000002 | 2866603 | Anonymous_Gtid |         1 |     2866682 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 2866682 | Query          |         1 |     2866759 | BEGIN                                |
| binlog.000002 | 2866759 | Table_map      |         1 |     2866828 | table_id: 94 (liking.wp_options)     |
| binlog.000002 | 2866828 | Delete_rows    |         1 |     2866936 | table_id: 94 flags: STMT_END_F       |
| binlog.000002 | 2866936 | Query          |         1 |     2867014 | COMMIT                               |
| binlog.000002 | 2867014 | Anonymous_Gtid |         1 |     2867093 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 2867093 | Query          |         1 |     2867179 | BEGIN                                |
| binlog.000002 | 2867179 | Table_map      |         1 |     2867247 | table_id: 95 (liking.wp_postmeta)    |
| binlog.000002 | 2867247 | Update_rows    |         1 |     2867347 | table_id: 95 flags: STMT_END_F       |
| binlog.000002 | 2867347 | Query          |         1 |     2867434 | COMMIT                               |
| binlog.000002 | 2867434 | Anonymous_Gtid |         1 |     2867513 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
+---------------+---------+----------------+-----------+-------------+--------------------------------------+

二、取到指定时间段的所有sql语句

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime="2022-10-27 16:54:00" --stop-datetime="2022-10-27 18:27:00" binlog.000288 > liking.sql

三、取delete语句

sed -n '/### DELETE FROM 数据脱敏.数据脱敏数据脱敏/,/COMMIT/p' liking.sql > liking-1.sql
得到类似如下结果:

head -50 liking-1.sql
### DELETE FROM `数据脱敏`.`数据脱敏数据脱敏`
### WHERE
###   @1=222248 /* INT meta=0 nullable=0 is_null=0 */
###   @2='数据脱敏数据脱敏' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
[此处省略第3~45个记录行]
###   @46='数据脱敏' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @47='订单' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @48='数据脱敏' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */

四、只保留以###开始的行数据

sed -n '/^###/'p liking-1.sql > liking-2.sql

五、去掉开头的###字符

sed 's/### //g' liking-2.sql > liking-3.sql
得到类似如下结果:

head -50 liking-3.sql
DELETE FROM `数据脱敏`.`数据脱敏数据脱敏`
WHERE
  @1=222248 /* INT meta=0 nullable=0 is_null=0 */
  @2='数据脱敏数据脱敏' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
[此处省略第3个~第45个字段]
  @46='数据脱敏' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
  @47='订单' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
  @48='数据脱敏' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */

六、以#作分隔符将以/*开始的内容用,替换

sed 's#/*.*#,#g' liking-3.sql > liking-4.sql
得到如下结果:

head -50 liking-4.sql
DELETE FROM `数据脱敏`.`数据脱敏数据脱敏`
WHERE
  @1=222248 ,
  @2='数据脱敏数据脱敏' ,
[此处省略第3个~第45个字段]
  @46='数据脱敏' ,
  @47='订单' ,
  @48='数据脱敏' ,

七、浏览结果做对应处理

鉴于还有一些insert、update语句,只取出前面的delete语句:
sed -n '1,493200p' liking-4.sql > liking-5.sql

八、全文替换关键字,将delete改成insert

将'DELETE FROM'改成'INSERT INTO'
将'WHERE'改成'SELECT'
sed 's#DELETE FROM#INSERT INTO#g' liking-5.sql > liking-6.sql
sed 's#WHERE#SELECT#g' liking-6.sql > liking-7.sql
得到如下结果:

head -50 liking-7.sql
INSERT INTO `数据脱敏`.`数据脱敏数据脱敏数据脱敏`
SELECT
  @1=222248 ,
  @2='数据脱敏数据脱敏' ,
[此处省略第3个~第45个字段]
  @46='数据脱敏' ,
  @47='订单' ,
  @48='数据脱敏' ,

九、匹配'@48='开头以','结尾的行,把','替换成';'

sed -r 's#(@48=.*)(,)#\1;#g' liking-7.sql > liking-8.sql
-r: 是指将正则表达式中的需要转义的字符设置成不需转义
括号用来匹配连续出现的内容
.*标识匹配除换行符\n外的任何单元符
为了把','去掉,单独(,)
\1是用来取()表示符的第一个,并在后面拼接上';'
本次恢复的记录都是48个字段,最后加分号,标识SQL结束,比较容易理解。
得到类似如下结果:

INSERT INTO `数据脱敏`.`数据脱敏数据脱敏数据脱敏`
SELECT
  @1=222248 ,
  @2='数据脱敏数据脱敏' ,
[此处省略第3个~第45个字段]
  @46='数据脱敏' ,
  @47='订单' ,
  @48='数据脱敏' ;

十、把'@??='替换成'',也就是去掉类似'@11='字样部分

sed -r 's#(@.=)(.)#\2#g' liking-8.sql > liking-9.sql
得到类似如下结果:

head -50 liking-9.sql
INSERT INTO `数据脱敏`.`数据脱敏数据脱敏`
SELECT
  222248 ,
  'CMHE-202200393' ,
  '数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏' ,
  4.1772e+07           ,
  '\'8000062202216124' ,
[此处省略第6个~第45个字段]
  '数据脱敏' ,
  '订单' ,
  '马蕾' ;

十一、最后拼接 commit 语句

sed -i '$a commit;' liking-9.sql
查看最后几行确认:

tail -51 liking-9.sql
INSERT INTO `数据脱敏`.`数据脱敏数据脱敏`
SELECT
  251837 ,
  'CMHE-202101003' ,
  '数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏' ,
  5.04e+08             ,
  '\'8000004202200006' ,
[此处省略第6个~第45个字段]
  '数据脱敏' ,
  '订单' ,
  '陈鹏3' ;
commit;

十二、浏览、调整个别异常格式字段

此时需大概浏览得到的文本结果,尤其关注转义符,如有必要,需及时调整。
发现尚存在类似如下字段格式
'\'8000004202200006'
如需去掉\'
则如下操作:
sed "s#\\'##g" liking-9.sql > liking-10.sql
最后确认如下:

tail -51 liking-10.sql
INSERT INTO `数据脱敏`.`数据脱敏数据脱敏`
SELECT
  251837 ,
  'CMHE-202101003' ,
  '数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏' ,
  5.04e+08             ,
  '8000004202200006' ,
[此处省略第6个~第45个字段]
  '数据脱敏' ,
  '订单' ,
  '陈鹏3' ;
commit;

十三、执行恢复数据

经过审视、调整后,此时得到的结果,已经可以正常执行INSERT操作恢复数据了!

写在最后,再次强调,备份是运维第一要务,备份的重要性再怎么强调也不为过,除非你可以承受数据丢失带来的后果。
数据千万行,备份最重要;数据不备份,丢失泪两行!

参考文档:
https://blog.csdn.net/qq_37701372/article/details/115188461

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4月前
|
关系型数据库 MySQL 索引
MySQL的全文索引查询方法
【8月更文挑战第26天】MySQL的全文索引查询方法
53 0
|
2月前
|
存储 关系型数据库 MySQL
环比、环比增长率、同比、同比增长率 ,占比,Mysql 8.0 实例(最简单的方法之一)(sample database classicmodels _No.2 )
环比、环比增长率、同比、同比增长率 ,占比,Mysql 8.0 实例(最简单的方法之一)(sample database classicmodels _No.2 )
115 1
|
2月前
|
存储 关系型数据库 MySQL
提高MySQL查询性能的方法有很多
提高MySQL查询性能的方法有很多
168 7
|
4月前
|
存储 关系型数据库 MySQL
mysql数据库查询时用到的分页方法有哪些
【8月更文挑战第16天】在MySQL中,实现分页的主要方法包括:1)使用`LIMIT`子句,简单直接但随页数增加性能下降;2)通过子查询优化`LIMIT`分页,提高大页码时的查询效率;3)利用存储过程封装分页逻辑,便于复用但需额外维护;4)借助MySQL变量实现,可能提供更好的性能但实现较复杂。这些方法各有优缺点,可根据实际需求选择适用方案。
393 2
|
1月前
|
关系型数据库 MySQL
Mysql 中日期比较大小的方法有哪些?
在 MySQL 中,可以通过多种方法比较日期的大小,包括使用比较运算符、NOW() 函数、DATEDIFF 函数和 DATE 函数。这些方法可以帮助你筛选出特定日期范围内的记录,确保日期格式一致以避免错误。
|
1月前
|
SQL 关系型数据库 MySQL
mysql数据误删后的数据回滚
【11月更文挑战第1天】本文介绍了四种恢复误删数据的方法:1. 使用事务回滚,通过 `pymysql` 库在 Python 中实现;2. 使用备份恢复,通过 `mysqldump` 命令备份和恢复数据;3. 使用二进制日志恢复,通过 `mysqlbinlog` 工具恢复特定位置的事件;4. 使用延迟复制从副本恢复,通过停止和重启从库复制来恢复数据。每种方法都有详细的步骤和示例代码。
188 2
|
2月前
|
SQL 关系型数据库 MySQL
MySQL误删怎么办?
本文详细介绍了如何通过MySQL的binlog日志恢复被误删除的数据。首先,确保binlog功能已开启并确认其模式(row模式更佳)。然后,通过查询binlog文件找到删除操作的具体位置。提供了两种恢复方案:一是直接找到并重放insert语句;二是通过逆向操作重新插入数据。文章还推荐了binlog2mysql等工具简化这一过程,帮助自动生成所需SQL语句。适合数据库管理员和开发者参考。
|
3月前
|
关系型数据库 MySQL 数据库
Python MySQL查询返回字典类型数据的方法
通过使用 `mysql-connector-python`库并选择 `MySQLCursorDict`作为游标类型,您可以轻松地将MySQL查询结果以字典类型返回。这种方式提高了代码的可读性,使得数据操作更加直观和方便。上述步骤和示例代码展示了如何实现这一功能,希望对您的项目开发有所帮助。
149 4
|
3月前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务,需要仔细地比较和转换数据类型。通过上述方法,可以在两个数据库系统之间建立起相互兼容的数据结构,为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应,还要注意特定数据类型在不同系统中的表现差异,确保数据的一致性和完整性。
36 4
|
3月前
|
关系型数据库 MySQL Unix
MySQL配置不区分大小写的方法
结论 通过适当配置 lower_case_table_names参数以及在数据定义和查询中选择合适的校对规则,可以灵活地控制MySQL中的大小写敏感性,以适应不同的应用场景和需求。这样的设置既可以增加数据库的兼容性,又可以在必要时利用大小写敏感性进行精确的数据处理。需要注意的是,修改 lower_case_table_names参数后,最好在数据库初始化时进行,以避免现有表名的大小写问题。
345 3