mysql binlog基本原理

作者：tracy_668

次浏览

次

2023-9-5

编辑推荐:

本文主要介绍了mysql binlog基本原理，希望对您的学习有所帮助。
本文来自于简书，由火龙果软件Linda编辑、推荐。

基于binlog的主从复制

Mysql 5.0以后，支持通过binary log(二进制日志)以支持主从复制。复制允许将来自一个MySQL数据库服务器（master) 的数据复制到一个或多个其他MySQL数据库服务器（slave)，以实现灾难恢复、水平扩展、统计分析、远程数据分发等功能。

二进制日志中存储的内容称之为事件，每一个数据库更新操作(Insert、Update、Delete，不包括Select)等都对应一个事件。

下面以mysql主从复制为例，讲解一个从库是如何从主库拉取binlog，并回放其中的event的完整流程。mysql主从复制的流程如下图所示：

主要分为3个步骤：

第一步：master在每次准备提交事务完成数据更新前，将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log event，简称event)

第二步：slave启动一个I/O线程来读取主库上binary log中的事件，并记录到slave自己的中继日志(relay log)中。

第三步：slave还会起动一个SQL线程，该线程从relay log中读取事件并在备库执行，从而实现备库数据的更新。

binlog的应用场景

读写分离

最典型的场景就是通过Mysql主从之间通过binlog复制来实现横向扩展，来实现读写分离。如下图所示：

在这种场景下：

有一个主库Master，所有的更新操作都在master上进行

同时会有多个Slave，每个Slave都连接到Master上，获取binlog在本地回放，实现数据复制。

在应用层面，需要对执行的sql进行判断。所有的更新操作都通过Master(Insert、Update、Delete等)，而查询操作(Select等)都在Slave上进行。由于存在多个slave，所以我们可以在slave之间做负载均衡。通常业务都会借助一些数据库中间件，如tddl、sharding-jdbc等来完成读写分离功能。

数据最终一致性

在实际开发中，我们经常会遇到一些需求，在数据库操作成功后，需要进行一些其他操作，如：发送一条消息到MQ中、更新缓存或者更新搜索引擎中的索引等。

如何保证数据库操作与这些行为的一致性，就成为一个难题。以数据库与redis缓存的一致性为例：操作数据库成功了，可能会更新redis失败；反之亦然。很难保证二者的完全一致。

遇到这种看似无解的问题，最好的办法是换一种思路去解决它：不要同时去更新数据库和其他组件，只是简单的更新数据库即可。如果数据库操作成功，必然会产生binlog。之后，我们通过一个组件，来模拟的mysql的slave，拉取并解析binlog中的信息。通过解析binlog的信息，去异步的更新缓存、索引或者发送MQ消息，保证数据库与其他组件中数据的最终一致。

在这里，我们将模拟slave的组件，统一称之为binlog同步组件。你并不需要自己编写这样的一个组件，已经有很多开源的实现，例如linkedin的databus，阿里巴巴的canal，美团点评的puma等。

当我们通过binlog同步组件完成数据一致性时，此时架构可能如下图所示：

缓存一致性

业务经常遇到的一个问题是，如何保证数据库中记录和缓存中数据的一致性。不妨换一种思路，只更新数据库，数据库更新成功后，通过拉取binlog来异步的更新缓存(通常是删除，让业务回源到数据库)。如果数据库更新失败，没有对应binlog，那么也不会去更新缓存，从而实现最终一致性。

可以看到，binlog是一把利器，可以保证数据库与与其他任何组件(es、mq、redis等)的最终一致。这是一种优雅的、通用的、无业务入侵的、彻底的解决方案。我们没有必要再单独的研究某一种其他组件如何与数据库保持最终一致，可以通过binlog来实现统一的解决方案。

在实际开发中，你可以简单的像上图那样，每个应用场景都模拟一个slave，各自连接到Mysql上去拉取binlog，master会给每个连接上来的slave一份完整的binlog拷贝，业务拿到各自的binlog之后进行消费，彼此之间互不影响。但是这样，有一些弊端，多个slave会给master带来一些额外管理上的开销，网卡流量也将翻倍的增长。

我们可以进行一些优化，之所以不同场景模拟多个slave来连接master获取同一份binlog，本质上要满足的是：一份binlog数据，同时提供给多个不同业务场景使用，彼此之间互不影响。

显然，消息中间件是一个很好的解决方案。现在很多主流的消息中间件，都支持consumer group的概念，如kafka、rocketmq等。同一个topic中的数据，可以由多个不同consumer group来消费，且不同的consumer group之间是相互隔离的，例如：当前消费到的位置(offset)。

因此，我们完全可以将binlog，统一都发送到MQ中，不同的应用场景使用不同的consumer group来消费，彼此之间互不影响。此时架构如下图所示：

通过这样方式，我们巧妙的达到了一份数据多个应用场景来使用。一般，一个Mysql实例中可能会创建多个库(Database)，通常我们会将一个库的binlog放到一个对应的MQ中的Topic中。

当将binlog发送到MQ中后，我们就可以利用MQ的一些高级特性了。例如binlog发送到MQ过快，消费方来不及消费，可以利用MQ的消息堆积能力进行流量削峰。还可以利用MQ的消息回溯功能，例如一个业务需要消费历史的binlog，此时MQ中如果还有保存，那么就可以直接进行回溯。

Binlog事件详解

Mysql已经经历了多个版本的发布，最新已经到8.x，然而目前企业中主流使用的还是Mysql 5.6或5.7。不同版本的Mysql中，binlog的格式和事件类型可能会有些细微的变化，不过暂时我们并不讨论这些细节。

总的来说，binlog文件中存储的内容称之为二进制事件，简称事件。我们的每一个数据库更新操作(Insert、Update、Delete等)，都会对应的一个事件。

从大的方面来说，binlog主要分为2种格式：

Statement模式：binlog中记录的就是我们执行的SQL；

Row模式：binlog记录的是每一行记录的每个字段变化前后得到值。

当我们选择不同的binlog模式时，在binlog文件包含的事件类型也不相同，如:

1)在Statement模式下，我们就看不到Row模式下独有的事件类型。

2)有一些类型的event，必须在我们开启某些特定配置的情况下，才会出现；

3)当然也会有一些公共的event类型，在任何模式下都会出现。

Mysql中定义了30多个event类型，这里并不打算将所有的事件类型提前列出，这样没有意义，只会让读者茫然不知所措。笔者将会在必要的地方，介绍遇到的每一种event类型的作用。目前我们先从宏观的角度对binlog有一个感性的认知。

多文件存储

mysql 将数据库更新操作对应的event记录到本地的binlog文件中，显然在一个文件中记录所有的event是不可能的，过大的文件会给我们的运维带来麻烦，如删除一个大文件，在I/O调度方面会给我们带来不可忽视的资源开销。

因此，目前基本上所有支持本地文件存储的组件，如MQ、Mysql等，都会控制一个文件的大小。在数据量较多的情况下，就分配到多个文件进行存储。

在mysql中，我们可以通过”show binary logs”语句，来查看当前有多少个binlog文件，以及每个binlog文件的大小，如下：

另外，mysql提供了：

max_binlog_size配置项，用于控制一个binlog文件的大小，默认是1G

expire_logs_days配置项，可以控制binlog文件保留天数，默认是0，也就是永久保留。

在实际生产环境中，一般无法保留所有的历史binlog。因为一条记录可能会变更多次，记录依然是一条，但是对应的binlog事件就会有多个。在数据变更比较频繁的情况下，就会产生大量的binlog文件。此时，则无法保留所有的历史binlog文件。

在mysql的percona分支上，还提供了max_binlog_files配置项，用于设置可以保留的binlog文件数量，以便我们更精确的控制binlog文件占用的磁盘空间。这是一个非常有用的配置，笔者曾经遇到一个库，大约10分钟就会产生一个binlog文件，也就是1G，按照这种增长速度，1天下来产生的binlog文件，就会占用大概144G左右的空间，磁盘空间可能很快就会被使用完。通过此配置，我们可以显示的控制binlog文件的数量，例如指定50，binlog文件最多只会占用50G左右的磁盘空间。

Binlog管理事件

所谓binlog管理事件，官方称之为binlog managent events，你可以认为是一些在任何模式下都有可能会出现的事件，不管你的配置binlog_format是Row、Statement还是Mixed。

以下通过“show binlog events”语法进行查看一个空的binlog文件，也就是只包含(部分)管理事件，没有其他数据更新操作对应的事件。如下：

在当前binlog v4版本中，每个binlog文件总是以Format Description Event作为开始，以Rotate Event结束作为结束。

在Event_Type列中，我们看到了三个事件类型：

Format_desc：也就是我们所说的Format Description Event，是binlog文件的第一个事件。在Info列，我们可以看到，其标明了Mysql Server的版本是5.7.10，Binlog版本是4。

Previous_gtids：该事件完整名称为，PREVIOUS_GTIDS_LOG_EVENT。熟悉Mysql 基于GTID复制的同学应该知道，这是表示之前的binlog文件中，已经执行过的GTID。需要我们开启GTID选项，这个事件才会有值，在后文中，将会详细的进行介绍。

Rotate：Rotate Event是每个binlog文件的结束事件。在Info列中，我们看到了其指定了下一个binlog文件的名称是mysql-bin.000004。

关于“show binlog events”语法显示的每一列的作用说明如下：

Log_name：当前事件所在的binlog文件名称

Pos：当前事件的开始位置，每个事件都占用固定的字节大小，结束位置(End_log_position)减去Pos，就是这个事件占用的字节数。细心的读者可以看到了，第一个事件位置并不是从0开始，而是从4。Mysql通过文件中的前4个字节，来判断这是不是一个binlog文件。这种方式很常见，很多格式的文件，如pdf、doc、jpg等，都会通常前几个特定字符判断是否是合法文件。

Event_type：表示事件的类型

Server_id：表示产生这个事件的mysql server_id，通过设置my.cnf中的server-id选项进行配置。

End_log_position：下一个事件的开始位置

Info：当前事件的描述信息

实战

开启binlog日志

查询mysql配置文件所在位置

which mysqld
/usr/sbin/mysqld

/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
2020-04-07T08:42:21.418712Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2020-04-07T08:42:21.418799Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

2.编辑配置文件/etc/my.cnf

在文件尾部添加：

log-bin=/var/lib/mysql/mysql-bin

server-id=123454 （5.7以上需要指定，用来在集群中区别服务器）

3.重启mysql

service mysqld restart

4.登陆mysql查看配置信息

show variables like '%log_bin%'

常用binlog日志操作命令

 1.查看所有binlog日志列表
      mysql> show master logs;

    2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后 
  一个操作事件pos结束点(Position)值
      mysql> show master status;

    3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件
      mysql> flush logs;
      注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志； 
  在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

    4.重置(清空)所有binlog日志
      mysql> reset master;

查看某个binlog日志内容，常用有两种方式：

1.使用mysqlbinlog自带查看命令法：

注: binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看binlog日志与数据库文件在同目录中

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013

下面截取一个片段分析：

  ...............................................................................
         # at 552
         #131128 17:50:46 server id 1  end_log_pos 665   Query  
 thread_id=11    exec_time=0     error_code=0 ---->执行时间:17:50:46；pos点:665
         SET TIMESTAMP=1385632246/*!*/;
         update zyyshop.stu set name='李四' where id=4  
  ---->执行的SQL
         /*!*/;
         # at 665
         #131128 17:50:46 server id 1  end_log_pos 692  
    Xid = 1454 ---->执行时间:17:50:46；pos点:692 
         ................................. 
  ..............................................

         注: server id 1     数据库主机的服务号；
             end_log_pos 665 pos点
             thread_id=11    线程号

2.上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

选项解析：

IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

LIMIT [offset,] 偏移量(不指定就是0)

row_count 查询总条数(不指定就是所有行)

  A.查询第一个(最早)的binlog日志：
        mysql> show binlog events\G; 
    
      B.指定查询 mysql-bin.000021 这个文件：
        mysql> show binlog events in 'mysql-bin.000021'\G;

      C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
        mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

      D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

      E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

  A.查询第一个(最早)的binlog日志：
        mysql> show binlog events\G; 
    
      B.指定查询 mysql-bin.000021 这个文件：
        mysql> show binlog events in 'mysql-bin.000021'\G;

      C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
        mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

      D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

      E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

Binlog结构和内容

日志由一组二进制日志文件（Binlog）,加上一个索引文件（index）；Binlog是一个二进制文件集合，每个Binlog以一个4字节的魔数开头，接着是一组Events；

1.魔数：0xfe62696e对应的是0xfebin；
2.Event：每个Event包含header和data两个部分； 
  header提供了Event的创建时间， 
  哪个服务器等信息，data部分提供的是 
  针对该Event的具体信息，如具体数据的修改；
3.第一个Event用于描述binlog文件的格式版本， 
  这个格式就是event写入binlog文件的格式；
4.其余的Event按照第一个Event的格式版本写入；
5.最后一个Event用于说明下一个binlog文件；
6.Binlog的索引文件是一个文本文件，其中内容为当前的binlog文件列表

Binlog的Event类型

官方提供的可能Event类型有36种，具体看下面的枚举：

enum Log_event_type { 
  UNKNOWN_EVENT= 0, 
  START_EVENT_V3= 1, 
  QUERY_EVENT= 2, 
  STOP_EVENT= 3, 
  ROTATE_EVENT= 4, 
  INTVAR_EVENT= 5, 
  LOAD_EVENT= 6, 
  SLAVE_EVENT= 7, 
  CREATE_FILE_EVENT= 8, 
  APPEND_BLOCK_EVENT= 9, 
  EXEC_LOAD_EVENT= 10, 
  DELETE_FILE_EVENT= 11, 
  NEW_LOAD_EVENT= 12, 
  RAND_EVENT= 13, 
  USER_VAR_EVENT= 14, 
  FORMAT_DESCRIPTION_EVENT= 15, 
  XID_EVENT= 16, 
  BEGIN_LOAD_QUERY_EVENT= 17, 
  EXECUTE_LOAD_QUERY_EVENT= 18, 
  TABLE_MAP_EVENT = 19, 
  PRE_GA_WRITE_ROWS_EVENT = 20, 
  PRE_GA_UPDATE_ROWS_EVENT = 21, 
  PRE_GA_DELETE_ROWS_EVENT = 22, 
  WRITE_ROWS_EVENT = 23, 
  UPDATE_ROWS_EVENT = 24, 
  DELETE_ROWS_EVENT = 25, 
  INCIDENT_EVENT= 26, 
  HEARTBEAT_LOG_EVENT= 27, 
  IGNORABLE_LOG_EVENT= 28,
  ROWS_QUERY_LOG_EVENT= 29,
  WRITE_ROWS_EVENT = 30,
  UPDATE_ROWS_EVENT = 31,
  DELETE_ROWS_EVENT = 32,
  GTID_LOG_EVENT= 33,
  ANONYMOUS_GTID_LOG_EVENT= 34,
  PREVIOUS_GTIDS_LOG_EVENT= 35, 
  ENUM_END_EVENT 
  /* end marker */
};

Event结构官网提供了3个版本，分别是v1，v3，v4：

v1：用在MySQL 3.23

v3：用在MySQL 4.0.2-4.1

v4：用在MySQL 5.0之后

现在MySQL的版本基本都使用5.0之后的版本，可以直接看v4，具体如下：

名字后面的两个数字表示：offset : length即从第几个字节开始，后面多少个字节用来存放数据.比如：timestamp(0 : 4)表示从第0个字节开始，往后四个字节用来存放timestamp

目前来说x=19，所有extra_headers是空的，y是fixed part的长度，不同的Event长度不一样。

Event简要分析

1.从一个最简单的实例来分析其中的Event，包括创建表，插入数据，更新数据，删除数据；binlog_format使用的是默认的STATEMENT；

CREATE TABLE `btest` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
insert into btest values(1,100,'zhaohui');
update btest set name='zhaohui_new' where id=1;
delete from btest where id=1;

2.查看所有的Events

show binlog events;

上图中一共出现了3中类型的Event，分别是Format_desc，Query和Xid，下面进行简单的分析

2.1Format_desc_Event

官网格式如下：

使用十六进制方式打开文件bin-log.000001，以下是Format_desc_Event的十六进制代码：

可以先看前面的4+103=107个字节，4字节是binlog的魔数，103字节是Format_desc_Event，其中有19字节是header；

注:Binlog日志是小端字节顺序

0x5A0504AA四个字节是timestamp；0x0F一个字节表示type_code；0x00000001四个字节为server_id；0x00000067四个字节是event_length，对应的十进制就是103；0x0000006b四个字节是next_position，即下一个Event的开始位置为107；ox0001两个字节是flags；header总计19字节。data总字节数=103-19即84字节，排除掉前面的57个字节，剩余27字节表示post-header lengths for all event types；post-header lengths：从START_EVENT_V3开始到第27个Event，每个Event的fixed part lengths；Format_desc_Event位置是15，可以在图中找到15的位置是0x54，对应十进制是84，即fixed part lengths=84，而这个值刚好是57+27=84，所以Format_desc_Event不存在variable part；

Query_Event

以下的create table产生的Query_Event的十六进制代码：

header共19字节，0x02一个字节表示type_code（Query_Event=2）;0x00000101四个字节是event_length，对应的十进制就是257（pos=107，End_log_pos=364）;Query_Event在post-header的第二个位置0x0d，所有fix part lengths=13；variable part=257-19-13=225字节,具体fix data和variable data：

在创建表产生一个Query_Event，insert、update以及delete执行之后分别产生了2个Query_Event和一个Xid_Event。

Xid_Event

以下的更新数据产生的Xid_Event的十六进制代码：

header共19字节，0x10一个字节表type_code（XID_EVENT=16）;0x0000001b四个字节是event_length，对应的十进制就是27（pos=536，End_log_pos=563）;2Xid_Event在post-header的第十六个位置0x00，所有fix part lengths=0；variable part=27-19=8字节,8字节：The XID transaction number。

insert、update以及delete执行之后分别产生了Xid_Event，事务提交产生的事件。

次浏览