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

Binlog（二进制日志）

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

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

  作用：

• 数据恢复：某些场景下的数据恢复工作需要借助 Bin log 完成
• 主从复制：在 master 端开启 Binlog ，Slave 端通过读取 Master 端的Binlog 实现数据同步

• 审计： 用户可以通过 Binlog 中的信息来进行审计，判断是否有对数据库进行注入攻击

  Binlog 在数据恢复中的应用

1.确定恢复点
2.提取相关事件
3.应用Binlog
4.验证恢复结果

---

开启Binlog

- 在 MySQL 配置文件中添加 log-bin
- log-bin 是日志文件的路径

---

关闭Binlog

 - 注释掉 log-bin ，或删除 log-bin 这一行
 - 然后添加一行 skip_log_bin

---

查看Binlog状态

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

---

查看是否开启 bin_log

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

 show golbal variables like 'log_bin';

---

查看Binlog 日志

 show binary logs;

---

常用配置

#启用二进制日志
#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 文件大小和保留时间

[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 ：指定数据库

命令行选项：

-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 内容

- 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 格式 - 无法完全避免所有主从同步问题
适用场景	主从复制中无复杂函数调用 - 对性能要求较高	- 关键业务场景，需确保数据一致性 - 存在不确定性的函数调用	- 默认选择，适用于大多数通用场景

修改当前会话日志格式

set session binlog_format = 'row';

修改全局日志格式

set global binlog_format = 'row';

---

解析二进制日志

通过偏移量定位

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

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

---

指定时间的解析

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

---

GTID 解析

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

---

解析单独一个库

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

---

解析加密的Binlog

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

---

Binlog清除

• 配置自动清除

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 之前的文件

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 控制

落盘频率

#查询配置
show global variables like "sync_binlog";

参数取值：

sync_binlog=0 禁用mysql服务将binlog同步到磁盘的功能，由操作系统控制binlog的刷盘，这种情况下性能会比较好，但是如果操作系统崩溃是会丢是部分事务，导致丢数据问题。
sync_binlog=1 表示每有一个事务都会同步一次到磁盘，这个是最安全的，但是如果随着事务增加，会导致写入磁盘频率增高，导致性能下降。
sync_binlog=N 表示有N个事务会同步一次到磁盘。

参数作用：

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