AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL的delete误操作的快速恢复方法

2018-11-04 14614
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介： 如果我们在数据库中不小心执行了类似“delete from t1”这样的不带where条件的语句，那么整张表的数据就全被删除了，如何在最短的时间恢复被删除的数据就显得十分关键。下面来演示如何通过binlog来快速恢复表数据。

如果我们在数据库中不小心执行了类似“delete from t1”这样的不带where条件的语句，那么整张表的数据就全被删除了，如何在最短的时间恢复被删除的数据就显得十分关键。下面来演示如何通过binlog来快速恢复表数据。
一、误删除表数据
一张person表的结构和数据如下，不小心误删除了整张表的数据：
image

二、根据误操作时间定位binlog位置
找到数据库的binlog存放位置，当前正在使用的binlog文件里面就有我们要恢复的数据。一般生产环境中的binlog文件都是几百M乃至上G的大小，我们不能逐行去找被删除的数据在什么位置，所以记住误操作的时间很重要，我们可以通过mysqlbinlog命令的--start-datetime参数快速定位数据位置。上图的误操作时间为20181104151800，解析出的binlog内容如下：

[root@cos7-jiang mysql]# mysqlbinlog -vv --start-datetime='2018-11-04 15:18:00' on.000004|more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#700101  8:00:00 server id 1  end_log_pos 120 CRC32 0x199f2da4     Start: binlog v 4, server v 5.6.42-log created 700101  8:00:
00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
AAAAAA8BAAAAdAAAAHgAAAABAAQANS42LjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaQt
nxk=
'/*!*/;
# at 120
#181104 15:18:37 server id 1  end_log_pos 192 CRC32 0x2224f8de     Query    thread_id=16    exec_time=0    error_code=0
SET TIMESTAMP=1541315917/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 192
#181104 15:18:37 server id 1  end_log_pos 252 CRC32 0x65fbbe3b     Table_map: `test`.`person` mapped to number 85
# at 252
#181104 15:18:37 server id 1  end_log_pos 435 CRC32 0x005d1b39     Delete_rows: table id 85 flags: STMT_END_F

BINLOG '
TZ3eWxMBAAAAPAAAAPwAAAAAAFUAAAAAAAEABHRlc3QABnBlcnNvbgAGAw/+DwMDBgoA/gEKAD47
vvtl
TZ3eWyABAAAAtwAAALMBAAAAAFUAAAAAAAEAAgAG/8ABAAAAAmppATEDd2VyqEA0AGpqyADAAgAA
AANsdW8BMQN3ZXKcQjQAJ2zZAMADAAAAAnl1ATAEamVndfombwA3JyEBwAQAAAACZGEBMAdiZWlq
aW5nOgZQAFfZQADABQAAAAJnagEwB2JlaWppbmcSBlAAh+jOAMAGAAAABHlhbmcBMAduYW5qaW5n
cg5MAIdSqwo5G10A
'/*!*/;
### DELETE FROM `test`.`person`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ji' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @3='1' /* STRING(1) meta=65025 nullable=1 is_null=0 */
###   @4='wer' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @5=3424424 /* INT meta=0 nullable=1 is_null=0 */
###   @6=13134442 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='luo' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @3='1' /* STRING(1) meta=65025 nullable=1 is_null=0 */
###   @4='wer' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @5=3424924 /* INT meta=0 nullable=1 is_null=0 */
###   @6=14248999 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='yu' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
###   @4='jegu' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @5=7284474 /* INT meta=0 nullable=1 is_null=0 */
###   @6=18949943 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='da' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
###   @4='beijing' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @5=5244474 /* INT meta=0 nullable=1 is_null=0 */
###   @6=4249943 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gj' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
###   @4='beijing' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @5=5244434 /* INT meta=0 nullable=1 is_null=0 */
###   @6=13559943 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='yang' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
###   @4='nanjing' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
###   @5=4984434 /* INT meta=0 nullable=1 is_null=0 */
###   @6=178999943 /* INT meta=0 nullable=1 is_null=0 */
# at 435
#181104 15:18:37 server id 1  end_log_pos 466 CRC32 0xab85d971     Xid = 197
COMMIT/*!*/;
# at 466
#181104 15:19:38 server id 1  end_log_pos 538 CRC32 0x8b9e1093     Query    thread_id=16    exec_time=0    error_code=0
SET TIMESTAMP=1541315978/*!*/;
BEGIN
/*!*/;
# at 538
#181104 15:19:38 server id 1  end_log_pos 598 CRC32 0xb1679f78     Table_map: `test`.`person` mapped to number 85
# at 598
#181104 15:19:38 server id 1  end_log_pos 656 CRC32 0xa5d7a2d6     Write_rows: table id 85 flags: STMT_END_F

通过上面的命令可以比较方便地逐页寻找被删除的数据，我们应该要找到被删除的数据在binlog中的起始和终止位置点，例如上面的被删除数据的位置点在192和435之间，这样我们可以保证在这两个位置之间只有我们需要的待恢复的数据，而没有其他数据。
本次测试的数据量很小，如果一张表有数十万行数据被误删除，我们通过上面的方式找位置点是很费时间的。这时可以通过下面的两个命令锁定起始和终止位置：
确定起始位置点：

mysqlbinlog -vv --start-datetime='2018-11-04 15:18:00' on.000004| head -1000 |more

确定终止位置点：

mysqlbinlog -vv --start-datetime='2018-11-04 15:18:00' --stop-datetime='2018-11-04 15:20:00' on.000004| tail -1000 |more

我们只看binlog输出的前N行和后N行，来找到起始和终止位置，这样可以大大节省时间。
三、将binlog里的delete语句转化为insert语句
Binlog是二进制文件，我们可以先把待恢复数据导出为可阅读文本：

mysqlbinlog -vv --start-position=192 --stop-position=435 on.000004 |grep ^"###" >/tmp/bin_data

接下来就是处理/tmp/bin_data文本，将里面的delete语句转化为insert语句，可以通过下面的语句实现转化：

cat /tmp/bin_data | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@6.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' >/tmp/person.sql

_1

四、将insert语句导入数据库中
Delete语句误操作只会删除表数据，而表结构还在。所以我们可以直接将文本里的insert语句导入到数据库中，即可完成数据恢复：

mysql -h127.0.0.1 -P3306 -uroot -p123 < /tmp/person.sql

补充和总结：
1、以上操作只针对delete误操作有效，且binlog模式是行模式；如果是drop或者truncate语句造成的误操作，亦或者binlog不是row模式，在binlog文件里是找不到完整的被删除数据，这个时候可以考虑通过备份进行恢复；
2、如果在误操作很久之后才意识到数据被误删除，记不清误操作的大致时间，那么可以找到误操作所在的binlog文件，将binlog解析为可阅读文本形式，然后借助文本编辑命令找误操作位置，再恢复；如果这种方式很慢的话，可以考虑通过备份恢复，或者从别的环境中导出这张表的数据再导入到当前环境中；
3、Mysqlbinlog命令重要参数
-vv 将二进制转换为可阅读文本
--start-datetime 起始时间
--stop-datetime 终止时间
--start-position 起始位置
--stop-position 终止位置
--base64-output=decode-row 查看最底层DML语句数据模块，前提是数据库参数binlog_rows_query_log_events打开
4、熟悉linux的文本编辑命令，如grep、sed、awk等
5、虽然binlog2sql工具也可以快速解析binlog，生成回滚SQL，但只要清楚上面的命令和操作流程，恢复速度也不会比binlog2sql慢很多，更何况如果环境没有安装这个工具。

文章标签：
云数据库 RDS MySQL 版
关系型数据库
数据库
Perl
MySQL
SQL
Linux
关键词：
云数据库 RDS MySQL 版方法
云数据库 RDS MySQL 版delete
云数据库 RDS MySQL 版误操作
云数据库 RDS MySQL 版恢复
云数据库 RDS MySQL 版恢复方法
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
未央&amp;
目录
相关文章
yuanzhengme
|
6月前
|
关系型数据库 MySQL 索引
MySQL的全文索引查询方法
【8月更文挑战第26天】MySQL的全文索引查询方法
yuanzhengme
90 0
小Tomkk
|
4月前
|
存储 关系型数据库 MySQL
环比、环比增长率、同比、同比增长率 ，占比，Mysql 8.0 实例（最简单的方法之一)（sample database classicmodels _No.2 ）
环比、环比增长率、同比、同比增长率 ，占比，Mysql 8.0 实例（最简单的方法之一)（sample database classicmodels _No.2 ）
小Tomkk
245 1
sunrr
|
4月前
|
存储 关系型数据库 MySQL
提高MySQL查询性能的方法有很多
提高MySQL查询性能的方法有很多
sunrr
348 7
愿天堂没有BUG（公众号同名）
|
7天前
|
SQL 关系型数据库 MySQL
Python中使用MySQL模糊查询的方法
本文介绍了两种使用Python进行MySQL模糊查询的方法：一是使用`pymysql`库，二是使用`mysql-connector-python`库。通过这两种方法，可以连接MySQL数据库并执行模糊查询。具体步骤包括安装库、配置数据库连接参数、编写SQL查询语句以及处理查询结果。文中详细展示了代码示例，并提供了注意事项，如替换数据库连接信息、正确使用通配符和关闭数据库连接等。确保在实际应用中注意SQL注入风险，使用参数化查询以保障安全性。
愿天堂没有BUG（公众号同名）
16 0
小王老师呀
|
6月前
|
存储 关系型数据库 MySQL
mysql数据库查询时用到的分页方法有哪些
【8月更文挑战第16天】在MySQL中，实现分页的主要方法包括：1）使用`LIMIT`子句，简单直接但随页数增加性能下降；2）通过子查询优化`LIMIT`分页，提高大页码时的查询效率；3）利用存储过程封装分页逻辑，便于复用但需额外维护；4）借助MySQL变量实现，可能提供更好的性能但实现较复杂。这些方法各有优缺点，可根据实际需求选择适用方案。
小王老师呀
645 2
蓝易云
|
2月前
|
SQL 存储 关系型数据库
MySQL/SqlServer跨服务器增删改查（CRUD）的一种方法
通过上述方法，MySQL和SQL Server均能够实现跨服务器的增删改查操作。MySQL通过联邦存储引擎提供了直接的跨服务器表访问，而SQL Server通过链接服务器和分布式查询实现了灵活的跨服务器数据操作。这些技术为分布式数据库管理提供了强大的支持，能够满足复杂的数据操作需求。
蓝易云
115 12
蓝易云
|
2月前
|
存储 缓存 关系型数据库
MySQL的count()方法慢
MySQL的 `COUNT()`方法在处理大数据量时可能会变慢，主要原因包括数据量大、缺乏合适的索引、InnoDB引擎的设计以及复杂的查询条件。通过创建合适的索引、使用覆盖索引、缓存机制、分区表和预计算等优化方案，可以显著提高 `COUNT()`方法的执行效率，确保数据库查询性能的提升。
蓝易云
789 12
2G冲浪词条
|
3月前
|
关系型数据库 MySQL
Mysql 中日期比较大小的方法有哪些？
在 MySQL 中，可以通过多种方法比较日期的大小，包括使用比较运算符、NOW() 函数、DATEDIFF 函数和 DATE 函数。这些方法可以帮助你筛选出特定日期范围内的记录，确保日期格式一致以避免错误。
2G冲浪词条
127 1
蓝易云
|
5月前
|
关系型数据库 MySQL 数据库
Python MySQL查询返回字典类型数据的方法
通过使用 `mysql-connector-python`库并选择 `MySQLCursorDict`作为游标类型，您可以轻松地将MySQL查询结果以字典类型返回。这种方式提高了代码的可读性，使得数据操作更加直观和方便。上述步骤和示例代码展示了如何实现这一功能，希望对您的项目开发有所帮助。
蓝易云
209 4
蓝易云
|
5月前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务，需要仔细地比较和转换数据类型。通过上述方法，可以在两个数据库系统之间建立起相互兼容的数据结构，为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应，还要注意特定数据类型在不同系统中的表现差异，确保数据的一致性和完整性。
蓝易云
64 4

热门文章

最新文章

  • 1
    美团面试：MySQL为什么 不用 Docker部署？
  • 2
    MySQL底层概述—2.InnoDB磁盘结构
  • 3
    MySQL底层概述—1.InnoDB内存结构
  • 4
    MySQL原理简介—1.SQL的执行流程
  • 5
    SpringBoot 通过集成 Flink CDC 来实时追踪 MySql 数据变动
  • 6
    嵌入式C++、STM32、MySQL、GPS、InfluxDB和MQTT协议数据可视化
  • 7
    如何实现 MySQL 的读写分离？
  • 8
    MySQL原理简介—3.生产环境的部署压测
  • 9
    【Azure Logic App】使用MySQL 新增行触发器遇见错误 ：“Unknown column 'created_at' in 'order clause'”
  • 10
    Docker Compose V2 安装常用数据库MySQL+Mongo
  • 1
    如何解决 MySQL 数据库服务器 CPU 飙升的情况
    58
  • 2
    join查询可以⽆限叠加吗？MySQL对join查询有什么限制吗？
    41
  • 3
    MySQL日志
    15
  • 4
    图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
    17
  • 5
    图解MySQL【日志】——两阶段提交
    18
  • 6
    图解MySQL【日志】——Redo Log
    18
  • 7
    MySQL和SQLSugar百万条数据查询分页优化
    35
  • 8
    【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
    15
  • 9
    【YashanDB 知识库】如何将 mysql 含有 group by 的 SQL 转换成崖山支持的 SQL
    19
  • 10
    数据库数据恢复——MySQL简介和数据恢复案例
    17

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    阿里云oss简介和如何对接使用