Binlog详解

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Mysql binlog是二进制日志文件,用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据),在mysql主从复制中就是依靠的binlog。

Mysql binlog是二进制日志文件,用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据),在mysql主从复制中就是依靠的binlog。可以通过语句“show binlog events in 'binlogfile'”来查看binlog的具体事件类型。binlog记录的所有操作实际上都有对应的事件类型的,MySQL binlog的三种工作模式:
(1)Row level(用到MySQL的特殊功能如存储过程、触发器、函数,又希望数据最大化一直则选择Row模式,我们公司选择的是row)
  简介:日志中会记录每一行数据被修改的情况,然后在slave端对相同的数据进行修改。
  优点:能清楚的记录每一行数据修改的细节
  缺点:数据量太大
(2)Statement level(默认)
  简介:每一条被修改数据的sql都会记录到master的bin-log中,slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行。在主从同步中一般是不建议用statement模式的,因为会有些语句不支持,比如语句中包含UUID函数,以及LOAD DATA IN FILE语句等
  优点:解决了 Row level下的缺点,不需要记录每一行的数据变化,减少bin-log日志量,节约磁盘IO,提高新能
  缺点:容易出现主从复制不一致
(3)Mixed(混合模式)
  简介:结合了Row level和Statement level的优点,同时binlog结构也更复杂。

binlog结构解析

binlog结构图如下:


img_7d94962758e3ca2d09dedf189a40abb6.png
image.png

binlog类似字节码一样定义了很多种类型,每个类型又有特定的结构。查看Mysql源码(mysql-5.7.14/libbinlogevents/include/binlog_event.h)对event类型的定义如下:

enum Log_event_type
{
  /**
    Every time you update this enum (when you add a type), you have to
    fix Format_description_event::Format_description_event().
  */
  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 is like LOAD_EVENT except that it has a longer
    sql_ex, allowing multibyte TERMINATED BY etc; both types share the
    same class (Load_event)
  */
  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,

  /**
    The PRE_GA event numbers were used for 5.1.0 to 5.1.15 and are
    therefore obsolete.
   */
  PRE_GA_WRITE_ROWS_EVENT = 20,
  PRE_GA_UPDATE_ROWS_EVENT = 21,
  PRE_GA_DELETE_ROWS_EVENT = 22,

  /**
    The V1 event numbers are used from 5.1.16 until mysql-trunk-xx
  */
  WRITE_ROWS_EVENT_V1 = 23,
  UPDATE_ROWS_EVENT_V1 = 24,
  DELETE_ROWS_EVENT_V1 = 25,

  /**
    Something out of the ordinary happened on the master
   */
  INCIDENT_EVENT= 26,

  /**
    Heartbeat event to be send by master at its idle time
    to ensure master's online status to slave
  */
  HEARTBEAT_LOG_EVENT= 27,

  /**
    In some situations, it is necessary to send over ignorable
    data to the slave: data that a slave can handle in case there
    is code for handling it, but which can be ignored if it is not
    recognized.
  */
  IGNORABLE_LOG_EVENT= 28,
  ROWS_QUERY_LOG_EVENT= 29,

  /** Version 2 of the Row events */
  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,

  TRANSACTION_CONTEXT_EVENT= 36,

  VIEW_CHANGE_EVENT= 37,

  /* Prepared XA transaction terminal event similar to Xid */
  XA_PREPARE_LOG_EVENT= 38,
  /**
    Add new events here - right above this comment!
    Existing events (except ENUM_END_EVENT) should never change their numbers
  */
  ENUM_END_EVENT /* end marker */
};

下面描述几个重要的EVENT类型:

QUERY_EVENT
QUERY_EVENT以文本的形式来记录事务的操作。QUERY_EVENT类型的事件通常在以下几种情况下使用:

  1. 事务开始时,执行的BEGIN操作。
  2. STATEMENT格式中的DML操作。
  3. ROW格式中的DDL操作。

固定数据部分:

  • 4字节表示发起这个语句的线程id,对于临时表来说是必须的。这也有助于DBA知道谁在master上干了啥。
  • 4字节表示语句执行的时长,单位为秒。只对于DBA的监控有用。
  • 1字节表示语句执行对应的数据库名称的长度。名称在可变数据部分。
  • 2字节表示在master执行语句的错误码。错误码定义在include/mysqld_error.h文件中。0表示没有错误。
  • 2字节表示状态变量长度。

可变数据部分:

  • 大于等于0的状态变量。每个状态变量包含一个字节码,标识存储变量,后面跟着变量的值。变量的值的字节长度是根据变量规定好的。详见下面变量说明
  • 默认数据库名
  • SQL语句。slave知道变量中其他字段的长度,所以通过减法计算,可以知道语句的长度。

下面的列表提供了每个变量的基本信息:

  • Q_FLAGS2_CODE=0。值为一个4字节的位字段。这个变量只会在5.0中写入。
  • Q_SQL_MODE_CODE = 1。 他是一个每一位代表SQL_MODE中的一个值,参考最后源码的解释。
  • Q_CATALOG_CODE = 2.。只在MYSQL 5.0.0到5.0.3使用不考虑
  • Q_AUTO_INCREMENT = 3。 2 bytes非负整数用于表示参数auto_increment_increment和auto_increment_offset,这个只会在auto_increment大于1的时候出现。
  • Q_CHARSET_CODE = 4。 6 bytes用于表示character_set_client,collation_connection和collation_server参数(totally 2+2+2=6 bytes)参考最后源码解释
  • Q_TIME_ZONE_CODE = 5。用于描述time zone信息
  • Q_CATALOG_NZ_CODE = 6。用于描述catalog name,长度占用一个字节,随后这个值为std
  • Q_LC_TIME_NAMES_CODE = 7。 2 bytes 非负整数,只有当lc_time_names不设置为en_US的时候使用
  • Q_CHARSET_DATABASE_CODE = 8。2 bytes 非负整数为collation_database系统变量,5.7源码解释说这部分新版本不一定使用。
  • Q_TABLE_MAP_FOR_UPDATE_CODE = 9。

FORMAT_DESCRIPTION_EVENT
FORMAT_DESCRIPTION_EVENT是binlog version 4中为了取代之前版本中的START_EVENT_V3事件而引入的。它是binlog文件中的第一个事件,而且,该事件只会在binlog中出现一次。MySQL根据FORMAT_DESCRIPTION_EVENT的定义来解析其它事件。
它通常指定了MySQL Server的版本,binlog的版本,该binlog文件的创建时间。
固定数据部分:

  • 2字节表示binlog格式版本,对于5.0及以上版本,是4.
  • 50字节表示Mysql服务器版本,后面带着0x00。
  • 4字节表示事件创建的时间戳。多余的参数,和头中的时间戳重复。
  • 1字节表示头长度。当前的值为19.

可变数据部分:

  • 空。

ROWS_EVENT
对于ROW格式的binlog,所有的DML语句都是记录在ROWS_EVENT中。
ROWS_EVENT分为三种:WRITE_ROWS_EVENT,UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT,分别对应insert,update和delete操作。
对于insert操作,WRITE_ROWS_EVENT包含了要插入的数据
对于update操作,UPDATE_ROWS_EVENT不仅包含了修改后的数据,还包含了修改前的值。
对于delete操作,仅仅需要指定删除的主键(在没有主键的情况下,会给定所有列)
对于QUERY_EVENT事件,是以文本形式记录DML操作的。而对于ROWS_EVENT事件,并不是文本形式,所以在通过mysqlbinlog查看基于ROW格式的binlog时,需要指定-vv --base64-output=decode-rows。

XID_EVENT
在事务提交时,不管是STATEMENT还是ROW格式的binlog,都会在末尾添加一个XID_EVENT事件代表事务的结束。该事件记录了该事务的ID,在MySQL进行崩溃恢复时,根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事务。
固定数据部分:

可变数据部分:

  • 8字节,XID事务号

ROTATE_EVENT
当binlog文件的大小达到max_binlog_size的值或者执行flush logs命令时,binlog会发生切换,这个时候会在当前的binlog日志添加一个ROTATE_EVENT事件,用于指定下一个日志的名称和位置。
固定数据部分:

  • 8字节。下个日志文件的第一个事件的位置。经常包含数字4(表示下个binlog的下个事件从位置4开始)。这个字段在v1中不存在。可以推测,这个值是4。
    可变数据部分:
  • 下个binlog文件的名字。文件名不是以null结尾的,他的长度是事件长度-固定数据长度。

GTID_LOG_EVENT
GTID即全局事务ID(global transaction identifier)是Mysql5.6版本引入的,GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增,所以GTID能够保证每个MySQL实例事务的执行(不会重复执行同一个事务,并且会补全没有执行的事务)。在启用GTID模式后,MySQL实际上为每个事务都分配了个GTID。
譬如:

# at 448
#160818  5:37:32 server id 1  end_log_pos 496 CRC32 0xaeb24aac     GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:3'/*!*/;
# at 496
#160818  5:37:32 server id 1  end_log_pos 571 CRC32 0x042ca092     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1471469852/*!*/;
BEGIN
/*!*/;
# at 571
#160818  5:37:32 server id 1  end_log_pos 674 CRC32 0xa35beb37     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1471469852/*!*/;
insert into test.t1 values(2,'b')
/*!*/;
# at 674
#160818  5:37:32 server id 1  end_log_pos 705 CRC32 0x1905d8c6     Xid = 12
COMMIT/*!*/;

PREVIOUS_GTIDS_LOG_EVENT
开启GTID模式后,每个binlog开头都会有一个PREVIOUS_GTIDS_LOG_EVENT事件,它的值是上一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT,实际上,在数据库重启的时候,需要重新填充gtid_executed的值,该值即是最新一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT。
mysql-bin.000033日志中的Previous_gtids是cad449f2-5d4f-11e6-b353-000c29c64704:1,GTID是cad449f2-5d4f-11e6-b353-000c29c64704:2和cad449f2-5d4f-11e6-b353-000c29c64704:3,这样,在下一个日志,即mysql-bin.000034中的Previous_gtids是cad449f2-5d4f-11e6-b353-000c29c64704:1-3。

STOP_EVENT
当MySQL数据库停止时,会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止。
固定数据部分:


  • 可变数据部分:

HEARTBEAT_LOG_EVENT
这个事件是由master发给slave的,让slave知道master还活着。这类事件不会再binlog或relay log中出现。他们由master的dump事件线程产生,然后直接发给了slave。slave收到后,校验事件内容后,直接抛弃这个事件,而不会写到relay log中。
固定数据部分


  • 可变数据部分
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
存储 SQL 关系型数据库
binlog,undolog,redolog
binlog,undolog,redolog
95 3
|
6月前
|
关系型数据库 MySQL 数据库
Mysql数据库redo log及binlog的写入
Mysql数据库redo log及binlog的写入
|
SQL 存储 关系型数据库
MySQL中的二进制日志(binlog)与中继日志(Relay log)
MySQL中的二进制日志(binlog)与中继日志(Relay log)
684 0
|
SQL 关系型数据库 MySQL
Mysql使用binlog增量备份与恢复
Mysql使用binlog增量备份与恢复
313 0
|
6月前
|
SQL 存储 关系型数据库
MySQL-日志&二进制日志binlog初探
MySQL-日志&二进制日志binlog初探
107 0
|
11月前
|
Java 关系型数据库 MySQL
binlog4j
binlog4j
93 0
|
SQL 存储 关系型数据库
mysqldump+binlog+gtid 实现数据库的增量备份(上)
mysqldump+binlog+gtid 实现数据库的增量备份
463 0
|
SQL 存储 关系型数据库
mysqldump+binlog+gtid 实现数据库的增量备份(下)
mysqldump+binlog+gtid 实现数据库的增量备份(下)
360 0
|
关系型数据库 MySQL
可以同时解析多个binlog吗
1.可以同时解析多个binlog吗 2. innodb_buffer_pool_instances设置多少合适
165 0
|
AliSQL 关系型数据库 MySQL
Binlog In Redo
Introduce the feature which persists binlog into redo on RDS-8.0
395 0
Binlog In Redo