MySQL 提供了 4 种不同的日志，分别是错误日志（error log）、普通日志（general log）、慢日志（slow log）以及二进制日志（binlog）。其中错误日志记录了系统启动、运行以及停止过程中遇到的一些问题；普通日志记录了MySQL执行的所有语句以及语句开始执行的时间等信息，用户可以选择性打开它；慢日志记录了 MySQL 所有慢查询的相关信息；而二进制日志则以事件（event）的形式记录了 MySQL 的库表结构以及表数据的所有变更信息。本篇文章将详细讲解二进制日志所涉及的一些内容。
一、简介
binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。
从宏观上来看，binlog由一系列binlog文件和一个index文件组成。数据库的所有变更信息以事件的形式记录在binlog文件，index文件记录了当前使用了哪些binlog文件。binlog文件以一个4字节的常量作为开头（标识这是一个binlog文件），后面跟着一系列的binlog事件。对于不同的binlog格式，相同语句记录在binlog文件中的事件也有所不同。
1. binlog的作用
总的来说，二进制日志主要有以下几种作用：
- 恢复（recovery）
某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。
- 复制（replication）
其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。
- 审计（audit）
用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。
除了上面介绍的几个作用外，binlog对于事务存储引擎的崩溃恢复也有非常重要的作用。在开启binlog的情况下，为了保证binlog与redo的一致性，MySQL将采用事务的两阶段提交协议。当MySQL系统发生崩溃时，事务在存储引擎内部的状态可能为prepared和commit两种。对于prepared状态的事务，是进行提交操作还是进行回滚操作，这时需要参考binlog：如果事务在binlog中存在，那么将其提交；如果不在binlog中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。
2. index文件
为了管理所有的binlog文件，MySQL额外创建了一个base-name.index文件，它按顺序记录了MySQL使用的所有binlog文件。如果你想自定义index文件的名称，可以设置log_bin_index=file参数。千万不要在mysqld运行的时候手动修改index文件的内容，这样会使mysqld产生混乱。
二、binlog的使用
1. 开启binlog
如果想开启binlog，默认关闭，可以在MySQL配置文件中通过配置参数log-bin = [base-name]启动二进制日志。如果不指定base-name，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，例如mysql-bin.000001，所在目录为数据库所在目录（datadir）。顺序说一下，对于二进制文件当满足下面三种情况时会创建新的文件，文件后缀会自增。
- 文件大小达到max_binlog_size参数设置值时。
- 执行flush logs命令。
- 重启mysqld进程。
Note
你可能会有顾虑，当文件后缀从 000001 增长到 999999 时会怎样？有网友测试过，当文件达到 999999 时又会回到 000001，并不会有什么异常。
2. 选择binlog的格式
通过配置BINLOG_FORMAT参数的值，可以选择binlog的格式。参数BINLOG_FORMAT有3个可选的值：STATEMENT、ROW和MIXED，分别代表3种不同的binlog格式。
- STATEMENT
顾名思义，STATEMENT格式的binlog记录的是数据库上执行的原生SQL语句。这种方式有好处也有坏处。
好处就是相当简单，简单地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的SQL语句，在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑，所以相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间。并且通过mysqlbinlog工具容易读懂其中的内容。
坏处就是同一条SQL在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的SQL。例如，使用INSERT INTO TB1 VALUE(CUURENT_DATE())这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化。存储过程和触发器在使用基于语句的复制模式时也可能存在问题。另外一个问题就是基于语句的复制必须是串行化的。这要求大量特殊的代码，配置，例如InnoDB的next-key锁等。并不是所有的存储引擎都支持基于语句的复制。
- ROW
从MySQL5.1开始支持基于行的复制，也就是基于数据的复制，基于行的更改。这种方式会将实际数据记录在二进制日志中，它有其自身的一些优点和缺点，最大的好处是可以正确地复制每一行数据。一些语句可以被更加有效地复制，另外就是几乎没有基于行的复制模式无法处理的场景，对于所有的SQL构造、触发器、存储过程等都能正确执行。主要的缺点就是二进制日志可能会很大，而且不直观，所以，你不能使用mysqlbinlog来查看二进制日志。也无法通过看二进制日志判断当前执行到那一条SQL语句了。
现在对于ROW格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点。并且由于ROW格式记录行数据，所以可以基于这种模式做一些DBA工具，比如数据恢复，不同数据库之间数据同步等。
- MIXED
MIXED也是MySQL默认使用的二进制日志记录方式，但MIXED格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到UUID()、USER()、CURRENT_USER()、ROW_COUNT()等无法确定的函数。
3. binlog操作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# 查看当前正在使用的二进制文件和上一个事件结束的位置; mysql> show master status; # 查看所有的二进制文件; mysql> show binary logs; # 查看指定的二进制文件并可以选择从某字段开始; mysql> show binlog events in 'MySQL-bin-00001' [from position]; # 删除二进制文件; mysql> purge binary logs to 'MySQL-bin-00001'; # 重建日志文件（包括查询日志、慢查询日志、二进制日志）; mysql> flush logs; # 暂停记录二进制日志（只能对当前会话生效）; mysql> SET sql_log_bin = 0 |
4. binlog的相关参数
max_binlog_size
可以通过max_binlog_size参数来限定单个binlog文件的大小（默认1G），如果当前binlog文件的大小达到了参数指定的阈值，会创建一个新的binlog文件作为当前活跃的binlog文件，后续所有对数据库的修改都会记录到新的binlog文件中。
对于binlog文件的大小，有个需要注意的地方是，binlog文件可能会大于max_binlog_size参数设定的阈值。由于一个事务所产生的所有事件必须记录在同一个binlog文件中，所以即使binlog文件的大小达到max_binlog_size参数指定的大小，也要等到当前事务的所有事件全部写入到binlog文件中才能切换，这样就会出现binlog文件的大小大于max_binlog_size参数指定的大小的情况。
binlog_cache_size
当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32K。此外，binlog_cache_size是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态，可以判断当前binlog_cache_size的设置是否合适。binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。
sync_binlog
在MySQL 5.7之前版本默认情况下，二进制日志并不是在每次写的时候同步的磁盘（用户可以理解为缓冲写）。因此，当数据库所在的操作系统发生宕机时，可能会有最后一部分数据没有写入二进制文件中，这会给恢复和复制带来问题。参数sync_binlog=[N]中的N表示每提交多少个事务就进行binlog刷新到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，每次事务提交时就会刷新binlog到磁盘；sync_binlog为0表示刷新binlog时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘；sync_binlog为N表示每N个事务提交会进行一次binlog刷新。如果使用Innodb存储引擎进行复制，并且想得到最大的高可用性，需要将此值设置为1。不过该值为1时，确时会对数据库IO系统带来一定的开销。
但是，即使将sync_binlog设为1，还是会有一种情况导致问题的发生。当使用InnoDB存储引擎时，在一个事务发出COMMIT动作之前，由于sync_binlog为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在MySQL数据库下次启动时，由于COMMIT操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。对于这个问题，MySQL使用了两阶段提交来解决的，简单说就是对于已经写入到binlog文件的事务一定会提交成功， 而没有写入到binlog文件的事务就会进行回滚，从而保证二进制日志和InnoDB存储引擎数据文件的一致性，保证主从复制的安全。
binlog-do-db&binlog-ignore-db
参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的二进制日志。默认为空，表示需要同步所有库的日志到二进制日志。
log-slave-update
如果当前数据库是复制中的slave角色，则它不会将master取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入，要设置log-slave-update。如果需要搭建master–>slave–>slave架构的复制，则必须设置该参数。
binlog-format
binlog_format参数十分重要，用来设置二进制日志的记录格式，前面已经详细说了，这里就忽略。
三、binlog的事件
下面主要解释 MySQL 5.6 后 ROW 格式的 binlog 格式以及和事务有关的 event，按照官方的说法 binlog 的格式经历了几个阶段：
- v1：13 bytes: timestamp + type code + server ID + event length，MySQL 3.23。
- v3：19 bytes: v1 fields + next position + flags，MySQL 4.0.2到4.1。
- v4：19 bytes or more: v3 fields + possibly other information，MySQL 5.0以上。
实际上还有一个 v2 版本，不过只在早期 4.0.x 的 MySQL 版本中使用过，但是 v2 已经过于陈旧并且不再被 MySQL 官方支持了。
MySQL binlog 以事件的形式来记录数据库的变更情况。通过执行 show binlog events in ‘binlog-file’ 命令来查看指定 binlog 文件中的事件，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> show binlog events in 'mysql-bin.000006'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000006 | 4 | Format_desc | 200 | 123 | Server ver: 5.7.18-log, Binlog ver: 4 | | mysql-bin.000006 | 123 | Previous_gtids | 200 | 154 | | | mysql-bin.000006 | 154 | Anonymous_Gtid | 200 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000006 | 219 | Query | 200 | 289 | BEGIN | | mysql-bin.000006 | 289 | Table_map | 200 | 332 | table_id: 336 (db.t1) | | mysql-bin.000006 | 332 | Write_rows | 200 | 372 | table_id: 336 flags: STMT_END_F | | mysql-bin.000006 | 372 | Xid | 200 | 403 | COMMIT /* xid=284 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 17 rows in set (0.00 sec) |
执行 show binlog events 命令后，我们可以获取事件类型、事件在文件中的位置等信息。如果 binlog 的格式为 STATEMENT，还能看出具体执行的 SQL 语句。
上面展示的是一个 ROW 格式下执行一条 DML 语句时产生的事件，其中从 Anonymous_Gtid 到 Xid 就是一条 DML 语句所产生的完整事件。如果是 DDL 语句就是一个 QUERY_EVENT 事件，包含执行语句。下面看一下一条 DML 语句产生的事件对应的 mysqlbinlog 解析的 binlog 日志。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
# at 4 #180122 5:09:59 server id 200 end_log_pos 123 CRC32 0xdc3de4ab Start: binlog v 4, server v 5.7.18-log created 180122 5:09:59 BINLOG ' d7hlWg/IAAAAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AavkPdw= '/*!*/; # at 123 #180122 5:09:59 server id 200 end_log_pos 154 CRC32 0xec81bf8d Previous-GTIDs # [empty] # at 154 #180122 5:10:06 server id 200 end_log_pos 219 CRC32 0x5539b63d Anonymous_GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #180122 5:10:06 server id 200 end_log_pos 289 CRC32 0x36fd7f59 Query thread_id=2145 exec_time=0 error_code=0 SET TIMESTAMP=1516615806/*!*/; SET @@session.pseudo_thread_id=2145/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; 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=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 289 #180122 5:10:06 server id 200 end_log_pos 332 CRC32 0x8c3b9983 Table_map: `db`.`t1` mapped to number 336 # at 332 #180122 5:10:06 server id 200 end_log_pos 372 CRC32 0xcae85254 Write_rows: table id 336 flags: STMT_END_F BINLOG ' frhlWhPIAAAAKwAAAEwBAAAAAFABAAAAAAEAAmRiAAJ0MQABAwABg5k7jA== frhlWh7IAAAAKAAAAHQBAAAAAFABAAAAAAEAAgAB//4BAAAAVFLoyg== '/*!*/; ### INSERT INTO `db`.`t1` ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ # at 372 #180122 5:10:06 server id 200 end_log_pos 403 CRC32 0x2d5bc1bb Xid = 284 COMMIT/*!*/; |
1. binlog事件格式
binlog event 由通用事件头（Event-header）、私有事件头（Post-header）和事件体（Payload）三个部分组成。所有的事件都包含事件头。在固定版本的 binlog 中，事件头的长度和格式是固定的。根据事件类型的不同，某些 binlog 事件还包含私有事件头。binlog 事件的最后一部分就是事件体，根据事件类型的不同，事件体的格式和包含的信息也各不相同，也有一些事件没有事件体，例如 stop_event 事件仅仅包含一个事件头。
所有的 binlog 事件都以一个 13 或者 19 字节的事件头开始，其中包含了该事件发生的时间、事件类型、事件长度以及 server-id 等信息。
| 字段 | 长度 | 位置 | 说明 |
| timestamp | 4字节 | event-header | 包含了该事件的开始执行时间，它表示为自 1970 年以来的秒数（UTC） |
| event_type | 1字节 | event-header | 指明了该事件的类型，1 表示 START_EVENT_V3，2 表示 QUERY_EVENT 等等。这些数字是在 enum Log_event_type 枚举中定义的 log_event.h（请参阅事件类和类型） |
| server_id | 4字节 | event-header | 标识产生该事件的 MySQL 服务器的 server-id |
| event_size | 4字节 | event-header | 标识了该事件的长度，包括通用事件头、私有事件头和事件体 3 个部分的长度。大多数事件都少于 1000 个字节，除非使用 LOAD DATA INFILE（其中事件包含加载的文件，所以它们可能很大） |
| log_pos | 4字节 | event-header | 对于 binlog 版本大于 1 的 binlog，通用事件头是 19 字节，log_pos 字段指示了下一个事件在 binlog 文件中的起始位置 |
| flags | 2字节 | event-header | 包含了一些额外的信息，例如，如果 FORMAT_DESCRIPTION_EVENT 事件的 flags 中包含了 LOG_EVENT_BINLOG_IN_USE_F 标志，表明当前 binlog 正在使用。想了解更多的话可以看看 MySQL 的相关官方文档 |
2. binlog事件类型
MySQL的binlog文件中记录的是对数据库的各种修改操作，以事件的形式来记录的。用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的几种log event有：Query event、Row event、Xid event等。其中Query event对应的是一条SQL语句，在DDL操作和STMT格式的binlog中用的比较多。Row event是个基础类，它的派生类有Row insert event、Row update event、Row delete event三种，分别对应ROW格式binlog的增、改、删操作。Xid event对应的是支持事务的commit操作，对于不支持事务的commit操作，记录的形式是Query event。其他还有一些event，比如Format log event、Rotate event等等，可以查看MySQL的官方文档了解更多相关信息。log event的种类一直在增加，比如MariaDB中新增的checkpoint event等。要MySQL本身就留有接口以便新增一个Log event，但是新增一个Log event时需要实现几个必要的方法函数，比如print、write、get_code_type等。binlog文件的内容就是各种Log event的集合。
下图展示了binlog文件中常用到的一些事件，然后对这些事件一一介绍一下。当然，还有一些binlog事件这里没有列出来，有兴趣可以参阅事件类和类型。
最初，二进制日志是使用基于语句的日志记录编写的。在MySQL 5.1.5中才开始添加了基于行的日志记录。下面几种事件类型专用于基于行的日志记录：TABLE_MAP_EVENT、WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT、DELETE_ROWS_EVENT。
FORMAT_DESCRIPTION_EVENT
格式描述事件是 binlog version 4 中为了取代之前版本中的 START_EVENT_3 事件而引入的。它是所有 binlog 文件中的第一个事件，该事件在一个 binlog 文件中仅会出现一次。MySQL 根据 FORMAT_DESCRIPTION_EVENT 事件的定义来解析 binlog 中的其他事件。
FORMAT_DESCRIPTION_EVENT 由事件头和事件体组成，事件体各字段具体含义如下：
| 字段 | 长度(字节) | 位置 | 说明 |
| binlog-version | int<2> | Payload | binlog版本 |
| mysql-server version | string[50] | Payload | 服务器版本 |
| create timestamp | int<4> | Payload | 该字段指明该binlog文件的创建时间。如果该binlog是由于切换而产生的（指flush logs命令或者binlog文件的大小达到max_binlog_size参数指定的值），那么将该字段设置为0 |
| event header length | int<1> | Payload | header 长度，19 字节 |
| event type header lengths | string[p] | Payload | 该字段是一个数组，记录了所有事件的私有事件头的长度 |
如果你需要解析 binlog，需要关注的就是 event type header lengths 这个字段，它保存了不同 event 的 post-header 长度，通常我们都不需要关注这个值，但是在解析后面非常重要的 ROWS_EVENT 的时候，就需要它来判断 TableID 的长度了。
QUERY_EVENT
QUERY_EVENT以文本的形式来记录信息。当binlog的格式是statement的时候，执行的SQL语句都记录在QUERY_EVENT中。
|
1 2 3 4 |
+------------------+------+----------------+-----------+-------------+----------------------------------------------+ | mysql-bin.000006 | 1221 | Query | 200 | 1291 | BEGIN | | mysql-bin.000006 | 1476 | Query | 200 | 1580 | use `db`; alter table t1 change id id bigint | +------------------+------+----------------+-----------+-------------+----------------------------------------------+ |
QUERY_EVENT 由事件头、私有事件头和事件体三个部分组成：
| 字段 | 长度 | 位置 | 说明 |
| slave-proxy-id | 4字节 | post-header | 某些查询可能会创建临时表，而这些临时表仅仅在当前的连接或会话中有效。为了区分不同连接或会话中的临时表，slave_proxy_id存储了不同连接或会话的线程id |
| execution time | 4字节 | post-header | 查询从开始执行到记录到binlog所花的时间，单位为秒 |
| schema length | 1字节 | post-header | schema字符串长度 |
| error-code | 2字节 | post-header | 错误码 |
| status-vars length | 2字节 | post-header | status-vars长度 |
| status-vars | status-vars length | payload | status-vars字段是以键值对的形式保存起来的一系列由SET命令设置的上下文信息，例如是否开启autocommit |
| schema | schema length | payload | 当前选择数据库 |
| query | 取决于查询的长度 | payload | query的文本格式，里面存储的可能是BEGIN、COMMIT字符串或者原生的SQL语句，等等 |
QUERY_EVENT类型的事件通常在以下几种情况中使用：
- 事务开始时，在binlog中记录一个类型为QUERY_EVENT的BEGIN事件。
- 在STATEMENT格式的binlog中，具体执行的SQL语句保存在QUERY_EVENT事件中。
- 对于ROW格式的binlog，所有的DDL操作以文本的格式记录在QUERY_EVENT事件中。
TABLE_MAP_EVENT
在 ROW 格式的 binlog 文件中，每个 ROWS_EVENT（包括：WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT、DELETE_ROWS_EVENT）事件之前都有一个 TABLE_MAP_EVENT，用于描述表的内部 id 和结构定义。也是为了保证 slave 正确复制数据的重要 event。
|
1 2 3 4 5 6 7 |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------+ | mysql-bin.000006 | 907 | Anonymous_Gtid | 200 | 972 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000006 | 972 | Query | 200 | 1042 | BEGIN | | mysql-bin.000006 | 1042 | Table_map | 200 | 1085 | table_id: 336 (db.t1) | | mysql-bin.000006 | 1085 | Write_rows | 200 | 1125 | table_id: 336 flags: STMT_END_F | | mysql-bin.000006 | 1125 | Xid | 200 | 1156 | COMMIT /* xid=320 */ | +------------------+------+----------------+-----------+-------------+-------------------------------------------------+ |
TABLE_MAP_EVENT 各个字段的含义如下：
| 字段 | 长度 | 位置 | 说明 |
| table id | int<4-6> | post-header | 表的 ID |
| flags | int<2> | post-header | 标志位，暂时未使用 |
| schema name length | int<1> | 数据库名称长度 | |
| schema name | schema name length | payload | 数据库的名称 |
| table name length | int<1> | 表名长度 | |
| table name | table name length | payload | 表名 |
| column-count | 1、3、4或9字节 | payload | 表的列数 |
| column-type-def | column-count | payload | 列的类型 |
| column-meta-def | 长度取决于列的类型 | payload | 列的元信息 |
| null-bitmap | (column-count+7)/8 | payload | 以位图的形式记录可以为NULL的列 |
这里的 table id 需要根据 post_header_len 来判断字节长度，而 post_header_len 就是存放到 FORMAT_DESCRIPTION_EVENT 事件里面的。
|
1 2 3 4 5 |
if post_header_len == 6 { 4 table id } else { 6 table id } |
这里需要注意，虽然我们可以用 table id 来代表一个特定的 table，但是因为 alter table 或者 rotate binlog event 等原因，master 会改变某个 table 的 table id，所以我们在外部不能使用这个 table id 来索引某个 table。
TABLE_MAP_EVENT 最需要关注的就是里面的 column meta 信息，因为后续如果要解析 ROWS_EVENT 的时候会根据这个来处理不同数据类型的数据。而 column def 则定义了每个列的类型。
WRITE_ROWS_EVENT/UPDATE_ROWS_EVENT/DELETE_ROWS_EVENT
对于STATEMENT格式的binlog，所有的增删改查操作的原生SQL语句都记录在QUERY_EVENT中，而对于ROW格式的binlog以ROWS_EVENT的形式记录对数据库数据的修改。ROWS_EVENT分为3种：WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT和DELETE_ROWS_EVENT，分别对应于INSERT、UPDATE和DELETE语句。
其中WRITE_ROWS_EVENT包含了要插入的数据；UPDATE_ROWS_EVENT不仅包含了行修改后的值，也包含了行修改前的值；DELETE_ROWS_EVENT仅仅包含了需要删除行的主键值/行号。另外，每个 ROWS_EVENT 中会包含此次操作改变的所有行，比如一条 SQL 删除了两行数据，那么在这个 DELETE_ROW_EVENT 中就包含了两条数据。这也就是为什么表没有主键或唯一键时会造成从库延迟。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# at 360 #190911 17:20:06 server id 100 end_log_pos 413 CRC32 0x183d45c7 Delete_rows: table id 142 flags: STMT_END_F BINLOG ' Rrx4XRNkAAAAMgAAAGgBAAAAAI4AAAAAAAEABXRjc3FsAAJ0MQACAwMAAwEBAMKG7pY= Rrx4XSBkAAAANQAAAJ0BAAAAAI4AAAAAAAEAAgAC/wABAAAAAQAAAAACAAAAAgAAAMdFPRg= '/*!*/; ### DELETE FROM `tcsql`.`t1` ### WHERE ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2=1 /* INT meta=0 nullable=1 is_null=0 */ ### DELETE FROM `tcsql`.`t1` ### WHERE ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ |
ROWS_EVENT 各个字段的含义，如下：
| 字段 | 长度 | 位置 | 说明 |
| table-id | 4-6字节 | post-header | 该ROWS_EVENT对应的表id |
| flags | 2字节 | post-header | 可以包含以下信息：该ROWS_EVENT是否是语句的最后一个事件，是否需要进行外键约束的检查，针对InnoDB的二级索引是否需要进行唯一性检查，该ROWS_EVENT是否包含了完整一行的数据，也就是说覆盖了所有列 |
| extra-data-len | 2字节 | post-header | 表所在数据库的名称 |
| extra-data | extra-data-len | post-header | extra-data的长度 |
| number of columns | 1、3、4或9字节 | payload | 仅在版本2的ROWS_EVENT中存在，用于携带额外的数据，主要目的是用于扩展 |
| columns-present-bitmap1 | (column-count+7)/8 | payload | 以位图的形式指示了该ROWS_EVENT包含了哪些列的数据 |
| columns-present-bitmap2 | (column-count+7)/8 | payload | 对于新版的UPDATE_ROWS_EVENT事件，不仅包含列修改后的值，还包含列修改前的值 |
| null-bitmap | (bit set in(column-count+7)/8) | payload | columns-present-bitmap中为空的列，会以NULL或者列对应的默认值补全 |
| value of columns | 取决于列的值 | payload | 列的数据 |
XID_EVENT
当事务提交时，不管是STATEMENT还是ROW格式的binlog，都会添加一个XID_EVENT事件作为事务的结束。该事件记录了该事务的id。在MySQL进行崩溃恢复的时候，根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事务。
|
1 2 3 |
+------------------+------+----------------+-----------+-------------+----------------------------------------------+ | mysql-bin.000006 | 1380 | Xid | 200 | 1411 | COMMIT /* xid=326 */ | +------------------+------+----------------+-----------+-------------+----------------------------------------------+ |
PREVIOUS_GTIDS_LOG_EVENT/GTID_LOG_EVENT
MySQL 5.6 引入全局事务 ID 的首要目的，是保证 Slave 在复制的时候不会重复执行相同的事务操作；其次，是用全局事务 IDs 代替由文件名和物理偏移量组成的复制位点，定位 Slave 需要复制的 binlog 内容。因此，MySQL 必须在写 binlog 时记录每个事务的全局 GTID，保证 Master/Slave 可以根据这些 GTID 忽略或者执行相应的事务。在实现上，MySQL 没有修改旧的 binlog 事件，而是新增了两类事件：
- PREVIOUS_GTIDS_LOG_EVENT：用于表示上一个binlog最后一个gitd的位置，每个binlog只有一个，当没有开启GTID时此事件为空。
- GTID_LOG_EVENT：当开启GTID时，每一个操作语句（DML/DDL）执行前就会添加一个GTID事件，记录当前全局事务ID；同时在MySQL 5.7版本中，组提交信息也存放在GTID事件中，有两个关键字段last_committed，sequence_number就是用来标识组提交信息的。
下面一个新的 binlog 文件在开启 GTID 后执行一个 DML 语句产生的事件：
|
1 2 3 4 5 6 7 8 9 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000008 | 4 | Format_desc | 200 | 123 | Server ver: 5.7.18-log, Binlog ver: 4 | | mysql-bin.000008 | 123 | Previous_gtids | 200 | 194 | 8ae691e7-33d8-11e7-be18-000c2916018b:1-2 | | mysql-bin.000008 | 194 | Gtid | 200 | 259 | SET @@SESSION.GTID_NEXT= '8ae691e7-33d8-11e7-be18-000c2916018b:3' | | mysql-bin.000008 | 259 | Query | 200 | 329 | BEGIN | | mysql-bin.000008 | 329 | Table_map | 200 | 372 | table_id: 155 (db.t1) | | mysql-bin.000008 | 372 | Write_rows | 200 | 416 | table_id: 155 flags: STMT_END_F | | mysql-bin.000008 | 416 | Xid | 200 | 447 | COMMIT /* xid=34 */ | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ |
来看一下对应mysqlbinlog -vv解析的信息：
|
1 2 3 4 5 6 7 8 9 |
# at 123 #180123 22:48:05 server id 200 end_log_pos 194 CRC32 0xde76092d Previous-GTIDs # 8ae691e7-33d8-11e7-be18-000c2916018b:1-2 # at 194 #180123 22:48:08 server id 200 end_log_pos 259 CRC32 0xacaf9041 GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= '8ae691e7-33d8-11e7-be18-000c2916018b:3'/*!*/; # at 259 #180123 22:48:08 server id 200 end_log_pos 329 CRC32 0x599d5c6c Query thread_id=4 exec_time=0 error_code=0 ..... |
关键看 Previous_gtids 与 Gtid 事件，相关信息与我们表述的基本一致。
ANONYMOUS_GTID_LOG_EVENT
这个事件是在 MySQL 5.7 版本中新增的，在 5.7 版本中 MySQL 为了识别事务是否在同一个组中，就将组提交（Group Commit）的信息存放在 GTID 中。那么如果用户没有开启 GTID 功能，即将参数 gtid_mode 设置为 OFF 呢？故 MySQL 5.7 又引入了称之为 Anonymous_Gtid（ANONYMOUS_GTID_LOG_EVENT）的二进制日志 event 类型。这意味着在 MySQL 5.7 版本中即使不开启 GTID，每个事务开始前也是会存在一个 Anonymous_Gtid，而这个 Anonymous_Gtid 事件中就存在着组提交的信息。反之，如果开启了 GTID 后，就不会存在这个 Anonymous_Gtid 了，从而组提交信息就记录在非匿名 GTID 事件中。
通过 mysqlbinlog 工具可以看到这个事件，有两个关键字段 last_committed 和 sequence_number 就是用来标识组提交信息的，如下：
|
1 2 3 |
# at 1752 #180123 5:35:21 server id 200 end_log_pos 1817 CRC32 0x10ed8d0c Anonymous_GTID last_committed=7 sequence_number=8 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; |
ROTATE_EVENT
当 binlog 文件的大小达到 max_binlog_size 参数设置的值时或者手动执行 flush logs 命令时，binlog 会发送切换，这时会在当前使用的 binlog 文件末尾添加一个 ROTATE_EVENT 事件，将下一个 binlog 文件的名称记录在该事件中。
| 字段 | 长度(字节) | 位置 | 说明 |
| position | int<8> | post-header | 位置信息 |
| name of the next binlog | string[p] | payload | binlog名称 |
它里面其实就是标明下一个 event 所在的 binlog filename 和 position。这里需要注意，当 slave 发送 binlog dump 之后，master 首先会发送一个 ROTATE_EVENT，用来告知 slave 下一个 event 所在位置，然后才跟着 FORMAT_DESCRIPTION_EVENT 事件。
STOP_EVENT
当 MySQL 停止时，会在当前 binlog 文件的结尾写入一个 STOP_EVENT 事件来表示数据库停止。STOP_EVENT 仅仅包含一个事件头，没有私有事件头和事件体部分，因为只需要在事件头的 type code 字段指定为 STOP_EVENT 就可以了，不需要携带额外的信息。
BINLOG_CHECKPOINT_EVENT
该事件是 MariaDB 引入的新事件，主要用于崩溃恢复。在两阶段事务提交过程中，当 MariaDB 崩溃时，我们需要根据 binlog 中事务的提交情况来决定是否提交存储引擎内部状态为 prepared 的事务。为了减少恢复过程中需要读取的 binlog 文件数，当某个 binlog 文件内部的所有事务都在存储引擎内部提交了，这时我们会在 binlog 中写入一个 BINLOG_CHECKPOINT_EVENT 事件。执行崩溃恢复的过程中，MariaDB 会根据读取的 BINLOG_CHECKPOINT_EVENT 来决定哪些 binlog 文件是可以不用扫描的。
|
1 2 3 4 5 |
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------------+-----------+-------------+--------------------------------------------+ | mysql-bin.000010 | 307 | Binlog_checkpoint | 300 | 350 | mysql-bin.000009 | +------------------+-----+-------------------+-----------+-------------+--------------------------------------------+ |
四、清理binlog
随着MySQL的运行，产生的binlog越来越多，当binlog占用的磁盘空间比较多的时候，就需要清理过期的或者不再需要的binlog文件。
1. 手动清理
通常，有两种方式来手动清理binlog，一种是使用MySQL提供的purge命令，一种使用系统自带的rm命令。purge命令的定义如下：
|
1 2 |
purge {binary | master} logs to "binlog-file-name" purge {binary | master} logs before "datetime-expr" |
其中第一种形式的purge命令的作用是将binlog-file-name之前的所有binlog文件清理掉，而第二种形式的purge命令的作用是将最后修改时间早于datetime-rxpr的binlog文件清理掉。
使用rm命令手动清理binlog的流程如下：
- 确保你的MySQL处于停止状态。
- 使用rm命令按顺序删除binlog文件。
- 修改index文件，把已经删除的binlog文件从index文件中删除。
在使用rm命令清理时，首先应该确保MySQL处于停止状态，因为我们要手动修改index文件。其次需要注意的是，index文件是按顺序记录使用了哪些binlog文件，所以使用rm命令来删除binlog文件时，一定要按照其在index文件中的顺序来清理，否则会出现问题。
2. 自动清理
除了手动清理binlog外，还有一种自动清理binlog的方法。在配置文件中添加expire_logs_days=N选项，这样MySQL只会保存N天的binlog，过期的binlog文件会被自动清理掉。
自动清理的具体实现是：当binlog文件发生切换或者MySQL启动时，会遍历index文件，找到第一个“最后修改时间”在N天内的binlog文件，然后将该binlog文件之前的所有binlog文件删除掉。
五、binlog_cache_mngr结构
对于非事务的存储引擎，所有的修改会立刻写入到binlog文件中。对于事务的存储引擎，事情会稍微复杂一点。因为一个事务可能包含多条语句，如果所有的修改立刻写入到binlog文件中，那么当用户需要回滚该事务的时候就会陷入麻烦之中。MySQL使用了binlog_cache_mngr结构来缓存一条事务产生的所有修改。如果用户执行提交操作，就将binlog_cache_mngr的内容写入到binlog文件中；如果用户执行回滚操作，将会丢弃binlog_cache_mngr内的修改，这样就保证binlog文件的内容和数据库的修改保持一致，如下图：
六、mysqlbinlog工具
MySQL的binlog以二进制的形式来描述数据库是如何被修改的，不像错误日志文件，慢查询日志文件那样用cat、head等命令来查看。想要查看二进制日志文件的内容，须通过MySQL提供的工具mysqlbinlog，mysqlbinlog工具可以将binlog中事件包含的信息以文本的形式打印出来。
1. STATEMENT格式的日志
对于STATEMENT格式的二进制日志文件，使用mysqlbinlog后，就可以看到执行的逻辑SQL语句，其使用方式如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# 查看二进制日志,可以选择重定向到.sql文件（可以利用.sql文件做即时点还原）; $ mysqlbinlog /mydata/mysql-bin.00001 > root.sql # 提取指定binlog日志事件的时间; $ mysqlbinlog --start-datetime='2015-11-24 15:23:45' --stop-datetime='2015-11-24 17:22:22' /mydata/mysql-bin.00001 # 提取指定binlog日志事件的位置; $ mysqlbinlog --start-position=177 --stop-position=358 /mydata/mysql-bin.00001 # 提取指定数据库binlog并转换字符集到UTF8; $ mysqlbinlog --database=test --set-charset=utf8 /mydata/mysql-bin.00001 # 远程提取日志; $ mysqlbinlog -utest -p -h192.168.1.116 -P3306 --stop-datetime="2015-12-15 20:30:23" --read-from-remote-server /mydata/mysql-bin.00001 |
基于STATEMENT的二进制文件格式
|
1 2 3 4 5 6 7 8 9 |
$ mysqlbinlog /mydata/mysql-bin.000001 ………………… # at 421 #151015 11:54:05 server id 1 end_log_pos 945 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1444881245/*!*/; insert into bb(id) value(200) /*!*/; # at 945 …………………… |
第一行at指明了该事件在binlog文件中的位置。
第二行描述了事件：事件发生的日期和时间、服务器ID、事务的结束位置、事件的位置、原服务器生成此事件时的线程ID、语句的时间戳和写入二进制日志文件的时间差、错误代码。
第三行给出了该事件锁执行的SQL语句。
第四行描述了事件的结束位置，相当于下一事件的开始位置。
2. ROW格式的日志
如果使用ROW格式的记录方式，则会发现mysqlbinlog的结果变得不可读了，我们看不到指定的SQL语句，反而是一大串我们看不懂的字符。其实只要加上参数-v或-vv（显示数据类型等信息），就能清楚地看到执行的具体信息了，mysqlbinlog会向我们解释了具体做的事情，比如更新一条语句会记录整个行更改的信息。使用方法如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$ mysqlbinlog -v /mydata/mysql-bin.00001 # at 447 #170330 14:19:25 server id 10 end_log_pos 607 CRC32 0x321d4518 Update_rows: table id 133 flags: STMT_END_F ### UPDATE `sbtest`.`sbtest` ### WHERE ### @1=1 ### @2=0 ### @3='' ### @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' ### SET ### @1=1 ### @2=1 ### @3='' ### @4='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' |
可以看出跟STATEMENT类型差异很大，ROW记录的是真正的数据信息，改变之前和改变之后的数据。如果想显示具体执行的语句，可以在配置文件添加binlog-rows-query-log-events=on参数，这样就会显示具体的逻辑执行语句了，但是有注释。具体显示结果如下。
|
1 |
# update sbtest set k=1 where id=1 |
不管是STATEMENT格式还是ROW格式，对于mysqlbinlog的输出是“可执行”的，一般可输出为.sql文件。将mysqlbinlog的输出作为mysql命令的输入，就能重放binlog中记录的修改，这对于MySQL的即时点数据恢复时很有价值的。
3. 十六进制转换格式
mysqlbinlog可以把生成的二进制日志内容转换成十六进制：
|
1 |
$ mysqlbinlog --hexdump master-bin.000001 |
十六进制输出由注释行（#）开始，因此对于上面的命令，输出可能如下所示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; # at 4 #051024 17:24:13 server id 1 end_log_pos 98 # Position Timestamp Type Master ID Size Master Pos Flags # 00000004 9d fc 5c 43 0f 01 00 00 00 5e 00 00 00 62 00 00 00 00 00 # 00000017 04 00 35 2e 30 2e 31 35 2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l| # 00000027 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |og..............| # 00000037 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| # 00000047 00 00 00 00 9d fc 5c 43 13 38 0d 00 08 00 12 00 |.......C.8......| # 00000057 04 04 04 04 12 00 00 4b 00 04 1a |.......K...| # Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13 # at startup ROLLBACK; |
十六进制输出当前包含以下列表中的元素。
Position：日志文件中的字节位置。
Timestamp：事件时间戳，在示出的例子中，’43 5c fc 9d’的十进制是1130167453，等同于’051024 17:24:13’。
Type：事件类型代码，在所示的例子中，’0f’的十进制为15，表示事件为FORMAT_DESCRIPTION_EVENT。
Master ID：创建事件的主服务器的server-id。
Size：事件的字节大小。
Master Pos：原始主日志文件中下一个事件的位置，‘62’的十进制为98。
Flags：16个标志，目前只使用了几个，其他人留将来使用。比如LOG_EVENT_BINLOG_IN_USE_F（01 00）表示日志文件正常关闭。
--base64-output=name：确定输出语句何时应为 base64-encoded 的 BINLOG 语句：”从不”禁用它，并且仅适用于没有基于行的事件的 binlog;如果还给出了 –verbose 选项，则”decode-rows”将行事件解码为注释的伪 SQL 语句;”auto”仅在必要时打印 base64（即，对于基于行的事件和格式描述事件）。 如果未提供 –base64-output[=name] 选项，则默认值为”auto”。
<参考>
MariaDB原理与实现
http://dev.mysql.com/doc/internals/en/binary-log.html
https://mariadb.com/kb/en/library/replication-protocol/
https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog-hexdump.html
