MySQL数据恢复（binlog）

[TOC]

# 查看是否开启 binlog 日志

连接到 MySQL，输入 `show variables like '%log_bin%';` 查看是否开启了二进制日志

![](https://shuxiaoyuan-201812.oss-cn-shanghai.aliyuncs.com/PicGo202108221502703.png)

如果未开启，自己去开启，略

# 查看 binlog 日志文件列表

`show master logs;`

![](https://shuxiaoyuan-201812.oss-cn-shanghai.aliyuncs.com/PicGo202108221513058.png)

# 查看最后一个 binlog 日志的编号名称及其最后一个操作事件 pos 结束点的值
`SHOW MASTER STATUS;`

![](https://shuxiaoyuan-201812.oss-cn-shanghai.aliyuncs.com/PicGo202108221517978.png)

# 生成一个新的 binlog 日志
`FLUSH LOGS;`

生成一个新的 `binlog` 气质很有必要，在你要恢复时，最好先生成一个新的 `binlog` 日志文件，这样，你接下来的所有操作记录都会在新的 `binlog` 日志文件里面，而不会影响到之前的 `binlog` 日志

每次数据库重启也会生成一个新的 `binlog` 日志

`mysqldump` 备份数据时加 `-F` 选项也会刷新 `binlog` 日志

![](https://shuxiaoyuan-201812.oss-cn-shanghai.aliyuncs.com/PicGo202108221519016.png)

# 清空所有 binlog 日志

`RESET MASTER;`

# 查看 binlog 文件内容
```
# 查看 binlog 全部信息，如果你的 binlog 很大，这样查看全部不是一个好的方法
SHOW BINLOG EVENTS IN 'binlog.000065';
```

查询结果如下图所示：

![](https://shuxiaoyuan-201812.oss-cn-shanghai.aliyuncs.com/PicGo202108221530151.png)

| 字段名  | 字段名解释  |
| ------------ | ------------ |
| log_name  | binlog 文件名  |
| pos  | 开始的 pos  |
| event_type | 事件类型 |
| server_id | 主机服务号 |
| end_log_pos | 结束的 pos |
| info | 信息 |

# 指定查询

通过 `SHOW BINLOG EVENTS IN 'binlog.000065';` 查看的是所有的，如果很大的时候，是灰常不方便的

指定 pos 起始位置开始查询
```
# 从 pos 为 124 处开始查询，跳过 2 行，显示 4 条，跟分页原理类似，其余不多叙述
SHOW BINLOG EVENTS IN 'binlog.000065' FROM 124 LIMIT 2,4;
```

查询结果如下图所示：

![](https://shuxiaoyuan-201812.oss-cn-shanghai.aliyuncs.com/PicGo202108221540296.png)

# 恢复

### 全量恢复

一般全量恢复需要保证你所有的 `binlog` 日志文件都在，而且恢复只能是依次恢复，根据 `binlog` 的 index 值从小到大依次恢复，这样恢复针对性太强，一般是建议在新机器上，安装一个全新的 MySQL 后执行全量的恢复。也有一个值得注意的地方就是，如果是最后你删库了，或者是怎么了，这个删库的信息也会记录在这个 `binlog` 日志上，所以你最后一个 `binlog` 不处理的话，就会发现，最后恢复出来还是空的

```
mysqlbinlog /var/lib/mysql/binlog.000001 | mysql -uroot -p
mysqlbinlog /var/lib/mysql/binlog.000002 | mysql -uroot -p
mysqlbinlog /var/lib/mysql/binlog.000003 | mysql -uroot -p
mysqlbinlog /var/lib/mysql/binlog.000004 | mysql -uroot -p
mysqlbinlog /var/lib/mysql/binlog.000005 | mysql -uroot -p
mysqlbinlog /var/lib/mysql/binlog.000006 | mysql -uroot -p
……
```

### 根据 pos 恢复

通过上述个各种分析，如果你能大致判断出 pos 基于什么范围的话，可以用这个

```
# 从 pos 124 到最后
mysqlbinlog /var/lib/mysql_back/binlog.000001 --start_position=124 | mysql -uroot -p

# 从 pos 124 到 6699
mysqlbinlog /var/lib/mysql_back/binlog.000001 --start_position=124 --stop_position=6699 | mysql -uroot -p
```

### 根据时间恢复

pos 需要分析语句，查看事务开始和结束的 pos，查看数写入是在哪一个 pos 段见，如果不想这么麻烦，可以基于时间的恢复

```
# 有以下三种方式，一看就应该明白哈
# 指定开始时间 - 文件最后
# 指定文件开始 - 结束时间
# 指定开始时间 - 结束时间
mysqlbinlog --start-datetime="2021-08-09 00:00:00" --stop-datetime="2021-08-21 20:40:00"  /var/lib/mysql_back/binlog.000001 | mysql -uroot -p
```

### 恢复指定数据库

可以在恢复的时候，指定需要恢复的数据库，从而避免其他数据库的数据干扰

```
mysqlbinlog --start-datetime="2021-08-09 00:00:00" --stop-datetime="2021-08-21 20:40:00" --database=xxx /var/lib/mysql_back/binlog.000001 | mysql -uroot -proot -v xxx
```

说明：都是亲身试验过的，暂时就先记录这么多，后续补充