侧边栏壁纸
博主头像
枕头下放双臭袜子博主等级

今我何功德,曾不事农桑

  • 累计撰写 166 篇文章
  • 累计创建 32 个标签
  • 累计收到 0 条评论

阿里云MYSQL RDS恢复误删数据

枕头下放双臭袜子
2022-08-16 / 0 评论 / 0 点赞 / 44 阅读 / 2,711 字 / 正在检测是否收录...

0、事发当时

我司java研发说,测试服RDS数据库mysql某张表不小心被删掉了,因为我们小团队没有DBA,所以研发找到我看看能不能一块恢复下数据。所以在这里分享下,一般情况下mysql数据被删掉的恢复过程(非生产环境主从集群各种复制的模式)

1、恢复思路

大体上恢复数据的思路是:首先确认能否从已备份文件中恢复被误删的数据,如果没有数据备份或者还处于数据备份周期的间隔,就要从binlog日志入手恢复被误删的数据了。

情况①: 有数据库数据备份(并且备份文件产生后该数据并未发生变化),也知道被误删的数据具体是什么,比如说某张表中主键ID是66的数据。这种情况就可以直接把备份的数据取出来,写入到原表中完成数据恢复即可。
1
情况②: 数据库并没有备份(或正处于备份周期间隔),此时就需要在binlog日志中找到该记录的所有操作,结合mysqlbinlog工具进一步分析执行,最终恢复被误删的数据。

上面两种情况说的很简单,但是实际中遇到的情况往往要复杂的多,比如不小心删除的是一部分数据或者是整张表,这部分数据有的备份了,有的没有备份,并且数据量很大。

2、尝试恢复

2.1 筛查数据备份或binlog日志

基于java研发说的删表的时间大概也就是2022-08-15的18:20-18:40左右,查看了阿里云RDS的数据备份时间和备份策略,发现刚好是数据备份的间隔期,也就是说没有数据备份。

数据备份时间和策略如图所示:

ali-rds-backup1

ali-rds-backup-period

既然数据备份无望,那就从binlog日志下手吧,因为阿里的RDS binlog日志时间显示挺友好的,就不需要自己从时间点出发筛查到具体的binlog日志文件了(这里根据java研发提供的时间段2022-08-15的18:20-18:40可以直接对应到binlog日志文件mysql-bin.004680)

ali-rds-binlog

找到对应的binlog日志文件之后,使用mysqlbinlog工具将二进制的binlog文件提取为具体的sql语句(因为没有mysql环境,这里选择使用docker构建一个临时环境使用mysqlbinlog工具)

# 注意使用对应mysql版本的mysqlbinlog工具,这里是mysql 8.0
$ sudo docker run --name mysql   -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0
$ sudo docker cp mysql-bin.004680 mysql:/home/
$ sudo docker exec -it mysql /bin/sh
# cd /home && ls
# mysqlbinlog mysql-bin.004680 --database laidiangongtest --base64-output=decode-rows -vv --skip-gtids=true  > binlog.sql 

## mysql-bin.004680:需要解析的 binlog 日志。
## --database:只列出该数据库下的行数据,但无法过滤 Rows_query_event。
## --base64-output=decode-rows -vv:显示具体 SQL 语句。
## --skip-gtids=true:忽略 GTID 显示。

docker-mysqlbinlog

sql文件down到本地之后,我这里直接使用文本编辑器进行全文搜索 表关键字 ,然后就搜索到了这么一条sql语句

binlog-sql-grep

好家伙,这货是执行了rename操作并不是delete、drop操作啊,不过这也省事儿了,找到被重命名的表恢复过来就解决了问题。

3、模拟误delete数据恢复

因为也不是专业的DBA选手嘛,这里为了学习和涨经验,特意创建了一个临时环境将其删除尝试恢复

3.1 恢复delete删除的数据

恢复delete误操作的时候,需要找到误操作在binlog日志中开始执行的偏移量position和结束执行的偏移量position。通过定位position使用mysqlbinlog命令再次读取binlog文件,得到误操作SQL的记录,通过将DELETE FROM语句转换为INSERT INTO语句并执行完成数据恢复

3.1.1 确认binlog日志情况

此种模拟情况下,假设的前提是没有备份数据(或处于备份数据间隔期),此时仅依赖于binlog日志进行数据恢复操作。所以首先要确认binlog日志是开启状态

$sudo docker run --name mysql   -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0
$sudo docker exec -it mysql /bin/sh
# mysql -uroot -p123456

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
3.1.2 创建测试数据
$sudo docker exec -it mysql /bin/sh
# mysql -uroot -p123456
mysql> CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8;
mysql> use test;
mysql> CREATE TABLE student (
    ->   id int(11) DEFAULT NULL,
    ->   name varchar(20) DEFAULT NULL,
    ->   age int(11) DEFAULT NULL,
    ->   sex varchar(20) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
mysql> INSERT INTO student VALUES ('1', 'a1', '18', '');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student VALUES ('2', 'a2', '16', '');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student VALUES ('3', 'a3', '17', '');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO student VALUES ('4', 'a4', '17', '');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student VALUES ('5', 'a5', '18', '');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student VALUES ('6', 'a6', '19', '');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student VALUES ('7', 'a7', '17', '');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student VALUES ('8', 'a8', '17', '');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student VALUES ('9', 'a9', '20', '');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student VALUES ('10', 'a10', '18', '');
Query OK, 1 row affected (0.01 sec)

mysql> UPDATE student SET name='a33' WHERE id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | a1   |   18 |      |
|    2 | a2   |   16 |      |
|    3 | a33  |   17 |      |
|    4 | a4   |   17 |      |
|    5 | a5   |   18 |      |
|    6 | a6   |   19 |      |
|    7 | a7   |   17 |      |
|    8 | a8   |   17 |      |
|    9 | a9   |   20 |      |
|   10 | a10  |   18 |      |
+------+------+------+------+
10 rows in set (0.00 sec)
3.1.3 执行delete误删操作
mysql> delete from student where age=17;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from student;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | a1   |   18 |      |
|    2 | a2   |   16 |      |
|    5 | a5   |   18 |      |
|    6 | a6   |   19 |      |
|    9 | a9   |   20 |      |
|   10 | a10  |   18 |      |
+------+------+------+------+
6 rows in set (0.00 sec)
3.1.4 基于binlog日志恢复数据

查看正在使用的binlog日志

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |     4641 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

使用mysqlbinlog工具将binlog二进制日志转换成具体的sql语句

# mysqlbinlog /var/lib/mysql/binlog.000002 --database test --base64-output=decode-rows -vv --skip-gtids=true  > binlog.sql

## mysql-bin.004680:需要解析的 binlog 日志。
## --database:只列出该数据库下的行数据,但无法过滤 Rows_query_event。
## --base64-output=decode-rows -vv:显示具体 SQL 语句。
## --skip-gtids=true:忽略 GTID 显示。

通过下图,可以将delete误删操作的偏移量定义为3865-4047
delete-binlog

然后根据偏移量使用mysqlbinlog工具将delete操作筛选出来

# mysqlbinlog /var/lib/mysql/binlog.000002 --database test --start-position=3865 --stop-position=4047 --base64-output=decode-rows -vv --skip-gtids=true  > delete.sql

然后使用sed命令将delete语句转换为insert语句

# cat delete.sql | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/VALUES(/g;' |sed -r 's/(@4.*),/\1);/g' | sed 's/@[1-9]=//g' > restore.sql
# cat restore.sql
INSERT INTO `test`.`student`
VALUES(
  3 ,
  'a33' ,
  17 ,
  '' );
INSERT INTO `test`.`student`
VALUES(
  4 ,
  'a4' ,
  17 ,
  '' );
INSERT INTO `test`.`student`
VALUES(
  7 ,
  'a7' ,
  17 ,
  '' );
INSERT INTO `test`.`student`
VALUES(
  8 ,
  'a8' ,
  17 ,
  '' );

命令参数说明:
sed -n ‘/###/p’:打印’###'开头的行。
sed ‘s/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/VALUES(/g;’:

s/### //g;s//*.*/,/g; 把’###’和//去掉
s/DELETE FROM/INSERT INTO/g; 把"delete from"换成"insert int"
s/WHERE/VALUES(/g; 把"where"换成"values("

sed -r ‘s/(@4.*),/\1);/g’:-r是正则表达式,在@4开头的一行末尾添加一个 ); ,这里表数据有几列,数字4就换成几
sed ‘s/@[1-9]=//g’:将@1-@9去除。
注:具体情况具体分析,根据不同数据库表结构来执行本条命令。

3.1.5 基于转换后语句恢复数据
# mysql -uroot -p123456 < restore.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
# mysql -uroot -p123456

   ........
   ........
   
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from test.student;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | a1   |   18 |      |
|    2 | a2   |   16 |      |
|    5 | a5   |   18 |      |
|    6 | a6   |   19 |      |
|    9 | a9   |   20 |      |
|   10 | a10  |   18 |      |
|   11 | a11  |   17 |      |
|    3 | a33  |   17 |      |
|    4 | a4   |   17 |      |
|    7 | a7   |   17 |      |
|    8 | a8   |   17 |      |
+------+------+------+------+
11 rows in set (0.00 sec)
3.2 恢复drop删除的表

恢复drop误操作的时候,就需要同时借助数据备份和binlog日志啦。恢复过程大致为:首先筛查drop操作的开始偏移量和结束偏移量,然后借助备份数据将数据恢复到执行drop的--start-position之前,然后在根据--stop-position基于备份数据将后一段数据也给补上,这样就相当于恢复了drop删掉的数据

描述的有点复杂,可以直接看图:
drop-mysql

所以恢复drop删除的数据时,最好是同时具有时间段吻合的数据备份文件和binlog日志文件,如果缺少了数据备份文件,如果拥有齐全的binlog日志文件也是可以恢复,就是会很麻烦。

这种情况就不在演示了。

4、总

从java研发的手残操作开始,稍微探究了一下mysql的常规数据恢复手段,稍微总结了一下供大家参考。
且以上几种数据恢复手段都是单节点的常规数据恢复手段,生产mysql高可用啦,主从复制啦手段肯定是必不可少的,就不必兜兜转转用这种手段恢复数据了。

参考:
1、MySQL恢复误删数据解决方案
2、MySQL【Delete误操作】使用binlog快速回滚
3、mysql通过binlog来恢复被删除的数据库
4、mysql数据误删除的恢复,drop表或库的恢复

0

评论