Mysql binlog 数据恢复

Mysql binlog 相关

MySQL 修改密码

sudo -s
mysql
use mysql

ALTER USER root@localhost  IDENTIFIED WITH caching_sha2_password BY '123456'

MySQL 允许 root 远程登录

mysql -u root -p
use mysql
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456'
flush privileges

开启 MySQL 的 binlog 日志

在my.inf主配置文件中直接添加三行

log-bin=/var/lib/mysql/mysql-bin
server-id=123454     #5.7以上需要

然后重启服务

查看数据库是否支持 binlog：

show variables like '%log_bin%'  

首先 binlog 需要配合定期备份一起恢复数据，如果数据库没有做定期备份，那只有 binlog 也是白瞎。

binlog 恢复实战一

先说明一下背景：一台 Mysql5.7 数据库，开启了 binlog，且设置了每日凌晨 1 点备份一次数据库。早上 10:30 数据库操作人员误 ##误更新/删除## 1W 条数据导致系统出现了严重问题。
11:00 关闭系统，停止运行，并开始着手数据恢复。

这里 ##误更新/删除/新增## 为简单的场景，比如更新或删除数据忘记添加 where 条件。我们可以通过找到当时时间点的 binlog，然后通过人工替换字符串的方式来恢复。

数据恢复步骤如下：

• 停止运行服务，并开始着手数据恢复。
• 数据库执行 flush logs; 将最新的 binlog 生成。
• 找到需要恢复的 binlog。
• 通过分析 binlog 生成恢复 sql。
• 执行 sql 进行恢复。

我们可以通过时间点来找到响应的 binlog 日志，比如我们找到的 binlog 日志名字为：binlog.000001

mysqlbinlog -D --database=release -v binlog.000001 > temp.sql

然后找到出事时的 binlog 日志，大约如下：

# at 599
BEGIN
### UPDATE `release`.`co_record`
### WHERE
###   @1='5c0751c4189d4cd5b9c7223ca0ad74689f4915b5421e4cc0ac50e1e6b640c787'
###   @2='2021-11-02 15:07:00'
### SET
###   @1='5c0751c4189d4cd5b9c7223ca0ad74689f4915b5421e4cc0ac50e1e6b640c787'
###   @2='2021-11-02 15:06:00'
# at 1923
COMMIT/*!*/;

需要对这种伪 sql 进行转换成恢复 sql。这个部分自行转换，转换好的 sql 应该长这个样子：

UPDATE `release`.`co_record` SET xxx='5c0751c4189d4cd5b9c7223ca0ad74689f4915b5421e4cc0ac50e1e6b640c787', xxx= '2021-11-02 15:06:00';

转换这个部分很烦，需要根据业务来灵活转换，但基本上就是字符串批量替换的事情。

binlog 恢复实战二

先说明一下背景：一台 Mysql5.7 数据库，开启了 binlog，且设置了每日凌晨 1 点备份一次数据库。早上 10:30 数据库操作人员 误更新/删除 1W 条数据导致系统出现了严重问题。
11:00 关闭系统，停止运行，并开始着手数据恢复。

这里 误更新/删除/新增 为复杂的场景，没有办法通过上一个办法恢复。这时候可以尝试全量恢复。

数据恢复步骤如下：

• 停止运行服务，并开始着手数据恢复。
• 数据库执行 flush logs; 将最新的 binlog 生成。
• 找到需要恢复的 binlog。
• 全量恢复今日凌晨1点备份的数据库。
• 恢复 binlog。

由于每天凌晨 1 点会备份数据，只要将包含凌晨 1 点的 binlog 和以后的 binlog 找到即可。比如我们找到 binlog.000001/binlog.000002/binlog.000003/binlog.000004 四个日志文件。
也许真实的场景只有一个或更多的 binlog 日志，但总结起来就分为四种日志：

• binlog 记录了 凌晨 1 点前的数据，结束于备份完成后
• binlog 记录了 备份完成后且不包含出事的记录
• binlog 记录了 出事时的记录
• binlog 记录了 出事后的记录且不包含出事的记录

其中 2 4 记录是最简单的，直接恢复就可以了。 1 3 需要倒出来处理一下才可以。

第一种情况的 binlog 包含了已经恢复的数据，当我们恢复了凌晨 1 点备份后，这份 binlog 我们就只需要恢复 1 点后的数据，所以我们要按照时间来过滤。
例子中的 933659 就是凌晨 1 点后第一个需要恢复的数据的位置

mysqlbinlog -D --database=release --start-position=933659 binlog.000001 | mysql -u root -p -v release

第二种情况和第四种情况就直接按照时间先后执行即可

mysqlbinlog -D --database=release  binlog.000002 | mysql -u root -p -v release

## 执行完 binlog.000003 后

mysqlbinlog -D --database=release  binlog.000004 | mysql -u root -p -v release

第三种情况是最难处理的，因为该 binlog 包含了一串错误的日志，我们需要将这部分记录去除，然后再恢复。我们这里是 误更新/删除/新增 1W 条数据。
这里就需要人工的将这部分数据的开始位置和结束位置找到，然后跳过这部分记录恢复。
比如我们这里例子中错误的记录是在 4500-67000 行之间，那么我们就需要分两步来恢复。

mysqlbinlog -D --database=release --stop-position=4500 binlog.000003 | mysql -u root -p -v release
mysqlbinlog -D --database=release --start-position=67000 binlog.000003 | mysql -u root -p -v release

其实恢复就是对数据库操作的重放，binlog 记录了所有数据库的操作，只要将错误和不要的 binlog 记录剔除出去，就可以恢复。

但是这里有一个问题，就是我们去除了误更新/删除/新增 1W 条数据的 binlog 记录，这个时候，其实后面的 binlog 恢复（10:30 以后的数据）就很容易出错。
因为用户是在错误的数据的基础上继续更新的，我们将错误的数据去除后，就和真实的数据对应不上了，所以很大情况下我们就只能恢复到 10:30 时的数据，再往后的数据很难找回。