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...
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...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Understand the MariaDB client/server protocol. This section details how clients communicate with the server, including connection, authentication, query execution, and result set handling.
Learn about the connection phase in the client/server protocol. This section details how clients establish initial communication with the server, including handshaking and authentication processes.
Understand the text protocol in the Server's client/server communication. This section details how SQL commands and results are exchanged as plain text, including command types and packet structures.
This section details the structure of response packets sent by the server when using the binary protocol, particularly for result sets.
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.
This plugin implements the caching_sha2_password authentication method, using an in-memory cache for fast authentication or RSA encryption for full verification.
Caching SHA256 first sends an SHA256-encrypted password. MySQL server has an in-memory cache of SHA256 key for successful authentication. When a cache hit occurs, the connection is validated, if not, using some more steps to a process similar to .
Caching SHA256 authentication possible exchanges:
This command drops an existing database from the server, functionally equivalent to the SQL statement DROP DATABASE.
This page is licensed: CC BY-SA / Gnu FDL
This command fetches rows from an existing result set of a prepared statement that was executed with a cursor.
The EOF_Packet marks the end of a result set or a sequence of packets, containing warning counts and server status flags.
The LOCAL_INFILE_Packet is sent by the server to request a file from the client during the execution of a LOAD DATA LOCAL INFILE statement.
Understand server response packets in MariaDB's client/server protocol. This section details the various types of packets sent by the server, including OK, Error, and Result Set packets.
Client sends an SHA-2 encrypted password.
Server result is either OK_Packet , ERR_Packet or "fast" authentication result.
If fast authentication result:
If connection uses SSL (SSLRequest Packet sent):
Client sends a clear password answer.
Else:
If client doesn't know server RSA public key:
Client sends a .
Server sends a .
Client sends an .
Ends with server sending either , .
Encryption is XOR(SHA256(password), SHA256(seed, SHA256(SHA256(password)))).
byte<32> encrypted password.
Result of fast authentication.
byte authentication result.
0x03 value means success authentication.
0x04 value means continue.
string password without encryption.
Value send is not 0x01 like sha256_password use, but 0x02.
byte<1> fixed 0x02 value.
byte<256> RSA encrypted password.
RSA encrypted value of XOR(password, seed) using server public key (RSA_PKCS1_OAEP_PADDING).
This page is licensed: CC BY-SA / Gnu FDL
End-of-file length bytes
Integer type:
Fixed-length integers
Length-encoded integers
String type:
Fixed-length strings
Null-terminated strings
Length-encoded strings
End-of-file length strings
The notation is "byte".
A fixed-length byte stores the value in a series of n bytes.
The notation is "byte".
Length encoded bytes are prefixed by a length-encoded integer which describes the length of the byte value, followed by the bytes value.
The notation is "byte".
Bytes whose length will be calculated by the packet remaining length.
Notation is "int".
A fixed-length integer stores the value in a series of n bytes. The least significant byte is always the first byte (little-endian format).
An int<4> with value 2 is stored as02 00 00 00
The notation is "int".
An integer which depending on its value is represented by n bytes.
The first byte represents the size of the integer:
If the value of first byte is
< 0xFB - Integer value is this 1 byte integer
0xFB - NULL value
0xFC - Integer value is encoded in the next 2 bytes (3 bytes total)
0xFD - Integer value is encoded in the next 3 bytes (4 bytes total)
0xFE - Integer value is encoded in the next 8 bytes (9 bytes total)
The notation is "string".
Fixed-length strings have a known hardcoded length.
The notation is "string".
Null-terminated strings have a variable size and are terminated by a 0x00 character
The notation is "string".
Length-encoded strings are prefixed by a length-encoded integer which describes the length of the string, followed by the string value.
An string of 512 "a" is encoded in 515 bytes :
fc 00 02 97 97 97 97 97 97 97 97 97 97 97 97 97
² .. a a a a a a a a a a a a a
...
The NULL value is encoded using null (0xfb) length.
An empty value is encoded with a 0 (0x00) length.
The notation is "string".
Strings whose length is calculated by the packet remaining length.
For an example, see the COM_STMT_PREPARE packet.
This page is licensed: CC BY-SA / Gnu FDL
Fixed-length bytes
Length-encoded bytes
This page is licensed: CC BY-SA / Gnu FDL
string schema name.
ERR_Packet or OK_Packet.
This page is licensed: CC BY-SA / Gnu FDL
06 00 00 00 02 74 65 73 74 63 .....testcThis page is licensed: CC BY-SA / Gnu FDL
int<4> process ID.
OK Packet or ERR Packet.
This page is licensed: CC BY-SA / Gnu FDL
Server terminates connection.
This page is licensed: CC BY-SA / Gnu FDL
int<1> 0x00: COM_SLEEP header.
This page is licensed: CC BY-SA / Gnu FDL
string human-readable string.
This page is licensed: CC BY-SA / Gnu FDL
int<4> statement id.
int<4> number of rows to fetch.
Returns one or more binary result set rows followed by an EOF packet.
This page is licensed: CC BY-SA / Gnu FDL
int<1> 0xfe : EOF header.
int<2> warning count.
This page is licensed: CC BY-SA / Gnu FDL
0xFB : LOCAL_INFILE header.string filename.
The client sends the file as the packet body. If the file is large, the contents are sent in multiple separate packets. After the file is sent, the client must send an empty packet to indicate that no more data will follow.
Once the client has finished sending the file, the server will respond with an OK_packet or an ERR_packet.
This page is licensed: CC BY-SA / Gnu FDL
Fixed data part:
uint<4> The ID of the file.
Variable data part:
byte Null terminated data block.
This page is licensed: CC BY-SA / Gnu FDL
Understand the standard packet structure in the MariaDB protocol, including the packet header, length, sequence number, and payload handling.
Client/server exchanges are done using the following format:
The standard MySQL/MariaDB packet has a 4-byte header and a packet body:
int<3> packet length;
sequence number;
packet body.
Packet length is the length of the packet body. Packet length size cannot be more than 3 bytes length value. The actual length of the packet is calculated as from the 3 bytes as length:
The maximum size of a packet (with all 3 bytes 0xff) is 16777215 , or 2^24-1 or 0xffffff, or 16MB-1 byte.
The sequence number indicates the exchange number when an exchange demands different exchanges. Whenever the client sends a query, the sequence number is set to 0 initially, and is incremented if clients need to split packets.
In more complex situations, when the client and server exchange several packets, for instance, authentication handshake, the rule of thumb for clients is to set sequence number = (last seq.number from received server packet + 1).
Example: Sending a packet COM_PING body has only one byte (0x10):
The server will then return an response with a sequence number of 1.
The packet length is 3 bytes, making a maximum size of (2^24 -1) bytes or 16Mbytes-1 byte. But the protocol allows sending and receiving larger data. For those cases, the client can send many packets for the same data, incrementing the sequence number for each packet.
The principle is to split data by chunks of 16M bytes. When the server receives a packet with 0xffffff length, it will continue to read the next packet. In case of a length of exactly 16M bytes, an empty packet must terminate the sequence.
Example: max_allowed_packet is set to a value > to 40M bytes. Sending a 40M bytes packet body: \
First packet:
Second packet:
Third packet:
The client must be aware of the variable value. The server has a buffer to store the body with a maximum size corresponding to this max_allowed_packet value. If the client sends more data than max_allowed_packet size, the socket will be closed.
For slow connections, the packet can be compressed. This is activated after the when the client indicates [[1-connecting-connecting#capabilities|COMPRESS] capability with the server having this functionality, too.
When activated, packets are composed of 7 bytes, a compress header and data. The compression algorithm used is ZLIB, widely available and supported by many languages and runtimes.
compressed packet length;
compression protocol sequence number;
uncompress body length;
compressed body;
Since compress body can contain many "standard packets", compress sequence number is incremented separately from sequence number. If the length of the uncompressed payload exceeds 0xffffff bytes, the uncompressed payload must be sent in several compressed protocol packets.
For small packets, using compression won't be efficient, so the client can choose to send uncompressed data. That is done by setting the compressed packet length to the real length and the uncompressed packet length to 0. (Data must then be uncompressed).
Example: Sending a packet COM_PING body when COMPRESS is enabled. This is 1 byte of data that has then no reason to be compressed, so:
The server returns an response with a compress sequence number of 1, and a sequence number of 1.
The server uncompresses data, and then must have the same packet as if there was no compression. If data size needs splitting, it's better to separate compress packet.
This page is licensed: CC BY-SA / Gnu FDL
This command retrieves a list of active threads and their current status, similar to the SHOW PROCESSLIST statement.
This page is licensed: CC BY-SA / Gnu FDL
This command deallocates a prepared statement on the server, freeing up associated resources.
This command resets the data of a prepared statement on the server, clearing any buffers or previous parameter values.
Resets a prepared statement on the client and server to state after preparing.
int<1> 0x1A COM_STMT_RESET header.
Statement ID.
or .
This page is licensed: CC BY-SA / Gnu FDL
The ERR_Packet is sent by the server to report an error, containing a numeric error code, SQL state marker, and a human-readable error message.
ERR_Packet indicates that an error occurred.
int<1> ERR_Packet header = 0xFF.
error code. see .
If (error code == 0xFFFF) /* progress reporting */:
stage.
max_stage.
progress.
Else:
If (next byte = '#'):
sql state marker '#'.
sql state.
Note that the ERR_Packet is supposed to send a server error to the client. In particular, all error codes in the range 2000 to 2999 and 5000 to 5999 (inclusive) are reserved for client errors and an ERR packet with such an error code will be considered malformed.
This page is licensed: CC BY-SA / Gnu FDL
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 --binlog-annotate-row-events (default on).
In the binary log, each ANNOTATE_ROWS event precedes the corresponding Table map event.
For additional information refer to the annotate_rows_log_event documentation.
Event Type is 160 (0xa0).
The SQL statement (not null-terminated).
This page is licensed: CC BY-SA / Gnu FDL
This command sends long data, such as BLOB or TEXT values, in chunks for a specific parameter of a prepared statement.
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.
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.
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 , 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).


string progress_info.
string human-readable error message.
Else:
string human-readable error message.
Client sends a clear password answer.
Else:
If client doesn't know server RSA public key:
Client sends a public key request.
Server sends a public key response.
Client sends an .
Ends with server sending either , .
string password without encryption.
byte<1> fixed 0x01 value.
byte<256> RSA encrypted password.
RSA encrypted value of XOR(password, seed) using server public key (RSA_PKCS1_OAEP_PADDING).
This page is licensed: CC BY-SA / Gnu FDL
int<2> option.
MYSQL_OPTION_MULTI_STATEMENTS_ON
0
MYSQL_OPTION_MULTI_STATEMENTS_OFF
1
EOF Packet on success or ERR packet.
This page is licensed: CC BY-SA / Gnu FDL
int<1> 0x18 COM_STMT_SEND_LONG_DATA header.
int<4> statement id.
int<2> parameter number.
byte data.
Server doesn't send response.
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
Event type is 161 (0xa1).
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.
Next Pos = 47 01 00 00 => 00 00 01 47 = 327.
Flags = 00 => 0.
Filename length = 10 00 00 00 = >00 00 00 10 => 16.
Filename = mysql-bin.000062.
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
0x050x00
Invalid value.
0x01
LAST_INSERT_ID.
0x02
Insert id (auto_increment).
This page is licensed: CC BY-SA / Gnu FDL
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
ROTATE_EVENTTimestamp set to 0.
Event Tye is ROTATE_EVENT.
Next Pos is set to 0.
Flags are set to LOG_ARTIFICIAL_F (0x20).
The content is the same as ROTATE_EVENT.
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
05 00 00 00 1A 04 00 00 00This command sends an SQL statement to the server for execution immediately, without the prepare/execute steps.
With the COM_QUERY command, the client sends the server an SQL statement to be executed immediately.
int<1> 0x03 : COM_QUERY header.
SQL statement.
The SQL statement should be properly escaped. The escape character is usually a backslash '' = 0x5c. However, if the status flag returned by the last had the NO_BACKSLASH_ESCAPES bit set, the escape character is a single quote (' = 0x60).
If the escape character is a backslash, the following characters are escaped:
Single quote (' = 0x60).
Backslash (\ = 0x5c).
Double quote (" = 0x22
If the escape character is a single quote, only the single quote (' = 0x60) can be escaped.
The server can answer with 4 different responses that can be differentiated by the first byte (packet header):
0xFF - if any error occurs.
0x00 - when query execution works without result set.
0xFB - if the query was LOCAL INFILE ....
This page is licensed: CC BY-SA / Gnu FDL
This command prepares an SQL statement on the server, returning a statement ID and metadata about parameters and columns.
Prepares a statement on the server.
0x16 COM_STMT_PREPARE header
SQL Statement
If anything goes wrong, the server will send an . If the command succeeds, different packets are received:
.
If number of parameters (count of '?' placeholders) > 0:
For each parameter:
0x00 COM_STMT_PREPARE_OK header.
statement ID.
number of columns in the returned result set (or 0 if statement does not return result set).
This page is licensed: CC BY-SA / Gnu FDL
This section provides an overview of the various events recorded in the binary log, which are the core units of replication data transmission.
Binary log events, or binlog events, are information about data modification made to a MariaDB server instance stored in the binary log files.
Each log file contains:
A 4-byte magic number, followed by a series of events describing data modifications:
The magic number bytes are 0xfe 0x62 0x69 0x6e = 0xfe 'b''i''n' (this is the BINLOG_MAGIC constant).
A 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 that specifies the next binary log filename or a 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
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).
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 4 bytes are:
packet length (the sent binlog event can be up to 2^24 - 1 - 1 data bytes).
packet sequence byte<1>(0 to 255).
Replication protocol status byte:
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
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 COM_BINLOG_DUMP.
The payload is:
uint<1> command (COM_REGISTER_SLAVE = 0x15).
uint<4> Replica server ID.
Replica hostname length.
Hostname.
Replica username length.
Username.
Replica password length.
Replica password.
Replica connection port.
Replication rank.
Master server ID.
Note:
Replica hostname, replica user, replica password, and replica port are usually not set.
Some replica 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).
This page is licensed: CC BY-SA / Gnu FDL
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 binary log 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 FLUSH LOGS statement occurs on the master server.
The ROTATE_EVENT is sent to the connected replica servers.
The Event Type is ROTATE_EVENT (0x4).
The position of the first event in the next log file. It always contains the number 4 (meaning the next event starts at position 4 in the next binary log).
The next binary log name. The filename is not null-terminated.
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
This packet type in the binary protocol transfers result set row data in a compact binary format, determined by the column types returned in metadata.
In contrast to the text protocol, the binary protocol transfers data according to the format of the field types returned in PACKET_METADATA.
Explains the handshake and acknowledgement process for semi-synchronous replication, ensuring data is committed on at least one replica.
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 marks the beginning of encrypted data in the binary log, defining the encryption scheme and key version for subsequent events.
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.
byte[0] + (byte[1]<<8) + (byte[2]<<16)01 00 00 00 10ff ff ff 00 ...ff ff ff 01 ...02 00 80 02 ...01 00 00 00 00 00 00 01 00 00 00 101b 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.00003412 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 .000062# 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 .-?..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)mLB3Understand the binary protocol for prepared statements. This section details how prepared statements are exchanged efficiently between client and server, optimizing performance and security.
This command creates a new database on the server, functionally equivalent to the SQL statement CREATE DATABASE.
This page is licensed: CC BY-SA / Gnu FDL
Null character (0x00).
Or a result set, when the query returns results (in case of a SELECT query, for example).
1b 00 00 00 03 44 52 4f 50 20 54 41 42 4c 45 20
.....DROP TABLE
49 46 20 45 58 49 53 54 53 20 62 75 6c 6b 31
IF EXISTS bulk1
slave port [2] = c9 5a => 23241.
rank [4] = 0.
master server id = 0.
Flags 00 00 = 0.
str_LEC
MYSQL_TYPE_DOUBLE
int_8
MYSQL_TYPE_ENUM
str_LEC
MYSQL_TYPE_FLOAT
int_4
MYSQL_TYPE_GEOMETRY
str_LEC
MYSQL_TYPE_INT24
int_4
MYSQL_TYPE_JSON
str_LEC
MYSQL_TYPE_LONGLONG
int_8
MYSQL_TYPE_LONG_BLOB
str_LEC
MYSQL_TYPE_LONG
int_4
MYSQL_TYPE_MEDIUM_BLOB
str_LEC
MYSQL_TYPE_NEWDECIMAL
str_LEC
MYSQL_TYPE_NULL
stored in bitmap
MYSQL_TYPE_SET
str_LEC
MYSQL_TYPE_STRING
str_LEC
MYSQL_TYPE_SHORT
int_2
MYSQL_TYPE_TINY_BLOB
str_LEC
MYSQL_TYPE_TINY
int_1
MYSQL_TYPE_VARCHAR
str_LEC
MYSQL_TYPE_VAR_STRING
str_LEC
MYSQL_TYPE_YEAR
int_4
This page is licensed: CC BY-SA / Gnu FDL
MYSQL_TYPE_BIT
str_LEC
MYSQL_TYPE_BLOB
str_LEC
MYSQL_TYPE_DATE MYSQL_TYPE_DATETIME MYSQL_TYPE_TIMESTAMP
int_11 (default)int_7 (no microseconds)int_4 (no time values)int_0 (no values)
MYSQL_TYPE_DECIMAL
0xef.uint<1> 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 rpl_semi_sync_master_enabled 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.
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 ef 00 2 bytes after the OK byte 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
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
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
fe 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
...
...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 10001391b 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: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 ....ee 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.(column_count + 7)/8 null bitmap
while (!eof) {
for (i=0; i < column_count; i++)
{
data (length depends on the data type)
}
}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.000034b8 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#...b3a b8 15 5a 03 01 00 00 00 17 00 00 00 09 0c 00 ..Z............
00 00 00 4e 99 ee 2c ...N..,If !DEPRECATE_EOF eof_packet.
If prepared statement returns result set and number of result set columns > 0:
For each column:
If !DEPRECATE_EOF .
string<1> -not used-
int<2> number of warnings.
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.
The ID of the loaded file.
Offset from the start of the statement to the beginning of the filename.
Offset from the start of the statement to the end of the filename.
How LOAD DATA INFILE handles duplicates (0x0: error, 0x1: ignore, 0x2: replace).
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.
This page is licensed: CC BY-SA / Gnu FDL
This command executes a bulk insert for a previously prepared statement, using a compact binary format for efficiency.
Executes a bulk insert of a previously prepared statement.
A command that returns a result set returns an error (Error packet).
Client to server.
0xfa : COM_STMT_BULK_EXECUTE header.
statement id.
.
The server can answer with 3 different responses:
0xff: if any errors occur.
0x00: when query execution works without result set.
A result set containing affected rows and auto-increment IDs when bulk flag SEND_UNIT_RESULTS is set.
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
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
Learn about the specific extensions and differences in the MariaDB protocol compared to MySQL, such as extended capability flags and metadata.
Here is a list of the differences between MariaDB and MySQL in terms of protocol, in order to help community driver maintainers.
MariaDB/MySQL servers can advertise feature support using capabilities. To expand the capabilities beyond the original 4 bytes, MariaDB utilizes 4 bytes, unused by MySQL, in the (server capabilities 3rd part). In order to avoid incompatibility in the future, those 4 bytes have to be read only if capability CLIENT_MYSQL is not set (server then being MariaDB).
This command executes a prepared statement using parameter values provided in the binary protocol format.
This command resets the session state (variables, tables, etc.) to its initial values without closing the connection.
COM_RESET_CONNECTION resets a connection without reauthentication.
The command does this:
Roll back any open transactions.
Reset transaction isolation level.
1F 00 00 00 16 53 45 4C 45 43 54 20 2A 20 46 52 .....SELECT * FR
1F 4D 20 74 65 73 74 5F 62 69 6E 64 5F 72 65 73 OM test_bind_res
75 6C 74 ultbulk_flagSEND_TYPES_TO_SERVERFor each parameter:
byte<1>: field type.
byte<1>: parameter type flag.
Until end of packet:
For each parameter (i.e param_count times):
byte<1>: parameter indicator.
If indicator == NONE:
byte : binary parameter value.
SEND_UNIT_RESULTS
64
Return generated affected rows and auto-increment IDs as a result set (only when server supports MARIADB_CLIENT_BULK_UNIT_RESULTS capability).
SEND_TYPES_TO_SERVER
128
Send types to server.
128
unsigned
0
NONE
Value follow
1
NULL
Value is null
2
DEFAULT
For INSERT/UPDATE, value is default
3
IGNORE
Value is default for insert, Is ignored for update
Delete user variables.
Remove temporary tables.
Remove all PREPARE statements.
Database will NOT be reset to initial value.
int<1> 0x1f : COM_RESET_CONNECTION Header
This page is licensed: CC BY-SA / Gnu FDL
-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).
n = 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 91# at 389
#171206 13:46:56 server id 10116 end_log_pos 424 Rand
SET @@RAND_SEED1=685157301, @@RAND_SEED2=758850369/*!*/;
# at 424MARIADB_CLIENT_CACHE_METADATA: Enables clients to cache metadata and avoid repeated network transmissions (since MariaDB 10.6.0).
MARIADB_CLIENT_EXTENDED_METADATA : Provides more detailed column metadata information for specific data types (since ).
MARIADB_CLIENT_STMT_BULK_OPERATIONS: Introduces a dedicated command, COM_STMT_BULK_EXECUTE, for efficient batch execution of statements.
MARIADB_CLIENT_BULK_UNIT_RESULTS: Allows for individual result sets for each bulk operation (since ).
Prepared statement metadata, which typically remains unchanged except during table alterations, can be cached by clients when the MARIADB_CLIENT_CACHE_METADATA capability is enabled. The server won't then send them again, unless they change. This significantly improves the performance of subsequent executions, especially for large metadata sets.
When MARIADB_CLIENT_CACHE_METADATA capability is set, the result set Column count packet format indicates if metadata follows or is skipped:
int column count,
if (MARIADB_CLIENT_CACHE_METADATA capability set) int<1> metadata follows (0 / 1).
Example
Java code:
Results with metadata caching:
The same, without metadata caching:
When the MARIADB_CLIENT_EXTENDED_METADATA capability is set, column definition packet can include additional type and format information.
For geometric fields: Detailed geometric data type (e.g., 'point', 'polygon').
For JSON fields: Type 'json'.
For UUID fields: Type 'uuid'.
The MARIADB_CLIENT_STMT_BULK_OPERATIONS capability enables the COM_STMT_BULK_EXECUTE command for efficient batch processing. However, note that only one result (OK or ERROR) is returned per batch, containing the total affected rows and the first auto-generated ID. For individual results, the MARIADB_CLIENT_BULK_UNIT_RESULTS capability can be set. The server will then return a result set containing for each unitary results (containing auto generated ids and affected rows).
Example
Java code:
Client send :
Server response:
MariaDB has specific authentication methods.
PARSEC plugin (from MariaDB 11.6.1)
MariaDB permits connection redirection.
Use Cases
Proxy Scenarios: Connection redirection is particularly beneficial when multiple servers share a single proxy.
Server Management: This feature can also be used during planned server shutdowns or restarts, allowing for a graceful transition to a new server.
Connectors can support 2 different levels:
On Connection Creation only: The redirection information is included in the initial OK_Packet sent by the server to the client. This allows the client to connect directly to the target server immediately.
Anytime Redirection: If redirection information becomes available later, the connector can handle it based on the existing transaction state.
No Transaction: If no transaction is in progress, the connector can redirect the connection directly.
Transaction in Progress: If a transaction is ongoing, the redirection information is stored until the transaction is completed. The transaction state is determined using server status flags like SERVER_STATUS_IN_TRANS in the "OK_Packet," "ERR_Packet," or "EOF_Packet."
A feature that enables TLS certificate validation without requiring client-side certificate configuration.
Requires a nonempty password.
Only supports the following authentication methods:
mysql_native_password
client_ed25519
parsec
When no SSL certificates are pre-configured, the server automatically generates a temporary self-signed certificate.
During connection establishment, the server embeds a special validation hash in the connection's "OK_Packet" information field.
The client connector must postpone SSL error handling until the connection phase is complete.
The client captures and stores the SHA256 fingerprint of the server's certificate.
If SSL errors occur, the client can only use specific authentication plugins (mysql_native_password/ed25519/parsec) to prevent potential password exposure.
At connection conclusion, the server sends an OK_Packet with a validation hash.
The client generates a hash using:
The password hash;
The server's seed;
Stored certificate fingerprint.
The SSL-error connection proceeds only if the client-generated hash matches the server-provided hash.
mysql_native_password:
Hash generation: SHA1(SHA1(password)).
ed25519:
Uses the Ed25519 cryptographic algorithm for hash generation.
parsec:
Hash generation involves combining
'P' character;
Number of iterations;
MySQL and MariaDB support session tracking when the CLIENT_SESSION_TRACK capability is set.
This is useful for connectors which have a method to set the transaction type, retrieving database for example to always have the server current value when changed. This permit to avoid executing some queries when not needed
Example of ending connection OK_Packet :
It indicates:
autocommit = ON
time_zone = SYSTEM
character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_results = utf8mb4
redirect_url =
A connector knows that character_set_client set to utf8mb4, then could avoid executing a command like "SET NAMES utf8mb4".
The X protocol is not supported.
Unsupported features and associate capabilities:
CLIENT_OPTIONAL_RESULTSET_METADATA: permits setting no METADATA at all for a connection. See Prepare statement skipping metadata's MariaDB implementation choice.
CLIENT_QUERY_ATTRIBUTES adds some metadata attributes
CLIENT_ZSTD_COMPRESSION_ALGORITHM permits zstd compression
MULTI_FACTOR_AUTHENTICATION Multifactor Authentication capability.
MariaDB connectors use specific criteria to determine if a server is a MariaDB instance during the initial handshake process.
The two key indicators used are:
Missing CLIENT_MYSQL capability: MariaDB does not set the CLIENT_MYSQL capability flag in the initial handshake packet.
Server version string: The server's version string is examined for the presence of the word "mariadb" (ignoring case sensitivity).
The reason is that some features like using COM_RESET_CONNECTION has no capability, and depend on the MySQL or MariaDB server version.
Connectors usually follow a two-step process for prepared statements:
Prepare: Send a COM_STMT_PREPARE command to the server, receiving a statement ID in response.
Execute: Send a COM_STMT_EXECUTE command, using the statement ID obtained in the previous step.
When the server support MARIADB_CLIENT_STMT_BULK_OPERATIONS capability, a specific statement ID value of -1 (or 0xffffffff in hexadecimal) can be used to indicate that the previously prepared statement can be reused. This enables connectors to pipeline the preparation and execution steps into a single request:
Send a COM_STMT_PREPARE then a COM_STMT_EXECUTE with statement ID -1 (0xffffffff) commands to the server.
Read the prepare and execute responses.
If the COM_STMT_PREPARE command returns an error (ERR_Packet), the subsequent COM_STMT_EXECUTE with statement ID -1 also fails and returns an error.
By eliminating the round trip for the separate COM_STMT_EXECUTE command, this approach improves performance for the first execution.
Traditionally, connectors send COM_STMT_PREPARE, wait for results, then execute COM_STMT_EXECUTE with statement_id received from the prepare result.
This description is for COM_STMT_EXECUTE, but COM_STMT_BULK_EXECUTE works exactly the same way.
A timeout for all commands can be set using SET max_statement_time=XXX with XXX in seconds.
Setting it for a specific query can be done using SET STATEMENT max_statement_time=XXX FOR ...
Connectors don't care about collations, but normally want to ensure charset in connection exchanges.
The only good solution is to use SET NAMES utf8mb4 or SET NAMES utf8mb4 COLLATE someUtf8mb4collation .
If they support session tracking, connectors can check if the character set of initially tracked variable character_set_connection corresponds to the expected value, then permit skipping this SET NAMES statement ( 'server default collation' from initial handshare packet cannot be trusted, since truncated to one byte. Recent mysql and mariadb collation can go on 2 bytes).
This page is licensed: CC BY-SA / Gnu FDL
Statement ID is the identifier of the prepared statement (from COM_STMT_PREPARE answer).
Value -1 (0xFFFFFFFF) can be used to indicate to use the last statement prepared on current connection if no COM_STMT_PREPARE has failed since.
This permit pipelining :
Send COM_STMT_PREPARE + COM_STMT_EXECUTE with statement ID -1.
Read COM_STMT_PREPARE + COM_STMT_EXECUTE response.
In case COM_STMT_PREPARE returns an error, COM_STMT_EXECUTE returns an error that statement ID -1 is unknown. This permits to avoid much of the network latency.
0
no cursor
1
read only
2
cursor for update
4
scrollable cursor
If the flags of the COM_STMT_EXECUTE request a cursor to be opened, the returned result only contains the column definitions and the EOF that terminates it, and the result set rows are fetched using separate COM_STMT_FETCH commands.
Whether a cursor is actually opened is indicated by the SERVER_STATUS_CURSOR_EXISTS bit in the first EOF packet in the response to the COM_STMT_EXECUTE. If it is not set in, no cursor is opened and a normal result set is returned.
Parameter type flag byte:
128
unsigned
The NULL-Bitmap indicates if parameters are null (one bit per parameter). If the parameter is NULL, the bit is set in the bitmap and the parameter value is not sent.
The size in bytes of the NULL-bitmap can be calculated with: (parameter number + 7) / 8
The encoding of the COM_STMT_EXECUTE parameters are the same as the encoding of the binary result sets.
The server can answer with 3 different responses:
0xff: ERR_Packet if any errors occur.
0x00: OK_packet when query execution works without result set.
One (or more) result set, when query execution return rows (in case of SELECT query, for example).
This page is licensed: CC BY-SA / Gnu FDL
The USER_VAR_EVENT logs the value of a user-defined variable, ensuring that statements using variables replicate consistently.
A USER_VAR_EVENT is written every time a statement uses a user defined variable.
Event Type is 14 (0x0e).
The length of the user variable name.
The name of the user variable.
NULL indicator.
If (not null indicator):
SET @foo:="bar" From mysqlbinlog Utility, CRC32This page is licensed: CC BY-SA / Gnu FDL
This command requests the server to shut down, it requires the SHUTDOWN privilege to be executed successfully.
stmt.execute("CREATE TABLE test_table (id int, val varchar(32))");
stmt.execute("INSERT INTO test_table VALUES (1, 'a'), (2, 'b')");
try (PreparedStatement prep = sharedConnBinary.prepareStatement("SELECT * FROM test_table WHERE id = ?")) {
prep.setInt(1, 1);
prep.executeQuery();
}Column count packet:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 02 00 00 01 02 00 | ...... |
+------+--------------------------------------------------+------------------+
row:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 08 00 00 02 00 00 01 00 00 00 01 61 | ...........a |
+------+--------------------------------------------------+------------------+
OK_Packet with a 0xFE header:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 07 00 00 03 FE 00 00 22 00 00 00 | ......."... |
+------+--------------------------------------------------+------------------+Column count packet:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 01 00 00 01 02 | ..... |
+------+--------------------------------------------------+------------------+
Column Definition packet:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 33 00 00 02 03 64 65 66 05 74 65 73 74 6A 0A 74 | 3....def.testj.t |
|000010| 65 73 74 5F 74 61 62 6C 65 0A 74 65 73 74 5F 74 | est_table.test_t |
|000020| 61 62 6C 65 02 69 64 02 69 64 0C 3F 00 0B 00 00 | able.id.id.?.... |
|000030| 00 03 00 00 00 00 00 | ....... |
+------+--------------------------------------------------+------------------+
Column Definition packet:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 35 00 00 03 03 64 65 66 05 74 65 73 74 6A 0A 74 | 5....def.testj.t |
|000010| 65 73 74 5F 74 61 62 6C 65 0A 74 65 73 74 5F 74 | est_table.test_t |
|000020| 61 62 6C 65 03 76 61 6C 03 76 61 6C 0C FF 00 80 | able.val.val.... |
|000030| 00 00 00 FD 00 00 00 00 00 | ......... |
+------+--------------------------------------------------+------------------+
row:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 08 00 00 04 00 00 01 00 00 00 01 61 | ...........a |
+------+--------------------------------------------------+------------------+
OK_Packet with a 0xFE header:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 07 00 00 05 FE 00 00 22 00 00 00 | ......."... |
+------+--------------------------------------------------+------------------+Statement stmt = connection.createStatement();
stmt.execute("CREATE TABLE test_table (id int, val varchar(32))");
try (PreparedStatement prep = connection.prepareStatement("INSERT INTO test_table VALUES (?, ?)")) {
prep.setInt(1, 1);
prep.setString(2, "a");
prep.addBatch();
prep.setInt(1, 2);
prep.setString(2, "b");
prep.addBatch();
prep.executeBatch();
}MARIADB_CLIENT_STMT_BULK_OPERATIONS:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 1B 00 00 00 FA FF FF FF FF 80 00 03 00 FD 00 00 | ................ |
|000010| 01 00 00 00 00 01 61 00 02 00 00 00 00 01 62 | ......a.......b |
+------+--------------------------------------------------+------------------+OK_Packet:
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| 2E 00 00 01 00 02 00 02 00 00 00 26 52 65 63 6F | ...........&Reco |
|000010| 72 64 73 3A 20 32 20 20 44 75 70 6C 69 63 61 74 | rds: 2 Duplicat |
|000020| 65 73 3A 20 30 20 20 57 61 72 6E 69 6E 67 73 3A | es: 0 Warnings: |
|000030| 20 30 | 0 |
+------+--------------------------------------------------+------------------++--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+------+--------------------------------------------------+------------------+
|000000| A6 00 00 02 00 00 00 02 40 00 00 00 9D 00 0E 0A | ........@....... |
|000010| 61 75 74 6F 63 6F 6D 6D 69 74 02 4F 4E 00 11 09 | autocommit.ON... |
|000020| 74 69 6D 65 5F 7A 6F 6E 65 06 53 59 53 54 45 4D | time_zone.SYSTEM |
|000030| 00 1D 14 63 68 61 72 61 63 74 65 72 5F 73 65 74 | ...character_set |
|000040| 5F 63 6C 69 65 6E 74 07 75 74 66 38 6D 62 34 00 | _client.utf8mb4. |
|000050| 21 18 63 68 61 72 61 63 74 65 72 5F 73 65 74 5F | !.character_set_ |
|000060| 63 6F 6E 6E 65 63 74 69 6F 6E 07 75 74 66 38 6D | connection.utf8m |
|000070| 62 34 00 1E 15 63 68 61 72 61 63 74 65 72 5F 73 | b4...character_s |
|000080| 65 74 5F 72 65 73 75 6C 74 73 07 75 74 66 38 6D | et_results.utf8m |
|000090| 62 34 00 0E 0C 72 65 64 69 72 65 63 74 5F 75 72 | b4...redirect_ur |
|0000a0| 6C 00 01 06 05 74 65 73 74 6A | l....testj |
+------+--------------------------------------------------+------------------+For each parameter:
byte<1>: field type.
byte<1>: parameter flag.
For each parameter (for instance, param_count times):
If parameter is not null:
byte binary parameter value.
Constant
Value
SHUTDOWN_DEFAULT
0
uint<1> variable type.
uint<4> collation number.
uint<4> The length of value.
string value.
uint<1> flags.
0x04
DECIMAL_RESULT
set @a:=1.2345
0x00
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
# 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=.} ....Salt;
Raw public key.
This command allows a connected client to re-authenticate as a different user without closing and reopening the connection.
COM_CHANGE_USERstring username.
If (server_capabilities & CLIENT_SECURE_CONNECTION):
int<1> length of authentication response.
string authentication response.
Else:
string authentication response.
string default schema name.
int<2> client character collation.
If (server_capabilities & CLIENT_PLUGIN_AUTH):
string authentication plugin name.
If (server_capabilities & CLIENT_CONNECT_ATTRS):
int size of connection attributes.
Loop:
key.
value.
Server response is like connection authentication :
An OK packet in case of success OK_Packet.
An error packet in case of error ERR_Packet.
Authentication switch:
If the client or server doesn't have PLUGIN_AUTH capability:
Server sends 0xFE byte.
Client sends old_password.
Else:
Server sends .
Client may have many exchanges with the server according to the .
Authentication switch ends with server sending either or .
If the authentication fails more than three times, all future COM_CHANGE_USER commands on the connection will return the #08S01 Unknown command error. This is an anti-brute-force mechanism designed to prevent rapid guessing of passwords.
This page is licensed: CC BY-SA / Gnu FDL
The OK_Packet is sent by the server to indicate the successful completion of a command, conveying affected row counts and server status flags.
OK_Packet is sent by the server to the client and indicates a successful completion of a command sent by the client before.
Server to client.
0x00 : OK_Packet header or (0xFE if CLIENT_DEPRECATE_EOF is set).
affected rows.
last insert id.
The length-encoded info string is not always included in the packet. Check the length of the packet to detect if there is data after the warning count. For the first OK_Packet in the connection it contains (if present) the . For the following OK_Packets it contains (if present) various human-readable information.
Values of server status flags:
While packet has remaining data:
.
If (session-change-type != SESSION_TRACK_STATE_CHANGE):
Each type of data has its own kind of format:
new current schema.
While there is remaining data:
variable data.
For each variable data:
variable name.
variable value.
Indicates if session state changes occurred. The value is represented as 1.
1 if session state tracking was enabled.
This tracker is not implemented by MariaDB.
Transaction characteristics.
Transaction characteristics is the set of SQL statements that reproduce the type and state of the current transaction. It can consist of the following SQL statements:
Transaction state string.
The transaction state string is always 8 characters long. The characters, in order, are:
No transaction: _ Explicit transaction: T Implicit transaction: I
Transaction read safe: _ Transaction read unsafe: r
Unknown transaction type: _
To determine if session tracking is enabled, check if the flag is set in server_capabilities.
This page is licensed: CC BY-SA / Gnu FDL
Every binary log event starts with a standardized header containing metadata such as the timestamp, event type, server ID, and event size.
All the binlog events stored in a binary log file have a common structure:
An event header;
Event data.
Timestamp (creation time).
(type_code).
Server_id (server which created the event).
Event Length (header + data).
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;
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 :
Encrypt the current IV of the binlog file in ECB mode;
XOR the remaining bytes with the encrypted IV.
These are generated on the fly, never written:
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
This page is licensed: CC BY-SA / Gnu FDL
This event provides a mapping between a table ID and its table definition, preceding row events to interpret the row data correctly.
Used for row-based binary logging beginning (binlog_format=ROW or MIXED).
This event precedes each row operation event and maps a table definition to a number, where the table definition consists of database and table names.
Event Type is 19 (0x13).
The table ID.
Reserved for future use.
Database name length.
The database name (null-terminated).
Table name length.
The table name (null-terminated).
The metadata block contains type specific metadata information for each column.
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 type.
Length.
Data.
Optional metadata types:
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
The GTID_EVENT marks the start of a new transaction event group, associating it with a Global Transaction ID (GTID) and providing commit flags.
int The number of columns in the table.
byte An array of 'n' column types, one byte per column.
int The length of the metadata block.
byte The metadata block;
byte Bit-field indicating whether each column can be NULL, one bit per column.
If (more_data_available):
byte Optional metadata block.
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.
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.
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).
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
Character set of columns, used if columns have different character sets. Returned as a sequence of collation numbers.
uint<8> GTID sequence.
Flags = 00 => 0.
crc32[4] = a8 f0 d8 b6 => b6 d8 f0 a8 => 0xb6d8f0a8.
# 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<.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 ..........int<2> warning count.
If packet has more data:
string info.
If (status flags & SERVER_SESSION_STATE_CHANGED) and session_tracking_supported (see CLIENT_SESSION_TRACK):
.
SERVER_QUERY_NO_INDEX_USED
32
The query did not use an index.
SERVER_STATUS_CURSOR_EXISTS
64
When using COM_STMT_FETCH, indicate that current cursor still has result.
SERVER_STATUS_LAST_ROW_SENT
128
When using COM_STMT_FETCH, indicate that current cursor has finished to send results.
SERVER_STATUS_DB_DROPPED
1<<8
Database has been dropped.
SERVER_STATUS_NO_BACKSLASH_ESCAPES
1<<9
Current escape mode is "no backslash escape".
SERVER_STATUS_METADATA_CHANGED
1<<10
A DDL change did have an impact on an existing PREPARE (an automatic re-prepare has been executed).
SERVER_QUERY_WAS_SLOW
1<<11
The query was slower than .
SERVER_PS_OUT_PARAMS
1<<12
This result set contains stored procedure output parameter.
SERVER_STATUS_IN_TRANS_READONLY
1<<13
Current transaction is a read-only transaction.
SERVER_SESSION_STATE_CHANGED
1<<14
Session state change. See for more information.
string session data change.
RTransaction write safe: _ Transaction write unsafe: w
Unknown transaction type: _ Read-write transaction: W
Transaction statement safe: _ Transaction statement unsafe: s
Transaction does not have resultsets: _ Transaction with result sets: S
No locked tables: _ Tables have been locked: L
SERVER_STATUS_IN_TRANS
1
A transaction is currently active.
SERVER_STATUS_AUTOCOMMIT
2
Autocommit mode is set.
SERVER_MORE_RESULTS_EXISTS
8
More results exists (more packets will follow).
SERVER_QUERY_NO_GOOD_INDEX_USED
16
Set if EXPLAIN would've shown Range checked for each record.
0
SESSION_TRACK_SYSTEM_VARIABLES
1
SESSION_TRACK_SCHEMA
2
SESSION_TRACK_STATE_CHANGE
3
SESSION_TRACK_GTIDS
4
SESSION_TRACK_TRANSACTION_CHARACTERISTICS
5
SESSION_TRACK_TRANSACTION_STATE
uint<4> Next Event position.
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.
f9 00 00 00 [4] Next Event pos => 00 00 00 f9 => 249 (pos 4 + event size)
00 00 [2] Event flags = 0
0x02
0x03
0x04
0x10
0x0d
0x0e
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.
Type[1] = 0xa2.
Flags[2] = 08 00 => LOG_EVENT_SUPPRESS_USE_F.
If flag & FL_GROUP_COMMIT_ID:
uint<8> commit_id.
Else if flag & (FL_PREPARED_XA or FL_COMPLETED_XA):
Else:
uint<6> 0.
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.
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).
commit_id[6] = 00 00 00 00 00 00 = 0.
CRC32[4] = 8e 66 9a 30.
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.
CRC32[4] = 37 d3 c8 23.
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.
A result set row packet contains the data for a single row in a query result, formatted as text or binary depending on the execution context.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE;
SET TRANSACTION READ ONLY;
SET TRANSACTION READ WRITE;
XA START <XA specification>;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....
...
...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..#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).
SELECT UNIX_TIMESTAMP();
SHOW VARIABLES LIKE 'SERVER_ID';
SET @master_heartbeat_period= 30000001024;
SET @master_binlog_checksum= @@global.binlog_checksum;
SELECT @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.
Then COM_REGISTER_SLAVE completes the registration.
The COM_BINLOG_DUMP marks the request of binlog events stream.
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:
FORMAT_DESCRIPTION_EVENT: 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.
FAKE_GTID_LIST_EVENT 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
BINLOG_CHECKPOINT EVENT packet: 2c 00 00 04 ... 16 1f fe 3f
GTID_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
This page is licensed: CC BY-SA / Gnu FDL
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..`string column data.
The byte representation of the string according to client character collation.
byte<1> 0x00 header.
byte<(number_of_columns + 7) / 8> NULL-Bitmap.
For each column:
If column value is not null:
If MYSQL_TYPE_DOUBLE type :
If MYSQL_TYPE_LONGLONG type :
If MYSQL_TYPE_INTEGER type :
The NULL-Bitmap indicates if a parameter for a column is null (one bit per parameter) beginning with the 3rd bit. NULL-bitmap size is (number_of_columns + 7) / 8.
DECIMAL has no fixed size, so will be encoded as string. A DECIMAL(10,2) with a value of -15.5 is stored as:
DOUBLE is the IEEE 754 floating-point value in Little-endian format on 8 bytes.
BIGINT is the value in Little-endian format on 8 bytes. Signed is defined by the Column field detail flag.
INTEGER is the value in Little-endian format on 4 bytes. Signed is defined by the Column field detail flag.
MEDIUMINT is similar to INTEGER binary encoding, even if MEDIUM int is 3-bytes encoded server side. (Last byte will always be 0x00).
FLOAT is the IEEE 754 floating-point value in Little-endian format on 4 bytes.
SMALLINT is the value in Little-endian format on 2 bytes. Signed is defined by the Column field detail flag.
YEAR uses the same format as SMALLINT.
TINYINT is the value of 1 byte. Signed is defined by the Column field detail flag.
DATE uses the same format as TIMESTAMP binary encoding, with a data length of 0 for the special '0000-00-00' value and 4 for the standard year/month/day format.
Data is encoded in 8 bytes without fractional seconds, 12 bytes with fractional seconds.
1
data length : 0 for special '0000-00-00 00:00:00' value. 4 with year + month + day of month only 7 for timestamps without fractional seconds 11 with fractional seconds
2-3
year on 2 bytes little-endian format
4
Month ( 1=january)
5
days of month
6
hour of day (0 if DATE type)
7
minutes (0 if DATE type)
Data is encoded in 9 bytes without fractional seconds, 13 bytes with fractional seconds.
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.
Event Type = 0x02.
Fixed data part:
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.
Variable data part:
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.
bitmask.
8-byte bitmask.
length
catalog name + '\0'
auto_increment increment
auto_increment offset
client character set
collation_connection
collation_server
length
time zone
length
catalog
code The mapping between code and names are defined in sql_locale.cc.
database collation
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
original event length
user name length
user name
host name length
host name
MySQL only
count
for (i=0;i < count; i++)
Null terminated database name
MySQL only
microsecond part
MariaDB only
microsecond part
MariaDB only
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.
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).
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).
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.
A ROWS_EVENT_V1 is written for row-based replication if data is inserted, deleted or updated.
A ROWS_EVENT (version 2) is written for row based replication if data is inserted, deleted or updated. MariaDB Server doesn't send version 2 row events.
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).
The table id.
Flags.
If rows_event is version 2:
Extra data length.
Table id refers to a table defined by . The special value 0xFFFFFF should have "end of statement flag" (0x0001) set and indicates that table maps can be freed.
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 . 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
The types that aren't listed here do not store data in the metadata block.
MYSQL_TYPE_BLOB and other blob typesStored 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:
MYSQL_TYPE_STRING, MYSQL_TYPE_SET and MYSQL_TYPE_ENUMStored 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.
MYSQL_TYPE_VARCHAR and other variable length string typesStored 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.
MYSQL_TYPE_DATETIMEThis 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.
MYSQL_TYPE_TIMEThis 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.
MYSQL_TYPE_DATETIME2Stored 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 .
MYSQL_TYPE_TIME2Stored 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 .
MYSQL_TYPE_TIMESTAMP2Stored 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 .
MYSQL_TYPE_DATEStored 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.
MYSQL_TYPE_TIMESTAMPStored 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
A result set consists of a sequence of packets including column counts, column definitions, and row data, terminated by an EOF or OK packet.
MariaDB Server sends the following packets as part of a result set.
A result set consists of different packets:
.
1 .
06 45 49 53 46 53 48 . - 1 5 . 5 0MYSQL_TYPE_MEDIUMINT type : MEDIUMINT Binary encodingIf MYSQL_TYPE_FLOAT type : FLOAT Binary encoding
If MYSQL_TYPE_SMALLINT type : SMALLINTBinary encoding
If MYSQL_TYPE_YEAR type : YEAR Binary encoding
If MYSQL_TYPE_TINYINT type : TINYINT Binary encoding
If MYSQL_TYPE_DATE type : DATE Binary encoding
If MYSQL_TYPE_TIMESTAMP type : TIMESTAMP Binary encoding
If MYSQL_TYPE_DATETIME type : TIMESTAMP Binary encoding
If MYSQL_TYPE_TIME type : TIME Binary encoding
If MYSQL_TYPE_NEWDECIMAL type : DECIMAL Binary encoding
If MYSQL_TYPE_TINY_BLOB, MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, MYSQL_TYPE_BLOB, MYSQL_TYPE_GEOMETRY, MYSQL_TYPE_STRING, MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VAR_STRING): byte value
8
seconds (0 if DATE type)
9-12
micro-second on 4 bytes little-endian format (only if data-length is > 7)
uint<2> The error code resulting from execution of the statement on the master.
uint<2> The length of the status variable block.
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
Flags = 00 00 => 0.
Status variables[n] = 00 ... 08 00.
The default database[string] = 00 = 0 (no default database).
The SQL statement[string] = TRUNCATE TABLE test.t4.
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.
0x00004000
OPTION_AUTO_IS_NULL
0x00080000
OPTION_NOT_AUTOCOMMIT
0x04000000
OPTION_NO_FOREIGN_KEY_CHECKS
0x08000000
OPTION_RELAXED_UNIQUE_CHECKS
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
0x00000040
DELETE_ROWS_EVENT
Delete existing row (version 2, MySQL only).
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).
string Extra data.
uint Number of columns.
byte Columns used. n = (number_of_columns + 7)/8.
If (event_type == UPDATE_ROWS_EVENT_v1):
byte 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.
byte<header_size>uncompressed length, stored in MyISAM format.
byte Null Bitmap (n = (number_of_columns + 7)/8).
string Column data. The length needs to be calculated by checking the column types from referring TABLE_MAP_EVENT.
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
MYSQL_TYPE_BLOB
MYSQL_TYPE_TIMESTAMP2
MYSQL_TYPE_DATETIME2
MYSQL_TYPE_TIME2
MYSQL_TYPE_FLOAT
MYSQL_TYPE_DOUBLE
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).
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).
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.
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.
1
MYSQL_TYPE_TINY_BLOB
2
MYSQL_TYPE_BLOB
3
MYSQL_TYPE_MEDIUM_BLOB
4
MYSQL_TYPE_LONG_BLOB
MYSQL_TYPE_LONGLONG
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 ....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 ....MARIADB_CLIENT_CACHE_METADATA capability set) OR (send metadata == 1):For each column (for instance, column_count times):
If not (CLIENT_DEPRECATE_EOF capability set) EOF_Packet.
If error:
Else:
If CLIENT_DEPRECATE_EOF capability:
OK_Packet with a 0xFE header.
Else .
It would be unsafe to assume that any packet with a 0xFE header is an OK packet (OK_Packet) or an EOF packet (EOF_Packet), because result-set row packets (ResultsetRow) can also begin with 0xFE when using the text protocol with a field length greater than 0xFFFFFF. To safely confirm that a packet with a 0xFE header is an OK packet (OK_Packet) or an EOF packet (EOF_Packet), you must also check that the packet length is less than 0xFFFFFF.
The column count packet describes the number of columns in the result set. It uses the following format:
int column count.
If (MARIADB_CLIENT_CACHE_METADATA capability set):
int<1> metadata follows (0 / 1).
The metadata indicator byte is only present if both the client and the server declare the MARIADB_CLIENT_CACHE_METADATA capability.
If the metadata byte is set to 1, the normal metadata follows the column definitions. If the metadata byte is set to 0, the Column Count Packet is immediately followed by the second EOF packet (EOF_Packet) or the result set rows if the CLIENT_DEPRECATE_EOF capability is set.
A column definition packet describes a column in the result set. It uses the following format:
The column type field in the column definition packet describes the base type of the column. It also indicates how the values are encoded for COM_STMT_EXECUTE parameters and binary result set rows.
0
MYSQL_TYPE_DECIMAL
1
MYSQL_TYPE_TINY
2
MYSQL_TYPE_SHORT
3
MYSQL_TYPE_LONG
The column details flag describes certain column attributes and whether certain column options are set.
It is a bitmask with the following flags:
1
NOT_NULL
field cannot be null
2
PRIMARY_KEY
field is a primary key
4
UNIQUE_KEY
field is unique
8
MULTIPLE_KEY
This extended column type information can be used to find out more specific details about the column type:
This page is licensed: CC BY-SA / Gnu FDL
4
MYSQL_TYPE_FLOAT
5
MYSQL_TYPE_DOUBLE
6
MYSQL_TYPE_NULL
Not used, nullness is indicated by the NULL-bitmap in the result
7
MYSQL_TYPE_TIMESTAMP
8
MYSQL_TYPE_LONGLONG
9
MYSQL_TYPE_INT24
10
MYSQL_TYPE_DATE
11
MYSQL_TYPE_TIME
12
MYSQL_TYPE_DATETIME
13
MYSQL_TYPE_YEAR
14
MYSQL_TYPE_NEWDATE
15
MYSQL_TYPE_VARCHAR
16
MYSQL_TYPE_BIT
17
MYSQL_TYPE_TIMESTAMP2
Used only in the replication protocol
18
MYSQL_TYPE_DATETIME2
Used only in the replication protocol
19
MYSQL_TYPE_TIME2
Used only in the replication protocol
245
MYSQL_TYPE_JSON
byte encoding (only used with MySQL, MariaDB uses MYSQL_TYPE_STRING for JSON)
246
MYSQL_TYPE_NEWDECIMAL
247
MYSQL_TYPE_ENUM
248
MYSQL_TYPE_SET
249
MYSQL_TYPE_TINY_BLOB
250
MYSQL_TYPE_MEDIUM_BLOB
251
MYSQL_TYPE_LONG_BLOB
252
MYSQL_TYPE_BLOB
253
MYSQL_TYPE_VAR_STRING
254
MYSQL_TYPE_STRING
255
MYSQL_TYPE_GEOMETRY
field is in a multiple key
16
BLOB
is this field a Blob
32
UNSIGNED
is this field unsigned
64
ZEROFILL_FLAG
is this field a zerofill
128
BINARY_COLLATION
whether this field has a binary collation
256
ENUM
Field is an enumeration
512
AUTO_INCREMENT
field auto-increment
1024
TIMESTAMP
field is a timestamp value
2048
SET
field is a SET
4096
NO_DEFAULT_VALUE_FLAG
field doesn't have default value
8192
ON_UPDATE_NOW_FLAG
field is set to NOW on UPDATE
32768
NUM_FLAG
field is num
The connection phase involves an initial handshake where the client and server exchange capabilities, default settings, and authentication data to establish a session.
Connection is done by many exchanges:
(Create socket)
If first byte from server is 0xFF:
Packet is an , socket has to be closed.
Else:
Packet is an .
If SSL/TLS connection:
Client sends and switches to SSL mode for sending and receiving the following messages:
protocol version.
server version
MariaDB Server 10.X versions are by default prefixed "5.5.5-".
and later versions do not have a "5.5.5-" default prefix.
If the client requests a TLS/SSL connection, the first response is an SSL connection request packet, followed by a handshake response packet. If no TLS is required, client directly sends a handshake response packet.
client capabilities.
max packet size.
client's default character set and collation.
reserved.
Automatic Encrypted Connections (+):
Previously, failed SSL connections due to self-signed certificates prevented communication. + introduces a secondary validation method that works for all servers.
Even without a valid SSL certificate, the connector can still authenticate by remembering the server's fingerprint (unique identifier). However, it needs to confirm the connection is secure.
The confirmation method depends on the connection type. When using secure MitM-proof methods, like Unix sockets, connector can automatically validate the connection. Otherwise, a shared secret is used.
The shared secret is only used if the authentication plugin password is hashable (for instance, mysql_native_password , client_ed25519, or parsec) and not empty.
It's calculated by hashing the user's hash password with the authentication seed and the server fingerprint.
Password hash is generated depending on authentication plugin:
ed25519 : identical to password encryption.
mysql_native_password : identical to password encryption.
parsec: ext-salt + raw ed25519 public key.
For servers running or later, the final confirmation packet contains:
encryption (actually only 0x01 = SHA256 encryption)
shared secret.
If the calculated shared secret matches the received one, the SSL connection is considered valid (host validation is not needed). Otherwise, the connection must be closed for security reasons.
client capabilities.
max packet size.
client's default character set and collation.
reserved.
If the authentication plugin needs further rounds of data exchange (like parsec), the server sends additional plugin authentication data (optionally prefixed with 0x01) to which the client sends an additional response. This can be repeated in multiple rounds. It ends with one of the following:
The server responds with an , an , or an Authentication Switch Request packet.
(If client and server support CLIENT_AUTH capability):
0xFE : Authentication switch request header.
authentication plugin name.
authentication plugin data.
deprecated — send an 8 byte encrypted password.
Authentication plugin data format:
8-byte seed.
Client response:
old format encrypted password.
Since password is transmitted in clear, this has been used only when using SSL connection
Send clear password to server.
Client response:
password without encryption.
SHA-1 encrypted password with server seed.
Authentication plugin data format:
seed.
Client response:
SHA1-encrypted password.
The password is encrypted with: SHA1( password ) ^ SHA1( seed + SHA1( SHA1( password ) ) ) .
Interactive exchanges to permit fill passwords — for example for 2-step authentication.
Authentication plugin data format:
password type.
prompt message.
The server can send one or many requests. For each of them, the client must display this prompt message to the user, to permit the user to type requested information, then send it to the server in format. Password type indicates the answer format (2 means "read the input with the echo enabled", 4 means "password-like input, echo disabled")
First authentication format (from authentication switch packet) can be empty.
This end when the server sends an , or .
GSSAPI implementation.
Authentication plugin data format:
serverPrincipalName (UTF-8 format).
mechanisms (UTF-8 format).
Client must exchange packet with server until having a mutual authentication. The only difference compared to standard client-server GSSAPI authentication is that exchanges contain standard protocol with packet headers.
The ed25519 plugin uses the Elliptic Curve Digital Signature Algorithm to securely store users' passwords and to authenticate users.
See .
The server sends a random nonce that the client signs.
authentication plugin data format:
seed.
Client response:
ed25519 encrypted password.
Authentication plugin data format:
server nonce.
Client has to send an empty packet to request "ext-salt".
Format of ext-salt is:
'P' (denotes KDF algorithm = PBKDF2).
iteration factor. number of iterations correspond to 1024 << iteration factor (0x0 means 1024, 0x1 means 2048, etc.).
salt.
The client must then:
Generate derived key = hash password with PBKDF2 (sha512 digest) with iteration number and salt from ext-salt.
Generate a client 32 bytes nonce.
Generate the signature with ed25519 of an array concatenation of server nonce + client nonce with the generated derived key as private key.
Client response:
client nonce.
signature.
Server and Client have different capabilities, here is the possibles values. client with capabilities CLIENT_MYSQL + CONNECT_WITH_DB will have a value of 9 (1 + 8).
The 20-byte string 'seed' is calculated by concatenating scramble first part (8 bytes) and scramble second part from . After that, the client calculates a password hash using the password and seed by using ^ (bitwise xor), + (string concatenation) and SHA1 as follows:
This page is licensed: CC BY-SA / Gnu FDL
Client sends Handshake response packet.
Server sends either:
An OK packet in case of success OK_Packet.
An error packet in case of error ERR_Packet.
Further authentication data, if requested by the authentication plugin.
The content of this authentication data is defined by the authentication plugin.
The server may send 0x01 byte first to escape the authentication data, particularly if the data starts with the 0x00 or 0xFE or 0XFF byte.
This optional first 0x01 byte must always be skipped by the client.
Authentication switch:
If the client or server doesn't have PLUGIN_AUTH capability:
Server sends 0xFE byte.
int<4> connection id.
string<8> authentication plugin data (1st part).
string<1> reserved byte.
int<2> server capabilities (1st part).
int<1> server default collation.
int<2> status flags.
int<2> server capabilities (2nd part).
If (server_capabilities & PLUGIN_AUTH):
int<1> plugin data length.
Else:
int<1> 0x00.
string<6> filler.
If (server_capabilities & CLIENT_MYSQL):
string<4> filler.
Else:
int<4> server capabilities 3rd part. MariaDB specific flags /* or later */.
If (server_capabilities & PLUGIN_AUTH):
string authentication plugin name.
If not (server_capabilities & CLIENT_MYSQL):
int<4> extended client capabilities
Else:
string<4> reserved.
If not (server_capabilities & CLIENT_MYSQL):
int<4> extended client capabilities.
Else:
string<4> reserved.
string username.
If (password):
If (server_capabilities & PLUGIN_AUTH_LENENC_CLIENT_DATA):
string authentication data.
Else if (server_capabilities & CLIENT_SECURE_CONNECTION):
length of authentication response.
authentication response (length is indicated by previous field).
Else:
authentication response null ended.
Else:
string<1>\0 (empty password).
If (server_capabilities & CLIENT_CONNECT_WITH_DB):
string default database name.
If (server_capabilities & CLIENT_PLUGIN_AUTH):
string authentication plugin name.
If (server_capabilities & CLIENT_CONNECT_ATTRS):
int size of connection attributes.
While packet has remaining data:
key.
value.
LOCAL_FILES
128
Can use .
IGNORE_SPACE
256
Ignore spaces before (.
CLIENT_PROTOCOL_41
1 << 9
4.1 protocol.
CLIENT_INTERACTIVE
1 << 10
SSL
1 << 11
Can use SSL.
TRANSACTIONS
1 << 13
SECURE_CONNECTION
1 << 15
4.1 authentication.
MULTI_STATEMENTS
1 << 16
Enable/disable multi-statement support.
MULTI_RESULTS
1 << 17
Enable/disable multi-results.
PS_MULTI_RESULTS
1 << 18
Enable/disable multi-results for PrepareStatement.
PLUGIN_AUTH
1 << 19
Client supports plugin authentication.
CONNECT_ATTRS
1 << 20
Client sends connection attributes.
PLUGIN_AUTH_LENENC_CLIENT_DATA
1 << 21
Enable authentication response packet to be larger than 255 bytes.
CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS
1 << 22
Client can handle expired passwords.
CLIENT_SESSION_TRACK
1 << 23
Enable/disable session tracking in OK_Packet.
CLIENT_DEPRECATE_EOF
1 << 24
EOF_Packet deprecation: OK_Packet replace EOF_Packet at the end of the result set when in text format. EOF_Packet between columns definition and resultsetRows is deleted.
CLIENT_OPTIONAL_RESULTSET_METADATA
1 << 25
Not in use for MariaDB.
CLIENT_ZSTD_COMPRESSION_ALGORITHM
1 << 26
Support zstd protocol compression.
CLIENT_CAPABILITY_EXTENSION
1 << 29
Reserved for future use.
CLIENT_SSL_VERIFY_SERVER_CERT
1 << 30
Client verify server certificate. Deprecated, client has options to indicate if server certificate must be verified.
CLIENT_REMEMBER_OPTIONS
1 << 31
MARIADB_CLIENT_PROGRESS
1 << 32
Client support progress indicator.
MARIADB_CLIENT_COM_MULTI
1 << 33
Permit COM_MULTI protocol.
MARIADB_CLIENT_STMT_BULK_OPERATIONS
1 << 34
Permit bulk insert.
MARIADB_CLIENT_EXTENDED_METADATA
1 << 35
Add extended metadata information.
MARIADB_CLIENT_CACHE_METADATA
1 << 36
Permit skipping metadata.
MARIADB_CLIENT_BULK_UNIT_RESULTS
1 << 37
When enabled, indicate that bulk command can use STMT_BULK_FLAG_SEND_UNIT_RESULTS flag that permits to return a result set of all affected rows and auto-increment values.
CLIENT_MYSQL
1
Set by older MariaDB versions. MySQL named this CLIENT_LONG_PASSWORD.
FOUND_ROWS
2
CONNECT_WITH_DB
8
You can specify database on connect.
COMPRESS
32
Can use compression protocol
SHA1( passwd) ^ SHA1( seed + SHA1( SHA1( passwd ) ) )old_password.Else:
Server sends Authentication switch request.
Client may have many exchanges with the server according to the Plugin.
Authentication switch ends with server sending either OK_Packet or ERR_Packet.