Mysql-经典实战案例2数据误删如何恢复
目录
Mysql-经典实战案例（2）—数据误删如何恢复？
背景：
在数据库运维和开发过程中，误删数据堪称技术人最"心惊肉跳"的操作之一。一条不慎执行的DELETE语句，可能瞬间让重要数据消失无踪。传统的数据恢复往往依赖备份回滚，不仅操作复杂，还可能面临备份不及时的尴尬。为此，美团技术团队开源的MyFlash闪回工具应运而生——这款基于MySQL binlog解析的轻量级工具，无需全量备份即可通过逆向操作生成回滚SQL，为数据安全提供了灵活可靠的"后悔药"。本文将手把手带您实践MyFlash的部署与应用，让您掌握这项关键时刻能挽救危机的硬核技能。
通过使用myflash 工具恢复误删数据
myflash工具下载
一、安装
（务必要做！！否则回退会失败）
yum install -y gcc glib2 glib2-devel
unzip MyFlash-master.zip
cd MyFlash-master
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback确认安装
cd /MyFlash-master/binary
./flashback -h二、环境准备（误删行数据）
创建测试数据
create table emp
(
id INT(11),
name VARCHAR(25)
);
insert into emp values(1,'a')
insert into emp values(1,'b')误删数据
delete from emp where id = 1;
commit;此时表内已无数据
mysql> select * from emp;
Empty set (0.00 sec)三、恢复
查看当前binlog
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysqldata/my3306/log/mysql-bin
mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000003 | 194 | | | 1d6df993-f5e0-11ed-aa30-005056b388fd:1-1130 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)解析binlog
确认要恢复的gtid为1d6df993-f5e0-11ed-aa30-005056b388fd:1131（在delete操作之前）
[root@qiuyang1 binary]# mysqlbinlog -v /data/mysqldata/my3306/log/mysql-bin.000003 | egrep -i 'GTID_NEXT|DELETE FROM `test`.`emp`'
SET @@SESSION.GTID_NEXT= '1d6df993-f5e0-11ed-aa30-005056b388fd:1131'/*!*/;
### DELETE FROM `test`.`emp`
### DELETE FROM `test`.`emp`
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;使用flashback工具 生产闪回binlog文件
工具目录：
填写对应的数据库名、表名、要恢复的gtid
./flashback --binlogFileNames=/data/mysqldata/my3306/log/mysql-bin.000003 --databaseNames=test --tableNames=emp --sqlTypes=delete --include-gtids='1d6df993-f5e0-11ed-aa30-005056b388fd:1131'在当前目录会生成binlog_output_base.flashback文件
使用mysqlbinlog 生成sql文件
mysqlbinlog --skip-gtids binlog_output_base.flashback >flash.sql进入mysql数据库，应用该sql文件
mysql -uroot -p
source flash.sql验证
数据已回复
mysql> select * from emp;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 1 | b |
+------+------+
2 rows in set (0.00 sec)四、环境准备（误删表）
创建测试数据
反复执行insert，最后构建出一张百万级别的表
CREATE TABLE test2(id INT PRIMARY KEY AUTO_INCREMENT);
INSERT INTO test2() VALUES(),(),(),();
INSERT INTO test2(id) SELECT id+(SELECT COUNT(*) FROM test2) FROM test2;
mysql> select count(*) from test2;
+----------+
| count(*) |
+----------+
| 2097152 |误删表
模拟场景：由于where条件的输入失误，导致整表被删
delete from test2 where 1=1;
mysql> select count(*) from test2;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.09 sec)五、恢复
查看当前binlog
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysqldata/my3306/log/mysql-bin
mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000003 | 194 | | | 1d6df993-f5e0-11ed-aa30-005056b388fd:1-1130 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)解析binlog
确认要恢复的gtid为1d6df993-f5e0-11ed-aa30-005056b388fd:1136（在delete操作之前）
由于删除的行数过大，命令后面加上|more 查看在执行delete之前的gtid
[root@qiuyang1 binary]# mysqlbinlog -v /data/mysqldata/my3306/log/mysql-bin.000003 | egrep -i 'GTID_NEXT|DELETE FROM `test`.`test2`' |more
SET @@SESSION.GTID_NEXT= '1d6df993-f5e0-11ed-aa30-005056b388fd:1131'/*!*/;
SET @@SESSION.GTID_NEXT= '1d6df993-f5e0-11ed-aa30-005056b388fd:1132'/*!*/;
SET @@SESSION.GTID_NEXT= '1d6df993-f5e0-11ed-aa30-005056b388fd:1133'/*!*/;
SET @@SESSION.GTID_NEXT= '1d6df993-f5e0-11ed-aa30-005056b388fd:1134'/*!*/;
SET @@SESSION.GTID_NEXT= '1d6df993-f5e0-11ed-aa30-005056b388fd:1135'/*!*/;
SET @@SESSION.GTID_NEXT= '1d6df993-f5e0-11ed-aa30-005056b388fd:1136'/*!*/;
### DELETE FROM `test`.`test2`
### DELETE FROM `test`.`test2`
### DELETE FROM `test`.`test2`
### DELETE FROM `test`.`test2`
### DELETE FROM `test`.`test2`
### DELETE FROM `test`.`test2`使用flashback工具 生产闪回binlog文件
工具目录：
填写对应的数据库名、表名、要恢复的gtid
./flashback --binlogFileNames=/data/mysqldata/my3306/log/mysql-bin.000003 --databaseNames=test --tableNames=test2 --sqlTypes=delete --include-gtids='1d6df993-f5e0-11ed-aa30-005056b388fd:1136'在当前目录会生成binlog_output_base.flashback文件
使用mysqlbinlog 生成sql文件
mysqlbinlog --skip-gtids binlog_output_base.flashback >flash.sql进入mysql数据库，应用该sql文件
mysql -uroot -p
source flash.sql验证
mysql> select count(*) from test2;
+----------+
| count(*) |
+----------+
| 2097152 |