mysql数据误删后的数据回滚

第一步：下载MyFlash工具

# 创建文件夹
mkdir /back_data
cd /back_data
# 下载压缩包
wget https://codeload.github.com/Meituan-Dianping/MyFlash/zip/master 
# 安装编译相关软件
yum install gcc -y
yum install glib2 glib2-devel -y
# 解压缩包
yum -y install unzip
unzip master
# 进入软件目录
cd /back_data/MyFlash-master
# 编译
sh build.sh
#验证
cd binary
./flashback --help

如果显示

[root@localhost binary]# ll
总用量 7380
-rwxr-xr-x. 1 root root 87648 10月 31 14:20 flashback
-rwxr-xr-x. 1 root root 7463125 11月 5 2020 mysqlbinlog20160408
[root@localhost binary]# ./flashback --help
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(,)
[root@localhost binary]#

即安装成功!

第二步：开启binlog日志

#登录数据库 （一般或是mysql -uroot -p123456）
[root@localhost binary]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
#查询binlog日志是否开启
 mysql> show variables%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.01 sec)
mysql> SHOW VARIABLES LIKE '%binlog_row_image%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| binlog_row_image | FULL |
+------------------+-------+
1 row in set (0.00 sec)
mysql>
#开启binlog日志
#第一个：binlog_format=row 
[root@localhost ~]# cd /etc/
[root@localhost etc]# vim my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
server_id=1
#第二个：binlog_row_image=FULL。
#默认开启
#重启mysqld
[root@localhost etc]# systemctl restart mysqld

第三步：检查第二步

[root@localhost etc]# systemctl restart mysqld
[root@localhost etc]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW VARIABLES LIKE '%binlog_row_image%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| binlog_row_image | FULL |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------+
| log_bin | ON |
| log_bin_basename | /application/mysql/data/mysql-bin |
| log_bin_index | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------------------+
6 rows in set (0.00 sec)
mysql>

第四步：开始测试

基础用法

# 查看所有binglog日志
SHOW MASTER LOGS;
# 当前使用的日志
show master status;
# 查看日志记录
show binlog events in '日志文件名';

建立测试表

create database if EXISTS itcast;
use itcast;
create table tb_user(
	id int(11) not null,
	name varchar(50) not null,
	sex varchar(1),
	primary key (id)
)engine=innodb default charset=utf8;
insert into tb_user(id,name,sex) values(1,'Tom','1');
insert into tb_user(id,name,sex) values(2,'Trigger','0');
insert into tb_user(id,name,sex) values(3,'Dawn','1');

第五步：误删数据库

查看删库后的binlog

第六步：新建binglog，减少外来日志影响

之后立即flush logs; 生成新的binlog

由于我们执行 flush logs 命令新生了一个文件，所以我们执行的删除的命令应该在 binlog.000001 文件里面

查看具体的命令行

show binlog events in 'mysql-bin.000001';

得到数据恢复的起始位置为 120，结束位置为 1166，

mysqlbinlog辅助恢复

接下来使用 mysqlbinlog 命令执行 binlog 文件，恢复数据，命令如下：

mysqlbinlog -v /application/mysql-5.6.40/data/mysql-bin.000001 --start-position=120 --stop-position=1166 | mysql -uroot -p123456