MySQL误删数据除了跑路还能怎么办？

作者：初语2265_781 | 来源：互联网 | 2023-09-24 19:18

写在前面在工作中不管是程序bug，运维的失误，等，都有可能导致数据误删除，或者是误操作，此时我们就必须快速的

写在前面

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

1&＃xff1a;误删除行数据 2&＃xff1a;误删除表数据 3&＃xff1a;误删除库数据 4&＃xff1a;误删除MySQL实例

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

1&＃xff1a;误删除行数据

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

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

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

1.1&＃xff1a;安装myflash

在这里下载源码包。
环境要求&＃xff1a;

1、binlog格式必须为row&＃xff0c;且binlog_row_image&＃61;full 2、仅支持5.6与5.7 3、只能回滚DML&＃xff08;增、删、改&＃xff09;

安装&＃xff1a;

unzip MyFlash-master.zip mv MyFlash-master /usr/local/MyFlash/ gcc -w &＃96;pkg-config --cflags --libs glib-2.0&＃96; source/binlogParseGlib.c -o binary/flashback

查看帮助&＃xff1a;

[root&＃64;localhost MyFlash-master]# ./binary/flashback -h Usage:flashback [OPTION?]Help Options:-h, --help Show help optionsApplication 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&＃xff1a;准备测试数据

-- 建库 create database cym; use cym; -- 建表 CREATE TABLE &＃96;t1&＃96; (&＃96;id&＃96; int(11) NOT NULL AUTO_INCREMENT,&＃96;name&＃96; varchar(20) DEFAULT NULL,PRIMARY KEY (&＃96;id&＃96;) ) ENGINE&＃61;InnoDB charset&＃61;utf8mb4; -- 插入数据 flush logs; insert into t1 values (1,&＃39;a&＃39;),(2,&＃39;b&＃39;);

查看binlog如下&＃xff1a;

1.3&＃xff1a;查看数据

mysql> select * from t1; &＃43;----&＃43;------&＃43; | id | name | &＃43;----&＃43;------&＃43; | 1 | a | | 2 | b | &＃43;----&＃43;------&＃43; 2 rows in set (0.00 sec)

1.4&＃xff1a;查看生成的binlog

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

[root&＃64;localhost MyFlash-master]# mysqlbinlog -vv /usr/local/mysql/log-bin.000003 /*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;1*/; ... &＃39;/*!*/; ### INSERT INTO &＃96;cym&＃96;.&＃96;t1&＃96; ### SET ### &＃64;1&＃61;1 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;a&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### INSERT INTO &＃96;cym&＃96;.&＃96;t1&＃96; ### SET ### &＃64;1&＃61;2 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;b&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ # at 386 #220823 14:49:22 server id 1 end_log_pos 417 CRC32 0xe1b8e2bd Xid &＃61; 30 COMMIT/*!*/; SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;AUTOMATIC&＃39; /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE&＃61;&＃64;OLD_COMPLETION_TYPE*/; /*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;0*/;

1.5&＃xff1a;使用myflash生成反操作的binlog

[root&＃64;localhost MyFlash-master]# ./binary/flashback --binlogFileNames&＃61;/usr/local/mysql/log-bin.000003 --outBinlogFileNameBase&＃61;dongshimummy [root&＃64;localhost MyFlash-master]# ll | grep &＃39;mummy&＃39; -rw-r--r-- 1 root root 250 Aug 23 15:14 dongshimummy.flashback

查看其内容&＃xff1a;

[root&＃64;localhost MyFlash-master]# mysqlbinlog -vv dongshimummy.flashback /*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;1*/; ... &＃39;/*!*/; ### DELETE FROM &＃96;cym&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;1 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;a&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### DELETE FROM &＃96;cym&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;2 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;b&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ # at 219 #220823 14:49:22 server id 1 end_log_pos 250 CRC32 0xe1b8e2bd Xid &＃61; 30 COMMIT/*!*/; SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;AUTOMATIC&＃39; /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE&＃61;&＃64;OLD_COMPLETION_TYPE*/; /*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;0*/;

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

1.6&＃xff1a;恢复数据

恢复前查看数据

mysql> select * from t1; &＃43;----&＃43;------&＃43; | id | name | &＃43;----&＃43;------&＃43; | 1 | a | | 2 | b | &＃43;----&＃43;------&＃43; 2 rows in set (0.00 sec)

恢复数据并查看数据

[root&＃64;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.7&＃xff1a;更新场景恢复数据

准备测试数据

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

注意此时我们并没有flush logs&＃xff0c;即不会生成新的

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

在这里插入图片描述

恢复数据

[root&＃64;localhost MyFlash-master]# binary/flashback --start-position&＃61;3084 --binlogFileNames&＃61;/usr/local/mysql/log-bin.000003 --outBinlogFileNameBase&＃61;dongshimeimei /*生成闪回文件*/ [root&＃64;localhost MyFlash-master]# mysql -uroot -p -e"select * from cym.t1 where id&＃61;1" /*查看恢复数据前的值*/ Enter password: &＃43;----&＃43;--------&＃43; | id | name | &＃43;----&＃43;--------&＃43; | 1 | a20000 | &＃43;----&＃43;--------&＃43; [root&＃64;localhost MyFlash-master]# mysqlbinlog dongshimeimei.flashback | mysql -uroot -p /*恢复数据*/ Enter password: [root&＃64;localhost MyFlash-master]# mysql -uroot -p -e"select * from cym.t1 where id&＃61;1" /*查看恢复数据前的值*/ Enter password: &＃43;----&＃43;------&＃43; | id | name | &＃43;----&＃43;------&＃43; | 1 | a | &＃43;----&＃43;------&＃43;

1.8&＃xff1a;只恢复部分事务数据

注意打开gtid&＃xff0c;如下配置&＃xff1a;

[mysqld] ... gtid-mode&＃61;on # 启用gtid类型&＃xff0c;否则就是普通的复制架构 enforce-gtid-consistency&＃61;true # 强制GTID的一致性

准备测试数据&＃xff1a;

1.8.1&＃xff1a;准备测试数据

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

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

mysql> begin; Query OK, 0 rows affected (0.00 sec)mysql> update t1 set name&＃61;&＃39;aaaaa&＃39; where id&＃61;1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> commit; Query OK, 0 rows affected (0.00 sec)

查看该事务对应的gtid&＃xff1a;

[root&＃64;localhost MyFlash-master]# mysqlbinlog -vv /usr/local/mysql/log-bin.000004 | grep &＃39;aaaaa&＃39; -B 30 ... SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;a5e2c832-2291-11ed-9728-000c2933f83c:4&＃39;/*!*/; ... &＃39;/*!*/; ### UPDATE &＃96;cym&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;1 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;a&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### SET ### &＃64;1&＃61;1 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;aaaaa&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */

从SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;a5e2c832-2291-11ed-9728-000c2933f83c:4&＃39;可以看到值是a5e2c832-2291-11ed-9728-000c2933f83c:4&＃xff0c;记住该值&＃xff0c;后面需要用到。

事务2执行操作
插入新的行insert into t1 values (3,&＃39;ccccc&＃39;),(4,&＃39;ddddd&＃39;);&＃xff1a;

mysql> begin; Query OK, 0 rows affected (0.00 sec)mysql> insert into t1 values (3,&＃39;ccccc&＃39;),(4,&＃39;ddddd&＃39;); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql> commit; Query OK, 0 rows affected (0.01 sec)

查看该事务对应的gtid&＃xff1a;

SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;a5e2c832-2291-11ed-9728-000c2933f83c:5&＃39;/*!*/; ... &＃39;/*!*/; ### INSERT INTO &＃96;cym&＃96;.&＃96;t1&＃96; ### SET ### &＃64;1&＃61;3 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;ccccc&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### INSERT INTO &＃96;cym&＃96;.&＃96;t1&＃96; ### SET ### &＃64;1&＃61;4 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;ddddd&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */

从SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;a5e2c832-2291-11ed-9728-000c2933f83c:5&＃39;可以看到值是a5e2c832-2291-11ed-9728-000c2933f83c:5&＃xff0c;记住该值&＃xff0c;后面需要用到。

事务3执行操作
更新ID为2的name为bbbb2222&＃xff0c;操作如下&＃xff1a;

mysql> start transaction; Query OK, 0 rows affected (0.00 sec)mysql> update t1 set name&＃61;&＃39;bbbb2222&＃39; where id&＃61;2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> commit; Query OK, 0 rows affected (0.00 sec)

查看该事务对应的gtid&＃xff1a;

[root&＃64;localhost MyFlash-master]# mysqlbinlog -vv /usr/local/mysql/log-bin.000004 | grep &＃39;bbbb2222&＃39; -A 5 -B 30 ... SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;a5e2c832-2291-11ed-9728-000c2933f83c:6&＃39;/*!*/; ... &＃39;/*!*/; ### UPDATE &＃96;cym&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;2 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;b&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### SET ### &＃64;1&＃61;2 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;bbbb2222&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ...

从&＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;a5e2c832-2291-11ed-9728-000c2933f83c:6&＃39;可以看到值是a5e2c832-2291-11ed-9728-000c2933f83c:6&＃xff0c;记住该值&＃xff0c;后面需要用到。

当前数据状态

在这里插入图片描述

恢复事务2&＃xff0c;事务3
事务2 gtid:a5e2c832-2291-11ed-9728-000c2933f83c:5,事务3gtid&＃xff1a;a5e2c832-2291-11ed-9728-000c2933f83c:6,生成闪回文件&＃xff1a;

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

生成的闪回文件如下&＃xff1a;

[root&＃64;localhost MyFlash-master]# mysqlbinlog -vv dongshigohome1.flashback /*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;1*/; /*!50003 SET &＃64;OLD_COMPLETION_TYPE&＃61;&＃64;&＃64;COMPLETION_TYPE,COMPLETION_TYPE&＃61;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 &＃39; aaUEYw8BAAAAdwAAAHsAAAABAAQANS43LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABppQRjEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA ATZEKLI&＃61; &＃39;/*!*/; # at 123 #220824 13:32:20 server id 1 end_log_pos 170 CRC32 0x0a7a0a8a Table_map: &＃96;cym&＃96;.&＃96;t1&＃96; 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_FBINLOG &＃39; 5LcFYxMBAAAALwAAAKoAAAAAAG0AAAAAAAEAA2N5bQACdDEAAgMPAlAAAooKego&＃61; 5LcFYx8BAAAAOQAAAOMAAAAAAG0AAAAAAAEAAgAC///8AgAAAAhiYmJiMjIyMvwCAAAAAWIfFOcm &＃39;/*!*/; ### UPDATE &＃96;cym&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;2 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;bbbb2222&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### SET ### &＃64;1&＃61;2 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;b&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ # at 227 #220823 18:08:27 server id 1 end_log_pos 258 CRC32 0x73720c50 Xid &＃61; 16 COMMIT/*!*/; # at 258 #220823 18:41:47 server id 1 end_log_pos 305 CRC32 0xeb7b8cc1 Table_map: &＃96;cym&＃96;.&＃96;t1&＃96; 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_FBINLOG &＃39; 664EYxMBAAAALwAAADEBAAAAAG0AAAAAAAEAA2N5bQACdDEAAgMPAlAAAsGMe&＃43;s&＃61; 664EYyABAAAAOQAAAGoBAAAAAG0AAAAAAAEAAgAC//wDAAAABWNjY2Nj/AQAAAAFZGRkZGTFTfgV &＃39;/*!*/; ### DELETE FROM &＃96;cym&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;3 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;ccccc&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### DELETE FROM &＃96;cym&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;4 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;ddddd&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ # at 362 #220823 18:08:27 server id 1 end_log_pos 393 CRC32 0x73720c50 Xid &＃61; 16 COMMIT/*!*/; SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;AUTOMATIC&＃39; /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE&＃61;&＃64;OLD_COMPLETION_TYPE*/; /*!50530 SET &＃64;&＃64;SESSION.PSEUDO_SLAVE_MODE&＃61;0*/;

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

恢复数据并查看

[root&＃64;localhost MyFlash-master]# mysqlbinlog -vv --skip-gtids dongshigohome1.flashback | mysql -uroot -p Enter password: [root&＃64;localhost MyFlash-master]#

查看数据&＃xff1a;

mysql> select * from t1; &＃43;----&＃43;-------&＃43; | id | name | &＃43;----&＃43;-------&＃43; | 1 | aaaaa | | 2 | b | &＃43;----&＃43;-------&＃43; 2 rows in set (0.00 sec)

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

1.9&＃xff1a;如何预防数据误操作

开启sql_safe_update 。
review代码&＃xff0c;sql审计。

2&＃xff1a;误删除表数据

需要定期备份数据库&＃xff0c;并开启binlog日志&＃xff0c;接下来通过具体的实战来看下如何操作。

2.1&＃xff1a;模拟正常的业务操作

创建数据库testdb_restore_table&＃xff0c;创建2张表t1,t2,并插入测试数据&＃xff1a;

create database testdb_restore_table; use testdb_restore_table; CREATE TABLE &＃96;t1&＃96; (&＃96;id&＃96; int(11) NOT NULL AUTO_INCREMENT,&＃96;name&＃96; varchar(20) DEFAULT NULL,PRIMARY KEY (&＃96;id&＃96;) ) ENGINE&＃61;InnoDB charset&＃61;utf8mb4; CREATE TABLE &＃96;t2&＃96; (&＃96;id&＃96; int(11) NOT NULL AUTO_INCREMENT,&＃96;age&＃96; int(20) DEFAULT NULL,PRIMARY KEY (&＃96;id&＃96;) ) ENGINE&＃61;InnoDB charset&＃61;utf8mb4;insert into t1 values (1,&＃39;aaaaa&＃39;),(2,&＃39;bbbbbb&＃39;); insert into t2 values (1,23),(2,34);

查看此时数据&＃xff1a;

mysql> use testdb_restore_table; Database changed mysql> show tables; &＃43;--------------------------------&＃43; | Tables_in_testdb_restore_table | &＃43;--------------------------------&＃43; | t1 | | t2 | &＃43;--------------------------------&＃43; 2 rows in set (0.00 sec)mysql> select * from t1; &＃43;----&＃43;--------&＃43; | id | name | &＃43;----&＃43;--------&＃43; | 1 | aaaaa | | 2 | bbbbbb | &＃43;----&＃43;--------&＃43; 2 rows in set (0.00 sec)mysql> select * from t2; &＃43;----&＃43;------&＃43; | id | age | &＃43;----&＃43;------&＃43; | 1 | 23 | | 2 | 34 | &＃43;----&＃43;------&＃43; 2 rows in set (0.00 sec)

2.2&＃xff1a;模拟某时刻全量备份数据

[root&＃64;localhost MyFlash-master]# mysqldump --databases --set-gtid-purged&＃61;OFF testdb_restore_table -uroot -p > /tmp/testdb_restore_table_backup20220825_1.sql Enter password:

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

2.3&＃xff1a;模拟对表t1的各种正常业务操作

mysql> update t1 set name&＃61;&＃39;aaaaa1&＃39; where id&＃61;1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t1; &＃43;----&＃43;--------&＃43; | id | name | &＃43;----&＃43;--------&＃43; | 1 | aaaaa1 | | 2 | bbbbbb | &＃43;----&＃43;--------&＃43; 2 rows in set (0.00 sec)mysql> insert into t1 values(3,&＃39;cccccc&＃39;); Query OK, 1 row affected (0.00 sec)mysql> select * from t1; &＃43;----&＃43;--------&＃43; | id | name | &＃43;----&＃43;--------&＃43; | 1 | aaaaa1 | | 2 | bbbbbb | | 3 | cccccc | &＃43;----&＃43;--------&＃43; 3 rows in set (0.00 sec)

即更新了ID&＃61;1的行&＃xff0c;增加了ID&＃61;3的行。

2.4&＃xff1a;模拟误删除

误删除表t1&＃xff1a;

mysql> delete from t1 where id>0; Query OK, 3 rows affected (0.01 sec)mysql> select count(*) from t1; &＃43;----------&＃43; | count(*) | &＃43;----------&＃43; | 0 | &＃43;----------&＃43; 1 row in set (0.00 sec)

2.5&＃xff1a;在另外一个MySQL实例恢复数据

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

在新实例创建数据库testdb_restore_table

mysql> create database testdb_restore_table; Query OK, 1 row affected (0.00 sec)mysql> use testdb_restore_table; Database changedmysql> show tables; Empty set (0.00 sec)

恢复备份的全量数据

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; &＃43;----&＃43;--------&＃43; | id | name | &＃43;----&＃43;--------&＃43; | 1 | aaaaa | | 2 | bbbbbb | | 1 | 23 | | 2 | 34 | &＃43;----&＃43;--------&＃43; 4 rows in set (0.00 sec)

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

[root&＃64;localhost tmp]# mysqlbinlog -vv /usr/local/mysql/log-bin.000007 | egrep &＃39;aaaaa1&＃39; -B 30 ### &＃64;2&＃61;34 /* INT meta&＃61;0 nullable&＃61;1 is_null&＃61;0 */ # at 1811 #220825 18:21:42 server id 1 end_log_pos 1842 CRC32 0xb38ce58a Xid &＃61; 97 COMMIT/*!*/; # at 1842 #220825 18:24:04 server id 1 end_log_pos 1907 CRC32 0xf416ca14 GTID last_committed&＃61;6 sequence_number&＃61;7 rbr_only&＃61;yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;a5e2c832-2291-11ed-9728-000c2933f83c:62&＃39;/*!*/; # at 1907 #220825 18:24:04 server id 1 end_log_pos 1995 CRC32 0x7dd3689d Query thread_id&＃61;3 exec_time&＃61;0 error_code&＃61;0 SET TIMESTAMP&＃61;1661423044/*!*/; BEGIN /*!*/; # at 1995 #220825 18:24:04 server id 1 end_log_pos 2059 CRC32 0xab5d0664 Table_map: &＃96;testdb_restore_table&＃96;.&＃96;t1&＃96; 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&＃61; &＃39;/*!*/; ### UPDATE &＃96;testdb_restore_table&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;1 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;aaaaa&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### SET ### &＃64;1&＃61;1 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;aaaaa1&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ -- ...

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

获取误删除数据事务的gtid

[root&＃64;localhost tmp]# mysqlbinlog -vv /usr/local/mysql/log-bin.000007 | egrep &＃39;DELETE FROM &＃96;testdb_restore_table&＃96;.&＃96;t1&＃96;&＃39; -B 30 ... SET &＃64;&＃64;SESSION.GTID_NEXT&＃61; &＃39;a5e2c832-2291-11ed-9728-000c2933f83c:64&＃39;/*!*/; ... &＃39;/*!*/; ### DELETE FROM &＃96;testdb_restore_table&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;1 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;aaaaa1&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### DELETE FROM &＃96;testdb_restore_table&＃96;.&＃96;t1&＃96; ### WHERE ### &＃64;1&＃61;2 /* INT meta&＃61;0 nullable&＃61;0 is_null&＃61;0 */ ### &＃64;2&＃61;&＃39;bbbbbb&＃39; /* VARSTRING(80) meta&＃61;80 nullable&＃61;1 is_null&＃61;0 */ ### DELETE FROM &＃96;testdb_restore_table&＃96;.&＃96;t1&＃96;

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

恢复数据

[root&＃64;localhost tmp]# mysqlbinlog -vv --start-datetime&＃61;&＃39;2022-08-25 18:24:00&＃39; --exclude-gtids&＃61;&＃39;a5e2c832-2291-11ed-9728-000c2933f83c:64&＃39; /tmp/log-bin.000007 | mysql -uroot -p Enter password:

恢复后查看数据&＃xff1a;

mysql> select * from t1; &＃43;----&＃43;--------&＃43; | id | name | &＃43;----&＃43;--------&＃43; | 1 | aaaaa1 | | 2 | bbbbbb | | 3 | cccccc | &＃43;----&＃43;--------&＃43; 3 rows in set (0.00 sec)

可以看到恢复成功了。