前言

这段时间学习了林晓斌的《MySQL实战45讲》，其中说到了mysql支持将数据恢复到半个月内的任意一秒的状态，文章中只是说明了大致的原理与实现流程，缺少了实践环节。而且，此前团队产品经过了等保三级验收，其中包含团队需要有灾备演练与恢复相关的知识储备，与能力。因此，希望通过这篇文章来将数据库恢复的整个流程梳理一遍。

原理部分

一条更新语句在mysql内部的执行流程

下面是mysql的逻辑架构图

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。（详细内容可以拜读《MySQL实战45讲》）

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

更新流程中还涉及到两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）

redo log

InnoDB 特有的日志
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
redo log 是循环写的，空间固定会用完

补充：
redo log可以配置为一组4个文件，每个文件可以写入1GB的内容。那么redo log中总共可以写入4GB日志。从头开始写，写到末尾就又回到开头循环写。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，此时redo log的空间满了，这时候不能再执行新的更新，得先停下来把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

binlog

binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”
binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

tips
binlog启动后有三种模式：row、statement、mixed

区别：row格式文件比较大，statement比较小，row格式保存的是一行一行的数据，statement保存的是sql语句，mixed格式介于二者之间，statement容易丢数据，row格式则不会。为保证数据的准确性，同时节约空间，一般选择mixed模式

详细区别可以参考 Mysql Binlog三种格式详细介绍

update语句是如何将log分别记录到binlog 和 redolog中的

#创建 表T
mysql> create table T(ID int primary key, c int);

#向T 表插入数据
mysql> update T set c=c+1 where ID=2;

复制代码

上述更新语句，在mysql内部的流程为：

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的

最后三步看上去有点“绕”,将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”。

（此处可以思考一下为啥需要进行“两阶段提交”，而不是直接提交）

So,怎样让数据库恢复到误删之前的状态？

先说思路

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到误删表之前的那个时刻。

准备工作

1.数据库进行每日整库备份

主要是通过mysqldump命令（数据库导出）+ crontab（linux定时任务）来实现

具体操作这篇文章写的非常详细

在执行完mysqldump命令之后可以执行 mysql的flush logs 命令。这样binlog文件会创建一个新的文件，方便后续定位。

2.需要开启mysql的binlog功能。

编辑/etc/my.cnf文件，加入以下配置项（如果该文件不存在可以参考此处）

#配置启动打开binlog
log-bin=/var/lib/mysql/mysql-bin
#配置serverid
server-id=1
#设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format = mixed
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
##binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
复制代码

修改配置后需要重启mysql服务。

检查配置是否生效

#log_bin ON-打开，OFF-关闭
show variables like 'log_bin%'; 

#binlog 日志模式 STATEMENT、ROW、MIXED，此处应当设置为mixed
show variables like 'binlog_%'; 
复制代码

以下正式开始恢复

1.最近一次的备份文件导入到数据库中

（假设最近备份的文件为log_bak_20210706.sql）

#登陆数据库
mysql -uroot -p -Dlog_test  
#临时关闭binlog记录功能（打开也没有影响，就是后续去找binlog范围的时候，会有很多没用的记录）
mysql> set sql_log_bin=0;
#导入对应的sql备份文件
mysql> source  /xx/xx/xx/log_bak_20210706.sql 

复制代码

2.查询binlog，找到需要恢复到的时间节点

（假设20210706备份完成后，生成log_name为 mysql-bin.000006）

执行命令mysql> show binlog events in 'mysql-bin.000006';

返回以下内容：

对应字段含义为

Log_name:此条log存在那个文件中，从上面可以看出这些log皆存在与mysql_bin.000006文件中。 
Pos:log在bin-log中的开始位置 
Event_type:log的类型信息 
Server_id:可以查看配置中的server_id,表示log是那个服务器产生 
End_log_pos：log在bin-log中的结束位置 
Info:log的一些备注信息，可以直观的看出进行了什么操作 
复制代码

如果想要恢复到执行delete from sys_wx where open_id = '00007 操作前，则查询到对应的log开始为4，结束为止为933（933-1144为同一个事物）