数据库之binlog

原创已于 2025-08-23 16:40:07 修改 · 2.7k 阅读

37 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

面试很懂系列

于 2025-08-23 16:37:32 首次发布

这是一篇关于Binlog的解释与教程，并附上了博主在之前项目中使用在此总结的STAR回答策略

原文参见binlog

先来解释binlog是什么

二进制日志(binlog)是MySQL数据库的核心组件，承担着数据恢复，主从复制等关键功能。

再说binlog的核心原理与作用

本质

是MySQL服务器层面生成的二进制格式日志，记录了数据库中所有数据修改操作（DDL和DML）但不包含查询操作（SELECT、SHOW等）

知识复习：DDL：数据定义语言，用于定义或修改数据库结构，如表、索引、视图等，包括CREATE、ALTER、DROP；DML：数据操作语言：用于操作数据库中的数据，主要包括插入、更新、修改和删除操作，包括INSERT、UPDATE、DELETE。DML不会改变数据库中表的结构，只会改变表中的数据。

核心特点

逻辑日志：记录操作的逻辑内容，而非物理磁盘变化
追加写入：以事件（event）为单位追加到日志文件，满后自动轮转
非事务性：即使事务回滚，已写入binlog的内容也不会删除

应用场景

数据恢复：通过重放binlog恢复误删或误改的数据
主从复制：从库通过读取主库的binlog实现数据同步
审计追踪：分析binlog可追溯特定时间的数据变更操作

格式与区别

格式	特点	适用场景
STATEMENT	记录SQL语句原文	简单场景，日志体积小
ROW	记录行的变更细节（前后镜像）	数据一致性要求高的场景（如主从复制）
MIXED	自动切换前两种格式（默认ROW）	兼顾体积与一致性的通用场景

生产环境建议优先使用ROW格式，避免因存储过程、函数等导致的主从数据不一致问题。

binlog的配置与管理

1.配置基本参数

-- 启用binlog（必填）
log_bin=/var/lib/mysql/mysql-bin
-- 服务器ID（主从复制必填，唯一标识）
server-id = 1
-- 日志格式
binlog_format = ROW
-- 日志过期时间（天）
expire_logs_days = 7
-- 单个日志文件大小限制（默认1GB）
max_binlog_size = 500M
-- 不记录binlog的数据库
binlog-ignore-db = information_schema
-- 配置生效需重启MySQl服务，通过下面代码验证配置
SHOW VARIABLES LIKE 'LOG_BIN%'

2.日志文件组成

启用binlog后，会生成两类文件：

索引文件（index）：记录所有binlog文件的列表，如mysql-bin.index
日志文件（.000001,.000002...）：实际存储日志事件，按序号递增

3.常用管理命令

-- 查看当前binlog文件夹
SHOW BINARY LOGS;

-- 查看当前正在写入的binlog
SHOW MASTER STATUS;

-- 手动刷新binlog（生成新文件）
FLUSH LOGS;

-- 删除指定日志文件（谨慎操作）
PURGE BINARY LOGS TO 'mysql-bin.000005';

-- 删除7天前的日志文件
PURGE BINARY LOGS BEFORE DATE_SUB(CURRENT_DATE,INTERVAL 7 DAY);

BINLOG日志内容解析

1.查看binlog元数据

使用SHOW BINLOG EVENTS命令查看指定日志文件的事件列表：

-- 查看mysql-bin.000003前的10个事件
SHOW BINLOG EVENTS IN 'mysql-bin.000003'LIMIT 10;

结果包含关键信息：

Log_name:日志文件名
Pos:事件在文件中的起始位置
Events_type:事件类型（如Query、Write_rows、Delete_rows）
info:事件描述信息

2.使用mysqlbinlog工具解析

# 基本用法（ROW格式需加-v参数）
mysqlbinlog -v /var/lib/mysql/mysql-bin.000003

# 按时间范围解析
mysqlbinlog --start-datetime="2023-10-01 08:00:00" \
            --stop-datetime="2023-10-01 10:00:00" \
            -v /var/lib/mysql/mysql-bin.000003

# 按位置范围解析
mysqlbinlog --start-position=154 --stop-position=1230 \
            -v /var/lib/mysql/mysql-bin.000003

3.ROW格式日志解读

### DELETE FROM `test`.`users`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='张三' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=25 /* INT meta=0 nullable=1 is_null=0 */

基于 binlog 的实战操作

1. 数据恢复场景

当发生误删除或误更新时，可通过 binlog 恢复数据，步骤如下：

场景：误删 test 库 users 表中 id=1 的记录，需恢复该记录。

操作步骤：

找到删除操作所在的 binlog 文件和位置

mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/mysql-bin.000003 | grep -A 10 "DELETE FROM `test`.`users`"

假设找到删除操作位于位置 154-567

生成反向恢复 SQL（将 DELETE 转为 INSERT）

mysqlbinlog --start-position=154 --stop-position=567 \
  --database=test --skip-gtids \
  /var/lib/mysql/mysql-bin.000003 | \
  sed -n '/###/p' | sed 's/### //g;s/DELETE FROM/INSERT INTO/g;s/WHERE/VALUES(/;s/;/);/'

执行恢复 SQL

INSERT INTO `test`.`users` VALUES (1, '张三', 25);

2. 主从复制中的 binlog 应用

主库通过 binlog 向从库同步数据的核心流程：

主库启用 binlog，配置log_bin和server-id

从库配置server-id，通过CHANGE MASTER TO指定主库信息：

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_pass',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;