三大日志之一binlog

MySQL

1. 什么是 binlog

Binary log 是 MySQL server 层维护的重要二进制日志，用于记录数据库的所有更改操作。它记录了所有的 DDL 和 DML 语句（除了 select 和 show 外），并以事务的形式保存在磁盘中，以二进制的形式存储，不能直接使用 cat，tail 进行查看，可以使用官方自带工具 mysqlbinlog 或者 binlog2sql 进行查看。

binlog 日志主要用于数据恢复和主从复制。

binlog 日志默认是开启的，可以通过 show variables like '%log_bin%' 查看。如果为 OFF，开启方法如下：

[mysqld]
#开启binlog日志
log_bin=ON
#binlog日志的基本文件名
log_bin_basename=/var/lib/mysql/mysql-bin
#binlog文件的索引文件，管理所有binlog文件
log_bin_index=/var/lib/mysql/mysql-bin.index

#有关binlog其他的一些配置信息
#设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format = mixed
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m

上面两种方法都需要重启数据库配置才会生效。启用之后会在数据目录下看到两类文件：

一类是二进制日志索引文件，记录当前 binlog 文件列表，后缀名为.index，另一类是二进制日志文件，文件名后缀为.00000*

二进制日志文件不是只有一个文件，而是一组文件，当遇到以下3种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：

MySQL服务器停止或重启时。
使用 flush logs 命令。
当 binlog 文件大小超过 max_binlog_size 变量的值时。

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       157 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

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

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       201 | No        |
| binlog.000002 |       157 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

2. binlog 日志格式

在binlog 有三种格式：

Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

2.1. Statement

Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。（比如update user set name="张三" where id >1 and id <10000,假设被修改的数据有2000条，那么Row的日志量就是2000条，而Statement只是这条sql语句一条日志而已，所以Statement的日志量相对Row会少很多）

但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。

所以使用 Statement 格式会出现一些数据一致性问题。

2.2. Row

从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。

Row 格式的日志内容会非常清楚地记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

2.3. Mixed

从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。

在 Mixed 模式下，系统会自动判断该用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。

Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种

3. binlog 操作命令

# 查看当前服务器使用的biglog文件及大小
show binary logs;

# 查看最新一个binlog日志文件名称和Position
show master status;

# 事件查询命令
# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

# 查看 binlog 内容
show binlog events;

# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'master.000003';

# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=3; 

# 删除当前的binlog文件
reset master; 

# 删除slave的中继日志
reset slave;

# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';

# 删除指定日志文件
purge master logs to 'master.000003';

4. 查看 binlog 内容

在执行完flush logs 后，找一个表随便删除两条数据，然后我们看下 binlog 日志内容都有哪些

# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#250409 14:59:47 server id 1  end_log_pos 126 CRC32 0x76186609 	Start: binlog v 4, 
#server v 8.0.41 created 250409 14:59:47
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
4xr2Zw8BAAAAegAAAH4AAAABAAQAOC4wLjQxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAQlmGHY=
'/*!*/;
# at 126
#250409 14:59:47 server id 1  end_log_pos 157 CRC32 0xb09e7028 	Previous-GTIDs
# [empty]
# at 157
#250409 16:20:53 server id 1  end_log_pos 236 CRC32 0x0b8be272 	Anonymous_GTID	
#last_committed=0       sequence_number=1	rbr_only=yes	
#original_committed_timestamp=1744186854024213	
#immediate_commit_timestamp=1744186854024213	transaction_length=301

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1744186854024213 (2025-04-09 16:20:54.024213 CST)
# immediate_commit_timestamp=1744186854024213 (2025-04-09 16:20:54.024213 CST)
/*!80001 SET @@session.original_commit_timestamp=1744186854024213*//*!*/;
/*!80014 SET @@session.original_server_version=80041*//*!*/;
/*!80014 SET @@session.immediate_server_version=80041*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 236
#250409 16:20:53 server id 1  end_log_pos 314 CRC32 0x173000f0 	Query	thread_id=15	exec_time=1    error_code=0
SET TIMESTAMP=1744186853/*!*/;
SET @@session.pseudo_thread_id=15/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 314
#250409 16:20:53 server id 1  end_log_pos 383 CRC32 0x47927666 	Table_map: `employees`.`departments` mapped to number 111
# has_generated_invisible_primary_key=0
# at 383
#250409 16:20:53 server id 1  end_log_pos 427 CRC32 0x51c72595 	Delete_rows: table id 111 flags: STMT_END_F

BINLOG '
5S32ZxMBAAAARQAAAH8BAAAAAG8AAAAAAAMACWVtcGxveWVlcwALZGVwYXJ0bWVudHMAAv4PBP4Q
oAAAAgP8/wBmdpJH
5S32ZyABAAAALAAAAKsBAAAAAG8AAAAAAAEAAgAC/wAEZDAwNwJOQpUlx1E=
'/*!*/;
### DELETE FROM `employees`.`departments`
### WHERE
###   @1='d007' /* STRING(16) meta=65040 nullable=0 is_null=0 */
###   @2='NB' /* VARSTRING(160) meta=160 nullable=0 is_null=0 */
# at 427
#250409 16:20:53 server id 1  end_log_pos 458 CRC32 0x858b1e8e 	Xid = 156
COMMIT/*!*/;
# at 458
#250409 16:20:54 server id 1  end_log_pos 537 CRC32 0x6e5435ba 	Anonymous_GTID	
#last_committed=1       sequence_number=2	rbr_only=yes	
#original_committed_timestamp=1744186854028991	
#immediate_commit_timestamp=1744186854028991	transaction_length=303
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1744186854028991 (2025-04-09 16:20:54.028991 CST)
# immediate_commit_timestamp=1744186854028991 (2025-04-09 16:20:54.028991 CST)
/*!80001 SET @@session.original_commit_timestamp=1744186854028991*//*!*/;
/*!80014 SET @@session.original_server_version=80041*//*!*/;
/*!80014 SET @@session.immediate_server_version=80041*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 537
#250409 16:20:54 server id 1  end_log_pos 615 CRC32 0xba6bb200 	Query	thread_id=15	exec_time=0    error_code=0
SET TIMESTAMP=1744186854/*!*/;
BEGIN
/*!*/;
# at 615
#250409 16:20:54 server id 1  end_log_pos 684 CRC32 0x298ccac6 	Table_map: `employees`.`departments` mapped to number 111
# has_generated_invisible_primary_key=0
# at 684
#250409 16:20:54 server id 1  end_log_pos 730 CRC32 0xd66fbeb4 	Delete_rows: table id 111 flags: STMT_END_F

BINLOG '
5i32ZxMBAAAARQAAAKwCAAAAAG8AAAAAAAMACWVtcGxveWVlcwALZGVwYXJ0bWVudHMAAv4PBP4Q
oAAAAgP8/wDGyowp
5i32ZyABAAAALgAAANoCAAAAAG8AAAAAAAEAAgAC/wAEZDAxMAROQk5CtL5v1g==
'/*!*/;
### DELETE FROM `employees`.`departments`
### WHERE
###   @1='d010' /* STRING(16) meta=65040 nullable=0 is_null=0 */
###   @2='NBNB' /* VARSTRING(160) meta=160 nullable=0 is_null=0 */
# at 730
#250409 16:20:54 server id 1  end_log_pos 761 CRC32 0x1e473991 	Xid = 158
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

我们不就只删除了两条数据吗，怎么 binlog 输出的日志那么多？而且看到这密密麻麻的，一时间也看不懂。不急，我们来逐行分析分析。

想这一份二进制文件，我们可以分为四个部分去看

全局设置

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;  -- 启用伪从模式（兼容旧版本）
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE... */ -- 存储过程相关设置
DELIMITER /*!*/;                                       -- 修改语句分隔符

binlog 文件头

# at 4
#250409 14:59:47 server id 1  end_log_pos 126 CRC32 0x76186609 	Start: binlog v 4, 
#server v 8.0.41 created 250409 14:59:47
# Warning: this binlog is either in use or was not closed properly.
#二进制日志头（Base64编码的元数据）
BINLOG '   
4xr2Zw8BAAAAegAAAH4AAAABAAQAOC4wLjQxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAQlmGHY=
'/*!*/;

GTID 上下文

# at 126
#250409 14:59:47 server id 1  end_log_pos 157 CRC32 0xb09e7028
Previous-GTIDs # [empty]  -- 没有之前的全局事务ID

事务详情

# at 157
#250409 16:20:53 server id 1  end_log_pos 236 CRC32 0x0b8be272
Anonymous_GTID... rbr_only=yes  -- 匿名事务（未启用GTID），使用行复制模式(RBR)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  -- 事务隔离级别

# 事务元数据
original_commit_timestamp=1744186854024213 (2025-04-09 16:20:54.024213 CST)
Xid = 156  -- 事务ID

# 执行上下文设置（字符集、时区等）
SET TIMESTAMP=1744186853;
SET @@session.pseudo_thread_id=15...

# 具体操作
Table_map: `employees`.`departments` mapped to number 111
### DELETE FROM `employees`.`departments`
### WHERE
###   @1='d007'  -- 部门编号
###   @2='NB'    -- 部门名称
COMMIT

5. 如何删除 binlog

删除mysql的binlog日志有两种方法：自动删除和手动删除

自动删除

永久生效：修改mysql的配置文件my.cnf，添加binlog过期时间的配置项：expire_logs_days=30，然后重启mysql，这个有个致命的缺点就是需要重启mysql。

临时生效：进入mysql，用以下命令设置全局的参数：set global expire_logs_days=30; （上面的数字30是保留30天的意思。）

手动删除

可以直接删除binlog文件，但是可以通过mysql提供的工具来删除更安全，因为purge会更新mysql-bin.index中的条目，而直接删除的话，mysql-bin.index文件不会更新。mysql-bin.index的作用是加快查找binlog文件的速度。

（1）直接删除

找到binlog所在目录，用rm binglog直接删除

（2）通过mysql提供的工具来删除

删除之前可以先看一下purge的用法：

#删除所有binlog日志，新日志编号从头开始
mysql> RESET MASTER;
#删除mysql-bin.010之前所有日志
mysql> PURGE MASTER LOGS TO 'mysql-bin.010';
#删除2003-04-02 22:46:26之前产生的所有日志
mysql> PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26'

如果觉得文章对你有用，请随意赞赏

三大日志之一binlog

https://www.zhangyan1997.xyz/archives/san-da-ri-zhi-zhi-yi-binlog

作者

张颜

发布于

2025-06-26

更新于

2025-06-26

许可协议

CC BY 4.0

三大日志之一binlog

1. 什么是 binlog

2. binlog 日志格式

2.1. Statement

2.2. Row

2.3. Mixed

3. binlog 操作命令

4. 查看 binlog 内容

5. 如何删除 binlog

作者

发布于

更新于

许可协议

评论