mysql通过binlog做数据恢复

🕗 发布于 2024-09-23 00:13 mysql 数据库 binlog数据恢复

1 介绍

binlog（二进制日志）在 MySQL 中具有非常重要的作用。它记录了数据库的所有更改操作，主要用于数据恢复、复制和审计等方面。以下是 binlog 的主要作用：

1.数据恢复
binlog 可以用于恢复数据库中的数据。当数据库发生故障时，可以通过应用 binlog 来恢复数据到某个特定的时间点或事务
2.主从复制
binlog 是 MySQL 主从复制的基础。主服务器上的所有更改都会记录在 binlog 中，从服务器通过读取 binlog 来同步数据。这样可以实现数据的高可用性和负载均衡。
3.审计
binlog 记录了所有的数据更改操作，可以用来进行审计。例如，可以查看哪些用户在何时进行了哪些更改，这对于安全性和合规性非常重要。
4.故障诊断
binlog 可以帮助诊断数据库中的问题。通过查看 binlog 文件，可以了解数据库中发生了哪些具体的更改操作，从而定位问题原因。
5.数据迁移
binlog 可以用于数据迁移。通过读取 binlog 文件，可以在新的数据库实例上应用相同的更改，从而实现数据迁移。
6.数据一致性
binlog 可以保证数据的一致性。在分布式系统中，通过应用 binlog 可以确保多个节点之间的数据一致。
7.事务回滚
虽然 binlog 主要用于前向应用（即恢复到某个时间点），但在某些情况下也可以用于事务回滚。例如，在某些故障场景下，可以使用 binlog 来回滚部分事务。
8.数据备份
binlog 可以配合定期的全量备份来实现增量备份。通过结合全量备份和 binlog 文件，可以恢复到任意时间点的数据状态。

本文主要通过demo简单描述本地mysql（windows系统）如何配置和使用binlog做数据恢复。

2 演示

2.1 安装mysql

具体安装步骤可以参考我的上一篇文档。
https://blog.csdn.net/weixin_49702090/article/details/140555877

唯一差异是需要在 my.ini 配置文件中加上 binlog 的一些配置，以下是完整的 my.ini配置文件：

空行是为了给你们看方便，你们使用的时候最好不要有空行

[mysqld]
#设置3306端口
port=3306
#自定义设置mysql的安装目录，即解压mysql压缩包的目录
basedir=D:\\software\\1_mysql\\mysql-5.7.37-winx64
#自定义设置mysql数据库的数据存放目录
datadir=D:\\software\\1_mysql\\mysql-5.7.37-winx64\\data
#允许最大连接数
max_connections=200
#允许连接失败的次数，这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
#服务端使用的字符集默认为UTF-8
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB


#开启binlog，不开启的话以下不需要配置
#如果用于复制环境，每个服务器的 server-id 必须唯一
server-id=1
#指定二进制日志的存储位置和前缀
log-bin=D:\\software\\1_mysql\\binlog
#同步二进制日志到磁盘的频率，值为 0 表示不强制同步
sync_binlog=1
#设置二进制日志记录的格式为行格式
binlog_format=ROW
#单个二进制日志文件的最大大小为1GB
max_binlog_size=1G
#二进制日志文件保存 7 天后自动删除
expire_logs_days=7


[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口和默认字符集
port=3306
default-character-set=utf8mb4

2.2 重启mysql，通过命令验证binlog是否配置生效

SHOW VARIABLES LIKE ‘log_bin’;

在这里插入图片描述
值 ON 表示生效。

生效后会生成一些binlog文件在本地，这些文件策略就在my.ini配置文件中，比如存放在哪里，保留的时间策略等。我使用binlog比较久，下面是我本地生成的一些binlog文件：
在这里插入图片描述

2.3 模拟数据

模拟建库建表，并分批次插入一些数据，到时候用来测试恢复插入的某一批数据。
最终删除所有数据 delete from 表名

以下过程开始恢复数据！！！

2.4. 确定上面插入数据过程生成的binlog文件是哪个

通过生成的binlog文件的修改时间可以看到我在模拟数据的过程中，一些事务操作存在了 binlog.000009 这个文件下，以下操作我都基于binlog.000009 这个文件！！！第一次启用 binlog 应该只有一个 binlog 文件，所以比较好确定。

2.5 分析binlog文件，找到要恢复的那一次insert的起始位置和结束位置

这里有两种方式：

2.5.1 方式1，通过mysql命令直接查看起始&结束位置

mysql> show binlog events in ‘binlog.000009’ ;

在这里插入图片描述

可以通过 BEGIN 、COMMIT 看到，这一次操作在binlog中的起始位置和结束位置分别为： 1637 和 2385，记住这两个索引，恢复数据的时候我们仅恢复这一次的插入操作。

2.5.2 方式2，通过阅读本地生成的binlog文件查找起始&结束位置

binlog文件（binlog.000009）是二进制文件，不能直接查看，所以我们得将二进制文件转换为可阅读的文本文件（.txt）。

一、生成 txt 文件

重新打开一个DOS窗口，不要在mysql中执行。通过以下命令：
这里的路径根据你实际路径为准。

mysqlbinlog --no-defaults D:\software\1_mysql\binlog.000009 > D:\software\1_mysql\binlog000009.txt

可以检查下生成的txt文件是否存在。
在这里插入图片描述
二、分析 txt 文件

这里我就简单说一下关键的信息，太多了其实我也不懂，可以网上多搜搜。

在这里插入图片描述

这两种方式看起来，我们要恢复的那一批数据的起始索引和结束索引都是：1637 和 2385

2.6 根据binlog生成sql备份文件

说直白点就是在 binlog000009 把我们要恢复的那一批数据根据起始索引和结束索引摘出来，单独输出到一个文件中（D:\software\1_mysql\output.sql），lg_test 是我这次的测试数据库库名，你们改一下不能写错了。

mysqlbinlog --no-defaults D:\software\1_mysql\binlog.000009 --start-position=1637 --stop-position=2385 -d lg_test > D:\software\1_mysql\output.sql

生成的output.sql文件如下：
在这里插入图片描述
如果我们通过第二种方式解析binlog文件的话，可以完全打开两个文件对比下，看到我们要回滚的那部分操作已经被摘出来了！

2.7 回滚

需要在mysql中操作，注意mysql中的斜杠是 / 不要写错了.

mysql> source D:/software/1_mysql/output.sql ;