MySQL误删恢复方法2

2022-11-14 150

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，集群版 2核4GB 100GB

云数据库 RDS MySQL，高可用版 2核4GB 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