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

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

2018-11-04 14802
版权
举报
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 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

        

          
        

        
        

          

          AI 代码解读

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

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

        

          
        

        
        

          

          AI 代码解读

确定终止位置点：

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

        

          
        

        
        

          

          AI 代码解读

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

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

        

          
        

        
        

          

          AI 代码解读

接下来就是处理/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

        

          
        

        
        

          

          AI 代码解读

_1

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

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

        

          
        

        
        

          

          AI 代码解读

补充和总结：
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;
+关注
26文章
目录
打赏
0
1
0
1
32412
分享
相关文章
yuanzhengme
|
8月前
|
关系型数据库 MySQL 索引
MySQL的全文索引查询方法
【8月更文挑战第26天】MySQL的全文索引查询方法
yuanzhengme
110 0
游客kufrkwrbkmpsa
|
3天前
|
SQL 存储 关系型数据库
【YashanDB知识库】共享从 MySQL异常处理CONTINUE HANDLER的改写方法
【YashanDB知识库】共享从 MySQL异常处理CONTINUE HANDLER的改写方法
游客kufrkwrbkmpsa
17 2
蓝易云
|
3天前
|
存储 关系型数据库 MySQL
MySQL细节优化：关闭大小写敏感功能的方法。
通过这种方法，你就可以成功关闭 MySQL 的大小写敏感功能，让你的数据库操作更加便捷。
蓝易云
42 19
游客kufrkwrbkmpsa
|
3天前
|
SQL 关系型数据库 MySQL
【YashanDB知识库】MySQL field 函数的改写方法
【YashanDB知识库】MySQL field 函数的改写方法
游客kufrkwrbkmpsa
16 3
游客kufrkwrbkmpsa
|
6天前
|
存储 关系型数据库 MySQL
【YashanDB知识库】共享从 MySQL异常处理CONTINUE HANDLER的改写方法
本文介绍了MySQL中`CONTINUE HANDLER FOR NOT FOUND`的用法及其在YashanDB中的改写方法。通过一个示例存储过程，展示了如何使用游标和异常处理机制来应对“未找到数据”的情况。在MySQL中，`CONTINUE HANDLER`用于捕获此类异常；而在YashanDB中，则需改用游标的`%NOTFOUND`属性和`NO_DATA_FOUND`异常处理。文章对比了两者的执行效果，帮助用户顺利完成从MySQL到YashanDB的业务迁移。
游客kufrkwrbkmpsa
23 2
游客kufrkwrbkmpsa
|
6天前
|
SQL 关系型数据库 MySQL
【YashanDB知识库】MySQL field 函数的改写方法
本文来自YashanDB官网，介绍将MySQL的FIELD函数改写到YashanDB的方法。MySQL中，FIELD函数用于自定义排序；而在YashanDB中，可使用DECODE或CASE语句实现类似功能。示例展示对表`t1`按指定顺序排序的过程，提供两种改写方式，结果均符合预期。
游客kufrkwrbkmpsa
21 0
游客kufrkwrbkmpsa
|
6天前
|
存储 关系型数据库 MySQL
【YashanDB知识库】MySQL返回结果集的存储过程的改写方法
本文介绍了将MySQL存储过程改写至YashanDB的解决方案。由于MySQL存储过程可直接返回结果集，而YashanDB需通过返回SYS_REF_CURSOR的函数实现类似功能，因此需要对代码进行转换。示例中展示了如何将MySQL存储过程`proc1`改写为YashanDB函数，并调整JDBC应用代码以适配REF_CURSOR输出参数，从而正确获取查询结果。此方法确保了跨数据库场景下的兼容性与功能性。
游客kufrkwrbkmpsa
19 0
小Tomkk
|
6月前
|
存储 关系型数据库 MySQL
环比、环比增长率、同比、同比增长率 ，占比，Mysql 8.0 实例（最简单的方法之一)（sample database classicmodels _No.2 ）
环比、环比增长率、同比、同比增长率 ，占比，Mysql 8.0 实例（最简单的方法之一)（sample database classicmodels _No.2 ）
小Tomkk
303 1
sunrr
|
6月前
|
存储 关系型数据库 MySQL
提高MySQL查询性能的方法有很多
提高MySQL查询性能的方法有很多
sunrr
372 7
愿天堂没有BUG（公众号同名）
|
2月前
|
SQL 关系型数据库 MySQL
Python中使用MySQL模糊查询的方法
本文介绍了两种使用Python进行MySQL模糊查询的方法：一是使用`pymysql`库，二是使用`mysql-connector-python`库。通过这两种方法，可以连接MySQL数据库并执行模糊查询。具体步骤包括安装库、配置数据库连接参数、编写SQL查询语句以及处理查询结果。文中详细展示了代码示例，并提供了注意事项，如替换数据库连接信息、正确使用通配符和关闭数据库连接等。确保在实际应用中注意SQL注入风险，使用参数化查询以保障安全性。
愿天堂没有BUG（公众号同名）
33 0

热门文章

最新文章

  • 1
    Canal是怎么伪装成 MySQL slave？
    17
  • 2
    ELK实现nginx、mysql、http的日志可视化实验
    18
  • 3
    无缝集成 MySQL，解锁秒级 OLAP 分析性能极限，完成任务可领取三合一数据线！
    10
  • 4
    docker拉取MySQL后数据库连接失败解决方案
    20
  • 5
    mysql注入-字符编码技巧
    15
  • 6
    seatunnel配置mysql2hive
    16
  • 7
    缓存与数据库的一致性方案，Redis与Mysql一致性方案，大厂P8的终极方案（图解+秒懂+史上最全）
    24
  • 8
    MySQL8官方YUM仓库使用指南
    19
  • 9
    简单聊聊MySQL的三大日志（Redo Log、Binlog和Undo Log）各有什么区别
    15
  • 10
    Dataphin功能Tips系列（50）-离线集成输入端支持mysql数据源分库分表
    16
  • 1
    容器技术实践：在Ubuntu上使用Docker安装MySQL的步骤。
    31
  • 2
    MySQL细节优化：关闭大小写敏感功能的方法。
    42
  • 3
    【YashanDB知识库】MySQL field 函数的改写方法
    16
  • 4
    【YashanDB知识库】共享从 MySQL异常处理CONTINUE HANDLER的改写方法
    17
  • 5
    【YashanDB 知识库】YashanDB 支持 MySQL 多表更新语句的解决方法
    12
  • 6
    【YashanDB 知识库】MySQL 和 YashanDB 隐式转换不一致引起的报错
    8
  • 7
    去哪面试：1Wtps高并发，MySQL 热点行 问题， 怎么解决？
    42
  • 8
    【YashanDB知识库】YashanDB 支持MySQL多表更新语句的解决方法
    15
  • 9
    【YashanDB知识库】MySQL和YashanDB 隐式转换不一致引起的报错
    31
  • 10
    【YashanDB知识库】MySQL field 函数的改写方法
    21

    • 相关课程

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

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    基于ECS搭建云上博客
    AI助理

    你好，我是AI助理

    可以解答问题、推荐解决方案等