MySQL binlog中的事件類(lèi)型詳解
更新時(shí)間：2016年08月18日 15:36:13 作者：iVictor
這篇文章主要介紹了MySQL binlog中的事件類(lèi)型詳解,介紹的非常詳細(xì)，具有參考借鑒價(jià)值，需要的朋友可以參考下
MySQL binlog記錄的所有操作實(shí)際上都有對(duì)應(yīng)的事件類(lèi)型的，譬如STATEMENT格式中的DML操作對(duì)應(yīng)的是QUERY_EVENT類(lèi)型，ROW格式下的DML操作對(duì)應(yīng)的是ROWS_EVENT類(lèi)型。
首先，看看源碼中定義的事件類(lèi)型
源碼位置：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 */
};
實(shí)際上還是蠻多的，下面就挑幾個(gè)重點(diǎn)的說(shuō)一下
QUERY_EVENT
QUERY_EVENT以文本的形式來(lái)記錄事務(wù)的操作。
QUERY_EVENT類(lèi)型的事件通常在以下幾種情況下使用：
1. 事務(wù)開(kāi)始時(shí)，執(zhí)行的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文件中的第一個(gè)事件，而且，該事件只會(huì)在binlog中出現(xiàn)一次。MySQL根據(jù)FORMAT_DESCRIPTION_EVENT的定義來(lái)解析其它事件。
它通常指定了MySQL Server的版本，binlog的版本，該binlog文件的創(chuàng)建時(shí)間。
譬如：
# 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
對(duì)于ROW格式的binlog，所有的DML語(yǔ)句都是記錄在ROWS_EVENT中。
ROWS_EVENT分為三種：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分別對(duì)應(yīng)insert，update和delete操作。
對(duì)于insert操作，WRITE_ROWS_EVENT包含了要插入的數(shù)據(jù)
對(duì)于update操作，UPDATE_ROWS_EVENT不僅包含了修改后的數(shù)據(jù)，還包含了修改前的值。
對(duì)于delete操作，僅僅需要指定刪除的主鍵（在沒(méi)有主鍵的情況下，會(huì)給定所有列）
對(duì)于QUERY_EVENT事件，是以文本形式記錄DML操作的。而對(duì)于ROWS_EVENT事件，并不是文本形式，所以在通過(guò)mysqlbinlog查看基于ROW格式的binlog時(shí)，需要指定-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
在事務(wù)提交時(shí)，不管是STATEMENT還是ROW格式的binlog，都會(huì)在末尾添加一個(gè)XID_EVENT事件代表事務(wù)的結(jié)束。該事件記錄了該事務(wù)的ID，在MySQL進(jìn)行崩潰恢復(fù)時(shí)，根據(jù)事務(wù)在binlog中的提交情況來(lái)決定是否提交存儲(chǔ)引擎中狀態(tài)為prepared的事務(wù)。
ROTATE_EVENT
當(dāng)binlog文件的大小達(dá)到max_binlog_size的值或者執(zhí)行flush logs命令時(shí)，binlog會(huì)發(fā)生切換，這個(gè)時(shí)候會(huì)在當(dāng)前的binlog日志添加一個(gè)ROTATE_EVENT事件，用于指定下一個(gè)日志的名稱(chēng)和位置。
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實(shí)際上為每個(gè)事務(wù)都分配了個(gè)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
開(kāi)啟GTID模式后，每個(gè)binlog開(kāi)頭都會(huì)有一個(gè)PREVIOUS_GTIDS_LOG_EVENT事件，它的值是上一個(gè)binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT，實(shí)際上，在數(shù)據(jù)庫(kù)重啟的時(shí)候，需要重新填充gtid_executed的值，該值即是最新一個(gè)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，這樣，在下一個(gè)日志，即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
當(dāng)MySQL數(shù)據(jù)庫(kù)停止時(shí)，會(huì)在當(dāng)前的binlog末尾添加一個(gè)STOP_EVENT事件表示數(shù)據(jù)庫(kù)停止。
譬如：
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中的事件類(lèi)型詳解，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
您可能感興趣的文章:
- mysql的計(jì)劃任務(wù)與事件調(diào)度實(shí)例分析
- MySQL計(jì)劃任務(wù)(事件調(diào)度器) Event Scheduler介紹
- MySQL定時(shí)任務(wù)(EVENT事件)如何配置詳解
- 帶你了解MySQL中的事件調(diào)度器EVENT
- mysql事件之修改事件（ALTER EVENT）、禁用事件（DISABLE）、啟用事件（ENABLE）、事件重命名及數(shù)據(jù)庫(kù)事件遷移操作詳解
- mysql定時(shí)任務(wù)(event事件)實(shí)現(xiàn)詳解
- 老生常談mysql event事件調(diào)度器(必看篇)
- 詳解MySQL用事件調(diào)度器Event Scheduler創(chuàng)建定時(shí)任務(wù)
- 逐步講解MySQL中定時(shí)事件計(jì)劃的創(chuàng)建
- MySQL的事件調(diào)度器使用介紹
- mysql事件的開(kāi)啟和調(diào)用
- MySQL 使用事件（Events）完成計(jì)劃任務(wù)
相關(guān)文章
Mysql用戶權(quán)限分配實(shí)戰(zhàn)項(xiàng)目詳解
用戶是數(shù)據(jù)庫(kù)的使用者和管理者,MySQL通過(guò)用戶的設(shè)置來(lái)控制數(shù)據(jù)庫(kù)操作人員的訪問(wèn)與操作范圍,這篇文章主要給大家介紹了關(guān)于Mysql用戶權(quán)限分配實(shí)戰(zhàn)項(xiàng)目的相關(guān)資料,需要的朋友可以參考下2023-12-12
這篇文章主要介紹了Mysql如何優(yōu)化查詢速度問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.22.0 下載安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2020-11-11
使用MySQL Slow Log來(lái)解決MySQL CPU占用高的問(wèn)題
在Linux VPS系統(tǒng)上有時(shí)候會(huì)發(fā)現(xiàn)MySQL占用CPU高，導(dǎo)致系統(tǒng)的負(fù)載比較高。這種情況很可能是某個(gè)SQL語(yǔ)句執(zhí)行的時(shí)間太長(zhǎng)導(dǎo)致的。優(yōu)化一下這個(gè)SQL語(yǔ)句或者優(yōu)化一下這個(gè)SQL引用的某個(gè)表的索引一般能解決問(wèn)題2013-03-03
MySQL數(shù)據(jù)庫(kù)之?dāng)?shù)據(jù)表操作
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)之?dāng)?shù)據(jù)表操作，文章基于MySQL數(shù)據(jù)庫(kù)的相關(guān)資料展開(kāi)詳細(xì)的數(shù)據(jù)表操作的詳情，具有一定的參考價(jià)值，需要的小伙伴可以參考一下2022-05-05
mysql字符串拼接并設(shè)置null值的實(shí)例方法
在本文中小編給大家整理的是關(guān)于mysql 字符串拼接+設(shè)置null值的實(shí)例內(nèi)容以及具體方法，需要的朋友們可以學(xué)習(xí)下。2019-09-09
MySQL報(bào)錯(cuò)ERROR?2002?(HY000):?Canot?connect?to?local?MyS
ERROR?2002是一個(gè)常見(jiàn)的錯(cuò)誤,這可能會(huì)阻礙數(shù)據(jù)庫(kù)的正常使用,本文就來(lái)介紹一下該錯(cuò)誤的解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下2024-07-07
這篇文章主要介紹了MySQL sql_mode的使用詳解，幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2021-05-05
mysql允許外網(wǎng)訪問(wèn)以及修改mysql賬號(hào)密碼實(shí)操方法
這篇文章主要介紹了mysql允許外網(wǎng)訪問(wèn)以及修改mysql賬號(hào)密碼實(shí)操方法，有需要的朋友們可以參考學(xué)習(xí)下。2019-08-08
Mysql 數(shù)據(jù)庫(kù)訪問(wèn)類(lèi)
Mysql數(shù)據(jù)庫(kù)訪問(wèn)類(lèi) 實(shí)現(xiàn)代碼，對(duì)于想學(xué)習(xí)mysql操作類(lèi)的朋友值得一看2009-02-02