MySQL binlog 作用介绍

发布时间： 更新时间： 总字数：4744 阅读时间：10m 作者：IP：上海 网址

MySQL 的 binlog（二进制日志） 是数据库的核心组件之一，主要用于记录所有对数据库的修改操作

介绍

  • Binlog 是 MySQL 实现高可用、数据安全和可扩展性的基石，主要服务于数据复制、灾难恢复和审计

主从复制（Replication）

  • 作用：Binlog 是实现 MySQL 主从复制的核心，主库（Master）将修改操作记录到 binlog 中，从库（Slave）读取这些日志并重放（Replay），从而实现数据同步
  • 流程：
    1. 主库将数据变更（INSERT、UPDATE、DELETE 等）写入 binlog
    2. 从库通过 I/O 线程拉取主库的 binlog
    3. 从库的 SQL 线程解析并执行 binlog 中的操作，保持数据一致性

数据恢复（Point-in-Time Recovery）

  • 作用：结合全量备份和 binlog，可将数据库恢复到任意时间点
  • 场景：
    • 误删数据时，通过全量备份 + 重放 binlog 到误操作前的时间点
    • 硬件故障导致数据丢失后，恢复至最近可用状态
  • 工具：使用 mysqlbinlog 工具解析 binlog，提取特定时间段内的 SQL 操作

记录数据变更

  • 所有 修改数据的操作（DML：INSERT/UPDATE/DELETE）
    • update 修改数据与原数据相同时，更新不生效，也不会产生 binlog
  • 部分 修改结构的操作（DDL：CREATE/ALTER/DROP）
  • 不记录 不修改数据的操作（如 SELECT）

审计与数据分析

  • 审计：通过解析 binlog，追踪数据库的历史操作，满足合规性要求
  • 数据分析：分析数据变更模式（如高频更新表）

与其他日志的协作

  • 与 Redo Log 的区别：
    • Binlog：MySQL 服务层日志，记录逻辑操作，用于复制和恢复
    • Redo Log：InnoDB 引擎层日志，记录物理页修改，用于崩溃恢复
  • 两阶段提交：事务提交时，InnoDB 先写 redo log（Prepare），再写 binlog，最后提交 redo log（Commit），确保数据一致性

binlog 的格式

  • STATEMENT：基于 SQL 语句的复制（statement-based replication, SBR）
    • 记录原始 SQL 语句（节省空间，但可能因函数/触发器导致主从不一致）
  • ROW：基于行的复制（row-based replication, RBR），MySQL innodb 默认 binlog 格式
    • 记录每行数据的变化（更安全，兼容性更好；缺点是会产生大量的日志，如 alter table 的时日志会暴涨）
  • MIXED：混合模式复制（mixed-based replication, MBR）
    • 混合模式，自动选择 STATEMENT 或 ROW

合理配置 binlog 格式（推荐 ROW）和定期清理策略，可平衡性能与存储成本。

配置与管理

  • 启用 binlog
ini 
# my.cnf 配置示例
[mysqld]
server_id = 1                        # 主从复制需唯一

expire_logs_days = 7                 # 自动清理 7 天前的日志
binlog_format = ROW                  # 推荐使用 ROW 格式
# log_bin = /var/lib/mysql/mysql-bin # 启用 binlog
log-bin=mysql-bin                    # 二进制日志文件，文件名后缀为.00000*）
log-bin-index=mysql-bin.index        # 二进制日志索引文件
max_binlog_size          = 1G        # 最大和默认值是 1G
sync-binlog              = 1

# 只记录指定的库
#binlog-do-db=db_name
# 不记录指定的库
#binlog-ignore-db=db_name
  • 常用命令：
    • 查看 binlog 列表：SHOW BINARY LOGS;
    • 删除旧日志：PURGE BINARY LOGS BEFORE '2023-01-01';
    • 查看日志内容：mysqlbinlog mysql-bin.000001

binlog 常用命令

# 是否启用binlog日志
show variables like 'log_bin';

# 查看 log 类型
SHOW VARIABLES LIKE '%log_bin%';

# 查询 expire_logs_days 命令
SHOW VARIABLES LIKE 'expire_logs_days';
set global expire_logs_days=60;

查看 binlog 日志信息

# 查看本节点 binlog 的文件信息
show binary logs;

# 查看正在写入的二进制文件，及当前position
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1024 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.08 sec)

刷新 binlog

mysql > flush binary logs;

mysql> flush logs;
Query OK, 0 rows affected (0.12 sec)

mysql>  show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       202 | No        |
| mysql-bin.000002 |      1024 | No        |
| mysql-bin.000003 |       128 | No        |
+------------------+-----------+-----------+
3 rows in set (0.07 sec)

查看 binlog 的详细信息

# 查询binlog日志
mysql> show binlog events;

# 查询 mysql-bin.0000xx
mysql> show binlog events in 'mysql-bin.0000xx';

# 查询 mysql-bin.0000xx，从pos点 1024 开始查起：
mysql> show binlog events in 'mysql-bin.0000xx' from 1024;

# 查询 mysql-bin.0000xx，从pos点 1024 开始查起，查询10条
mysql> show binlog events in 'mysql-bin.0000xx' from 1024 limit 10;

# 查询 mysql-bin.0000xx，从pos点 1024 开始查起，偏移2行，查询10条
mysql> show binlog events in 'mysql-bin.0000xx' from 1024 limit 2,10;

binlog 相关参数

  • binlog_chache_size: 默认 32768
  • max_binlog_cache_size
  • sync_binlog 控制 binlog 写入磁盘
    • sync_binlog = 0 时，表示 innodb 不会主动控制将 binlog 落盘，innodb 仅仅会将 binlog 写入到 OS Cache 中，至于什么时间将 binlog 刷入磁盘中完全依赖于操作系统
    • sync_binlog = 1 时，表示事物 commit 时将 binlog 落盘（推荐）
    • sync_binlog=N 当 N 大于 1 时，表示开启 组提交（group commit），如 N=5，那 MySQL 就会等收集 5 个 binlog 后再将这 5 个 binlog 同步到磁盘上
  • binlog_rows_query_log_events=ON 启用，可以显示查询日志
mysql> show variables like '%binlog_cache%';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| binlog_cache_size     | 32768                |
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
2 rows in set (0.00 sec)

relaylog

在从节点上，内容和 binlog 一致，是从主节点同步过来的

mysql > show slave status;
...
               Relay_Log_File: relay-bin.xxxxxx
                Relay_Log_Pos: 1024
        Relay_Master_Log_File: mysql-bin.xxxxxx
...

mysql > show relaylog events in 'relay-bin.xxxxxx' from 1024 limit 10\G;

二阶段提交

MySQL 事务在提交的时候，会记录 事务日志（redo log） 和 二进制日志（binlog）

mysql 二阶段提交

图片参考

MySQL 将 redo log 的写入拆成了两个步骤：prepare 和 commit，即两阶段提交。两阶段提交的目的是为了让两份日志之间的逻辑一致。

binlog 分析工具

mysqlbinlog

安装

apt install mariadb-server
# or
apt install mysql-server

help

mysqlbinlog--help ...

常用参数：

  • --start-position=1024 起始 pos 点
  • --stop-position=2048 结束 pos 点
  • --start-datetime="2015-05-01 00:00:00" 起始时间点
  • --stop-datetime="2015-05-01 00:00:00" 结束时间点
  • --database=test 指定只恢复 test 数据库

常用命令

# 查看混合模式的二进制文件，默认使用 base64 编码
mysqlbinlog mysql-bin.001024 > mysql-bin.001024.sql

# 将 base64 转换成 sql
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.001024 > mysql-bin.001024.sql

mysqlbinlog --start-position='4'  --stop-position='100' mysqlbinlog.001024 > 001024.log;
mysqlbinlog --start-datetime='2015-05-01 00:00:00' mysqlbinlog.001024 > 001024.log;

binlog 分析

  • 针对基于 binlog 文件和位置
# 统计操作频繁的表，其中 column 命令来自 apt install bsdmainutils
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.001024 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}' | column -t | sort -k3nr | more
  • 针对基于 gtid 的？统计 insert、update、delete 执行语句以及每个表执行次数
$ mysqlbinlog --base64-output=DECODE-ROWS -v /usr/local/mnt/mysql/log/mysql-bin.xxxxxx  > xxxxxx.sql

$ awk '
/INSERT INTO `/ {match($0, /`([^`]*)`\.`([^`]*)`/, arr); print "INSERT\t" arr[1] "." arr[2]}
/UPDATE `/ {match($0, /`([^`]*)`\.`([^`]*)`/, arr); print "UPDATE\t" arr[1] "." arr[2]}
/DELETE FROM `/ {match($0, /`([^`]*)`\.`([^`]*)`/, arr); print "DELETE\t" arr[1] "." arr[2]}
' xxxxxx.sql | sort | uniq -c | awk '{print $2, $3, $1}' OFS='\t'

analysis_binlog

git clone https://gitee.com/mo-shan/analysis_binlog.git
cd analysis_binlog

# 将这里的mysqlbinlog_path改成mysqlbinlog工具的绝对路径,否则可能会因版本太低导致错误
sed -i 's#^mysqlbinlog="/data/mysql/base/bin/mysqlbinlog"#mysqlbinlog=\"/mysqlbinlog_path\"#g' bin/analysis_binlog

# 将这里的analysis_binlog_path改成analysis_binlog的家目录的绝对路径
sed -i 's#^work_dir=.*#work_dir=\"/analysis_binlog_path\"#g' bin/analysis_binlog

# 赋权
chmod +x bin/analysis_binlog
echo "export PATH=$(pwd)/bin:${PATH}" >> ${HOME}/.bashrc

# 统计操作表 SQL 类型，如 select、update、delete、insert
# -w : 指定并行数, 当需要分析多个binlog文件时该参数有效, 默认是1
# -t : 指定显示结果的格式/内容, 供选选项有"detail|simple"
# -s : 指定排序规则, 供选选项有"insert|update|delete"
analysis_binlog -bfile=/path-to/mysql-bin.000xxx,/path-to/mysql-bin.000xxy -w=2 -t=simple

统计结果在 /<path-to>/analysis_binlog/res/mysql-bin.000xxx.res 中

F&Q

binlog 太大占用大量磁盘空间的解决方法

  • binlog 会日渐增大，占用很大的磁盘空间，因此，要对 binlog 使用正确安全的方法清理掉一部分没用的日志

帮助命令：

mysql> help purge
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

The binary log is a set of files that contain information about data
modifications made by the MySQL server. The log consists of a set of
binary log files, plus an index file (see
http://dev.mysql.com/doc/refman/5.7/en/binary-log.html).

The PURGE BINARY LOGS statement deletes all the binary log files listed
in the log index file prior to the specified log file name or date.
BINARY and MASTER are synonyms. Deleted log files also are removed from
the list recorded in the index file, so that the given log file becomes
the first in the list.

This statement has no effect if the server was not started with the
--log-bin option to enable binary logging.

URL: http://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
# 0 表示永不过期
mysql> show variables like '%expire_logs_days%';
  • 解决方式：设置保留 7 天
mysql> set global expire_logs_days=7;

# 永久生效，在 my.cnf 中设置
[mysqld]
expire_logs_days = 7
  • 手动清理日志
mysql> PURGE BINARY LOGS TO 'binlog.1000xxx'
mysql> PURGE BINARY LOGS TO 'binlog.000001';

# 删除到指定 binlog
mysql> PURGE BINARY LOGS BEFORE '2015-06-22 12:00:00';

# 删除3天前的日志
mysql> PURGE BINARY LOGS BEFORE DATE_SUB( NOW(), INTERVAL 3 DAY);

实战，查看主库和从库正在使用的 binlog 是哪个文件

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000049
         Position: 763545655
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.20.22
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000049
          Read_Master_Log_Pos: 763860016
               Relay_Log_File: gse-2-relay-bin.000142
                Relay_Log_Pos: 763859782
        Relay_Master_Log_File: mysql-bin.000049
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
             Master_Server_Id: 1
                  Master_UUID: fe29dfe3-d853-11e9-8eda-6c92bf9969ab
             Master_Info_File: ../mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
...
1 row in set (0.01 sec)
  • 清理方法一：删除指定日期以前的日志索引中 binlog 日志文件
purge master logs before '2018-12-01 17:20:00';
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);  # 删除10天前日志
35 2 * * * mysql -e "PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);" >/dev/null 2>&1
  • 清理方法二：删除指定日志文件的日志索引中 binlog 日志文件
purge master logs to'mysql-bin.000048';

注意：

  • 时间和文件名一定不可以写错，尤其是时间中的年和文件名中的序号，以防不小心将正在使用的 binlog 删除！！！
  • 切勿删除正在使用的 binlog！！！

自动清理 binlog

使用如下方法查询当前 binlog 的过期时间，若为 0 表示不过期

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

使用如下方法设置binlog过期时间，设置 30 表示 30 天后自动清理之前的过期日志

mysql> set global expire_logs_days = 30;

注意：

  • 过期时间设置的要适当，对于主从复制，要看从库的延迟决定过期时间，避免主库 binlog 还未传到从库便因过期而删除，导致主从不一致！

或通过配置文件配置

vim /etc/my.cnf
expire_logs_days = x  # binlog 自动删除的天数。默认值为0，表示`没有自动删除`

mysqlbinlog: unknown variable ‘default-character-set=utf8’

mysqlbinlog --no-defaults mysql-bin.000xxx

或修改 mysql 的字符集并重启服务

# /etc/my.cnf

[mysqld]
character-set-server = utf8
最新评论
本文总阅读量 次 本站总访问量 次 本站总访客数 人