mysql误删数据恢复

1. Binlog 日志介绍
Binlog 是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录 SELECT 和 SHOW 这类操作。Binlog 日志是以事件形式记录，还包含语句所执行的消耗时间。开启 Binlog 日志有以下两个最重要的使用场景。

主从复制：在主库中开启 Binlog 功能，这样主库就可以把 Binlog 传递给从库，从库拿到 Binlog 后实现数据恢复达到主从数据一致性。

数据恢复：通过 mysqlbinlog 工具来恢复数据。

Binlog 文件名默认为“主机名_binlog-序列号”格式，例如 oak_binlog-000001，也可以在配置文件中指定名称。文件记录模式有 STATEMENT、ROW 和 MIXED 三种，具体含义如下。

ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在 slave 端对相同的数据进行修改。

优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。

缺点：批量操作，会产生大量的日志，尤其是 alter table 会让日志暴涨。

STATMENT（statement-based replication, SBR）：每一条被修改数据的 SQL 都会记录到 master 的 Binlog 中，slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。简称 SQL 语句复制。

优点：日志量小，减少磁盘 IO，提升存储和恢复速度

缺点：在某些情况下会导致主从数据不一致，比如 last_insert_id()、now()等函数。

MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择写入模式。

理念
首先使用binlog恢复数据，指的不是通过回滚binlog日志实现恢复的，而是指的是将删除的数据，通过一定的方式找回后，重新导入到该数据库，进行恢复的。

3、实战
3.1 Binlog 状态查看
show variables like 'log_bin';
我这里是已经开启的，如果未开启，请按照下面方法开启

3.2 开启 Binlog 功能
修改 /etc/my.cnf 或 my.ini 配置文件，在[mysqld]下面增加 log_bin=mysql-bin，重启 MySQL 服务。
log-bin=mysql-bin
binlog_format=ROW

3.3 再次查看
show variables like 'log_bin';

常用命令
show binary logs; //等价于show master logs;
show master status;
show binlog events;
show binlog events in 'mysql-bin.000001';

在服务器中也可以直接使用如下命令：
mysqlbinlog （参数选项）logfilename

参数选项：
-d :指定数据库名称，只列出指定的数据库相关操作。
-o : 忽略掉日志中的前 n 行命令。
-v :将事件重构成sql语句
-vv ：将事件重构成sql语句，并且输出注释信息

4、方案一

4.1、前提条件：
a:开启了binlog日志功能
b:之前的binlog日志未清除
c:此种方案可以实现binlog_format 为MIXED 和ROW的数据恢复

4.2、开始恢复
查看日志，大致确定下需要恢复的数据位于哪个日志文件中
show binary logs;

查看 binlog 日志事件并确定数据的开始和结束的offset

注意：我们恢复数据，这种方式不是通过删除的事件恢复的，是通过之前创建事件恢复的，所以我们要看清楚offset的位置

执行恢复：在 mysql 数据所在的文件下，也可以不在，如果不在请补全文件路径

//按指定时间恢复 
mysqlbinlog --no-defaults --start-datetime="2021-07-09 12:10:49" --stopdatetime="2021-07-09 12:17:37" mysql-binlog.000008 | mysql -uroot -proot
//按事件位置号恢复 
mysqlbinlog --no-defaults --start-position=234 --stop-position=1373 mysql-binlog.000008 | mysql -uroot -proot
#如果出现警告是密码直接输入不安全，可以先不输入密码，回车之后输入

--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间
--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地服务器的时间取值和上述一样
--start-position：从二进制日志中读取指定 position 事件位置作为开始。
--stop-position：从二进制日志中读取指定 position 事件位置作为事件截至
--database=lanebin 指定只恢复 lanebin 数据库(一台主机上往往有多个数据库，只限本地 log 日志)

不常用选项：
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取 binlog 日志
--read-from-remote-server 从某个 MySQL 服务器上读取 binlog 日志

5.方案二
针对于方案一的情况，如果数据量非常大的情况，恢复速度会很慢，过程有些困难。第二种方案就比较简洁，效率更快一些。
前置条件：
a.使用的binlog_format为ROW级别
开始操作：
查看日志，大致确定下需要恢复的数据位于哪个日志文件中
show binary logs;
查看日志并确定删除范围
show binlog events in 'binlog.000011';

使用命令导出日志记录
按照offset导出
mysqlbinlog --no-defaults --start-position=236 --stop-position=747 --base64-output=decode-rows --skip-gtids=true -v /var/lib/mysql/binlog.000011 | sed -n '/### DELETE FROM lanebin.user/,/COMMIT/p' > /root/mysql/resultsql-new.sql
按照时间导出
mysqlbinlog --no-defaults --start-datetime="2021-07-09 12:10:49" --stopdatetime="2021-07-09 12:17:37" --base64-output=decode-rows --skip-gtids=true -v /var/lib/mysql/binlog.000011 | sed -n '/### DELETE FROM lanebin.user/,/COMMIT/p' > /root/mysql/resultsql-new.sql
导出全部
mysqlbinlog --no-defaults --base64-output=decode-rows --skip-gtids=true -v /var/lib/mysql/binlog.000011 | sed -n '/### DELETE FROM lanebin.user/,/COMMIT/p' > /root/mysql/resultsql-new.sql

正则匹配，清除无效数据
cat resultsql-new.sql | sed -n '/###/p' | sed 's/### //g;s//*./,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.),/\1;/g' | sed 's/@[1-30]=//g' > t1.sql
也可以自己在编辑软件中，直接编辑resultsql-new.sql 将它修改成sql后，放到数据库中进行恢复数据
最终效果：