mysql 数据恢复之 binlog 恢复初探

352 阅读4分钟

一、测试数据准备

1.1、创建表

CREATE TABLE `test_binlog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

1.2、新增数据

insert into test_binlog (`name`, `age`) values ('aa', 12);
insert into test_binlog (`name`, `age`) values ('bb', 23);
insert into test_binlog (`name`, `age`) values ('cc', 34);

初始数据.png

二、模拟实际操作

场景假设:

我们需要删除 id = 3 的记录,却把 id = 2 的记录也删除了,同时还新增了两条记录

delete from test_binlog where id = 2; (误删)
delete from test_binlog where id = 3; (需要删除)

insert into test_binlog (`name`, `age`) values ('dd', 45);
insert into test_binlog (`name`, `age`) values ('ee', 56);

误删后的数据.png

三、数据恢复

3.1、查看当前 binlog 日志文件

show master status;

我们记录好当前的日志文件信息 binlog.000490,恢复数据要用到

当前日志.png

3.2、刷新 binlog 日志文件

目的: 我们之后所要操做的内容都会被记录到新的日志文件中,我们只需要对旧的日志做恢复就行了

flush logs;

刷新后,我们再次查看 binlog 日志文件,会发现新增了一个编号

刷新日志.png

3.3、查看旧日志文件详细信息

show binlog events in 'binlog.000490';

日志详细.png

3.4、数据恢复

首先我们要明白,恢复数据的原理是什么?

因为我们对数据库的每个操作,都会记录到 binlog 日志文件中,所以,所谓的恢复数据,实际上就是丢掉不需要的误操作记录,重新执行一次正确的操作。

由前面的误删场景知道,id = 2 的记录是我们需要恢复的

3.4.1、查找数据有效位置

从日志详情中找到 id = 2 最后的有效信息操作的起始位置和结束位置;

红色框 表示误删的操作;

蓝色框 表示误删操作的起始位置(BEGIN对应的Pos值)和结束位置(COMMIT对应的End_log_pos值);

从误删的位置往前找操作日志,可以发现,我们 id = 2 记录最后的有效操作是新增操作(如果对该记录存在修改就是修改操作),找到其对应的起始位置和结束位置;

绿色框 表示该记录最后一次有效的操作;

橙色框 表示最后一次有效操作的起始位置(BEGIN对应的Pos值)和结束位置(COMMIT对应的End_log_pos值);

所以,我们实际要找的是 橙色框 对应的位置信息。

误删位置.png

3.4.2、恢复数据 --- 行数据

在这里我们使用 mysqlbinlog 工具对误删数据行进行恢复。

3.4.2.1、查找日志文件位置

首先我们需要找到 binlog 日志文件的位置,可以从mysql配置文件中查找。

我这里是小皮面板集成开发环境默认配置,日志文件位置再 data 目录。

show variables like 'log_bin%';

日志文件配置信息.png

日志文件位置.png

3.4.2.2、恢复数据

具体涉及到的目录根据自己的实际情况修改

1、打开 cmd 操作窗口

2、进入 mysqlbinlog 的工具目录

工具目录.png

3、执行恢复语句

mysqlbinlog --start-position=946 --stop-position=1280 ..\data\binlog.000490 | mysql -u root -p

恢复数据异常.png

此时你会发现存在 ERROR,进行解决:

方案一:修改 mysql 配置文件, 将 default-character-set=utf8mb4 修改为 character-set-server = utf8mb4 ;注意:修改后需要重启 mysql ;该方案可以再 mysql 服务不忙的时候进行,否则代价可能会比较大。

方案二:修改执行命令,追加 --no-defaults 选项

mysqlbinlog --no-defaults --start-position=946 --stop-position=1280 ..\data\binlog.000490 | mysql -u root -p

恢复数据成功.png

命令执行成功,我们重新查询数据,可以看到,id = 2 的数据已经成功恢复:

恢复后的数据.png

3.4.3、恢复数据 --- 表恢复

如果是误删了数据表,恢复逻辑也是一样的,核心点就是找到误操作之前最后一次有效的位置区间。

误删表的有效区间是:从创建表开始 到 删除表操作 之间的所有操作

四、附录

记录部分操作命令

// 刷新 binlog 日志文件,生成新的日志文件
flush logs;

// 查看当前最新的日志文件信息
show master status;

// 查看指定日志文件详细信息
show binlog events in 'binlog.000490';

// 恢复数据命令
mysqlbinlog --no-defaults --start-position=946 --stop-position=1280 ..\data\binlog.000490 | mysql -u root -p

至此,mysql 数据恢复之 binlog 恢复,就完成了