欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL binlog中的事件类型详解

程序员文章站 2023-12-19 14:27:04
mysql binlog记录的所有操作实际上都有对应的事件类型的,譬如statement格式中的dml操作对应的是query_event类型,row格式下的dml操作对应的...

mysql binlog记录的所有操作实际上都有对应的事件类型的,譬如statement格式中的dml操作对应的是query_event类型,row格式下的dml操作对应的是rows_event类型。

首先,看看源码中定义的事件类型

源码位置:mysql-5.7.14/libbinlogevents/include/binlog_event.h

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 */
};

实际上还是蛮多的,下面就挑几个重点的说一下

query_event

query_event以文本的形式来记录事务的操作。

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

1. 事务开始时,执行的begin操作。

2. statement格式中的dml操作

3. row格式中的ddl操作

譬如:

mysql> show binlog events in 'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| log_name | pos | event_type | server_id | end_log_pos | info |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000021 | 4 | format_desc | 1 | 120 | server ver: 5.6.31-log, binlog ver: 4 |
| mysql-bin.000021 | 120 | query | 1 | 195 | begin |
| mysql-bin.000021 | 195 | query | 1 | 298 | insert into test.t1 values(1,'a') |
| mysql-bin.000021 | 298 | xid | 1 | 329 | commit /* xid=25 */ |
| mysql-bin.000021 | 329 | query | 1 | 408 | begin |
| mysql-bin.000021 | 408 | query | 1 | 515 | use `test`; insert into test.t1 values(2,'b') |
| mysql-bin.000021 | 515 | xid | 1 | 546 | commit /* xid=33 */ |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+

format_description_event

format_description_event是binlog version 4中为了取代之前版本中的start_event_v3事件而引入的。它是binlog文件中的第一个事件,而且,该事件只会在binlog中出现一次。mysql根据format_description_event的定义来解析其它事件。

它通常指定了mysql server的版本,binlog的版本,该binlog文件的创建时间。

譬如:

# at 4
#160817 11:00:10 server id 1 end_log_pos 120 crc32 0x03010da1 start: binlog v 4, server v 5.6.31-log created 160817 11:00:10
# warning: this binlog is either in use or was not closed properly. 
mysql> show binlog events in 'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| log_name | pos | event_type | server_id | end_log_pos | info |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000021 | 4 | format_desc | 1 | 120 | server ver: 5.6.31-log, binlog ver: 4 |
... 

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。

譬如:

mysql> show binlog events in 'mysql-bin.000027';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| log_name | pos | event_type | server_id | end_log_pos | info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000027 | 4 | format_desc | 1 | 120 | server ver: 5.6.31-log, binlog ver: 4 |
| mysql-bin.000027 | 120 | query | 1 | 188 | begin |
| mysql-bin.000027 | 188 | table_map | 1 | 236 | table_id: 80 (test.t1) |
| mysql-bin.000027 | 236 | write_rows | 1 | 278 | table_id: 80 flags: stmt_end_f |
| mysql-bin.000027 | 278 | xid | 1 | 309 | commit /* xid=198 */ |
| mysql-bin.000027 | 309 | query | 1 | 377 | begin |
| mysql-bin.000027 | 377 | table_map | 1 | 425 | table_id: 80 (test.t1) |
| mysql-bin.000027 | 425 | update_rows | 1 | 475 | table_id: 80 flags: stmt_end_f |
| mysql-bin.000027 | 475 | xid | 1 | 506 | commit /* xid=199 */ |
| mysql-bin.000027 | 506 | query | 1 | 574 | begin |
| mysql-bin.000027 | 574 | table_map | 1 | 622 | table_id: 80 (test.t1) |
| mysql-bin.000027 | 622 | delete_rows | 1 | 664 | table_id: 80 flags: stmt_end_f |
| mysql-bin.000027 | 664 | xid | 1 | 695 | commit /* xid=200 */ |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)

xid_event

在事务提交时,不管是statement还是row格式的binlog,都会在末尾添加一个xid_event事件代表事务的结束。该事件记录了该事务的id,在mysql进行崩溃恢复时,根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事务。

rotate_event

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

mysql> show binlog events in 'mysql-bin.000028';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| log_name | pos | event_type | server_id | end_log_pos | info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000028 | 4 | format_desc | 1 | 120 | server ver: 5.6.31-log, binlog ver: 4 |
| mysql-bin.000028 | 120 | rotate | 1 | 167 | mysql-bin.000029;pos=4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
# at 120
#160817 12:34:26 server id 1 end_log_pos 167 crc32 0xd965567c rotate to mysql-bin.000029 pos: 4 

gtid_log_event

在启用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/*!*/;
mysql> show binlog events in 'mysql-bin.000033';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| log_name | pos | event_type | server_id | end_log_pos | info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000033 | 4 | format_desc | 1 | 120 | server ver: 5.6.31-log, binlog ver: 4 |
| mysql-bin.000033 | 120 | previous_gtids | 1 | 191 | cad449f2-5d4f-11e6-b353-000c29c64704:1 |
| mysql-bin.000033 | 191 | gtid | 1 | 239 | set @@session.gtid_next= 'cad449f2-5d4f-11e6-b353-000c29c64704:2' |
| mysql-bin.000033 | 239 | query | 1 | 314 | begin |
| mysql-bin.000033 | 314 | query | 1 | 417 | insert into test.t1 values(1,'a') |
| mysql-bin.000033 | 417 | xid | 1 | 448 | commit /* xid=11 */ |
| mysql-bin.000033 | 448 | gtid | 1 | 496 | set @@session.gtid_next= 'cad449f2-5d4f-11e6-b353-000c29c64704:3' |
| mysql-bin.000033 | 496 | query | 1 | 571 | begin |
| mysql-bin.000033 | 571 | query | 1 | 674 | insert into test.t1 values(2,'b') |
| mysql-bin.000033 | 674 | xid | 1 | 705 | commit /* xid=12 */ |
| mysql-bin.000033 | 705 | rotate | 1 | 752 | mysql-bin.000034;pos=4 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
11 rows in set (0.00 sec)

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> show binlog events in 'mysql-bin.000033';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| log_name | pos | event_type | server_id | end_log_pos | info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000033 | 4 | format_desc | 1 | 120 | server ver: 5.6.31-log, binlog ver: 4 |
| mysql-bin.000033 | 120 | previous_gtids | 1 | 191 | cad449f2-5d4f-11e6-b353-000c29c64704:1 |
| mysql-bin.000033 | 191 | gtid | 1 | 239 | set @@session.gtid_next= 'cad449f2-5d4f-11e6-b353-000c29c64704:2' |
| mysql-bin.000033 | 239 | query | 1 | 314 | begin |
| mysql-bin.000033 | 314 | query | 1 | 417 | insert into test.t1 values(1,'a') |
| mysql-bin.000033 | 417 | xid | 1 | 448 | commit /* xid=11 */ |
| mysql-bin.000033 | 448 | gtid | 1 | 496 | set @@session.gtid_next= 'cad449f2-5d4f-11e6-b353-000c29c64704:3' |
| mysql-bin.000033 | 496 | query | 1 | 571 | begin |
| mysql-bin.000033 | 571 | query | 1 | 674 | insert into test.t1 values(2,'b') |
| mysql-bin.000033 | 674 | xid | 1 | 705 | commit /* xid=12 */ |
| mysql-bin.000033 | 705 | rotate | 1 | 752 | mysql-bin.000034;pos=4 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
11 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000034';
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| log_name | pos | event_type | server_id | end_log_pos | info |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| mysql-bin.000034 | 4 | format_desc | 1 | 120 | server ver: 5.6.31-log, binlog ver: 4 |
| mysql-bin.000034 | 120 | previous_gtids | 1 | 191 | cad449f2-5d4f-11e6-b353-000c29c64704:1-3 |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
2 rows in set (0.00 sec)

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。

# at 120
#160818 5:39:38 server id 1 end_log_pos 191 crc32 0x4e84f3b5 previous-gtids
# cad449f2-5d4f-11e6-b353-000c29c64704:1-3 

stop_event

当mysql数据库停止时,会在当前的binlog末尾添加一个stop_event事件表示数据库停止。

譬如:

mysql> show binlog events in 'mysql-bin.000030';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| log_name | pos | event_type | server_id | end_log_pos | info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000030 | 4 | format_desc | 1 | 120 | server ver: 5.6.31-log, binlog ver: 4 |
| mysql-bin.000030 | 120 | stop | 1 | 143 | |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.04 sec)
# at 120
#160818 5:18:04 server id 1 end_log_pos 143 crc32 0xf20ddc85 stop

以上所述是小编给大家介绍的mysql binlog中的事件类型详解,希望对大家有所帮助

上一篇:

下一篇: