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

写在前面

在工作中不管是程序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;

mysql> show binary logs;
&＃43;----------------&＃43;-----------&＃43;
| Log_name | File_size |
&＃43;----------------&＃43;-----------&＃43;
| log-bin.000001 | 462 |
| log-bin.000002 | 722 |
| log-bin.000003 | 417 |
&＃43;----------------&＃43;-----------&＃43;
3 rows in set (0.00 sec)


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;

-- 建库
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;);


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)


可以看到恢复成功了。

3&＃xff1a;误删库

过程同2&＃xff1a;误删除表数据。

写在后面

参考文章列表&＃xff1a;

[美团] Myflash 的安装使用 。