MySQL误删恢复方法2

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 实际工作中总会发生数据误删除的场景,在没有备份情况下,如何快速恢复误删数据就显得非常重要。本文基于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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
关系型数据库 MySQL
解决MySQL无法插入中文的方法
解决MySQL无法插入中文的方法
15 0
|
1月前
|
设计模式 NoSQL Java
常用的设计模式以及操作Redis、MySQL数据库、各种MQ、数据类型转换的方法
常用的设计模式以及操作Redis、MySQL数据库、各种MQ、数据类型转换的方法
|
3月前
|
存储 Oracle 关系型数据库
mysql存储过程调试方法
mysql存储过程调试方法
213 0
|
4月前
|
缓存 关系型数据库 MySQL
MySQL索引原理与实践:优化数据库性能的有效方法3.0
全文索引,主键索引,唯一索引,覆盖索引,组合索引,普通索引,外键索引,空间索引,前缀索引,哈希索引等 在接下来MySQL索引原理与实践3.0中我会重点介绍mysql索引优化等一些方面相关的理论与实践,有小伙伴是从3.0开始看的,可以优先看一下1.0/2.0 http://t.csdnimg.cn/hHn9A
47 0
|
4月前
|
缓存 关系型数据库 MySQL
Django操作MySQL数据库的优化方法
Django操作MySQL数据库的优化方法
|
4月前
|
搜索推荐 算法 关系型数据库
【MYSQL高级】Mysql查询语句优化方法
【MYSQL高级】Mysql查询语句优化方法
40 1
|
22天前
|
存储 监控 关系型数据库
mysql 主备延迟的原因及解决思路,优化方法
MySQL 主备延迟(replication delay)是指主服务器(master)上的数据更新操作与备服务器(slave)上相同数据更新操作之间的时间差。这种延迟可能会导致数据不一致,影响系统的可用性和可靠性。以下是主备延迟的常见原因、解决思路和优化方法: ### 常见原因 1. **网络延迟**:主备服务器之间的网络不稳定或带宽不足。 2. **硬件性能**:备服务器的硬件性能不足,如 CPU、内存、磁盘 I/O 等。 3. **大量数据写入**:主服务器上的大量数据写入操作导致备服务器难以同步。 4. **复杂的查询**:备服务器执行复杂的查询操作,影响同步速度。 5. **二进制
|
4月前
|
关系型数据库 MySQL API
flinkcdc不做任何处理,直接mysql同步到 mysql 的过程中 sink 使用 哪个方法?
flinkcdc不做任何处理,直接mysql同步到 mysql 的过程中 sink 使用 哪个方法?
109 1
|
1月前
|
SQL 关系型数据库 MySQL
MySQL优化:12种提升SQL执行效率的有效方法
在数据库管理和优化的世界里,MySQL作为一个流行的关系型数据库管理系统,其性能优化是任何数据密集型应用成功的关键。优化MySQL数据库不仅可以显著提高SQL查询的效率,还能确保数据的稳定性和可靠性。
|
2月前
|
SQL 关系型数据库 MySQL
【SQL编程】Greenplum 与 MySQL 数据库获取周几函数及函数结果保持一致的方法
【SQL编程】Greenplum 与 MySQL 数据库获取周几函数及函数结果保持一致的方法
21 0