MySQL 开启 binlog 并利用日志恢复误删数据

前置准备：检查与开启 binlog

binlog（Binary Log）是 MySQL 的增量备份日志，记录了所有修改数据的 SQL 语句（如 INSERT、UPDATE、DELETE）。它是主从复制和数据恢复的基础。在进行恢复操作前，需确认该功能已开启。

连接 MySQL 服务后，执行以下 SQL 查看当前配置：

mysql> show variables like 'log_bin';

结果解读：

• OFF：表示未开启 binlog，此时无法通过日志恢复数据，需先进行配置。
• ON：表示已开启，可直接进行后续操作。

第 1 步：配置 binlog

若未开启，请编辑 MySQL 配置文件 /etc/my.cnf（具体路径视系统安装方式而定），在 [mysqld] 段落下添加以下配置：

server-id=1
log-bin=/var/lib/mysql/mysql-bin

注意：若为分布式集群环境，每个节点的 server-id 必须唯一；单节点测试时可设为 1。配置完成后需重启 MySQL 服务使生效。

第 2 步：验证配置生效

重启服务：

[root@localhost ~]# systemctl stop mysqld.service
[root@localhost ~]# systemctl start mysqld.service

再次登录 MySQL 执行查询：show variables like 'log_bin';。当返回值为 ON 时，即代表 binlog 已成功启用。

实验环境搭建

为了模拟真实的数据恢复场景，我们创建一个简单的数据库和表，并插入若干条初始数据，最后执行误删除操作。

第 3 步：创建测试库表及数据

执行以下 SQL 创建数据库 hbk 及测试表 t_delete_test：

CREATE DATABASE `hbk` DEFAULT CHARACTER SET utf8;
use hbk;
CREATE TABLE `t_delete_test` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用客户端工具或命令行向表中插入若干行测试数据，确保数据正常写入并触发 binlog 记录。

第 4 步：模拟误删除

假设因操作失误，需要删除部分符合条件的数据。在此场景中，删除 ID 小于等于 4 的记录，并提交事务：

mysql> delete from t_delete_test where id<=4;
Query OK, 4 rows affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

此时，业务侧看到的部分数据已经丢失，我们需要通过 binlog 将其找回。

数据恢复实战

第 5 步：定位 binlog 事件 Position

首先，找到包含建表语句到删除操作之间的 binlog 文件及其对应的位置点（Position）。可以通过以下命令列出指定日志文件的事件：

mysql > show binlog events in 'mysql-bin.000001'\G

在执行输出的过程中，观察每一行的 Pos 列。你需要识别出两个关键位置点：

1. 起始位置（Start Pos）：位于建表语句（Create Table）之后的下一个位置点。此处应恢复此前可能存在的历史数据，确保结构一致性。
2. 终止位置（Stop Pos）：位于 DELETE 操作开始之前的位置点。避免将删除语句本身也重新执行一遍。

如下图所示，可以看到在特定时间点发生了 Delete 操作，而在这之前的某个 Position（例如 704）之后是有效的插入/创建数据，而 Delete 操作发生在另一个较高的 Position（例如 3101）。我们需要提取的是从 704 到 3101 之间的日志片段。

第 6 步：执行数据恢复

退出 MySQL，在服务器终端下使用 mysqlbinlog 工具导出指定区间的日志，并直接管道输送给 MySQL 客户端执行。这相当于在本地重放了这段时间内的有效变更。

执行恢复命令（替换为你的实际路径和用户信息）：

[root@localhost mysql]# /usr/bin/mysqlbinlog  
  /var/lib/mysql/mysql-bin.000001 
  --start-position=704 
  --stop-position=3101 | mysql -uroot -p hbk

输入密码后，如果命令执行无报错，且查询数据库发现之前误删的数据重新出现，则代表恢复成功。

常用参数选项详解

• --start-position=<num> 指定恢复的起始字节偏移量（Position）。
• --stop-position=<num> 指定恢复的结束字节偏移量（Position）。
• --start-datetime="YYYY-MM-DD HH:MM:SS" 按时间戳定义起始恢复点。
• --stop-datetime="YYYY-MM-DD HH:MM:SS" 按时间戳定义停止恢复点。
• --database=<db_name> 仅解析指定数据库的日志（仅限本地 binlog，远程主库不受此限）。

补充说明：若需从远程 MySQL 服务器拉取 binlog 日志，可使用 -u, -p, -h 连接参数配合 --read-from-remote-server 选项，但在内网数据恢复场景中，通常直接读取本地磁盘上的 binlog 文件即可。

进阶：如何查看 Binlog 事件

如果需要进一步分析某个 binlog 文件的内容，可以使用 SQL 语法进行查询：

1. 查看当前最早的一个 binlog 文件名及内容：

   SHOW BINLOG EVENTS\G;

2. 查看指定文件 mysql-bin.000002 的所有事件：

   SHOW BINLOG EVENTS IN 'mysql-bin.000002'\G;

3. 从指定 Position（如 624）开始查看：

   SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 624\G;

4. 限制显示条数（如查看 10 条）：

   SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 624 LIMIT 10\G;

5. 跳过指定行数并查看后续数据：

   SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 624 LIMIT 2,10\G;

以上就是 MySQL 通过 binlog 开启与数据恢复的详细内容，更多关于 MySQL 日志管理与故障排查的资料请关注本站其它相关文章！