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

目录

• • 一、默认状态
  • 二、如何检查 binlog 状态
  • 三、如何开启 binlog
  • • 3.1 临时开启（重启后失效）
    • 3.2 永久开启（需修改配置文件）
    • 3.3 验证是否开启成功
    • 3.4 查看 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
                     • 生产环境必须开启（主从复制/数据恢复依赖）

                     • server_id=1

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

                         • binlog_format = ROW

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

                               • expire_logs_days = 7

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

                                   • sync_binlog = 1

                                     • 作用：控制binlog刷盘频率

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

                                     • 可选值：

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

                                       • 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 功能。

                    整理完毕，完结撒花~ 🌻