首页 > 资讯 > 数据库 >MySQL之误删数据如何处理

263

分享到

MySQL之误删数据如何处理

mysql 数据库恢复误删数据 mysqlbinlog mysqldump 2023-08-16 20:08:26 263人浏览独家记忆

摘要

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

写在前面

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

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

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

1：误删除行数据

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

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

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

1.1：安装myflash

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

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

安装：

unzip MyFlash-master.zipmv MyFlash-master /usr/local/MyFlash/GCc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback

查看帮助：

[root@localhost MyFlash-master]# ./binary/flashback -hUsage:  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：准备测试数据

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

查看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.3：查看数据

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

1.4：查看生成的binlog

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

[root@localhost MyFlash-master]# mysqlbinlog -vv /usr/local/mysql/log-bin.000003 ;...';### INSERT INTO `cym`.`t1`### SET###   @1=1 ###   @2='a' ### INSERT INTO `cym`.`t1`### SET###   @1=2 ###   @2='b' # at 386#220823 14:49:22 server id 1  end_log_pos 417 CRC32 0xe1b8e2bd  Xid = 30COMMIT;SET @@SESSION.GTID_NEXT= 'AUTOMATIC'  ;DELIMITER ;# End of log file;;

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

查看其内容：

[root@localhost MyFlash-master]# mysqlbinlog -vv dongshimummy.flashback ;...';### DELETE FROM `cym`.`t1`### WHERE###   @1=1 ###   @2='a' ### DELETE FROM `cym`.`t1`### WHERE###   @1=2 ###   @2='b' # at 219#220823 14:49:22 server id 1  end_log_pos 250 CRC32 0xe1b8e2bd  Xid = 30COMMIT;SET @@SESSION.GTID_NEXT= 'AUTOMATIC'  ;DELIMITER ;# End of log file;;

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

1.6：恢复数据

恢复前查看数据

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

恢复数据并查看数据

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

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: 0mysql> update t1 set name='a20000' where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

注意此时我们并没有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.8：只恢复部分事务数据

注意打开gtid，如下配置：

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

准备测试数据：

-- 建库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.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: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)

查看该事务对应的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 ###   @2='a' ### SET###   @1=1 ###   @2='aaaaa'

从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: 0mysql> commit;Query OK, 0 rows affected (0.01 sec)

查看该事务对应的gtid：

SET @@SESSION.GTID_NEXT= 'a5e2c832-2291-11ed-9728-000c2933f83c:5';...';### INSERT INTO `cym`.`t1`### SET###   @1=3 ###   @2='ccccc' ### INSERT INTO `cym`.`t1`### SET###   @1=4 ###   @2='ddddd'

从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: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)

查看该事务对应的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 ###   @2='b' ### SET###   @1=2 ###   @2='bbbb2222' ...

从@@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'

生成的闪回文件如下：

[root@localhost MyFlash-master]# mysqlbinlog -vv dongshigohome1.flashback ;;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 'aaUEYw8BAAAAdwAAAHsAAAABAAQANS43LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAabppQRjEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAATZEKLI=';# 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_FBINLOG '5LcFYxMBAAAALwAAAKoAAAAAAG0AAAAAAAEAA2N5bQACdDEAAgMPAlAAAooKego=5LcFYx8BAAAAOQAAAOMAAAAAAG0AAAAAAAEAAgAC///8AgAAAAhiYmJiMjIyMvwCAAAAAWIfFOcm';### UPDATE `cym`.`t1`### WHERE###   @1=2 ###   @2='bbbb2222' ### SET###   @1=2 ###   @2='b' # at 227#220823 18:08:27 server id 1  end_log_pos 258 CRC32 0x73720c50  Xid = 16COMMIT;# 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_FBINLOG '664EYxMBAAAALwAAADEBAAAAAG0AAAAAAAEAA2N5bQACdDEAAgMPAlAAAsGMe+s=664EYyABAAAAOQAAAGoBAAAAAG0AAAAAAAEAAgAC//wDAAAABWNjY2Nj/AQAAAAFZGRkZGTFTfgV';### DELETE FROM `cym`.`t1`### WHERE###   @1=3 ###   @2='ccccc' ### DELETE FROM `cym`.`t1`### WHERE###   @1=4 ###   @2='ddddd' # at 362#220823 18:08:27 server id 1  end_log_pos 393 CRC32 0x73720c50  Xid = 16COMMIT;SET @@SESSION.GTID_NEXT= 'AUTOMATIC'  ;DELIMITER ;# End of log file;;

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

恢复数据并查看

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

查看数据：

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

可以看到除了事务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);

查看数据：

mysql> use testdb_restore_table;Database changedmysql> 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)

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

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

即将全量数据备份到/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: 0mysql> 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)

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

2.4：模拟误删除

误删除表t1：

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

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

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

[root@localhost tmp]# mysqlbinlog -vv /usr/local/mysql/log-bin.000007 | egrep 'aaaaa1' -B 30###   @2=34 # at 1811#220825 18:21:42 server id 1  end_log_pos 1842 CRC32 0xb38ce58a         Xid = 97COMMIT;# 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;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=0SET 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 ###   @2='aaaaa' ### SET###   @1=1 ###   @2='aaaaa1' --...

可以看到时间大概是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 ###   @2='aaaaa1' ### DELETE FROM `testdb_restore_table`.`t1`### WHERE###   @1=2 ###   @2='bbbbbb' ### DELETE FROM `testdb_restore_table`.`t1`

从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 -pEnter password:

恢复后查看数据：

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

可以看到恢复成功了。