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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 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慢很多,更何况如果环境没有安装这个工具。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
关系型数据库 MySQL 索引
MySQL的全文索引查询方法
【8月更文挑战第26天】MySQL的全文索引查询方法
42 0
|
7天前
|
存储 关系型数据库 MySQL
提高MySQL查询性能的方法有很多
提高MySQL查询性能的方法有很多
50 6
|
2月前
|
存储 关系型数据库 MySQL
mysql数据库查询时用到的分页方法有哪些
【8月更文挑战第16天】在MySQL中,实现分页的主要方法包括:1)使用`LIMIT`子句,简单直接但随页数增加性能下降;2)通过子查询优化`LIMIT`分页,提高大页码时的查询效率;3)利用存储过程封装分页逻辑,便于复用但需额外维护;4)借助MySQL变量实现,可能提供更好的性能但实现较复杂。这些方法各有优缺点,可根据实际需求选择适用方案。
204 2
|
18天前
|
关系型数据库 MySQL 数据库
Python MySQL查询返回字典类型数据的方法
通过使用 `mysql-connector-python`库并选择 `MySQLCursorDict`作为游标类型,您可以轻松地将MySQL查询结果以字典类型返回。这种方式提高了代码的可读性,使得数据操作更加直观和方便。上述步骤和示例代码展示了如何实现这一功能,希望对您的项目开发有所帮助。
58 4
|
18天前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务,需要仔细地比较和转换数据类型。通过上述方法,可以在两个数据库系统之间建立起相互兼容的数据结构,为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应,还要注意特定数据类型在不同系统中的表现差异,确保数据的一致性和完整性。
26 4
|
18天前
|
关系型数据库 MySQL Unix
MySQL配置不区分大小写的方法
结论 通过适当配置 lower_case_table_names参数以及在数据定义和查询中选择合适的校对规则,可以灵活地控制MySQL中的大小写敏感性,以适应不同的应用场景和需求。这样的设置既可以增加数据库的兼容性,又可以在必要时利用大小写敏感性进行精确的数据处理。需要注意的是,修改 lower_case_table_names参数后,最好在数据库初始化时进行,以避免现有表名的大小写问题。
37 3
|
26天前
|
存储 关系型数据库 MySQL
技术解析:MySQL中取最新一条重复数据的方法
以上提供的两种方法都可以有效地从MySQL数据库中提取每个类别最新的重复数据。选择哪种方法取决于具体的使用场景和MySQL版本。子查询加分组的方法兼容性更好,适用于所有版本的MySQL;而窗口函数方法代码更简洁,执行效率可能更高,但需要MySQL 8.0及以上版本。在实际应用中,应根据数据量大小、查询性能需求以及MySQL版本等因素综合考虑,选择最合适的实现方案。
131 6
|
25天前
|
存储 缓存 关系型数据库
MySQL 查询优化方法
在数据库应用中,高效的查询性能至关重要。本文探讨了常用的 MySQL 查询优化方法,包括索引优化(选择合适的索引字段、复合索引、定期维护索引)、查询语句优化(避免全表扫描、限制返回行数、避免使用不必要的函数)、表结构优化(选择合适的数据类型、分区表、定期清理无用数据)及数据库配置优化(调整缓存大小、优化存储引擎参数)。通过这些方法,可以显著提高 MySQL 的查询性能,为应用程序提供更好的用户体验。
|
1月前
|
SQL 关系型数据库 MySQL
学习MySQL操作的有效方法
学习MySQL操作的有效方法
37 3
|
2月前
|
关系型数据库 MySQL Shell
MySQL回滚脚本: 误操作delete binlog回滚shell脚本
MySQL回滚脚本: 误操作delete binlog回滚shell脚本

热门文章

最新文章