Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Understand the replication protocol. This section details how primary and replica servers communicate, exchanging binary log events to ensure data consistency and enable high availability.
An artificial event sent to the replica to indicate the name of the binary log file on the master, ensuring the replica knows which file is being read.
When a slave server connects to a MariaDB master server, the first binlog event sent is Fake ROTATE_EVENT. This event is similar to ROTATE_EVENT, but it's artificial and its purpose is to tell the replica server which the binlog file name of the master is.
This matters when the replica connects with the GTID option (no filename is given) or when using file and pos with empty file name (usually file='' and pos = 4).
The Event Type is ROTATE_EVENT (0x4).
Timestamp set to 0.
Event Tye is ROTATE_EVENT.
Next Pos is set to 0.
The content is the same as .
pos = the requested pos from slave, usually 4.
filename = the master binlog filename.
If it is the first fake rotate event, and the global server variable @@binlog_checksum is set to CRC32:
crc32_checksum (4 Bytes).
This page is licensed: CC BY-SA / Gnu FDL
Used during LOAD DATA INFILE operations, this event marks the beginning of the data load and contains the initial query information.
This event is written to the binary log file for LOAD DATA INFILE events if the server variable binlog_mode is set to "STATEMENT".
Event Type = 0x11.
Fixed data part:
The ID of the file.
Variable data part:
Null terminated data block.
This page is licensed: CC BY-SA / Gnu FDL
Describes the continuous packet stream format used to transmit binary log events from the primary server to the replica over the network.
The binary log events stored in a binary log file can be sent over the network in order to replicate data changes from the master server (where data changes are written in binary logs) to replica servers which apply data changes to their own databases.
The MariaDB replica replication protocol consists of:
A registration phase to master;
Events receiving (the master sending data when changes are available).
This event accompanies row-based events to provide the original SQL query text, which is useful for auditing and debugging replication.
ANNOTATE_ROWS_EVENT events accompany row events and describe the query which caused the row event.
You can enable this with (default on).
In the binary log, each ANNOTATE_ROWS event precedes the corresponding Table map event.
For additional information refer to the documentation.
This command is sent by a replica to the primary server to request the start of the binary log event stream from a specific file and position.
This is a command the replica sends to the master after .
The master server sends the from the requested file and position, or if GTID registration is in use, from the GTID value set in the earlier registration phase.
The payload is:
command (COM_BINLOG_DUMP = 0x12).
The requested binlog position.
The ROTATE_EVENT indicates a log rotation, specifying the name of the next binary log file and the position where writing will continue.
When a file exceeds the configured size limit, a ROTATE_EVENT is written at the end of the file, pointing to the next file in the sequence.
ROTATE_EVENT is generated locally and written to the binary log on the master. It is also written when a statement occurs on the master server.
The ROTATE_EVENT is sent to the connected replica servers.
LOG_ARTIFICIAL_F (0x20).This section is related to events sending only.
Binlog Network streams are requested with COM_BINLOG_DUMP , and each Binlog Event is prepended with a status byte. The data sent over network is then MariaDB network protocol (4 bytes) + 1 byte status flag + event data.
MariaDB network protocol 4 bytes are:
Replication protocol status byte:
uint<1> OK (0) or ERR (ff) or End of File, EOF, (fe).
Due to the 1 byte status flag, the effective data payload is event_size + 1. This means than an event of exactly 16M bytes (2 ^ 24 - 1) cannot be sent in one transmission: it requires 2 packets instead.
packet size [3] = 23 00 00 => 00 00 23 => 35 (ok byte + event size).
pkt sequence [1] = 04.
OK indicator [1] = 0 (OK).
Header, 19 bytes.
Content, string.
This page is licensed: CC BY-SA / Gnu FDL
BINLOG_SEND_ANNOTATE_ROWS_EVENTCOM_BINLOG_DUMPEvent Type is 160 (0xa0).
string The SQL statement (not null-terminated).
This page is licensed: CC BY-SA / Gnu FDL
Event type is set to 163 (0xa3).
Time stamp set to 0.
NextPos tells which is the binlog position of next event.
Flags are set to ARTIFICIAL (0x20).
The content is the same as the "real" GTID_LIST.
of GTIDs
domain_id
server_id
sequence
...
This page is licensed: CC BY-SA / Gnu FDL
uint<2> Flags.
uint<4> Slave server_id.
string The requested binlog file name.
Flags, usually set to 0. It can be set to BINLOG_SEND_ANNOTATE_ROWS_EVENT (0x02) if the replica server wants to receive the MariaDB 10 ANNOTATE_ROWS events.
It can also be set to BINLOG_DUMP_NON_BLOCK (1), in the case the replica is receiving an EOF packet after the last event sent by the master.
Requested binlog position can be 4 when registering to master server for the very first time
or when requesting events from a particular binlog file from the beginning of it.
The requested binlog file can empty when registering for the very first time if master log file is unknown or with GTID registration (not required).
After sending events to the replica the server kills the connection.
When replication resumes or it is restarted (STOP REPLICA; START REPLICA), the replica server always
sends the latest binlog file name and position, even if GTID registration is in place.
After 4 bytes network protocol header we can see:
Command [1] = 12.
Requested binlog position [4] = 34 06 00 00 => 00 00 06 34 = 1588.
Flags [2] = 02 00 => 2 = BINLOG_SEND_ANNOTATE_ROWS_EVENT.
Binlog file[n] = mysql-bin.000034.
This page is licensed: CC BY-SA / Gnu FDL
The Event Type is ROTATE_EVENT (0x4).
This page is licensed: CC BY-SA / Gnu FDL
Logged during binlog rotation or checkpoints, this event lists the GTIDs present in the binary log to help replicas determine their replication state.
Logged in every binlog to record the current replication state. Consists of the last GTID seen for each replication domain.
The Global Transaction ID, GTID for short, consists of three components:
Replication domain ID;
Master server ID;
Sequence ID.
It's represented as three numbers separated with dashes (-); for example: 1-1222-1011 .
It's usually written after the . If binary log encryption is enabled, it is written after the .
Event type is 163 (0xa3).
Number of GTIDs.
For (i=0; i < gtid_count; i++):
Replication Domain ID.
Server_ID.
The minimum content size for one GTID is 4 + (4 + 4 + 8) * 1 = 20 bytes .
From the utility:
170824 9:52:04 server id 10124 end_log_pos 292 CRC32 0xb6d8f0a8 Gtid list [0-10124-3584]
Event Time = a4 85 9e 59 ===> 2017-08-24 9:52:04.
Event Type = a3 => 163.
Server_id = 8c 27 00 00 => 00 00 27 8c => 10124.
Event Size = 2b => 43 (header[19] + 1 GTID(20 bytes) + CRC32[4].
The content example with one GTID is 20 bytes + 4 bytes CRC32:
Number of GTIDs[4] = 01 00 00 00 => 1.
GTID[0] replication_domain[4] = 00 00 00 00 => 0.
GTID[0] Server_id[4] = 8c 27 00 00 => 00 00 27 8c => 10124.
GTID[0] Sequence[8] = 00 0e 00 00 00 00 00 00 ===> 3584.
This page is licensed: CC BY-SA / Gnu FDL
This event is used for LOAD DATA INFILE operations, managing the execution phase similar to a QUERY_EVENT but with extra static fields for file handling.
This event is written to the binary log file for LOAD DATA INFILE events. The event format is similar to a QUERY_EVENT, except that it has extra static fields.
Event Type = 0x12.
The ID of the thread that issued this statement on the master.
The time in seconds that the statement took to execute.
The length of the name of the database which was the default database when the statement was executed. This name appears later, in the variable data part. It is necessary for statements such as INSERT INTO t VALUES(1) that don't specify the database and rely on the default database previously selected by USE.
Zero or more status variables. Each status variable consists of one byte code identifying the variable stored, followed by the value of the variable. The format of the value is variable-specific. The number of bytes 'n' is the length of the status variable block (read in fixed data part)
The default database name (null-terminated).
The SQL statement. By subtraction the size of the statement can be known.
This page is licensed: CC BY-SA / Gnu FDL
A marker event indicating a checkpoint in the binary log, used to ensure consistency and safe rotation of log files.
Binlog Checkpoint Event, Event Type is 161 (0xa1). This event specifies a binlog file such that XA crash recovery can start from that file.
Event type is 161 (0xa1).
Log filename length.
Log filename.
Event Timestamp = 12 ad 26 5a.
Event Type = a1 => 161.
Server_id = 84 27 00 00 => 00 00 27 84 = 10116.
Event Size = 27 00 00 00 => 00 00 00 27 = 39.
Filename length = 10 00 00 00 = >00 00 00 10 => 16.
Filename = mysql-bin.000062.
This page is licensed: CC BY-SA / Gnu FDL
This event records integer values for auto-increment columns or the LAST_INSERT_ID function, ensuring that these values are replicated deterministically.
The RAND_EVENT records the two seed values used for the random number generator, ensuring that calls to the RAND() function produce identical results on replicas.
The SQL function RAND() generates a random number.
A RAND_EVENT contains two seed values that set the rand_seed1 and rand_seed2 system variables that are used to compute the random number.
Event Type is 13 (0x0d).
The value for the first seed.
The value for the second seed.
c0 e6 27 5a 0d 84 27 00 00 23 00 00 00 a8 01 00 ..'Z..'..#...... 00 00 00 b5 ab d6 28 00 00 00 00 41 23 3b 2d 00 ......(......... 00 00 00 ....
This page is licensed: CC BY-SA / Gnu FDL
The XID_EVENT signifies the commit of a transaction, containing the transaction ID (XID) to ensure atomicity across replication.
An XID event is generated for a COMMIT of a transaction that modifies one or more tables of an XA-capable storage engine.
Event Type is XID_EVENT (0x10).
The XID transaction number.
Event size is: header[19] + XID[8] + CRC32[4] of (header + xid).
Event Time ee b7 15 5a => 5a 15 b7 ee => 1511372782 [2017-11-22 18:46:22].
Event Type = 10.
Server_id 01 00 00 00 => 1.
Event Size 1f 00 00 00 => 31.
XID 66 00 00 00 00 00 00 00 => 102.
09 30 45 a8 => a8 45 30 09 => 2823106569.
This page is licensed: CC BY-SA / Gnu FDL
This event records the preparation phase of an XA transaction, storing the XID to support two-phase commit and recovery.
An XA_PREPARE_LOG_EVENT records the prepare phase of a distributed transaction using the XA log. It is used to ensure atomicity and consistency of transactions.
Event Type is XA_PREPARE_LOG_EVENT (0x26).
One Phase Commit.
Format ID.
Length of gtrid.
Length of bqual.
Payload:
xid, where n is sum of gtrid and bqual lengths.
This page is licensed: CC BY-SA / Gnu FDL
A heartbeat event sent over the network by the master when there are no binlog events, ensuring the replica knows the connection is still active.
This event marks the beginning of encrypted data in the binary log, defining the encryption scheme and key version for subsequent events.
This section provides an overview of the various events recorded in the binary log, which are the core units of replication data transmission.
The STOP_EVENT is written to the binary log when the server shuts down, serving as a marker for a clean stop.
The master server writes the event to the when it shuts down, or when resuming after a mariadbd process crash.
A new binary log file is always created, but there is no ROTATE_EVENT.
STOP_EVENT is the last written event after a clean shutdown, or when resuming after a crash.
This event is never sent to replica servers.
This command is used by a replica to register its details, such as server ID, hostname, and port, with the primary server.
This command is sent by the replica server to start MariaDB replication, and should be sent before requesting binlog events with .
The payload is:
command (COM_REGISTER_SLAVE = 0x15).
Replica server ID.
packet #n: 3 bytes length + sequence + status + [event_header + (event data - 1)]
packet #n+1: 3 bytes length + sequence + last byte of the event data.T 127.0.0.1:8808 -> 127.0.0.1:57157 [AP]
23 00 00 04 00 00 00 00 00 1b 67 2b 00 00 22 00 '.........g+..&.
00 00 ed 01 00 00 20 00 66 6f 6f 2d 62 69 6e 2e ...... .log-bin.
31 30 30 30 31 33 39 1000139ee b7 15 5a a0 01 00 00 00 36 00 00 00 80 0b 00 ...Z.....6......
00 00 00 69 6e 73 65 72 74 20 69 6e 74 6f 20 74 ...insert into t
65 73 74 2e 74 34 20 76 61 6c 75 65 73 28 31 30 est.t4 values(10
30 29 6d 4c 42 33 0)mLB31b 00 00 00 12 34 06 00 00 02 00 75 27 00 00 6d .....4.....u'..m
79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 33 34 ysql-bin.000034T 127.0.0.1:8808 -> 127.0.0.1:57157 [AP]
30 00 00 4d 00 bc 4e 21 5a 04 d9 27 00 00 2f 00 0..M..N!Z..'../.
00 00 c0 01 00 00 00 00 04 00 00 00 00 00 00 00 ................
6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 31 39 mysql-bin.000019
b2 bc db bf ....uint<8> GTID sequence.
Flags = 00 => 0.
crc32[4] = a8 f0 d8 b6 => b6 d8 f0 a8 => 0xb6d8f0a8.
uint<2> The error code resulting from execution of the statement on the master.
uint<2> The length of the status variable block.
uint<4> The ID of the loaded file.
uint<4> Offset from the start of the statement to the beginning of the filename.
uint<4> Offset from the start of the statement to the end of the filename.
uint<1> How LOAD DATA INFILE handles duplicates (0x0: error, 0x1: ignore, 0x2: replace).
Flags = 00 => 0.
Flags 00 00 = 0.
Timestamp is set to 0.
Next position is set to last position.
Type is set to HEARTBEAT_EVENT (0x1b).
string The current master binary log name.
packet size [3] = 23 00 00 => 00 00 23 => 35 (ok byte + event size).
pkt sequence [1] = 04.
OK indicator [1] = 0 (OK).
Timestamp [4] = 00 00 00 00 => 0.
Event Type [1] = 1b => 27.
Server_id [4] = 67 2b 00 00 => 00 00 2b 67 => 111111.
Event Size [4] = 22 00 00 00 => 00 00 00 26 => 34 (header + data).
Next_pos [4] = ed 01 00 00 => 00 00 01 ed => 493.
Flags [2] == 20 00 => 00 20 = > 32.
log-bin.1000139.
This page is licensed: CC BY-SA / Gnu FDL
Event type is 164 (0xa4).
All data of following events in the binlog file are encrypted, except for the event_length field.
The 16 byte encryption IV is generated from the 12 byte nonce (uint<12>) in the binlog plus the current position of the event being encrypted (uint<4>). This means the last four bytes of the IV change for every event and the first 12 bytes change for every binlog file.
Since the event_length is always unencrypted, the encrypted data block has to be modified before it can be decrypted:
Store event_length.
Copy the first four bytes (encrypted timestamp) to event_length position (offset=9).
Decrypt starting from offset 4 and store result at offset 4 of decrypted buffer.
The unencrypted block now also needs to be modified:
Move unencrypted timestamp value from offset 9 to the beginning (offset=0).
Store event_length at position 9.
timestamp [4] = b8 5f 5a 59 => 59 5a 5f b8 => 1499094968 [2017-07-03 17:16:08].
type [1} = a4 => 164.
server_id [4} = 5d 00 00 00 => 00 00 00 5d => 93.
event_size [4] = 28 00 00 00 => 00 00 00 28 => 40 [header + content + crc32(header + content)].
next_pos [4] = 21 01 00 00 => 00 00 01 21 => 289.
flags [2] = 00 00 => 0.
Enc scheme [1] = 01 => 1.
Enc key ver [4] = 01 00 00 00 => 00 00 00 01 => 1.
Nonce [12] = eWP&cY7F/;3#.
06 bb da 62 => 62 da bb 06 => 1658501894.
This page is licensed: CC BY-SA / Gnu FDL
0x620x690x6e0xfe 'b''i''n'BINLOG_MAGICA series of binlog events.
Each event contains the 'header' followed by 'data bytes':
The header bytes provide information about:
The event type;
The creation time;
Which server created the event;
Flags and so forth.
The data bytes provide information specific to the type of event.
The remaining events are interpreted according to the version.
The final event is usually a log-rotation event ROTATE_EVENT that specifies the next binary log filename or a STOP_EVENT written during server shutdown.
Note: in case of a server crash there is no terminating event (no ROTATE nor STOP).
This page is licensed: CC BY-SA / Gnu FDL
Event header with EventType set to STOP_EVENT (0x03).
Event header NextPos set to EOF .
No special flags added.
The event has no data.
Event size = header[19] + 0 bytes data + 4 CRC32 = 23.
This page is licensed: CC BY-SA / Gnu FDL
uint<1> Replica hostname length.
string Hostname.
uint<1> Replica username length.
string Username.
uint<1> Replica password length.
string Replica password.
uint<2> Replica connection port.
uint<4> Replication rank.
uint<4> Master server ID.
Note:
Replica hostname, replica user, replica password, and replica port are usually not set.
Some replica replication parameters can be used for such settings (report_host, report_port etc).
Replication rank is not set.
Master server ID is not set either.
The replica server is configured with:
server-id=10101;
report-host=slave_n_1;
report-port=23241.
We can see from the example:
server_id [4] = 75 27 00 00 => 10101.
hostname_len [1] = 09.
hostname[n] = slave_n_1 (9 bytes).
username len [1] = 0 (not set).
password len [1] = 0 (not set).
slave port [2] = c9 5a => 23241.
rank [4] = 0.
master server id = 0.
This page is licensed: CC BY-SA / Gnu FDL
0x00
Invalid value.
0x01
LAST_INSERT_ID.
0x02
Insert id (auto_increment).
Explains the handshake and acknowledgement process for semi-synchronous replication, ensuring data is committed on at least one replica.
Regular MariaDB replication is asynchronous. MariaDB also includes semisynchronous replication semi-synchronous Binlog Event.
If the user variable @rpl_semi_sync_slave is set, 2 extra bytes are added after the status byte of a binlog network stream and before the normal binlog event header.
semi-sync indicator, always 0xef.
semi-sync flags, either 0x00 (no ACK) or 0x01 (ACK).
Note : The packet size, as in the network protocol header, is then: event_size + 1 byte status + 2 bytes semi-sync replication.
The MariaDB server sets the user variable whenever it is starting replication. For MariaDB Connector/C , the following query must be executed before the call to mariadb_rpl_open() is made to enable semi-sync replication.
If the semi-sync flag is set to 0x01, the master waits for a Semi Sync ACK packet from the slave and when the Semi Sync ACK is seen, the master acknowledges the client which has issued the transaction with a standard OK_Packet or a ERR_Packet.
The master can then write the transaction to the binary log and send the next events to the replica.
Note : The master only requests Semi Sync ACKs if is enabled. If it is not enabled, the semi-sync flag will always be 0x00.
This event is sent by the replica only if the semi-sync flag is set to 0x01.
semi-sync indicator, always 0xef;
the next position of received event;
binlog file name.
Sending an ACK when the semi-sync flag is set to 0x0 causes an error, and the connection is closed.
We can see:
2a 00 00 [3 bytes] packet size.
06 [1] sequence.
00 [1] status byte = 00 => OK.
ef 00 [2] bytes => semi sync indicator (0xef) and semi-sync flag (00).
The master sets the Semi-Sync ACK request in the XID_EVENT event:
We see the 2 semi sync bytes: ef and 01. The latter, being 1, means the replica server must send the Semi Sync ACK packet.
We also see in the binlog event header:
Event Type [1] = 10 XID_EVENT.
Next Event pos [4] = 4a 05 00 00 => 1354.
This is sent by the replica server after the XID_EVENT receiving.
We see:
The semi sync indicator [1] = 0xef, sent before anything else.
The Next Event position [8] = 4a 05 00 00 00 00 00 00 => 1354 which is the next position of the XID_EVENT above.
The binlog filename = mysql-bin.000034.
Please note:
There is no terminating CRC32.
The packet sequence now start starts from 0.
This page is licensed: CC BY-SA / Gnu FDL
This descriptor event appears at the start of every binary log file, defining the server version, binlog version, and header lengths for all event types.
This is a descriptor event that is written to the beginning of a binary log file, at position 4 (after the 4 magic number bytes). The whole event written to disk is byte<19> event header + data fields.
The Event Type is 15 (0x0f).
The binary log format version. This is 4 .
The MariaDB server version (example: 10.8.1-debug-log), padded with 0x00 bytes on the right.
Timestamp in seconds when this event was created (this is the moment when the binary log was created). This value is redundant; the same value occurs in the timestamp header field.
Checksum Algorithm Type
CRC32 4 bytes (value matters only if checksum algo is CRC32)
timestamp => 4d af 15 5a.
type = 0f => 15.
server_id = 1.
Event Size = fc => 252.
format version = 04 00 => 4.
server's version = 10.2.10-MariaDB-log .... [50 bytes].
create time = 4d af 15 5a.
header_length = 13 => 19.
timestamp => 12 ad 26 5aa.
type = 0f => 15.
server_id = 84 27 00 00 => 00 00 27 84 => 10116.
Event Size = fc => 245.
format version = 04 00 => 4.
server's version = 10.1.16-MariaDB.log .... [50 bytes].
create time = 12 ad 26 5a.
header_length = 13 => 19.
This page is licensed: CC BY-SA / Gnu FDL
a4 85 9e 59 a3 8c 27 00 00 2b 00 00 00 24 01 00 ...Y..'..+...$..
00 00 00 01 00 00 00 00 00 00 00 8c 27 00 00 00 ............'...
0e 00 00 00 00 00 00 a8 f0 d8 b6 ..........12 ad 26 5a a1 84 27 00 00 27 00 00 00 47 01 00 ..&Z..'..'...G..
00 00 00 10 00 00 00 6d 79 73 71 6c 2d 62 69 6e .......mysql-bin
2e 30 30 30 30 36 32 .000062ee b7 15 5a 10 01 00 00 00 1f 00 00 00 f2 0b 00 ...Z............
00 00 00 66 00 00 00 00 00 00 00 09 30 45 a8 ...f.........0E.T 127.0.0.1:8808 -> 127.0.0.1:57157 [AP]
23 00 00 04 00 00 00 00 00 1b 67 2b 00 00 22 00 '.........g+..&.
00 00 ed 01 00 00 20 00 66 6f 6f 2d 62 69 6e 2e ...... .log-bin.
31 30 30 30 31 33 39 1000139b8 5f 5a 59 a4 5d 00 00 00 28 00 00 00 21 01 00 ._ZY.]...(...!..
00 00 00 01 01 00 00 00 65 57 50 26 63 59 37 46 ........eWP&cY7F
2f 3b 33 23 06 bb da 62 /;3#...bfe 62 69 6e a4 85 9e 59 0f 8c 27 00 00 f5 00 00 |.bin...Y..'.....|
00 f9 00 00 00 00 00 04 00 31 30 2e 31 2e 32 34 |.........10.1.24|
2d 4d 61 72 69 61 44 42 00 6c 6f 67 00 00 00 00 |-MariaDB.log....|
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00 00 00 00 00 00 00 00 00 00 00 a4 85 9e 59 13 |..............Y.|
38 0d 00 08 00 12 00 04 04 04 04 12 00 00 dd 00 |8...............|
04 1a 08 00 00 00 08 08 08 02 00 00 00 0a 0a 0a |................|
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
04 13 04 00 01 ab 5b a2 e0 a4 85 9e 59 a3 8c 27 |......[.....Y..'|
00 00 2b 00 00 00 24 01 00 00 00 00 01 00 00 00 |..+...$.........|
00 00 00 00 8c 27 00 00 00 0e 00 00 00 00 00 00 |.....'..........|
...
...DELIMITER /*!*/;
# at 4
#170824 9:52:04 server id 10124 end_log_pos 249 CRC32 0xe0a25bab Start: binlog v 4, server v 10.1.24-MariaDB created 170824 9:52:04 at startup
ROLLBACK/*!*/;
BINLOG '
pIWeWQ+MJwAA9QAAAPkAAAAAAAQAMTAuMS4yNC1NYXJpYURCAGxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACkhZ5ZEzgNAAgAEgAEBAQEEgAA3QAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQAAatbouA=
'/*!*/;
# at 249
#170824 9:52:04 server id 10124 end_log_pos 292 CRC32 0xb6d8f0a8 Gtid list [0-10124-3584]
# at 292
#170824 9:52:04 server id 10124 end_log_pos 334 CRC32 0xf2dc685f Binlog checkpoint log-bin.000011
# at 334
#170824 9:52:13 server id 10124 end_log_pos 376 CRC32 0xe958a0ae GTID 0-10124-3585 trans
...
...3a b8 15 5a 03 01 00 00 00 17 00 00 00 09 0c 00 ..Z............
00 00 00 4e 99 ee 2c ...N..,1b 00 00 00 15 75 27 00 00 09 73 6c 61 76 65 5f .....u'...slave_
6e 5f 31 00 00 c9 5a 00 00 00 00 00 00 00 00 n_1...Z.......# at 738
#180610 11:20:56 server id 1 end_log_pos 770 CRC32 0xf5a23f2d Intvar
SET LAST_INSERT_ID=1/*!*/;78 ed 1c 5b 05 01 00 00 00 20 00 x..[..... .
00 00 02 03 00 00 00 00 01 01 00 00 00 00 00 00 ................
00 2d 3f a2 f5 .-?..# at 389
#171206 13:46:56 server id 10116 end_log_pos 424 Rand
SET @@RAND_SEED1=685157301, @@RAND_SEED2=758850369/*!*/;
# at 424-19 gives the size of the extra headers field at the end of the header for other events.byte Variable-sized. An array that indicates the post-header lengths for all event types. There is one byte per event type that the server knows about. The value 'n' comes from the following formula:
Flags = 00 => 0.
checksum_algo = 01 => 1 (CRC32).
CRC32 bytes = d6 ce 13 e2.
Flags = 00 => 0.
checksum_algo = 0 => 0 (NONE).
CRC32 bytes = 2b 91 c2 91 (useless).
SET @rpl_semi_sync_slave=1T 127.0.0.1:23240 -> 127.0.0.1:41054 [AP]
2a 00 00 06 00 ef 00 00 00 00 00 1b d9 27 00 00 *............'..
27 00 00 00 79 04 00 00 00 00 6d 79 73 71 6c 2d '...y.....mysql-
62 69 6e 2e 30 30 30 30 33 34 ed ef e1 f0 bin.000034....22 00 00 0c 00 ef 01 17 d0 37 5a 17 d0 37 5a 10 "............7Z.
d9 27 00 00 1f 00 00 00 4a 05 00 00 00 00 6f 00 .?......J.....o.
00 00 00 00 00 00 44 30 aa fc ......D0..19 00 00 00 ef 4a 05 00 00 00 00 00 00 6d 79 73 .....J.......mys
71 6c 2d 62 69 6e 2e 30 30 30 30 33 34 ql-bin.000034n = event_size - header length - offset (2 + 50 + 4 + 1) - checksum_algo - checksum4d af 15 5a 0f 01 00 00 00 fc 00 00 00 00 01 00 M..Z............
00 00 00 04 00 31 30 2e 32 2e 31 30 2d 4d 61 72 .....10.2.10-Mar
69 61 44 42 2d 6c 6f 67 00 00 00 00 00 00 00 00 iaDB-log.......
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 4d af 15 5a 13 ...........M..Z.
00 12 00 04 04 04 04 12 00 00 e4 00 04 1a 08 00 ................
00 00 08 08 08 02 00 00 00 0a 0a 0a 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 04 13 04 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 04 13 04 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 04 13 04 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 04 13 04 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 04 13 04 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 04 13 04 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 04 13 04 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 04 13 04 00 ................
0d 08 08 08 0a 0a 0a 01 d6 ce 13 e2 ............12 ad 26 5a 0f 84 27 00 00 f5 00 00 00 f9 00 00 ..&Z..'.........
00 01 00 04 00 31 30 2e 31 2e 31 36 2d 4d 61 72 .....10.1.16-Mar
69 61 44 42 00 6c 6f 67 00 00 00 00 00 00 00 00 iaDB.log........
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 12 ad 26 5a 13 38 0d 00 08 .........&Z.8...
00 12 00 04 04 04 04 12 00 00 dd 00 04 1a 08 00 ................
00 00 08 08 08 02 00 00 00 0a 0a 0a 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 04 13 04 00 ................
00 2b 91 c2 910x00
STRING_RESULT
set @a:="foo"
0x01
REAL_RESULT
set @a:= @@timestamp
0x02
INT_RESULT
set @a:= 4
0x03
ROW_RESULT
(not in use)
0x01
unsigned
This page is licensed: CC BY-SA / Gnu FDL
The GTID_EVENT marks the start of a new transaction event group, associating it with a Global Transaction ID (GTID) and providing commit flags.
For global transaction ID, used to start a new transaction event group, instead of the old BEGIN query event, and also to mark stand-alone (DDL).
GTID_EVENT event type is 162 (0xa2).
Type[1] = 0xa2.
Flags[2] = 08 00 => LOG_EVENT_SUPPRESS_USE_F.
GTID sequence.
Replication Domain ID.
Flags.
If flag & FL_GROUP_COMMIT_ID:
commit_id.
Else if flag & (FL_PREPARED_XA or FL_COMPLETED_XA):
format_id.
gtid_length.
bqual_length.
xid, where n is sum of gtrid and bqual lengths.
Else:
0.
GTID seq[8] = 9b 26 00 00 00 00 00 00 => 9883.
domain id[4] = 00 00 00 00 00 => 0.
flags[1] = 29 => 41 (FL_DDL =32 + FL_ALLOW_PARALLEL=8 + FL_STANDALONE=1).
GTID seq[8] = 9c 26 00 00 00 00 00 00 => 9884.
domain id[4] = 00 00 00 00 00 => 0.
flags[1] = 0c => 12 (FL_ALLOW_PARALLEL=8 + FL_TRANSACTIONAL=4).
commit_id[6] = 00 00 00 00 00 00 = 0.
This page is licensed: CC BY-SA / Gnu FDL
# at 511
#180610 10:26:43 server id 1 end_log_pos 554 CRC32 0x7dd93d6b User_var
SET @`foo`:=_utf8 X'626172' COLLATE `utf8_general_ci`/*!*/;c3 e0 1c 5b 0e 01 00 00 00 2b 00 ...[.....+.
00 00 2a 02 00 00 00 00 03 00 00 00 66 6f 6f 00 ..*.........foo.
00 21 00 00 00 03 00 00 00 62 61 72 6b 3d d9 7d .!.......bark=.} ....0x04
DECIMAL_RESULT
set @a:=1.2345
FL_WAITED
16
Set if a row lock wait (or other wait) is detected during the execution of the transaction.
FL_DDL
32
Set for event group containing DDL.
FL_PREPARED_XA
64
Set for XA transaction.
FL_COMPLETED_XA
128
XA transaction completed (committed or rolled back).
CRC32[4] = 8e 66 9a 30.
CRC32[4] = 37 d3 c8 23.
FL_STANDALONE
1
Set when there is no terminating COMMIT event.
FL_GROUP_COMMIT_ID
2
Set when event group is part of a group commit on the master. Groups with same commit_id are part of the same group commit.
FL_TRANSACTIONAL
4
Set for an event group that can be safely rolled back (no MyISAM, for instance).
FL_ALLOW_PARALLEL
8
Reflects the (negation of the) value of @@SESSION.skip_parallel_replication at the time of commit.
BEGIN
#171205 18:22:52 server id 10124 end_log_pos 652 CRC32 0x23c8d337 GTID 0-10124-9884 trans
TBALE_MAP
#171205 18:22:52 server id 10124 end_log_pos 752 CRC32 0x52601513 Table_map: `test`.`t4` mapped to number 92
WRITE
#171205 18:22:52 server id 10124 end_log_pos 790 CRC32 0x8869c123 Write_rows: table id 92 flags: STMT_END_F
COMMIT
#171205 18:22:52 server id 10124 end_log_pos 821 CRC32 0x15517636 Xid = 42004#171205 17:44:27 server id 10124 end_log_pos 535 CRC32 0x309a668e GTID 0-10124-9883 ddl
#171205 17:44:27 server id 10124 end_log_pos 610 CRC32 0xda151470 Query thread_id=819 ...eb cc 26 5a a2 8c 27 00 00 2a 00 00 00 17 02 00 ...&Z..'..*.....
00 08 00 9b 26 00 00 00 00 00 00 00 00 00 00 29 ...&..........).
00 00 00 00 00 00 8e 66 9a 30 ......f.0.ec d5 26 5a a2 8c 27 00 00 2a 00 00 00 8c 02 00 ..&Z..'..*......
00 08 00 9c 26 00 00 00 00 00 00 00 00 00 00 0c ....&...........
00 00 00 00 00 00 37 d3 c8 23 ......7..#This event provides a mapping between a table ID and its table definition, preceding row events to interpret the row data correctly.
19 (0x13).uint<1> Database name length.
string The database name (null-terminated).
uint<1> Table name length.
string The table name (null-terminated).
The number of columns in the table.
An array of 'n' column types, one byte per column.
The length of the metadata block.
The metadata block;
Bit-field indicating whether each column can be NULL, one bit per column.
If (more_data_available):
Optional metadata block.
The metadata block contains type specific metadata information for each column.
MYSQL_TYPE_BLOB
1
Number of bytes for length: e.g. 4 bytes means length is stored in a 4 byte integer).
MYSQL_TYPE_DATETIME2
1
Length of microseconds.
MYSQL_TYPE_DECIMAL
2
Not in use anymore.
MYSQL_TYPE_DOUBLE ,MYSQL_TYPE_FLOAT
1
length (4 or 8 bytes).
Optional metadata are available if the global server variable BINLOG_ROW_METADATA is set to MIN or FULL.
The metadata block consists of one or more of the following blocks:
Optional metadata types:
SIGNEDNESS
1
MIN
Data contains a bitmap indicating which integer columns are signed.
DEFAULT_CHARSET
2
MIN
Character set of string columns, used if most columns have the same result. Columns with other character sets will follow as pair (column_index, collation number).
COLUMN_CHARSET
3
MIN
This page is licensed: CC BY-SA / Gnu FDL
# at 847
#171206 13:43:00 server id 10124 end_log_pos 892 CRC32 0xbe3c6b05 Table_map: `test`.`t4` mapped to number 33
# at 892d4 e5 27 5a 13 8c 27 00 00 2d 00 00 00 7c 03 00 ..'Z..'..-...|..
00 00 00 21 00 00 00 00 00 01 00 04 74 65 73 74 ...!........test
00 02 74 34 00 01 03 01 01 05 6b 3c be ..t4......k<.MYSQL_TYPE_STRING
2
The first byte contains type (MYSQL_TYPE_STRING, MYSQL_TYPE_ENUM, or MYSQL_TYPE_SET). The second byte contains the length.
MYSQL_TYPE_NEWDECIMAL
2
Precision, Scale.
MYSQL_TYPE_TIME2
1
Length in microseconds.
MYSQL_TYPE_TIMESTAMP2
1
Length in microseconds.
MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VAR_STRING
2
Defined varchar length. If the value is > 255, the length is stored in 2 bytes, otherwise in 1 byte.
Character set of columns, used if columns have different character sets. Returned as a sequence of collation numbers.
COLUMN_NAME
4
FULL
List of Column names, the first byte specifies the length of the column name.
SET_STR_VALUE
5
FULL
List of set values: First byte is the number of different values, followed by length/value pairs.
ENUM_STR_VALUE
6
FULL
Same as SET_STR_VALUE. Since ENUM values might have up to 0xFFFF members, the number of values is a length encoded integer.
GEOMETRY_TYPE
7
FULL
A sequence of bytes repesenting the type of GEOMETRY columns: 0 = GEOMETRY, 1 = POINT, 2 = LINESTRING, 3 = POLYGON, 4=MULTIPOINT, 5 = MULTILINESTRING, 6 = MULTIPOLYGON, 7 = GEOMETRYCOLLECTION.
SIMPLE_PRIMARY_KEY
8
FULL
A sequence of length encoded column indexes.
PRIMARY_KEY_WITH_PREFIX
9
FULL
A sequence of length encoded column indexes and prefix lengths.
ENUM_AND_SET_DEFAULT_CHARSET
10
FULL
The default character set number used for ENUM and SET columns.
ENUM_AND_SET_COLUMN_CHARSET
11
FULL
Character set of ENUM and SET columns, used if these columns have different character sets. Returned as a sequence of collation numbers.
uint<4> Timestamp (creation time).
uint<1> Event Type (type_code).
uint<4> Server_id (server which created the event).
uint<4> Event Length (header + data).
Next Event position.
.
For encrypted binlog events, only the event length is in plaintext, and everything else is encrypted.
To decrypt the binlog event:
Store the event length in memory;
Move the timestamp into the event length position;
Decrypt the whole payload except the first four bytes;
Move the timestamp back to its original position;
Copy the original event length back to its position.
Regardless of the cipher used to encrypt the binlogs, the encrypted data are the same size as the original unencrypted event. For events that are encrypted in CBC mode, and whose length is not a multiple of the cipher block size, the final partial block is encrypted using a form of residual block termination:
Encrypt the current IV of the binlog file in ECB mode;
XOR the remaining bytes with the encrypted IV.
0x02
0x03
0x04
0x10
0x0d
0x0e
These are generated on the fly, never written:
0x04
0xa3
0x0001
LOG_EVENT_BINLOG_IN_USE_F This flag only makes sense for Format_description_log_event. It is set when the event is written, and reset when a binlog file is closed (yes, it's the only case when MySQL modifies already written part of binlog). Thus it is a reliable indicator that binlog was closed correctly.
0x0002
LOG_EVENT_FORCED_ROTATE_F (unused).
0x0004
LOG_EVENT_THREAD_SPECIFIC_F If the query depends on the thread (for example: TEMPORARY TABLE).
0x0008
LOG_EVENT_SUPPRESS_USE_F Suppress the generation of 'USE' statements before the actual statement. This flag should be set for any events that does not need the current database set to function correctly. Most notable cases are 'CREATE DATABASE' and 'DROP DATABASE'.
0x0010
LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F (unused).
0x0020
LOG_EVENT_ARTIFICIAL_F Artificial events are created arbitrarily and not written to binary log.These events should not update the master log position when slave SQL thread executes them.
This is the first event in the binlog file at pos 4:
a4 85 9e 59 [4] Timestamp => 59 9e 85 a4 => 1503561124 = 2017-08-24 09:52:04
0f [1] Event Type = 0x0f = FORMAT_DESCRIPTION_EVENT
8c 27 00 00 [4] Server_id => 00 00 27 8c = 10124
f5 00 00 00 [4] Event length => 00 00 00 f5 => 245
f9 00 00 00 [4] Next Event pos => 00 00 00 f9 => 249 (pos 4 + event size)
00 00 [2] Event flags = 0
This page is licensed: CC BY-SA / Gnu FDL
Details the initialization phase where a replica connects to the primary, authenticates, sends capabilities, and registers for updates.
The replica server, when properly configured with CHANGE MASTER TO ... , can start MariaDB replication with the START REPLICA statement.
After authentication, some COM_QUERY packets are exchanged before sending COM_REGISTER_SLAVE and COM_BINLOG_DUMP.
The following COM_QUERY packets come from MariaDB 10.X replicas using GTID:
SELECT UNIX_TIMESTAMP();
SHOW VARIABLES LIKE 'SERVER_ID';
SET @master_heartbeat_period= 30000001024;
SET @master_binlog_checksum
Then COM_REGISTER_SLAVE completes the registration.
The COM_BINLOG_DUMP marks the request of binlog events stream.
, and :
In the example, we see that these two COM_QUERY commands are sent just after COM_REGISTER_SLAVE and before COM_BINLOG_DUMP:
SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled';
SET @rpl_semi_sync_slave= 1.
The example shows output up to COM_BINLOG_DUMP request, No Semi-Sync:
The MariaDB master always sends this, after the COM_BINLOG_DUMP:
;
: Next Pos in the header is set to 0 if not requesting binlog file from the beginning, and the GTID is not in use; otherwise, Next Pos is related to next event after FDE.
with latest GTID information.
After those first events, the master sends events related to changes in database to the connected replica binlog. The replica is just waiting for new events from master.
We can see:
FAKE_ROTATE_EVENT packet: 30 00 00 01 ... d5 3f ea d7
FORMAT_DESCRIPTION_EVENT packet: fd 00 00 02 00 ... 17 0b 12 63
FDE size is fc 00 00 00 (252)
Next Pos in FDE is 00 01 00 00 = >256 = 4 + FDE size (252)
FAKE_GTID_LIST_EVENT packet : 3c 00 00 03 00 ... b6 33 8a 22
This page is licensed: CC BY-SA / Gnu FDL
These events record row-level changes (WRITE, UPDATE, DELETE) for replication, with versions supporting different column counts and compression.
a4 85 9e 59 0f 8c 27 00 00 f5 00 00 00 f9 00 00 ...Y..'.........
00 00 00 04 00 31 30 2e 31 2e 32 34 2d 4d 61 72 .....10.1.24-Mar
69 61 44 42 00 6c 6f 67 00 00 00 00 00 00 00 00 iaDB.log....
...
...0x0f
0x13
0x1b
0xa0
0xa1
0xa2
0xa3
0xa4
0xa5
0xa6
0xa7
0xa8
0xa9
0xaa
0xab
0x0040
LOG_EVENT_RELAY_LOG_F Events with this flag set are created by slave IO thread and written to relay log.
0x0080
LOG_EVENT_IGNORABLE_F For an event, 'e', carrying a type code, that a slave,'s', does not recognize, 's' will check 'e' for LOG_EVENT_IGNORABLE_F, and if the flag is set, then 'e'is ignored. Otherwise, 's' acknowledges that it has found an unknown event in the relay log.
0x0100
LOG_EVENT_NO_FILTER_F (no description yet).
0x0200
LOG_EVENT_MTS_ISOLATE_F (no description yet).
0x8000
LOG_EVENT_SKIP_REPLICATION_F Flag set by application creating the event (with @@skip_replication);the replica skips replication of such events, if --replicate-events-marked-for-skip is not set to REPLICATE. This is a MariaDB flag; we allocate it from the end of the available values to reduce risk of conflict with new MySQL flags.
@@global.binlog_checksumSELECT @master_binlog_checksum;
SET @mariadb_slave_capability=4;
SELECT @@GLOBAL.gtid_domain_id GTID registration: domain ID;
SET @slave_connect_state='0-10201-9868' GTID registration: the requested GTID;
SET @slave_gtid_strict_mode=0 GTID registration: strict_mode;
SET @slave_gtid_ignore_duplicates=0 GTID registration: ignore_duplicates.
BINLOG_CHECKPOINT EVENT packet: 2c 00 00 04 ... 16 1f fe 3fGTID_LIST_EVENT packet: 2c 00 00 05 ... 4a 01 94 22
GTID_EVENT packet: 2b 00 00 06 ... 22 87 c0 61
QUERY_EVENT packet: 4c 00 00 07 ... 6e c8 89 60
T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
1a 00 00 00 15 75 27 00 00 08 53 42 73 6c 61 76 .....u'...SBslav
65 31 00 00 c9 5a 00 00 00 00 00 00 00 00 e1...Z........
T 127.0.0.1:23240 -> 127.0.0.1:42158 [AP]
07 00 00 01 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
33 00 00 00 03 53 48 4f 57 20 56 41 52 49 41 42 3....SHOW VARIAB
4c 45 53 20 4c 49 4b 45 20 27 72 70 6c 5f 73 65 LES LIKE 'rpl_se
6d 69 5f 73 79 6e 63 5f 6d 61 73 74 65 72 5f 65 mi_sync_master_e
6e 61 62 6c 65 64 27 nabled'
T 127.0.0.1:23240 -> 127.0.0.1:42158 [AP]
01 00 00 01 02 64 00 00 02 03 64 65 66 12 69 6e .....d....def.in
66 6f 72 6d 61 74 69 6f 6e 5f 73 63 68 65 6d 61 formation_schema
11 53 45 53 53 49 4f 4e 5f 56 41 52 49 41 42 4c .SESSION_VARIABL
45 53 11 53 45 53 53 49 4f 4e 5f 56 41 52 49 41 ES.SESSION_VARIA
42 4c 45 53 0d 56 61 72 69 61 62 6c 65 5f 6e 61 BLES.Variable_na
6d 65 0d 56 41 52 49 41 42 4c 45 5f 4e 41 4d 45 me.VARIABLE_NAME
0c 08 00 40 00 00 00 fd 01 00 00 00 00 5d 00 00 ...@.........]..
03 03 64 65 66 12 69 6e 66 6f 72 6d 61 74 69 6f ..def.informatio
6e 5f 73 63 68 65 6d 61 11 53 45 53 53 49 4f 4e n_schema.SESSION
5f 56 41 52 49 41 42 4c 45 53 11 53 45 53 53 49 _VARIABLES.SESSI
4f 4e 5f 56 41 52 49 41 42 4c 45 53 05 56 61 6c ON_VARIABLES.Val
75 65 0e 56 41 52 49 41 42 4c 45 5f 56 41 4c 55 ue.VARIABLE_VALU
45 0c 08 00 00 08 00 00 fd 01 00 00 00 00 05 00 E...............
00 04 fe 00 00 22 00 20 00 00 05 1c 72 70 6c 5f .....". ....rpl_
73 65 6d 69 5f 73 79 6e 63 5f 6d 61 73 74 65 72 semi_sync_master
5f 65 6e 61 62 6c 65 64 02 4f 4e 05 00 00 06 fe _enabled.ON.....
00 00 22 00 ..".
T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
1c 00 00 00 03 53 45 54 20 40 72 70 6c 5f 73 65 .....SET @rpl_se
6d 69 5f 73 79 6e 63 5f 73 6c 61 76 65 3d 20 31 mi_sync_slave= 1
T 127.0.0.1:23240 -> 127.0.0.1:42158 [AP]
07 00 00 01 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
1b 00 00 00 12 34 06 00 00 02 00 75 27 00 00 6d .....4.....u'..m
79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 33 34 ysql-bin.000034T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
5d 00 00 00 0a 35 2e 35 2e 35 2d 31 30 2e 32 2e ]....5.5.5-10.2.
31 30 2d 4d 61 72 69 61 44 42 2d 6c 6f 67 00 22 10-MariaDB-log."
00 00 00 7d 2e 6a 4f 2c 2c 36 6a 00 fe f7 08 02 ...}.jO,,6j.....
00 bf 81 15 00 00 00 00 00 00 07 00 00 00 38 74 ..............8t
60 64 54 59 44 28 38 24 48 7c 00 6d 79 73 71 6c `dTYD(8$H|.mysql
5f 6e 61 74 69 76 65 5f 70 61 73 73 77 6f 72 64 _native_password
00 .
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
a9 00 00 01 05 a2 38 80 70 03 00 40 08 00 00 00 ......8.p..@....
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 6d 73 61 6e 64 62 6f 78 00 14 52 42 ....msandbox..RB
0b e8 ae 56 ec ff ef 1f 1f 14 51 1d 4a 47 f4 32 ...V......Q.JG.2
56 74 6d 79 73 71 6c 5f 6e 61 74 69 76 65 5f 70 Vtmysql_native_p
61 73 73 77 6f 72 64 00 54 03 5f 6f 73 05 4c 69 assword.T._os.Li
6e 75 78 0c 5f 63 6c 69 65 6e 74 5f 6e 61 6d 65 nux._client_name
08 6c 69 62 6d 79 73 71 6c 04 5f 70 69 64 05 33 .libmysql._pid.3
30 30 31 33 0f 5f 63 6c 69 65 6e 74 5f 76 65 72 0013._client_ver
73 69 6f 6e 07 31 30 2e 32 2e 31 30 09 5f 70 6c sion.10.2.10._pl
61 74 66 6f 72 6d 06 78 38 36 5f 36 34 atform.x86_64
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
07 00 00 02 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
18 00 00 00 03 53 45 4c 45 43 54 20 55 4e 49 58 .....SELECT UNIX
5f 54 49 4d 45 53 54 41 4d 50 28 29 _TIMESTAMP()
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
01 00 00 01 01 26 00 00 02 03 64 65 66 00 00 00 .....&....def...
10 55 4e 49 58 5f 54 49 4d 45 53 54 41 4d 50 28 .UNIX_TIMESTAMP(
29 00 0c 3f 00 11 00 00 00 08 80 00 00 00 00 05 )..?............
00 00 03 fe 00 00 02 00 0b 00 00 04 0a 31 35 31 .............151
33 36 38 34 33 38 36 05 00 00 05 fe 00 00 02 00 3684386.........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
20 00 00 00 03 53 48 4f 57 20 56 41 52 49 41 42 ....SHOW VARIAB
4c 45 53 20 4c 49 4b 45 20 27 53 45 52 56 45 52 LES LIKE 'SERVER
5f 49 44 27 _ID'
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
01 00 00 01 02 64 00 00 02 03 64 65 66 12 69 6e .....d....def.in
66 6f 72 6d 61 74 69 6f 6e 5f 73 63 68 65 6d 61 formation_schema
11 53 45 53 53 49 4f 4e 5f 56 41 52 49 41 42 4c .SESSION_VARIABL
45 53 11 53 45 53 53 49 4f 4e 5f 56 41 52 49 41 ES.SESSION_VARIA
42 4c 45 53 0d 56 61 72 69 61 62 6c 65 5f 6e 61 BLES.Variable_na
6d 65 0d 56 41 52 49 41 42 4c 45 5f 4e 41 4d 45 me.VARIABLE_NAME
0c 08 00 40 00 00 00 fd 01 00 00 00 00 5d 00 00 ...@.........]..
03 03 64 65 66 12 69 6e 66 6f 72 6d 61 74 69 6f ..def.informatio
6e 5f 73 63 68 65 6d 61 11 53 45 53 53 49 4f 4e n_schema.SESSION
5f 56 41 52 49 41 42 4c 45 53 11 53 45 53 53 49 _VARIABLES.SESSI
4f 4e 5f 56 41 52 49 41 42 4c 45 53 05 56 61 6c ON_VARIABLES.Val
75 65 0e 56 41 52 49 41 42 4c 45 5f 56 41 4c 55 ue.VARIABLE_VALU
45 0c 08 00 00 08 00 00 fd 01 00 00 00 00 05 00 E...............
00 04 fe 00 00 22 00 10 00 00 05 09 73 65 72 76 ....."......serv
65 72 5f 69 64 05 31 30 32 30 31 05 00 00 06 fe er_id.10201.....
00 00 22 00 ..".
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
2a 00 00 00 03 53 45 54 20 40 6d 61 73 74 65 72 *....SET @master
5f 68 65 61 72 74 62 65 61 74 5f 70 65 72 69 6f _heartbeat_perio
64 3d 20 33 30 30 30 30 30 30 31 30 32 34 d= 30000001024
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
07 00 00 01 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
36 00 00 00 03 53 45 54 20 40 6d 61 73 74 65 72 6....SET @master
5f 62 69 6e 6c 6f 67 5f 63 68 65 63 6b 73 75 6d _binlog_checksum
3d 20 40 40 67 6c 6f 62 61 6c 2e 62 69 6e 6c 6f = @@global.binlo
67 5f 63 68 65 63 6b 73 75 6d g_checksum
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
07 00 00 01 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
1f 00 00 00 03 53 45 4c 45 43 54 20 40 6d 61 73 .....SELECT @mas
74 65 72 5f 62 69 6e 6c 6f 67 5f 63 68 65 63 6b ter_binlog_check
73 75 6d sum
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
01 00 00 01 01 2d 00 00 02 03 64 65 66 00 00 00 .....-....def...
17 40 6d 61 73 74 65 72 5f 62 69 6e 6c 6f 67 5f .@master_binlog_
63 68 65 63 6b 73 75 6d 00 0c 08 00 ff ff ff 00 checksum........
fa 00 00 27 00 00 05 00 00 03 fe 00 00 02 00 06 ...'............
00 00 04 05 43 52 43 33 32 05 00 00 05 fe 00 00 ....CRC32.......
02 00 ..
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
20 00 00 00 03 53 45 54 20 40 6d 61 72 69 61 64 ....SET @mariad
62 5f 73 6c 61 76 65 5f 63 61 70 61 62 69 6c 69 b_slave_capabili
74 79 3d 34 ty=4
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
07 00 00 01 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
1f 00 00 00 03 53 45 4c 45 43 54 20 40 40 47 4c .....SELECT @@GL
4f 42 41 4c 2e 67 74 69 64 5f 64 6f 6d 61 69 6e OBAL.gtid_domain
5f 69 64 _id
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
01 00 00 01 01 2d 00 00 02 03 64 65 66 00 00 00 .....-....def...
17 40 40 47 4c 4f 42 41 4c 2e 67 74 69 64 5f 64 .@@GLOBAL.gtid_d
6f 6d 61 69 6e 5f 69 64 00 0c 3f 00 15 00 00 00 omain_id..?.....
08 a0 00 00 00 00 05 00 00 03 fe 00 00 02 00 02 ................
00 00 04 01 30 05 00 00 05 fe 00 00 02 00 ....0.........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
28 00 00 00 03 53 45 54 20 40 73 6c 61 76 65 5f (....SET @slave_
63 6f 6e 6e 65 63 74 5f 73 74 61 74 65 3d 27 30 connect_state='0
2d 31 30 32 30 31 2d 39 38 36 38 27 -10201-9868'
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
07 00 00 01 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
1e 00 00 00 03 53 45 54 20 40 73 6c 61 76 65 5f .....SET @slave_
67 74 69 64 5f 73 74 72 69 63 74 5f 6d 6f 64 65 gtid_strict_mode
3d 30 =0
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
07 00 00 01 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
24 00 00 00 03 53 45 54 20 40 73 6c 61 76 65 5f $....SET @slave_
67 74 69 64 5f 69 67 6e 6f 72 65 5f 64 75 70 6c gtid_ignore_dupl
69 63 61 74 65 73 3d 30 icates=0
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
07 00 00 01 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
1b 00 00 00 15 75 27 00 00 09 73 6c 61 76 65 5f .....u'...slave_
6e 5f 31 00 00 c9 5a 00 00 00 00 00 00 00 00 n_1...Z........
T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
07 00 00 01 00 00 00 02 00 00 00 ...........
T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
1b 00 00 00 12 34 06 00 00 02 00 75 27 00 00 6d .....4.....u'..m
79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 33 34 ysql-bin.000034T 127.0.0.1:23240 -> 127.0.0.1:42219 [AP]
30 00 00 01 00 00 00 00 00 04 d9 27 00 00 2f 00 0..........'../.
00 00 00 00 00 00 20 00 04 00 00 00 00 00 00 00 ...... .........
6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 33 34 mysql-bin.000034
d5 3f ea d7 fd 00 00 02 00 fb cc 37 5a 0f d9 27 .?.........7Z..'
00 00 fc 00 00 00 00 01 00 00 00 00 04 00 31 30 ..............10
2e 32 2e 31 30 2d 4d 61 72 69 61 44 42 2d 6c 6f .2.10-MariaDB-lo
67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 g...............
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 13 38 0d 00 08 00 12 00 04 04 04 04 .....8..........
12 00 00 e4 00 04 1a 08 00 00 00 08 08 08 02 00 ................
00 00 0a 0a 0a 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 00 04 13 04 00 0d 08 08 08 0a 0a 0a ................
01 17 0b 12 63 3c 00 00 03 00 fb cc 37 5a a3 d9 ....c<......7Z..
27 00 00 3b 00 00 00 3b 01 00 00 00 00 02 00 00 '..;...;........
00 00 00 00 00 01 00 00 00 1e 00 00 00 00 00 00 ................
00 00 00 00 00 d9 27 00 00 86 26 00 00 00 00 00 ......'...&.....
00 b6 33 8a 22 2c 00 00 04 00 fb cc 37 5a a1 d9 ..3.",......7Z..
27 00 00 2b 00 00 00 66 01 00 00 00 00 10 00 00 '..+...f........
00 6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 33 .mysql-bin.00003
34 16 1f fe 3f 2c 00 00 05 00 00 00 00 00 a3 d9 4...?,..........
27 00 00 2b 00 00 00 34 06 00 00 20 00 01 00 00 '..+...4... ....
00 00 00 00 00 d9 27 00 00 8c 26 00 00 00 00 00 ......'...&.....
00 4a 01 94 22 2b 00 00 06 00 94 fd 38 5a a2 d9 .J.."+......8Z..
27 00 00 2a 00 00 00 5e 06 00 00 08 00 8d 26 00 '..*...^......&.
00 00 00 00 00 00 00 00 00 29 00 00 00 00 00 00 .........)......
22 87 c0 61 4c 00 00 07 00 94 fd 38 5a 02 d9 27 "..aL......8Z..'
00 00 4b 00 00 00 a9 06 00 00 00 00 21 00 00 00 ..K.........!...
00 00 00 00 00 00 00 1a 00 00 00 00 00 00 01 00 ................
00 20 54 00 00 00 00 06 03 73 74 64 04 08 00 08 . T......std....
00 08 00 00 66 6c 75 73 68 20 74 61 62 6c 65 73 ....flush tables
6e c8 89 60 n..`WRITE_ROWS_EVENT_V1
Insert new row.
UPDATE_ROWS_EVENT_V1
Update existing row.
DELETE_ROWS_EVENT_V1
Delete existing row.
WRITE_ROWS_COMPRESSED_EVENT_V1
Insert new row.
UPDATE_ROWS_COMPRESSED_EVENT_V1
Update existing row.
DELETE_ROWS_COMPRESSED_EVENT_V1
Delete existing row.
WRITE_ROWS_EVENT
Insert new row (version 2, MySQL only).
UPDATE_ROWS_EVENT
Update existing row (version 2, MySQL only).
WRITE_ROWS_EVENT_V1: Event Type is 23 (0x17).
UPDATE_ROWS_EVENT_V1: Event Type is 24 (0x18).
DELETE_ROWS_EVENT_V1: Event Type is 25 (0x19).
WRITE_ROWS_EVENT: Event Type is 30 (0xFD).
UPDATE_ROWS_EVENT: Event Type is 31 (0xFE).
DELETE_ROWS_EVENT: Event Type is 32 (0x20).
WRITE_ROWS_COMPRESSED_EVENT_V1: Event Type is 166 (0xA6).
UPDATE_ROWS_COMPRESSED_EVENT_V1: Event Type is 167 (0xA7).
DELETE_ROWS_COMPRESSED_EVENT_V1: Event Type is 168 (0xA8).
uint<6> The table id.
uint<2> Flags.
If rows_event is version 2:
uint<2> Extra data length.
Extra data.
Number of columns.
Columns used. n = (number_of_columns + 7)/8.
If (event_type == UPDATE_ROWS_EVENT_v1):
Columns used (Update). n = (number_of_columns + 7)/8.
If *_COMPRESSED_EVENT_V1 :
byte<1> header.
algorithm: (header & 0x07) >> 4 (always 0=zlib).
header_size: header & 0x07.
Null Bitmap (n = (number_of_columns + 7)/8).
Column data. The length needs to be calculated by checking the column types from referring TABLE_MAP_EVENT.
If (event_type == UPDATE_ROWS_EVENT_v1 ):
Null Bitmap_Update. n = (number_of_columns + 7)/8.
Update Column data. The length needs to be calculated by checking the used colums bitmap and column types from referring TABLE_MAP_EVENT.
Table id refers to a table defined by TABLE_MAP_EVENT. The special value 0xFFFFFF should have "end of statement flag" (0x0001) set and indicates that table maps can be freed.
0x0001
End of statement.
0x0002
No foreign key checks.
0x0004
No unique key checks.
0x0008
Indicates that rows in this event are complete.
0x0010
No check constraints.
The length of extra data.
Extra data, length is extra data length -2.
The row data is stored in a packed format where each field is encoded in a particular format. The encoding is almost identical to the binary protocol but there are a few differences.
The field metadata is stored in the metadata block of the TABLE_MAP_EVENT. The metadata is required to decode the events. The following list shows number of bytes a field uses from the metadata block.
2 bytes
MYSQL_TYPE_BIT
MYSQL_TYPE_ENUM
MYSQL_TYPE_SET
MYSQL_TYPE_NEWDECIMAL
MYSQL_TYPE_DECIMAL
MYSQL_TYPE_VARCHAR
MYSQL_TYPE_VAR_STRING
MYSQL_TYPE_STRING
1 byte
MYSQL_TYPE_TINY_BLOB
MYSQL_TYPE_MEDIUM_BLOB
MYSQL_TYPE_LONG_BLOB
The types that aren't listed here do not store data in the metadata block.
MYSQL_TYPE_NULL
Bit set in null bitmap, no value in row data.
MYSQL_TYPE_TINY
1 byte integer.
MYSQL_TYPE_YEAR
1 byte integer (year = value + 1900).
MYSQL_TYPE_SHORT
2 byte integer.
MYSQL_TYPE_INT24
3 byte integer.
MYSQL_TYPE_LONG
4 byte integer.
Stored as a length-encoded string where the string is preceded by a variable-sized integer that stores the length of the blob. The size of the preceding integer in bytes is stored as a one byte integer in the table metadata that is a part of the table map event.
For example, if the value 4 is stored in the table metadata, the length is stored as a 4 byte integer (for instance, uint32_t) followed by the data.
The exact column_type can be determined by the metadata length:
1
MYSQL_TYPE_TINY_BLOB
2
MYSQL_TYPE_BLOB
3
MYSQL_TYPE_MEDIUM_BLOB
4
MYSQL_TYPE_LONG_BLOB
Stored as a fixed-length string with the length of the string stored in the second byte of the table metadata. All three of these types are stored as MYSQL_TYPE_STRING in the binlog and the real type of the field is stored in the first byte of the metadata.
Stored as a length-encoded string where the string is preceded by a variable-sized integer that stores the length of the string. The field length is stored as a two byte integer in the table metadata.
If the field length is larger than 255, the string length is stored as a two byte integer. If the value is equal to or less than 255, the string length is stored as a one byte integer.
This field type is only used in MariaDB if the global variable mysql56_temporal_format is set to OFF.
Stored as an 8 byte value with the values stored as multiples of 100. This means that the stored value is in the format YYYYMMDDHHMMSS , and can be extracted by repeatedly calculating the remainder of dividing the value by 100 and dividing the value by 100. The following pseudo-code demonstrates extracting the value.
This field type is only used in MariaDB if the global variable mysql56_temporal_format is set to OFF.
Stored as a 3 byte value with the values stored as multiples of 100. This means that the stored value is in the format HHMMSS and can be extracted in the same way a MYSQL_TYPE_DATETIME is extracted. The following pseudo-code demonstrates extracting the value.
Stored as 4-byte value The number of decimals for the fractional part is stored in the table metadata as a one byte value. The number of bytes that follow the 5 byte datetime value can be calculated with the following formula: (decimals + 1) / 2 .
Stored as 3-byte value. The number of decimals for the fractional part is stored in the table metadata as a one byte value. The number of bytes that follow the 3 byte time value can be calculated with the following formula: (decimals + 1) / 2 .
Stored as a 4 byte UNIX timestamp (number of seconds since 00:00, Jan 1 1970 UTC) followed by the fractional second parts. The number of decimals for the fractional part is stored in the table metadata as a one byte value. The number of bytes that follow the 4 byte timestamp can be calculated with the following formula: (decimals + 1) / 2
Microseconds for MYSQL_TYPE_DATETIME2, MYSQL_TYPE_TIME2 , and MYSQL_TYPE_TIMESTAMP .
Stored as a 3 byte value where bits 1 to 5 store the day, bits 6 to 9 store the month and the remaining bits store the year.
Stored as a 4 byte UNIX timestamp (number of seconds since 00:00, Jan 1 1970 UTC).
This page is licensed: CC BY-SA / Gnu FDL
The QUERY_EVENT records text-based SQL statements for statement-based replication, capturing the query string and execution context like the default database.
This event is written into the binary log file for:
Statement-based replication (updating statements).
DDLs.
COMMIT related to nontransactional engines (like or ).
value = read_8_byte_value(row_data)
date_val= value / 1000000
time_val= value % 1000000
year = (date_val / 100) / 100
month = (date_val / 100) % 100
day = date_val % 100
hour= (time_val / 100) / 100
minute = (time_val / 100) % 100
second = time_val % 100time_val = read_3_byte_value(row_data); /* myisam pack format */
hour= (time_val / 100) / 100;
minute = (time_val / 100) % 100;
second = time_val % 100;val = read_5_byte_value(row_data) - 0x8000000000
d_val= val >> 17;
t_val = val % (1 << 17);
day= d_val % (1 << 5);
month= (d_val >> 5) % 13;
year= (d_val >> 5) / 13;
second= t_val % (1 << 6);
minute= (t_val >> 6) % (1 << 6);
hour= (t_val)(time_part >> 12);t_val = read_3_byte_value(row_data) - 0x800000
if (t_val < 0)
{
signed= 1;
t_val= - tval;
}
hour= (t_val >> 12) % (1 << 10);
minute= (t_val >> 6) % (1 << 6);
second= t_val % (1 << 6);len = (decimals + 1) / 2
val= read_len_byte_value(row_data)
llen= 0: microseconds= 0
len = 1 or 2: microseconds = val * 10000
len = 3 or 4: microseconds = 2-bytes val in myisam pack format
len = 5 or 6: microseconds = 3-bytes val in myisam pack format# at 1680
#180611 9:50:51 server id 1 end_log_pos 1754 CRC32 0x5415a8fb Write_rows: table id 23 flags: STMT_END_F
BINLOG '
2ykeWxMBAAAAPgAAAJAGAAAAABcAAAAAAAEABHRlc3QACWJ1bGtfbnVsbAAFDwMFE/YGFAAIAAMB
H1bULg8=
2ykeWxcBAAAASgAAANoGAAAAABcAAAAAAAEABf/gATMDAAAAAAAAAAAACECAAACDAP/gATMDAAAA
AAAAAAAACECAAACDAPuoFVQ=
'/*!*/;db 29 1e 5b 17 01 00 00 00 4a 00 .).[.....J.
00 00 da 06 00 00 00 00 17 00 00 00 00 00 01 00 ................
05 ff e0 01 33 03 00 00 00 00 00 00 00 00 00 08 ....3...........
40 80 00 00 83 00 ff e0 01 33 03 00 00 00 00 00 @........3......
00 00 00 00 08 40 80 00 00 83 00 fb a8 15 54 .....@........T ....byte<header_size>uncompressed length, stored in MyISAM format.
MYSQL_TYPE_TIMESTAMP2
MYSQL_TYPE_DATETIME2
MYSQL_TYPE_TIME2
MYSQL_TYPE_FLOAT
MYSQL_TYPE_DOUBLE
DELETE_ROWS_EVENT
Delete existing row (version 2, MySQL only).
MYSQL_TYPE_LONGLONG
8 byte integer.
MYSQL_TYPE_FLOAT
4 byte floating-point value (stored as a C float type).
MYSQL_TYPE_DOUBLE
8 byte floating-point value (stored as a C double type).
Event Type = 0x02.
Fixed data part:
uint<4> The ID of the thread that issued this statement on the master.
uint<4> The time in seconds that the statement took to execute.
uint<1> The length of the name of the database which was the default database when the statement was executed. This name appears later, in the variable data part. It is necessary for statements such as INSERT INTO t VALUES(1) that don't specify the database and rely on the default database previously selected by USE.
The error code resulting from execution of the statement on the master.
The length of the status variable block.
Variable data part:
byte Zero or more status variables. Each status variable consists of one byte code identifying the variable stored, followed by the value of the variable. The format of the value is variable-specific. The number of bytes 'n' is the length of the status variable block (read in fixed data part).
string The default database name (null-terminated).
string The SQL statement. By subtraction the size of the statement can be known.
uint<4> bitmask.
0x00004000
OPTION_AUTO_IS_NULL
0x00080000
OPTION_NOT_AUTOCOMMIT
0x04000000
OPTION_NO_FOREIGN_KEY_CHECKS
0x08000000
OPTION_RELAXED_UNIQUE_CHECKS
uint<8> 8-byte bitmask.
0x00000001
MODE_REAL_AS_FLOAT
0x00000002
MODE_PIPES_AS_CONCAT
0x00000004
MODE_ANSI_QUOTES
0x00000008
MODE_IGNORE_SPACE
0x00000010
MODE_NOT_USED
0x00000020
MODE_ONLY_FULL_GROUP_BY
uint<1> length
string<length +1> catalog name + '\0'
uint<2> code The mapping between code and names are defined in sql_locale.cc.
uint<2> database collation
uint<8> table bittmask Every bit of this variable represents a table, and is set to 1 if the corresponding table is to be updated by this statement.
Not in use any more
uint<4> original event length
MySQL only
uint<3> microsecond part
MariaDB only
uint<3> microsecond part
MariaDB only
uint<8> xid
Event Time[4] = 71 17 28 5a ===> 1512576881.
Event Type[1] = 2.
Server_id[4] = 8c 27 00 00 ===> 10124.
Event Size = 55 00 00 00 ===> 85.
Next Pos = 01 09 00 00 ===> 2305.
Flags = 00 00 => 0.
Thread ID[4] = 66 01 00 00 ===> 358.
Execution Time[4] = 00 00 00 00 => 0 seconds.
Statement default database name len[1] = 00 => 0 (no default database).
Error code[2] = 00 00 => 0 (no errors).
Status variable block len[2] = 1a 00 => 26.
Status variables[n] = 00 ... 08 00.
The default database[string] = 00 = 0 (no default database).
The SQL statement[string] = TRUNCATE TABLE test.t4.
4a 69 9e ed.
Thread ID[4] = 66 01 00 00 ===> 358.
Execution Time[4] = 10 00 00 00 => 1 second.
Statement default database name len[1] = 04 => 4 (default database is "test").
Error code[2] = 00 00 => 0 (no errors).
Status variable block len[2] = 1a 00 => 26.
Status variables[n] = 00 ... 08 00.
The default database[string] = 74 65 73 74 00 ⇒ test.
The SQL statement[string] = TRUNCATE TABLE test.t4.
This page is licensed: CC BY-SA / Gnu FDL
71 17 28 5a 02 8c 27 00 00 55 00 00 00 01 09 00 q.(Z..'..U......
00 00 00 66 01 00 00 00 00 00 00 00 00 00 1a 00 ...f............
00 00 00 00 00 01 00 00 00 50 00 00 00 00 06 03 .........P......
73 74 64 04 08 00 08 00 08 00 00 54 52 55 4e 43 std........TRUNC
41 54 45 20 54 41 42 4c 45 20 74 65 73 74 2e 74 ATE TABLE test.t
34 4a 69 9e ed 4Ji..MariaDB []> use test;
Database changed
MariaDB [test]> TRUNCATE TABLE t4;
...
ce 22 28 5a 02 8c 27 00 00 54 00 00 00 87 0c 00 ."(Z..'..T......
00 00 00 66 01 00 00 01 00 00 00 04 00 00 1a 00 ...f............
00 00 00 00 00 01 00 00 00 50 00 00 00 00 06 03 .........P......
73 74 64 04 08 00 08 00 08 00 74 65 73 74 00 54 std.......test.T
52 55 4e 43 41 54 45 20 54 41 42 4c 45 20 74 34 RUNCATE TABLE t4
08 f1 09 16 ....0x00000040
MODE_NO_UNSIGNED_SUBTRACTION
0x00000080
MODE_NO_DIR_IN_CREATE
0x00000100
MODE_POSTGRESQL
0x00000200
MODE_ORACLE
0x00000400
MODE_MSSQL
0x00000800
MODE_DB2
0x00001000
MODE_MAXDB
0x00002000
MODE_NO_KEY_OPTIONS
0x00004000
MODE_NO_TABLE_OPTIONS
0x00008000
MODE_NO_FIELD_OPTIONS
0x00010000
MODE_MYSQL323
0x00020000
MODE_MYSQL40
0x00040000
MODE_ANSI
0x00080000
MODE_NO_AUTO_VALUE_ON_ZERO
0x00100000
MODE_NO_BACKSLASH_ESCAPES
0x00200000
MODE_STRICT_TRANS_TABLES
0x00400000
MODE_STRICT_ALL_TABLES
0x00800000
MODE_NO_ZERO_IN_DATE
0x01000000
MODE_NO_ZERO_DATE
0x02000000
MODE_INVALID_DATES
0x04000000
MODE_ERROR_FOR_DIVISION_BY_ZERO
0x08000000
MODE_TRADITIONAL
0x10000000
MODE_NO_AUTO_CREATE_USER
0x20000000
MODE_HIGH_NOT_PRECEDENCE
0x40000000
MODE_NO_ENGINE_SUBSTITUTION
0x80000000
MODE_PAD_CHAR_TO_FULL_LENGTH