【MySQL 数据宝典】【磁盘结构】- 006 bin log 二进制日志的基本模式和参数设置

最新推荐文章于 2025-06-24 23:30:24 发布

黄金时代的架构之路

最新推荐文章于 2025-06-24 23:30:24 发布

阅读量1.3k

点赞数 35

CC 4.0 BY-SA版权

分类专栏： MySQL 数据宝典文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/fvdfsdafdsafs/article/details/138110423

一、基本概念

binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是不会记录SELECT和SHOW这类操作。

1.1 特点

binlog在MySQL的Server层实现(引擎共用)
binlog为逻辑日志,记录的是一条SQL语句的原始逻辑
- binlog不限制大小,追加写入,不会覆盖以前的日志.
- 默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。

开启Binlog日志有以下两个最重要的使用场景:

主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
数据恢复：通过mysqlbinlog工具来恢复数据。

二、日志的三种模式

2.1 ROW（row-based replication, RBR）

日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。而且不会出现某些特定情况下存储过程或function，以及trigger的调用和触发器无法被正确复制的问题。
缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。

2.2 STATMENT（statement-based replication, SBR）

记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）, slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。

优点：日志量小，减少磁盘IO，提升存储和恢复速度
缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

2.3 MIXED（mixed-based replication, MBR）

以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。
企业场景如何选择binlog的模式

如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式，Statement。
如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式。
如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row 模式；但是要注意，该模式的binlog日志量增长非常快.

跳过语句模式：默认情况下，MySQL复制使用的是语句模式（Statement mode），即主服务器上执行的SQL语句会被发送到从服务器执行。然而，对于某些存储过程，MySQL可能无法正确复制执行过程中的SQL语句，因为存储过程可能包含了诸如非确定性函数、临时表等语句，这些语句在从服务器上可能无法正确执行，从而导致复制的不一致性。

函数依赖：存储过程中可能会调用其他存储过程或自定义函数，而这些函数的结果可能会因为主服务器和从服务器上的数据不一致而产生不同的结果，导致数据不一致。

依赖外部数据：存储过程可能会依赖外部的数据或环境变量，而这些数据在主服务器和从服务器上可能不同，导致存储过程的执行结果不一致。

并发执行问题：如果多个客户端同时执行存储过程，可能会出现并发执行的问题，从而导致数据不一致。

三、 Binlog写入机制

3.1 binlog文件结构

MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。
- 比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。

3.2 binlog落盘策略

binlog 的写入顺序: binlog cache (write) -> OS cache -> (fsync) disk.

write表示: 写入文件系统缓存,fsync表示持久化到磁盘的时机
binlog刷数据到磁盘由参数sync_binlog进行配置
- sync_binlog=0 的时候，表示每次提交事务都只 write,不 fsync;
- sync_binlog=1 的时候，表示每次提交事务都会执行 fsync;
- sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

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