MySQL日志文件之二进制日志Binlog详解

admin管理员组

文章数量:1436987

MySQL日志文件之二进制日志Binlog详解

Binlog（二进制日志）

数据库管理中不可或缺的重要工具，记录了数据库中的所有变更操作，为数据恢复、主从复制和审计等关键功能提供了基础

• 是MySQL 服务器层维护的一种二进制格式的日志文件，记录了所有DDL（数据定义语句）和DML（数据操作语句），但不包括 select 和 show 等

---

作用：

• 数据恢复：某些场景下的数据恢复工作需要借助 Bin log 完成
• 主从复制：在 master 端开启 Binlog ，Slave 端通过读取 Master 端的Binlog 实现数据同步
• 审计： 用户可以通过 Binlog 中的信息来进行审计，判断是否有对数据库进行注入攻击

---

Binlog 在数据恢复中的应用

1.确定恢复点

2.提取相关事件

3.应用Binlog

4.验证恢复结果

---

开启Binlog

代码语言：powershell复制

- 在 MySQL 配置文件中添加 log-bin

- log-bin 是日志文件的路径

---

关闭Binlog

代码语言：powershell复制

 - 注释掉 log-bin ，或删除 log-bin 这一行

 - 然后添加一行 skip_log_bin

---

查看Binlog状态

代码语言：bash复制

 - 使用 show master status; 和 show binary logs; 命令查看Binlog状态和Binlog文件列表

---

查看是否开启 bin_log

• log-bin 的值为ON即为开启状态，OFF即为关闭状态

代码语言：sql复制

 show golbal variables like 'log_bin';

---

查看Binlog 日志

代码语言：sql复制

 show binary logs;

---

常用配置

代码语言：bash复制

#启用二进制日志
#log_bin 指定了二进制日志文件的前缀名称。日志文件将以这个前缀开始，后跟一个数字后缀
[mysqld]
log_bin = mysql-bin


#设置日志格式
#ROW每行变化都记录在日志中。
#statement 记录执行的SQL语句
#MIXED 结合ROW和STATEMENT，根据情况选择最合适的模式
[mysqld]
binlog_format = ROW | STATEMENT | MIXED


#指定日志文件大小，max_binlog_size指定了单个binlog 文件的最大尺寸。当达到这个大小时，MySQL将创建一个新的日志文件
[mysqld]
max_binlog_size = 100M


#二进制日志缓存大小，binlog_cache_size指定了事务日志的大小，用于ROW 格式的binlog
[mysqld]
binlog_cache_size = 32K



#二进制索引文件
#log_bin_index 指定了二进制日志索引文件的名称，该文件记录了所有当前活跃和旧的binlog文件的位置
[mysqld]
log_bin_index = mysql-bin.index



#同步二进制日志到磁盘
#binlog_sync 控制何时将事务日志从缓存同步到磁盘。1表示每次事务后都同步，这会降低性能但确保数据安全。
[mysqld]
binlog_sync = 1



#二进制日志过期天数

#expire_logs_days 指定了binlog文件在被自动删除前可以保留的天数。
[mysqld]
expire_logs_days = 7



#最大二进制日志文件数量
#max_binlog_files 指定了服务器将保留的最大binlog文件数量。当超过这个数量时，最旧的文件将被删除
[mysqld]
max_binlog_files = 100



#启用GTID模式
#gtid_mode 启用全局事务标识符（GTID），用于复制和恢复
[mysqld]
gtid_mode = ON



#启用自动位置同步
#log_slave_updates 允许从服务器将复制的更新记录到自己的binlog中。
[mysqld]
log_slave_updates = 1

---

设置Binlog大小和保留时间

• 在MySQL配置文件中使用 max_binlog_size 和 expire_logs_days 参数控制Binlog 文件大小和保留时间

代码语言：bash复制

[mysqld]
#设置binlog文件最大为500M
max_binlog_size = 500M



#设置binlog最大保存天数为14天
expire_logs_days = 14

---

Binlog 工具使用：mysqlbinlog

基本语法： mysqlbinlog options log_fle

常用选项：

-start-datetime, stop-datetime ：指定时间范围

-start-position -stop-position ：指定位置范围

-database ：指定数据库

命令行选项：

代码语言：bash复制

-h 或 --host: 指定连接的 MySQL 主机。

-P 或 --port: 指定 MySQL 服务器的端口号。

-u 或 --user: 指定连接 MySQL 服务器的用户名。

-p 或 --password: 提示用户输入连接 MySQL 服务器的密码。

--base64-output=[DECODED | ENCODED]: 控制输出是否以 Base64 编码格式。

--start-datetime: 指定开始解析的日期时间。

--stop-datetime: 指定结束解析的日期时间。

--start-position: 指定开始解析的日志位置。

--stop-position: 指定结束解析的日志位置。

--database: 指定要解析的数据库名。

--table: 指定要解析的表名。

--result-file: 将输出写入指定的文件。

--verbose: 显示详细的解析过程信息。

--skip-gtids: 跳过 GTID (Global Transaction ID) 信息的解析。

参数：

binlog-file: 指定要解析的二进制日志文件名。

其他参数，如数据库名、表名等，用于过滤解析的内容。

解析Binlog 内容

代码语言：bash复制

- mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001 | grep -i "update"

--base64-output=DECODE-ROWS: 此选项会将基于行的事件（row-based events）从Base64编码格式解码并转化为伪SQL形式的输出。

这意味着如果日志中存储的是基于行的变化数据，则这些变化会被翻译成类似于 INSERT、UPDATE 或 DELETE 的 SQL 语句。


-v (verbose): 提高输出的详细程度。当设置此标志时，除了显示实际发生的更改外，还会提供更多的上下文信息，比如表结构定义等

---

Binlog 日志格式

statement

基于SQL语句的复制，记录执行的SQL 语句，文件较小，但在某些情况下可能导致主从不一致

statement-based replication SBR

row

基于行的复制（RBR）。记录实际的数据行变化，文件较大，但能确保主从一致性

row-based replication RBR

mixed

混合模式复制（MBR）。默认使用 statement 格式，在特定情况下自动切换到 ROW 格式

mixed-based replication MBR 日志格式修改 在配置文件中修改，新增配置 binlog_format = row

特性/格式	STATEMENT	ROW	MIXED
定义	记录 SQL 语句本身	记录受影响行的具体变化	结合 STATEMENT 和 ROW 的特点，在特定条件下自动切换
优点	日志量小 - 减少 IO 开销 - 提升性能	数据一致性更高 - 避免因函数导致的主从不一致问题	继承了 STATEMENT 的高性能和 ROW 的高准确性 - 动态适应不同场景
缺点	可能导致主从数据不一致（如涉 NOW() 或 UUID() 等函数时）	日志量大，尤其是批量更新或删除时	实际应用较少，通常仍倾向于使用 ROW 格式 - 无法完全避免所有主从同步问题
适用场景	主从复制中无复杂函数调用 - 对性能要求较高	关键业务场景，需确保数据一致性 - 存在不确定性的函数调用	默认选择，适用于大多数通用场景

修改当前会话日志格式

代码语言：sql复制

set session binlog_format = 'row';

修改全局日志格式

代码语言：sql复制

set global binlog_format = 'row';

---

解析二进制日志

通过偏移量定位

代码语言：sql复制

show master status;    #查询偏移量，position参数的值

代码语言：powershell复制

mysqlbinlog --start-position=196 mysql-bin.000007 -vv >/data/01.sql

---

指定时间的解析

代码语言：powershell复制

mysqlbinlog --start-datetime="2025-02-20 00:00:00" mysql-bin.000007 > /data/02.sql

---

GTID 解析

代码语言：powershell复制

mysqlbinlog --include-gtids 'e7d171f1-f04c-11ef-8afe-000c297262ee:1-43' mysql-bin.000008 -vv > /data/03.sql

---

解析单独一个库

代码语言：powershell复制

mysqlbinlog --start-position=572 --stop-position=965 -d test1mysql-bin.000008 -vv > /data/04.sql

---

解析加密的Binlog

代码语言：powershell复制

mysqlbinlog --read-from-remote-server -uroot -padmin --start-position=196 mysql-bin.000009 -vv >/data/05.sql

---

Binlog清除

• 配置自动清除

代码语言：sql复制

set global expire_logs_days=0;

set global binlog_expire_logs_seconds = 604800;

flush logs;

#expire_logs_days和binlog_expire_logs_seconds不能同时设置

#expire_logs_days是自动清除天数

#binlog_expire_logs_seconds自动清除秒数

• 删除指定Binlog 之前的文件

代码语言：sql复制

show binary logs;

purge binary logs to 'mysql-bin.000002';

show binary logs;

---

注意事项

• 尽量采用自动清除的方式
• 确保要清除的Binlog日志所有从库都没有在使用
• 监控磁盘使用率

---

Binlog 落盘

• 过程

1. 创建Binlog 缓冲区

- MySQL 启动时会为 每个客户端线程 分配一个线程级（Thread-Level）的 Binlog 缓存，用于临时存储事务中的变更操作。

- **关键点**

- 每个线程独立管理自己的 Binlog 缓存（避免并发写入冲突）。

- 此外，还有一个全局 Binlog 缓冲区（内存队列），用于合并所有线程缓存的数据。

2. 有变更操作，先写Binlog 缓冲区

- **当有数据变更操作（如 INSERT/UPDATE/DELETE）时：**

- 变更会先写入当前线程的 线程级 Binlog 缓存。

- 事务提交时，该线程的 Binlog 缓存会被复制到 全局 Binlog 缓冲区。

- **关键点：**

- 未提交的事务不会进入全局缓冲区（保证原子性）。

- 全局缓冲区是所有线程共享的，用于后续批量写入磁盘。

3. 将Binlog 缓冲区的数据写入到磁盘

- 主线程（Main Thread） 负责将全局 Binlog 缓冲区的数据写入磁盘文件。

- 写入时机由参数 sync_binlog 控制

落盘频率

代码语言：sql复制

#查询配置

show global variables like "sync_binlog";

参数取值：

代码语言：bash复制

sync_binlog=0 禁用mysql服务将binlog同步到磁盘的功能，由操作系统控制binlog的刷盘，这种情况下性能会比较好，但是如果操作系统崩溃是会丢是部分事务，导致丢数据问题。

sync_binlog=1 表示每有一个事务都会同步一次到磁盘，这个是最安全的，但是如果随着事务增加，会导致写入磁盘频率增高，导致性能下降。

sync_binlog=N 表示有N个事务会同步一次到磁盘。

参数作用：

sync_binlog 是 MySQL 的一个重要参数，主要用于控制二进制日志（Binary Log,Binlog）与存储设备之间的同步频率。它的设置直接影响到 MySQL 系统的性能以及数据的安全性和一致性

本文标签： MySQL日志文件之二进制日志Binlog详解