程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL binlog中的事件類型,mysqlbinlog

MySQL binlog中的事件類型,mysqlbinlog

編輯:MySQL綜合教程

MySQL binlog中的事件類型,mysqlbinlog


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

 

參考

1. MariaDB原理與實現

2. MySQL官方文檔

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved