MySQL 学习（八）如何打开binlog日志

知识回顾：

binlog 是一个二进制格式的文件，记录了对 MySQL 数据库执行更改的所有 写操作，例如更改数据库表和更改内容的 SQL 语句都会记录到 binlog 里，但是不会记录 SELECT 和 SHOW 这类操作。

一、默认状态

在MySQL 5.7及以上版本中，binlog默认是关闭的，而在MySQL 8.0版本中，binlog默认是开启的。具体表现如下：

MySQL 5.7及以下版本：

• 默认关闭：安装后需要手动配置开启
• 原因：早期版本认为不是所有环境都需要二进制日志功能

MySQL 8.0版本：

• 默认开启：安装后自动启用binlog
• 原因：
  • 增强的数据安全考虑
  • 为Group Replication等新功能提供基础支持
  • 符合现代数据库的运维需求

补充：为什么版本间存在差异？

MySQL 8.0 改为默认开启 binlog 主要基于以下考虑：

1. 数据安全：即使没有配置主从复制，binlog也能用于时间点恢复。
2. 新功能依赖：Group Replication、InnoDB Cluster 等都需要 binlog。
3. 现代硬件性能：现代服务器通常有能力承担 binlog 的额外开销。

---

二、如何检查 binlog 状态

您可以通过以下命令查看当前 binlog 是否启用：

SHOW VARIABLES LIKE '%log_bin%';

---

三、如何开启 binlog

3.1 临时开启（重启后失效）

如果只是临时测试，可以通过 MySQL 命令行开启（无需重启服务）：

SET GLOBAL log_bin = ON;
SET GLOBAL binlog_format = 'ROW';  -- 推荐使用ROW格式
SET GLOBAL sync_binlog = 1;       -- 每次事务提交都刷盘

3.2 永久开启（需修改配置文件）

1）找到 MySQL 配置文件

• Linux：/etc/my.cnf 或 /etc/msql/my.cnf
• Windows：my.ini（通常在 MySQL 安装目录）

2）添加以下配置（在 [mysqld] 段）

[mysqld]
# 基础配置
# 启用binlog并设置文件名前缀
# 注意1：确保目录存在，否则会启动失败
# 注意2：指定目录需要用反斜杠/或双反斜杠\\
log_bin = D:/java/mysql-5.7.33-winx64/binlog/mysql-bin
# 服务器唯一标识（主从复制必需），默认值0
server_id=1
# 推荐使用ROW格式
binlog_format = ROW
# 自动清理7天前的日志
expire_logs_days = 7# 增强持久性配置
# 每次事务提交都刷盘(最安全但性能略有影响)
sync_binlog = 1
# 记录完整的行数据
binlog_row_image = FULL

1. log_bin = mysql-bin

   • 作用：启用二进制日志(binlog)并设置文件名前缀
   • 默认值：
     • MySQL 5.7及以下：OFF（默认关闭）
     • MySQL 8.0+：ON（默认开启，前缀为 binlog）
   • 说明：
     • 启用后生成文件如 mysql-bin.000001、mysql-bin.000002
     • 生产环境必须开启（主从复制/数据恢复依赖）

2. server_id=1

   • 作用：服务器唯一标识（主从复制必需）
   • 默认值：0（未设置，主从复制会失败）
   • 说明：
     • 主从环境中每个实例必须配置唯一ID
     • 单机可设为1，集群中需分配不同值（如1,2,3…）

3. binlog_format = ROW

   • 作用：指定binlog记录格式
   • 默认值：
     • MySQL 5.7+：ROW
     • 更早版本：STATEMENT
   • 可选值：
     • ROW：记录行数据变更（推荐，最安全）
     • STATEMENT：记录原始SQL（有主从不一致风险）
     • MIXED：混合模式

4. expire_logs_days = 7

   • 作用：自动清理N天前的binlog
   • 默认值：0（不自动清理）
   • 说明：
     • 与 binlog_expire_logs_seconds 冲突（8.0+优先用后者）
     • 生产环境建议设置（避免磁盘占满）

5. sync_binlog = 1

   • 作用：控制binlog刷盘频率

   • 默认值：0（由系统决定刷盘时机）

   • 可选值：

     • 0：依赖系统缓存，性能高但可能丢失事务
     • 1：每次事务提交都刷盘（最安全，性能略低）
     • N：每N次事务提交刷盘（平衡安全性与性能）

6. binlog_row_image = FULL

   • 作用：ROW格式下记录的行数据内容

   • 默认值：FULL（MySQL 5.6+）

   • 可选值：

     • FULL：记录修改前后的完整行数据（推荐）
     • MINIMAL：仅记录被修改的列+主键
     • NOBLOB：排除未修改的BLOB/TEXT列

3）重启 MySQL 服务

# Linux系统
sudo systemctl restart mysqld# Windows
通过服务管理器重启MySQL服务

3.3 验证是否开启成功

• 方式一：重启后查看指定目录下是否存在binlog

• 方式二：通过命令查看

-- 检查binlog状态
SHOW VARIABLES LIKE '%log_bin%';-- 查看当前binlog文件
SHOW BINARY LOGS;-- 查看binlog格式
SHOW VARIABLES LIKE 'binlog_format';

3.4 查看 binlog 内容

由于 binlog 是一个二进制文件，直接查看是无法阅读的：

我们可以使用 mysqlbinlog 工具（MySQL自带）来进行解析之后查看：

mysqlbinlog mysql-bin.000001 > binlog-0000001.sql

查看解析之后的内容如下：

---

四、高级配置建议

1）为 binlog 单独分配目录（避免占满系统盘）

log_bin = /var/lib/mysql-binlogs/mysql-bin

2）调整 binlog 大小（默认为1G）

max_binlog_size = 100M # 根据业务调整

3）启用 binlog 加密（MySQL 8.0+）

binlog_encryption = ON

---

五、注意事项

1. 权限要求：操作需要 MySQL 管理员权限。
2. 磁盘空间：开启后会增加磁盘写入量，确保有足够空间。
3. 性能影响：sync_binlog=1 会降低写入性能，但保证数据安全。
4. 版本差异：
   • MySQL 5.7需手动开启
   • MySQL 8.0默认已开启，只需确认配置

---

六、开启后的日常维护

-- 定期清理旧binlog（也可设置expire_logs_days自动清理）
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';-- 查看binlog占用空间
SHOW BINARY LOG STATUS;

开启 binlog 后，将获得：

• 数据恢复能力
• 主从复制基础
• 操作审计能力

建议所有生产环境都启用 binlog 功能。

整理完毕，完结撒花~ 🌻