binlog解读2

最新推荐文章于 2024-01-19 06:01:06 发布

烧稀饭馏馍

最新推荐文章于 2024-01-19 06:01:06 发布

阅读量1k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/mengjiangui/article/details/111187374

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、binlog文件里面写入的是啥玩意？

答：1、binlog是由一个一个event组成，event是binlog的最小组成单元。

2、binlog文件头部固定以4个字节开头，这四个字节称为BINLOG_MAGIC(fe 62 69 6e)魔数，当使用mysqldump命令解析binlog文件时，来识别该文件属于binlog文件。

3、每个binlog文件以一个FORMAT_DESCRIPTION_EVENT类型的event开始。以一个Rotate类型的event结束（但也有特殊情况，当数据库出现宕机的情况，重新启动数据库会生成一个新的binlog文件，但是宕机之前的最新binlog文件中，不是以ROTATE_EVENT结束的）

在FORMAT_DESCRIPTION_EVENT和ROTATE_EVENT之间是各种不同event，每个event代表Master上不同的操作。

4、查看binlog的命令和event的命令以及binlog记录位点：

show binlog events in ‘mysql-bin.000111’(from position); show master status; show binary logs; 。

二、常见的event事件：

1、binlog事件：由<公有事件头>+[私有事件头post header]+[事件体event body]三部分组成.所有的binlog事件都包含公有事件头，另外两个部分是根据事件类型可选。

公有事件头内容（事件头根据binlog版本不同，分为13字节和19字节。mysql5+版本都是binlog v4版本，都是19个字节。）：

timestamp (4) -- seconds since unix epoch：第一个字段是事件生成的时间戳，占4个字节。
event_type(1) -- see Binlog Event Type：第二个字段是事件类型，占1个字节。
server_id (4) -- server-id of the originating mysql-server. Used to filter out events in circular replication：第三个字段是源mysql主机的server id，占4个字节，用于过滤循环复制中的事件。
event_size (4) -- size of the event (header, post-header, body)：第四个字段是事件大小，占4个字节，包括事件头、事件体的总大小。
log_pos (4) -- position of the next event：第五个字段是位置，占4个字节，表示下一个事件的位置。
flags (2) -- see Binlog Event Flag：最后一个字段标志状态，占2个字节。https://dev.mysql.com/doc/internals/en/binlog-event-flag.html

2、binlog版本问题：

binlog文件格式有以下几种：

v1：用于3.23版本；

v3：用于4.0.2到4.1版本；

v4：用于5.0及以上版本；

v2版本只在4.0.x版本中使用，目前已经不再支持了。

3、mysql5.7关闭gtid之后的binlog event类型：

ddl create table：Anonymous_Gtid event --> Query event --> XID event。

insert/update/delete：Anonymous_Gtid event --> Query event--> Table_map event --> Write_rows event（Delete_rows 或者Update_rows）à XID event

注：binlog事件的产生是以语句为单位而不是以数据行为单位，即一条语句会产生一个事件而不是每操作一行数据会有一个事件。

binlog事件介绍：

1、 FORMAT_DESCRIPTION_EVENT：

A format description event is the first event of a binlog for binlog-version 4. It describes how the other events are layed out. 这个事件定义了binlog header的格式，而且记录了一些元数据信息。

binlog-version (2) #binlog文件版本号，2个字节。
mysql-server version (50) #创建binlog的MySQL服务器版本，50个字节。
create_timestamp (4) #binlog创建时的时间戳，4个字节。
event_header_length (1) #binlog header 占用的字节数19，1个字节。
event type header length(39) #这里的是每个事件的post header的长度的字节数组，每一个事件一个字节。39个事件就有39个字节。这个数组长度不是固定的(每个版本包含的事件数很可能是不同的)。
CRC32(4): CRC32校验位，4个字节。

2、Rows_query event：

当binlog_rows_query_log_events这个参数开启时，会将执行的源sql语句存入这个事件中，但是在主从复制的过程中，从库并不会去执行这个事件。

3、 Query event：

The query event is used to send text querys right the binlog. 查询事件用于向binlog发送文本查询。

触发条件：

QUERY_EVENT类型的事件通常在以下几种情况下使用：

# 事务开始时，执行的BEGIN操作。

# STATEMENT格式中的DML操作。

# ROW格式中的DDL操作。

b、--------固定数据部分(私有事件头)-----

（1）、thread_id：4个字节，存储了不同连接或会话的线程ID。

（2）、execution time：4个字节，查询从开始执行到记录到binlog所花时间，单位s。

（3）、schema length：1个字节，schema字符长度6。即库名的长度。

（4）、error code：2个字节，错误码。

（5）、status-var length：2个字节，23 00，status-var的长度，为35

---------可变数据部分（事件体）--------

（6）、status var:35个字节，即以KV对的形式保存起来的一些了由SET命令设置的上下文信息。eg：

SET @@session.pseudo_thread_id=7322991/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=0/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

下面详细说一下这几个参数的含义：

1）会话系统变量pseudo_thread_id用于标记当前会话的mysql连接ID。

2）SET @@session.foreign_key_checks=1开启外键约束，=0是取消外键约束。

3）sql_auto_is_null：sql_auto_is_null 参数默认值为OFF 关闭 0，如果启用了此变量，则在成功插入自动生成的AUTO_INCREMENT值的语句之后，可以通过发出以下形式的语句来找到该值：SELECT * FROM tbl_name WHERE auto_col IS NULL。

4）@@session.unique_checks=1，这个参数是用于检查主键和唯一键是否重复的参数.

5）@@session.autocommit=1，MySQL从自动提交（autocommit）模式运行，这种模式会在每条语句执行完毕后把它作出的修改立刻提交给数据库并使之永久化。

6）@@session.sql_mode=1073741824，通过设置sql mode, 可以完成不同严格程度的数据校验，有效地保障数据准备性。例如：我在创建一个表时，该表中有一个字段为name，给name设置的字段类型时char(10)，如果我在插入数据的时候，其中name这个字段对应的有一条数据的长度超过了10，例如'1234567890abc'，超过了设定的字段长度10，那么不会报错，并且取前十个字符存上，也就是说你这个数据被存为了'1234567890',而'abc'就没有了。

7）auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535，auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535。

8）SET @@session.lc_time_name=0，日期的星期和月份名称，默认是英文的，set lc_time_names='zh_CN'，则可以设置为中文的。

9）SET @@session.character_set_client=45，id为45的字符集为utf8mb4字符集，33为utf8.客户端发送到server的字符串使用的字符集，server会按照该变量值来解析客户端发来的语句。

10）@@session.collation_connection=45，45对应utf8mb4_general_ci，字符集校对规则：是在字符集内用于字符比较和排序的一套规则，比如有的规则区分大小写，有的则无视。

①两个不同的字符集不能有相同的校对规则；

②每个字符集有一个默认校对规则；

③存在校对规则命名约定：以其相关的字符集名开始，中间包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束。

系统使用utf8字符集，若使用utf8_bin校对规则执行SQL查询时区分大小写，使用utf8_general_ci不区分大小写(默认的utf8字符集对应的校对规则是utf8_general_ci)。

校对规则是在字符集内用于比较字符的一套规则,可以控制 select 查询时where 条件大小写是否敏感的规则.如字段 col 在表中的值为 'abc','ABC','AbC' 在不同的校对规则下,where col='ABC'会有不同的结果。

每个列都应该有一个校对，如果没有显示指定，MySQL就使用属于该字符集的默认校对。如果指定了一个字符集和一个校对，字符集应该放在前面。

11）mysql字符集转换过程：

MySQL服务响应客户端操作的字符的字符集和客户端信息处理过程:

客户端发出的SQL语句，所使用的字符集由系统变量character_set_client来指定

MySQL服务端接收语句后，会用character_set_connection和collation_connection两个系统变量中的设置，并且会将客户端发送的语句字符集由character_set_client转到character_set_connection(除非用户执行语句时，已经对字符列明确指定了字符集）。对于语句中指定的字符串比较或排序，还需要应用collation_connection中指定的校对规则处理，而对于语句中指定的列的比较则无关collation_connection的设置了，因为对象的列表拥有自己的校对规则，他们拥有更高的优先级。

MySQL服务执行完语句后，会按照character_set_result系统变量设置的字符集返回结果集（或错误信息）到客户端。

可以用语句：show global variables like 'character_set_\%';查看这些系统变量设置。

12）@@session.collation_server=45：用途：当你创建数据库，且没有指定字符集、字符序（校验规则）时，server字符集、server字符序就会作为该数据库的默认字符集、排序规则。

如何指定：MySQL服务启动时，可通过命令行参数指定。也可以通过配置文件的变量指定。

server默认字符集、校验规则：在MySQL编译的时候，通过编译参数指定。

character_set_server、collation_server分别对应server字符集、server校验规则。

13）SET @@session.collation_database=DAFAULT 当前数据库的默认校对。每次用USE语句来“跳转”到另一个数据库的时候，这个变量的值就会改变。如果没有当前数据库，这个变量的值就是collation_server变量的值。

（7）、schema：6字节，表示选择的数据库。

（8）、00：默认的1个字节

（9）、sql文本：33个字节，即：query的文本格式，里面存储的是BEGIN（dml）或原生的SQL（ddl）等。

4、Table_map event：

用于从MySQL 5.1.5开始的基于行的二进制日志记录。每个ROW_EVENT之前都有一个TABLE_MAP_EVENT，用于描述表的内部ID和结构定义。

a、触发条件：# ROW格式中每个ROW_EVENT之前。

b、

固定数据部分：

（1）、table id：6 bytes //6个字节存储table id

（2）、reserve：2 bytes：Reserved for future use //2个字节保留未来使用

可变数据部分：

（1）、db name len：1 byte. The length of the database name. //数据库名长度：1字节。

（2）、db name：The database name (null-terminated). //数据库名：可变长度

（3）、table name len(1):01，表名占用1个字节。

（4）、table name：表名，可变长度。

（5）、00

（6）、column count(1)：列的个数，一个字节。

（7）、column type：//列类型数组，每一列1个字节。

（8）、column metadata len（1）：The length of the metadata block. //元数据块的长度。

（9）、column metadata：The metadata block; see log_event.h for contents and format. //元数据块

（10）、null bitmap：//位字段，指示每个列是否可以为空，每个列一位。如果表有N列，需要：INT((N+7)/8) 字节。

（11）、crc32（4）：4个字节，校验值。

c、Table_map event 的作用：

（1）、检测slave上的表的字段类型是否和master上的表的字段类型兼容，如果不兼容，slave会上报错误。

（2）、将数据转换为slave上表的字段类型，但仅限于字段类型兼容的情况。

（3）、为什么一个update在ROW模式下需要分解成两个event：一个Table_map，一个Update_rows。我们想象一下，一个update如果更新了10000条数据，（大rows会拆分成小rows）那么对应的表结构信息是否需要记录10000次?其实是对同一个表的操作，所以这里binlog只是记录了一个Table_map用于记录表结构相关信息,而后面的Update_rows记录了更新数据的行信息。他们之间是通过table_id来联系的。

d、Table_map event中的table_id的作用详解：

（1）、根据table id可以找到缓存中对用的表结构信息。

（2）、table_id和数据库表并非是固定的对应关系，table id在mysql实例生命周期内是递增的。

（3）、如果缓存中已经存在该表，则table id不变，而如果table cache中不存在时，则该值根据上一次操作的table id自增1；此外如果table_definition_cache缓存表中默认存放n个表定义，如果超出了该范围，则将最久未用的表定义置换出table cache。使用flush tables;会清空所有缓存的中表信息。

（4）、table_map_event和rows_event通过table_id进行关联，slave通过先解析table_map_event并存于内存中，而后rows_event通过table_id找到对应的表信息。

5、 WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT：

rows event是从库需要执行的具体语句。而且row event不记录具体的表结构信息。会记录每行数据的每个字段更改前后的值。

a、版本信息：

（1）、MySQL 5.1.0 to 5.1.15

DELETE_ROWS_EVENTv0；UPDATE_ROWS_EVENTv0；WRITE_ROWS_EVENTv0

（2）、MySQL 5.1.15 to 5.6.x

DELETE_ROWS_EVENTv1；UPDATE_ROWS_EVENTv1；WRITE_ROWS_EVENTv1

（3）、MySQL 5.6.x

DELETE_ROWS_EVENTv2；UPDATE_ROWS_EVENTv2；WRITE_ROWS_EVENTv2

b、

-------私有事件头------

（1）、table ID：table id与table map event中的table id对应。

（2）、flag：2个字节，可以包含以下信息。该事件是否是语句的最后一个事件，是否需要进行外键约束检查，针对innodb的二级索引是否需要进行唯一性检查，

该事件是否包含了完整一行数据也就是说覆盖了所有列。

。。。

c、参数BINLOG_ROW_EVENT_MAX_SIZE

（1）、这个参数是binlog每个event的字节上限，当超过这个值的时候，大event会拆分成小的event，比如说：event max size大小为8k，更新10000条数据，生产约181K的binlog日志，MySQL将这些日志按照每个最大8K的拆分成23个binlog rows event，每个binlog event大概包含400-450个行记录的更新日志。

（2）、那么同一个事务这么多event怎么知道是否是最后一个？

rows event的私有事件的post header中会记录一个flag标志，来判断是否是最后一个rows event，在binlog文件中的解读为：flags: STMT_END_F。

d、还有另一个情况也会产生多个rows event那就是当一个dml造成多个表的变更，即使每个表只变更了一行也会产生n个row event。

6、PREVIOUS_GTIDS_LOG_EVENT：

它记录了上一个binlog文件结束时执行到的gtid集合，包括已经删除的BINLOG。