我们常常听人说，只要你愿意，MySQL 可以恢复至半个月甚至一个月以内的任何一个状态。网上也有很多删库跑路的段子。。。
那么今天松哥想和大家来聊一聊 MySQL 中的 binlog，来手把手教大家如何利用 binlog 来恢复 MySQL 中的数据，这样，以后要是不小心删库了，那也不用跑路了。
MySQL 中的日志比较重要的有 binlog（归档日志）、redo log（重做日志）以及 undo log，那么跟我们本文相关的主要是 binlog，另外两个日志松哥将来有空了再和大家详细介绍。
1. binlog
binlog 我们中文一般称作归档日志，如果大家看过松哥之前发的 MySQL 主从搭建，应该对这个日志有印象，当我们搭建 MySQL 主从的时候就离不开 binlog（传送门：MySQL8 主从复制踩坑指南）。
binlog 是 MySQL Server 层的日志，而不是存储引擎自带的日志，它记录了所有的 DDL 和 DML(不包含数据查询语句)语句，而且是以事件形式记录，还包含语句所执行的消耗的时间等，需要注意的是：
- binlog 是一种逻辑日志，他里边所记录的是一条 SQL 语句的原始逻辑，例如给某一个字段 +1，注意这个区别于 redo log 的物理日志（在某个数据页上做了什么修改）。
- binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。
- 一般来说，我们在配置 binlog 的时候，可以指定 binlog 文件的有效期，这样在到期后，日志文件会自动删除，这样避免占用较多存储空间。
- MySQL 主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从机通过 binlog 来同步数据，进而实现主机和从机的数据同步。
- MySQL 数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。
首先我们在 docker 中安装好 MySQL，然后进入到容器中，通过如下命令可以查看 binlog 是否开启：
文章图片
这个 OFF 就表示 binlog 是一个关闭状态，没有开启，接下来我们来开启 binlog。
开启 binlog 主要是修改 MySQL 的配置文件 mysqld.cnf，该文件在容器的
/etc/mysql/mysql.conf.d 目录下。
文章图片
针对该配置文件，我们做如下修改：
# 这个参数表示启用 binlog 功能，并指定 binlog 的存储目录
log-bin=javaboy_logbin# 设置一个 binlog 文件的最大字节
# 设置最大 100MB
max_binlog_size=104857600# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 7# binlog 日志只记录指定库的更新（配置主从复制的时候会用到）
#binlog-do-db=javaboy_db# binlog 日志不记录指定库的更新（配置主从复制的时候会用到）
#binlog-ignore-db=javaboy_no_db# 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
sync_binlog=0# 为当前服务取一个唯一的 id（MySQL5.7 之后需要配置）
server-id=1各项配置的含义松哥已经在注视中说明了。截图如下：
文章图片
配置完成后，执行如下命令重启 mysql 容器（mysql1 是我这里容器的名字）：
docker restart mysql1重启之后，再次执行
show variables like 'log_bin%';
即可看到 binlog 已经开启了。
文章图片
这里除了 log_bin 变量外，还有两个变量名也值得我们关注：
- log_bin_basename：这个是将来产生的 binlog 日志文件的名称前缀，换句话说，根据大家目前所看到的配置，将来产生的 binlog 日志文件名为
javaboy_logbin.xxx，这个文件中将会用来记录所有的 DDL 和 DML 语句事件。 - log_bin_index：这个是 binlog 的索引文件，保存了所有 binlog 的目录，因为 binlog 可能会有多个。我们可以来查看一下现在的
javaboy_logbin.index文件：
文章图片
可以看到，目前只有一个 logbin 文件。
3. 常见 binlog 操作 接下来我们再来介绍几个常见的 binlog 操作命令。
- 查看所有 binlog 日志
show master logs;
文章图片
可以看到，我这里目前只有一个日志文件，文件名为
javaboy_logbin.000001，File_size 表示这个文件占用的字节大小是 154。- 查看 master 状态
文章图片
这个时候可以看到最新的 binlog 日志文件名称以及最后一个操作事件的 Position 值（这个值有啥用，我们后面会给大家详细介绍）。
- 刷新 binlog
flush logs 命令来手动刷新 binlog，手动刷新 binlog 之后，就会产生一个新的 binlog 日志文件，接下来所有的 binlog 日志都将记录到新的文件中。如下：
文章图片
由上图可以看到，我们刷新日志之后，再通过
show master logs 去查看日志，发现日志文件已经多了一个新产生的了，然后再通过 show master status 去查看最新的日志文件信息，发现也已经变为 javaboy_logbin.000002。- 重置 binlog
reset master 可以重置 binlog 日志文件，让日志重新从 000001 开始记录，不过如果当前主机有一个或者多个从机在运行，那么该命令就运行不了（因为从机是通过 binlog 来实现数据库同步的，主机把 binlog 清空了，从机会报找不到 binlog 的错误）。
文章图片
- 查看 binlog
文章图片
没有看到任何有用的信息。
为了查看 binlog，MySQL 为我们提供了两个官方工具，我们一个一个来看，首先是
mysqlbinlog 命令，如下：
文章图片
【手把手教你玩 MySQL 删库不跑路，直接把 MySQL 的 binlog 玩溜！】虽然看起来乱糟糟的，不过仔细看着其实都有迹可循。因为我这里是一个新安装的数据库，里边只是创建了一个名为 javaboy 的库，然后创建了一个名为 user 的表加了两条数据，其他什么事情都没做，所以创建库的脚本我们其实能够从纷杂的文件中找到。
产生的日志文件中有一个 end_log_pos 是日志文件的 pos 点，这个将来在数据恢复的时候有用。
不过这种查看方式不够人性化，我们说 binlog 是按照事件来记录日志的，所以如果我们能够按照事件的方式查看日志，就会好很多，我们再来看看如下一个命令：
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
这个表示以事件的方式来查看 binlog，这里涉及到几个参数：
- log_name：可以指定要查看的 binlog 日志文件名，如果不指定的话，表示查看最早的 binlog 文件。
- pos：从哪个 pos 点开始查看，凡是 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于我们可以指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的开头开始查看。
- offset：这是是偏移量，不指定默认就是 0。
- row_count：查看多少行记录，不指定就是查看所有。
show binlog events in 'javaboy_logbin.000001';
文章图片
这下就清晰多了，我们可以看到之前的所有操作，例如：
- 在 Pos 219-322 之间创建了一个库。
- 在 Pos 387-537 之间创建了一张表。
- 在 Pos 677-780 之间添加了一条记录。
- ...
我先来说说我这个数据库目前的情况。
这是一个新安装的数据库，里边我新建了一个数据库名为 javaboy，javaboy 库中新建了一张表名为 user，user 中有两条记录，如下：
文章图片
现在假设我们定期（每周三凌晨三点）对数据库进行备份。
现在凌晨三点了，数据库自动备份开始了，我们通过如下命令将数据库备份成 SQL 脚本，如下：
mysqldump -uroot -p --flush-logs --lock-tables -B javaboy>/root/javaboy.bak.sql
文章图片
这里有几个参数跟大家解释下：
- -u、-p 这两个就不用说了。
- --flush-logs：这个表示在导出之前先刷新 binlog，刷新 binlog 之后将会产生新的 binlog 文件，后续的操作都存在新的 binlog 中。
- --lock-tables：这个表示开始导出前，锁定所有表。需要注意的是当导出多个数据库时，--lock-tables 分别为每个数据库锁定表，因此这个选项不能保证导出文件中的表在数据库之间的逻辑一致性，不同数据库表的导出状态可以完全不同。
- -B：这个表示指定导出的数据库名称，如果使用
--all-databases或者-A代替-B表示导出所有的数据库。
这是星期三凌晨三点发生的事情。
接下来到了星期四早上，来上班了，一顿操作后，往数据库中又添加了两条操作，如下：
文章图片
接下来，小 X 今天跟领导吵架了很不爽，决定删除跑路：
文章图片
领导发现了大惊，当即要求立马恢复数据。这时候该你表现了。
首先，我们有星期三凌晨的备份文件，先用那个文件进行数据恢复：
文章图片
恢复之后，现在到星期三早上凌晨三点的数据有了。
从星期三早上凌晨三点到星期四的数据现在没了。
这个时候我们就要借助于 binlog 来恢复了。大家还记得，我们星期三凌晨三点执行备份的时候，用了一个参数叫做
--flush-logs，使用了该参数表示从备份那一刻起，新的 binlog 将产生在一个新的日志文件中，对于我们这里来说，新的 binlog 文件当然就是 javaboy_logbin.000002 了，我们去查看一下该文件：show binlog events in 'javaboy_logbin.000002';
我这里生成的该文件比较长，我截取其中一部分：
文章图片
可以看到，在 764-865 这个 Pos 中发生了删库跑路事件，那么我们只需要回放该文件将数据恢复到 764 这个位置即可。
由于
javaboy_logbin.000002 文件是在星期三凌晨三点备份之后产生的新文件，因此这个文件从起始到 764 这个 Pos 之间的操作，就是星期三凌晨三点到删库之前的操作了。那么我们来看下通过 binlog 来恢复数据的命令：
mysqlbinlog /var/lib/mysql/javaboy_logbin.000002 --stop-position=764 --database=javaboy | mysql -uroot -p那么这里涉及到两个参数：
- --stop-position=764 表示恢复到 764 这个 Pos，不指定的话就把按整个文件恢复了，如果按当前文件恢复的话，由于这个 binlog 文件中有删除数据库的语句，那么就会导致执行完该 binlog 之后，javaboy 库又被删除了。
- --database=javaboy 表示恢复 javaboy 这个库。
--start-position，这个表示起始的 Pos，不指定的话表示从头开始数据恢复。好啦，弄完之后，再来查看数据库：
文章图片
数据恢复啦～
注意：所有操作之前，记得该备份就备份（防止你操作错了又回不去），松哥为了省事上面省略了一些备份操作。
5. 小结 好啦，今天这篇文章主要是和小伙伴们分享了 MySQL 的 binlog 日志，并通过一个小案例来演示如何通过 binlog 实现数据库的删库恢复。好啦，感兴趣的小伙伴可以试试哦（别在生产库上试哦）～
推荐阅读
- @Transactional 导致报错 Lock wait timeout exceeded
- 同事乱用分页 MySQL 卡爆，我真是醉了...
- 查询 MySQL 字段注释的 5 种方法！
- MySQL数据查询太多会OOM吗（）
- 一致性视图是啥时候建立的（）
- MySQL夯实基础篇
- 亿级数据量系统优化思考