共计 11079 个字符,预计需要花费 28 分钟才能阅读完成。
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 |
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-08/134392.htm
