MySQL之误删数据如何处理

写在前面

在工作中不管是程序bug，运维的失误，等，都有可能导致数据误删除，或者是误操作，此时我们就必须快速的恢复数据，避免对正常业务造成过大的影响，甚至出现事故，本文我们按照如下的几种情况来分析下误删数据如何处理:

1：误删除行数据
2：误删除表数据
3：误删除库数据
4：误删除MySQL实例
1
2
3
4

下面我们就按照这个顺序来一起看下。

1：误删除行数据

对于这种情况，可以比较容易的使用flashback的功能来进行数据的恢复，目前业界已有的方式如下：

1：mysqlbinlog
    优点：字段简单的话可以快速生成sql，编程门槛低
    缺点：字段如果是比较复杂，如字段值中包含特殊字符时，需要考虑进行转义，容易出错。
2：给源码打patach，
    优点：可以复用MySQL server层binlog解析相关的代码，效率高
    缺点：版本敏感，升级困难，每次升级几乎等同于依次版本的重构（这里的重构指的是patch的闪回功能）
3：使用业界提供的binlog解析的库
    优点：如果是成熟的库，稳定性好，容易上手
    缺点：效率低，功能受限于所使用的binlog解析库
1
2
3
4
5
6
7
8
9

对于1考虑其易出错的问题，我们不再重点分析，对于2,给源码打patch，我真的很想分享一波，可是奈何实力不允许，我不会啊！所以我们就看下3中的佼佼者MyFlash 。

1.1：安装myflash

在这里 下载源码包。
环境要求：

1、binlog格式必须为row，且binlog_row_image=full
2、仅支持5.6与5.7
3、只能回滚DML（增、删、改）
1
2
3

安装：

unzip MyFlash-master.zip
mv MyFlash-master /usr/local/MyFlash/
gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback
1
2
3

查看帮助：

[root@localhost MyFlash-master]# ./binary/flashback -h
Usage:
  flashback [OPTION?]

Help Options:
  -h, --help                  Show help options

Application Options:
  --databaseNames             databaseName to apply. if multiple, seperate by comma(,)
  --tableNames                tableName to apply. if multiple, seperate by comma(,)
  --tableNames-file           tableName to apply. if multiple, seperate by comma(,)
  --start-position            start position
  --stop-position             stop position
  --start-datetime            start time (format %Y-%m-%d %H:%M:%S)
  --stop-datetime             stop time (format %Y-%m-%d %H:%M:%S)
  --sqlTypes                  sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
  --maxSplitSize              max file size after split, the uint is M
  --binlogFileNames           binlog files to process. if multiple, seperate by comma(,)  
  --outBinlogFileNameBase     output binlog file name base
  --logLevel                  log level, available option is debug,warning,error
  --include-gtids             gtids to process. if multiple, seperate by comma(,)
  --include-gtids-file        gtids to process. if multiple, seperate by comma(,)
  --exclude-gtids             gtids to skip. if multiple, seperate by comma(,)
  --exclude-gtids-file        gtids to skip. if multiple, seperate by comma(,)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

1.2：准备测试数据

-- 建库
create database cym; 
use cym;
-- 建表
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB charset=utf8mb4;
-- 插入数据
flush logs;
insert into t1 values (1,'a'),(2,'b');
1
2
3
4
5
6
7
8
9
10
11
12

查看binlog如下：

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 |       462 |
| log-bin.000002 |       722 |
| log-bin.000003 |       417 |
+----------------+-----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

1.3：查看数据

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

1.4：查看生成的binlog

关于binlog可以参考这篇文章 。

[root@localhost MyFlash-master]# mysqlbinlog -vv /usr/local/mysql/log-bin.000003 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
...
'/*!*/;
### INSERT INTO `cym`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### INSERT INTO `cym`.`t1`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
# at 386
#220823 14:49:22 server id 1  end_log_pos 417 CRC32 0xe1b8e2bd  Xid = 30
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

1.5：使用myflash生成反操作的binlog

[root@localhost MyFlash-master]# ./binary/flashback --binlogFileNames=/usr/local/mysql/log-bin.000003 --outBinlogFileNameBase=dongshimummy
[root@localhost MyFlash-master]# ll | grep 'mummy'
-rw-r--r-- 1 root root  250 Aug 23 15:14 dongshimummy.flashback
1
2
3

查看其内容：

[root@localhost MyFlash-master]# mysqlbinlog -vv dongshimummy.flashback 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
...
'/*!*/;
### DELETE FROM `cym`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### DELETE FROM `cym`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
# at 219
#220823 14:49:22 server id 1  end_log_pos 250 CRC32 0xe1b8e2bd  Xid = 30
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

可以看到生成了对应的删除语句。

1.6：恢复数据

• 恢复前查看数据

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

• 恢复数据并查看数据

[root@localhost MyFlash-master]# mysqlbinlog /root/study/myflash/MyFlash-master/dongshimummy.flashback | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from t1;
Empty set (0.00 sec)
1
2
3
4

1.7：更新场景恢复数据

• 准备测试数据

-- 建库
create database cym; 
use cym;
-- 建表
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB charset=utf8mb4;
mysql> update t1 set name='a10000' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t1 set name='a20000' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注意此时我们并没有flush logs，即不会生成新的

• 确定位点
  我们之所以没有执行flush logs，是为了模拟真实环境的情况，那么我们就需要来确定要同步的开始的位点是什么，可以像下面这样通过sql语句中的关键字来操作：

• 恢复数据

[root@localhost MyFlash-master]# binary/flashback --start-position=3084 --binlogFileNames=/usr/local/mysql/log-bin.000003 --outBinlogFileNameBase=dongshimeimei /*生成闪回文件*/
[root@localhost MyFlash-master]# mysql -uroot -p -e"select * from cym.t1 where id=1" /*查看恢复数据前的值*/
Enter password: 
+----+--------+
| id | name   |
+----+--------+
|  1 | a20000 |
+----+--------+
[root@localhost MyFlash-master]# mysqlbinlog dongshimeimei.flashback | mysql -uroot -p /*恢复数据*/
Enter password: 
[root@localhost MyFlash-master]# mysql -uroot -p -e"select * from cym.t1 where id=1" /*查看恢复数据前的值*/
Enter password: 
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

1.8：只恢复部分事务数据

注意打开gtid，如下配置：

[mysqld]
...
gtid-mode=on                  # 启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true # 强制GTID的一致性
1
2
3
4

准备测试数据：

-- 建库
create database cym; 
use cym;
-- 建表
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB charset=utf8mb4;
-- 插入数据
flush logs;
insert into t1 values (1,'a'),(2,'b');
1
2
3
4
5
6
7
8
9
10
11
12

1.8.1：准备测试数据

我们使用3个事务，执行不同的修改，具体操作如下。

• 事务1执行操作
  更新ID为1的name为aaaaa。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set name='aaaaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9

查看该事务对应的gtid：

[root@localhost MyFlash-master]# mysqlbinlog -vv /usr/local/mysql/log-bin.000004 | grep 'aaaaa' -B 30
...
SET @@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:4'/*!*/;
...
'/*!*/;
### UPDATE `cym`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='aaaaa' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
1
2
3
4
5
6
7
8
9
10
11
12

从SET @@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:4'可以看到值是a5e2c832-2291-11ed-9728-000c2933f83c:4，记住该值，后面需要用到。

• 事务2执行操作
  插入新的行insert into t1 values (3,'ccccc'),(4,'ddddd');：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (3,'ccccc'),(4,'ddddd');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)
1
2
3
4
5
6
7
8
9

查看该事务对应的gtid：

SET @@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:5'/*!*/;
...
'/*!*/;
### INSERT INTO `cym`.`t1`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ccccc' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### INSERT INTO `cym`.`t1`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ddddd' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
1
2
3
4
5
6
7
8
9
10
11

从SET @@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:5'可以看到值是a5e2c832-2291-11ed-9728-000c2933f83c:5，记住该值，后面需要用到。

• 事务3执行操作
  更新ID为2的name为bbbb2222，操作如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set name='bbbb2222' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9

查看该事务对应的gtid：

[root@localhost MyFlash-master]# mysqlbinlog -vv /usr/local/mysql/log-bin.000004 | grep 'bbbb2222' -A 5 -B 30
...
SET @@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:6'/*!*/;
...
'/*!*/;
### UPDATE `cym`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='bbbb2222' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
...
1
2
3
4
5
6
7
8
9
10
11
12
13

从@@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:6'可以看到值是a5e2c832-2291-11ed-9728-000c2933f83c:6，记住该值，后面需要用到。

• 当前数据状态

• 恢复事务2，事务3
  事务2 gtid:a5e2c832-2291-11ed-9728-000c2933f83c:5,事务3gtid：a5e2c832-2291-11ed-9728-000c2933f83c:6,生成闪回文件：

[root@localhost MyFlash-master]# ./binary/flashback --binlogFileNames=/usr/local/mysql/log-bin.000004 --outBinlogFileNameBase=dongshigohome1 --databaseNames=cym --tableNames=t1 --sqlTypes=insert,update --include-gtids='a5e2c832-2291-11ed-9728-000c2933f83c:5,a5e2c832-2291-11ed-9728-000c2933f83c:6'
1

生成的闪回文件如下：

[root@localhost MyFlash-master]# mysqlbinlog -vv dongshigohome1.flashback 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220823 18:01:13 server id 1  end_log_pos 123 CRC32 0xb2284436  Start: binlog v 4, server v 5.7.39-log created 220823 18:01:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
aaUEYw8BAAAAdwAAAHsAAAABAAQANS43LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABppQRjEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATZEKLI=
'/*!*/;
# at 123
#220824 13:32:20 server id 1  end_log_pos 170 CRC32 0x0a7a0a8a  Table_map: `cym`.`t1` mapped to number 109
# at 170
#220824 13:32:20 server id 1  end_log_pos 227 CRC32 0x26e7141f  Update_rows: table id 109 flags: STMT_END_F

BINLOG '
5LcFYxMBAAAALwAAAKoAAAAAAG0AAAAAAAEAA2N5bQACdDEAAgMPAlAAAooKego=
5LcFYx8BAAAAOQAAAOMAAAAAAG0AAAAAAAEAAgAC///8AgAAAAhiYmJiMjIyMvwCAAAAAWIfFOcm
'/*!*/;
### UPDATE `cym`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='bbbb2222' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
# at 227
#220823 18:08:27 server id 1  end_log_pos 258 CRC32 0x73720c50  Xid = 16
COMMIT/*!*/;
# at 258
#220823 18:41:47 server id 1  end_log_pos 305 CRC32 0xeb7b8cc1  Table_map: `cym`.`t1` mapped to number 109
# at 305
#220823 18:41:47 server id 1  end_log_pos 362 CRC32 0x15f84dc5  Delete_rows: table id 109 flags: STMT_END_F

BINLOG '
664EYxMBAAAALwAAADEBAAAAAG0AAAAAAAEAA2N5bQACdDEAAgMPAlAAAsGMe+s=
664EYyABAAAAOQAAAGoBAAAAAG0AAAAAAAEAAgAC//wDAAAABWNjY2Nj/AQAAAAFZGRkZGTFTfgV
'/*!*/;
### DELETE FROM `cym`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ccccc' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### DELETE FROM `cym`.`t1`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ddddd' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
# at 362
#220823 18:08:27 server id 1  end_log_pos 393 CRC32 0x73720c50  Xid = 16
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

可以看到按照相反的操作顺序，即事务3，事务2的顺序，生成了反向操作的sql语句，然后我们就可以用来恢复数据了。

• 恢复数据并查看

[root@localhost MyFlash-master]# mysqlbinlog -vv --skip-gtids dongshigohome1.flashback | mysql -uroot -p 
Enter password: 
[root@localhost MyFlash-master]# 
1
2
3

查看数据：

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | aaaaa |
|  2 | b     |
+----+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

可以看到除了事务1的修改，事务2和事务3都恢复成功了(๑•̀ㅂ•́)و✧。

1.9：如何预防数据误操作

• 开启sql_safe_update 。
• review代码，sql审计。

2：误删除表数据

需要定期备份数据库，并开启binlog日志，接下来通过实战看下如何操作。

2.1：模拟正常的业务操作

创建数据库testdb_restore_table，创建2张表t1,t2,并插入测试数据：

create database testdb_restore_table; 
use testdb_restore_table;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB charset=utf8mb4;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB charset=utf8mb4;

insert into t1 values (1,'aaaaa'),(2,'bbbbbb');
insert into t2 values (1,23),(2,34);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查看数据：

mysql> use testdb_restore_table;
Database changed
mysql> show tables;
+--------------------------------+
| Tables_in_testdb_restore_table |
+--------------------------------+
| t1                             |
| t2                             |
+--------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaaaa  |
|  2 | bbbbbb |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | age  |
+----+------+
|  1 |   23 |
|  2 |   34 |
+----+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

2.2：模拟某时刻全量备份数据

[root@localhost MyFlash-master]# mysqldump --databases --set-gtid-purged=OFF testdb_restore_table -uroot -p > /tmp/testdb_restore_table_backup20220825_1.sql
Enter password: 
1
2

即将全量数据备份到/tmp/testdb_restore_table_backup20220825_1.sql。

2.3：模拟对表t1的各种正常业务操作

mysql> update t1 set name='aaaaa1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaaaa1 |
|  2 | bbbbbb |
+----+--------+
2 rows in set (0.00 sec)

mysql> insert into t1 values(3,'cccccc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaaaa1 |
|  2 | bbbbbb |
|  3 | cccccc |
+----+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

即更新了ID=1的行，增加了ID=3的行。

2.4：模拟误删除

误删除表t1：

mysql> delete from t1 where id>0;/*这里写id>0的原因是我本地环境开启了sql_safe_update=ON*/
Query OK, 3 rows affected (0.01 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

2.5：在另外一个MySQL实例恢复数据

另选一个MySQL实例，不直接操作原来数据库的原因是，避免操作失误，对数据造成二次伤害。

• 在新实例创建数据库testdb_restore_table

mysql> create database testdb_restore_table;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb_restore_table;
Database changed

mysql> show tables;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8

• 恢复备份的全量数据

mysql> source /tmp/testdb_restore_table_backup20220825_1.sql;
Query OK, 0 rows affected (0.00 sec)
...
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 union select * from t2;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaaaa  |
|  2 | bbbbbb |
|  1 | 23     |
|  2 | 34     |
+----+--------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 获取备份后的第一个修改的时间

[root@localhost tmp]# mysqlbinlog -vv /usr/local/mysql/log-bin.000007 | egrep 'aaaaa1' -B 30
###   @2=34 /* INT meta=0 nullable=1 is_null=0 */
# at 1811
#220825 18:21:42 server id 1  end_log_pos 1842 CRC32 0xb38ce58a         Xid = 97
COMMIT/*!*/;
# at 1842
#220825 18:24:04 server id 1  end_log_pos 1907 CRC32 0xf416ca14         GTID    last_committed=6        sequence_number=7       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:62'/*!*/;
# at 1907
#220825 18:24:04 server id 1  end_log_pos 1995 CRC32 0x7dd3689d         Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1661423044/*!*/;
BEGIN
/*!*/;
# at 1995
#220825 18:24:04 server id 1  end_log_pos 2059 CRC32 0xab5d0664         Table_map: `testdb_restore_table`.`t1` mapped to number 118
# at 2059
#220825 18:24:04 server id 1  end_log_pos 2118 CRC32 0xd5640c69         Update_rows: table id 118 flags: STMT_END_F
...ZNU=
'/*!*/;
### UPDATE `testdb_restore_table`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='aaaaa' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='aaaaa1' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
--
...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

可以看到时间大概是220825 18:24:04,记住这个时间，我们会从该时间开始来恢复备份后产生的修改。

• 获取误删除数据事务的gtid

[root@localhost tmp]# mysqlbinlog -vv /usr/local/mysql/log-bin.000007 | egrep 'DELETE FROM `testdb_restore_table`.`t1`' -B 30
...
SET @@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:64'/*!*/;
...
'/*!*/;
### DELETE FROM `testdb_restore_table`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='aaaaa1' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### DELETE FROM `testdb_restore_table`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='bbbbbb' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### DELETE FROM `testdb_restore_table`.`t1`
1
2
3
4
5
6
7
8
9
10
11
12
13
14

从SET @@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:64'可以看出误删除表数据的gtid是a5e2c832-2291-11ed-9728-000c2933f83c:64，记住该值后面我们需要用其来跳过误删除数据的事务。

• 恢复数据
  从时间2022-08-25 18:24:00之后开始恢复数据，并且跳过gtida5e2c832-2291-11ed-9728-000c2933f83c:64。

[root@localhost tmp]# mysqlbinlog -vv --start-datetime='2022-08-25 18:24:00' --exclude-gtids='a5e2c832-2291-11ed-9728-000c2933f83c:64' /tmp/log-bin.000007 | mysql -uroot -p
Enter password: 
1
2

恢复后查看数据：

mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaaaa1 |
|  2 | bbbbbb |
|  3 | cccccc |
+----+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

可以看到恢复成功了。

3：误删库

过程同2：误删除表数据。

写在后面

参考文章列表：

[美团] Myflash 的安装使用 。

MySQL工具推荐 | 基于MySQL binlog的flashback工具 。