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 时的数据,再往后的数据很难找回。