谈到mysql ,最为重要的日志就是binlog.MySQL提供了4种不同的日志，分别是错误日志（error log）、普通日志（general log）、慢日志（slow log）以及二进制日志（binlog）。
1.错误日志（error log）:记录了系统启动、运行以及停止过程中遇到的一些问题
2.普通日志：记录了MySQL执行的所有语句以及语句开始执行的时间等信息，用户可以选择性打开它
3.慢日志：记录了MySQL所有慢查询的相关信息
4.二进制日志：binlog记录了MySQL的库表结构以及表数据的所有变更信息。
那么我们着重的讲一下binlog.
一、简介
binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。
从宏观上来看，binlog由一系列binlog文件和一个index文件组成。数据库的所有变更信息以事件的形式记录在binlog文件，index文件记录了当前使用了哪些binlog文件。binlog文件以一个4字节的常量作为开头（标识这是一个binlog文件），后面跟着一系列的binlog事件。对于不同的binlog格式，相同语句记录在binlog文件中的事件也有所不同。
1.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中存在，那么将其回滚，这样就保证了数据在主库和从裤之间的一致性。
1.2 index文件
为了管理所有的binlog文件，MySQL额外创建了一个base-name.index文件，它按顺序记录了MySQL使用的所有binlog文件。如果你想自定义index文件的名称，可以设置 log_bin_index=file参数。千万不要在mysqld运行的时候手动修改index文件的内容，这样会使mysqld产生混乱。
二、binlog的使用
2.1 开启binlog
如果想开启binlog，默认关闭，可以在MySQL配置文件中通过配置参数 log-bin = [base-name]启动二进制日志。如果不指定base-name，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，例如mysql-bin.000001，所在目录为数据库所在目录（datadir）。顺序说一下，对于二进制文件当满足下面三种情况时会创建新的文件，文件后缀会自增。
- 文件大小达到max_binlog_size参数设置值时。
- 执行flush logs命令。
- 重启mysqld进程。
PS：你可能会有顾虑，当文件后缀从000001增长到999999时会怎样？有网友测试过，当文件达到999999时又会回到000001，并不会有什么异常。
2.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()等无法确定的函数。
2.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
|
2.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
在默认情况下，二进制日志并不是在每次写的时候同步的磁盘（用户可以理解为缓冲写）。因此，当数据库所在的操作系统发生宕机时，可能会有最后一部分数据没有写入二进制文件中，这会给恢复和复制带来问题。参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。sync_binlog的默认值为0，如果使用Innodb存储引擎进行复制，并且想得到最大的高可用性，建议将该值设为ON。不过该值为ON时，确时会对数据库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
/
*
!
*
/
;
|
3.1 binlog事件格式
binlog事件由通用事件头（common-header）、私有事件头（post-header）和事件体（body）3部分组成。所有的事件都包含通用事件头。在固定版本的binlog中，通用事件头的长度和格式是固定的。根据事件类型的不同，某些binlog事件还包含私有事件头。binlog事件的最后一部分就是事件体，根据事件类型的不同，事件体的格式和包含的信息也各不相同，也有一些事件没有事件体，例如stop_event仅仅包含一个通用事件头。
所有的binlog事件都以一个13或者19字节的通用事件头开始，其中包含了该事件发生的时间、事件类型、事件长度以及server-id等信息。通用事件头定义如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
+=
===
===
===
===
===
===
===
===
===
+
|
timestamp
0
:
4
|
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
+
|
type
_code
4
:
1
|
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
+
|
server
_id
5
:
4
|
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
+
|
event
_length
9
:
4
|
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
+
|
next
_position
13
:
4
|
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
+
|
flags
17
:
2
|
+=
===
===
===
===
===
===
===
===
===
+
|
timestamp：包含了该事件的开始执行时间，它表示为自1970年以来的秒数（UTC）。
type code：指明了该事件的类型，1表示START_EVENT_V3，2表示QUERY_EVENT等等。这些数字是在enum Log_event_type枚举中定义的log_event.h（请参阅事件类和类型）。
server_id：标识产生该事件的MySQL服务器的server-id。
event length：标识了该事件的长度，包括通用事件头、私有事件头和事件体3个部分的长度。大多数事件都少于1000个字节，除非使用LOAD DATA INFILE（其中事件包含加载的文件，所以它们可能很大）。
next_position：对于版本大于1的binlog，通用事件头是19字节，next_position字段指示了下一个事件在binlog文件中的位置。
flags：包含了一些额外的信息，例如，如果FORMAT_DESCRIPTION_EVENT事件的flags中包含了LOG_EVENT_BINLOG_IN_USE_F标志，表明当前binlog正在使用。想了解更多的话可以看看MySQL的相关官方文档。
3.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 | 2字节 | event-body | binlog版本 |
| mysql-server version | 50字节 | event-body | 服务器版本 |
| create-timestamp | 4字节 | event-body | 该字段指明该binlog文件的创建时间。如果该binlog是由于切换而产生的（指flush logs命令或者binlog文件的大小达到max_binlog_size参数指定的值），那么将该字段设置为0 |
| event header length | 1字节 | event-body | 19 |
| event type header lengths | 数组 | event-body | 该字段是一个数组，记录了所有事件的私有事件头的长度 |
- 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由通用事件头、私有事件头和事件体3部分组成：
| 字段 | 长度 | 位置 | 说明 |
| 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 | event-body | status-vars字段是以键值对的形式保存起来的一系列由SET命令设置的上下文信息，例如是否开启autocommit |
| schema | schema length | event-body | 当前选择数据库 |
| query | 取决于查询的长度 | event-body | query的文本格式，里面存储的可能是BEGIN、COMMIT字符串或者原生的SQL语句，等等 |
QUERY_EVENT类型的事件通常在以下几种情况中使用：
1. 事务开始时，在binlog中记录一个类型为QUERY_EVENT的BEGIN事件。
2. 在STATEMENT格式的binlog中，具体执行的SQL语句保存在QUERY_EVENT事件中。
3. 对于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 | 4-6字节 | post-header | 表id |
| flags | 2字节 | post-header | 标志位，暂时未使用 |
| schema name | schema name length | event-body | 表所在数据库的名称 |
| table name | table name length | event-body | 表名 |
| column-count | 1、3、4或9字节 | event-body | 表的列数 |
| column-type-def | column-count | event-body | 列的类型 |
| column-meta-def | 长度取决于列的类型 | event-body | 列的元信息 |
| null-bitmap | (column-count+7)/8 | event-body | 以位图的形式记录可以为NULL的列 |
- 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各个字段的含义，如下：
| 字段 | 长度 | 位置 | 说明 |
| 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字节 | event-body | 仅在版本2的ROWS_EVENT中存在，用于携带额外的数据，主要目的是用于扩展 |
| columns-present-bitmap1 | (column-count+7)/8 | event-body | 以位图的形式指示了该ROWS_EVENT包含了哪些列的数据 |
| columns-present-bitmap2 | (column-count+7)/8 | event-body | 对于新版的UPDATE_ROWS_EVENT事件，不仅包含列修改后的值，还包含列修改前的值 |
| null-bitmap | (bit set in(column-count+7)/8) | event-body | columns-present-bitmap中为空的列，会以NULL或者列对应的默认值补全 |
| value of columns | 取决于列的值 | event-body | 列的数据 |
- 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文件的名称记录在该事件中。
|
1
2
3
4
5
|
+
--
--
--
--
--
--
--
--
--
+
--
--
-
+
--
--
--
--
--
--
--
--
+
--
--
--
--
--
-
+
--
--
--
--
--
--
-
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
-
+
|
Log_name
|
Pos
|
Event_type
|
Server_id
|
End_log_pos
|
Info
|
+
--
--
--
--
--
--
--
--
--
+
--
--
-
+
--
--
--
--
--
--
--
--
+
--
--
--
--
--
-
+
--
--
--
--
--
--
-
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
-
+
|
mysql
-
bin
.
000009
|
312
|
Rotate
|
200
|
359
|
mysql
-
bin
.
000006
;
pos
=
4
|
+
--
--
--
--
--
--
--
--
--
+
--
--
-
+
--
--
--
--
--
--
--
--
+
--
--
--
--
--
-
+
--
--
--
--
--
--
-
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
-
+
|
- STOP_EVENT
当MySQL停止时，会在当前binlog文件的结尾写入一个STOP_EVENT事件来表示数据库停止。STOP_EVENT仅仅包含一个通用事件头，没有私有事件头和事件体部分，因为只需要在公有事件头的type code字段指定为STOP_EVENT就可以了，不需要携带额外的信息。
|
1
2
3
4
5
|
+
--
--
--
--
--
--
--
--
--
+
--
--
-
+
--
--
--
--
--
--
--
--
+
--
--
--
--
--
-
+
--
--
--
--
--
--
-
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
-
+
|
Log_name
|
Pos
|
Event_type
|
Server_id
|
End_log_pos
|
Info
|
+
--
--
--
--
--
--
--
--
--
+
--
--
-
+
--
--
--
--
--
--
--
--
+
--
--
--
--
--
-
+
--
--
--
--
--
--
-
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
-
+
|
mysql
-
bin
.
000009
|
405
|
Stop
|
200
|
428
|
|
+
--
--
--
--
--
--
--
--
--
+
--
--
-
+
--
--
--
--
--
--
--
--
+
--
--
--
--
--
-
+
--
--
--
--
--
--
-
+
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
-
+
|
- 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文件。
4.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文件中的顺序来清理，否则会出现问题。
4.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中事件包含的信息以文本的形式打印出来。
6.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语句。
第四行描述了事件的结束位置，相当于下一事件的开始位置。
6.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的即时点数据恢复时很有价值的。
6.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）表示日志文件正常关闭。
437
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
