All pages
Powered by GitBook
1 of 71

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...

Client/Server Protocol

Understand the MariaDB client/server protocol. This section details how clients communicate with the server, including connection, authentication, query execution, and result set handling.

Client/Server Protocol Overview

Protocol Data TypesMariaDB Protocol Differences with MySQL

Client/Server Protocol Documentation

0 - Packet1 - Connecting2 - Text Protocol3 - Binary Protocol (Prepared Statements)4 - Server Response Packets

Replication Protocol Documentation

Replication Protocol

1 - Connecting

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.

ConnectingConnecting via caching_sha2_passwordConnecting via sha256_password

2 - Text Protocol

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.

Server Response Packets (Binary Protocol)

This section details the structure of response packets sent by the server when using the binary protocol, particularly for result sets.

Replication Protocol

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.

Connecting via caching_sha2_password

This plugin implements the caching_sha2_password authentication method, using an in-memory cache for fast authentication or RSA encryption for full verification.

Overview

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:

Protocol Data Types

This page defines the fundamental data types used in the MariaDB client/server protocol, including integers, strings, and binary representations.

List of Possible Types

Unknown type:

COM_DEBUG

This command forces the server to dump debug information to the standard output or log, requiring the SUPER privilege.

The COM_DEBUG command forces the server to dump debug information to stdout. It requires the privilege.

Fields

  • 0xOD : COM_DEBUG

COM_DROP_DB

This command drops an existing database from the server, functionally equivalent to the SQL statement DROP DATABASE.

This command is deprecated and not used by MariaDB connectors any more. Use the SQL statements or instead.

This page is licensed: CC BY-SA / Gnu FDL

COM_INIT_DB

This command selects the default database for the current connection, functionally equivalent to the USE statement.

COM_INIT_DB is used to specify the default schema for the connection.

Fields

  • 0x02 : COM_INIT_DB

COM_PING

This command checks if the server is alive and reachable, the server responds with an OK packet if it is running.

COM_PING permits sending a packet containing one byte to check that the connection is active.

Fields

  • 0x0e : COM_PING header.

COM_PROCESS_KILL

This command asks the server to terminate a specific connection thread, functionally equivalent to the KILL statement.

Forces the server to terminate a specified connection.

Fields

  • 0xC COM_PROCESS_KILL.

COM_QUIT

This command instructs the server to close the connection and release associated resources.

Using the COM_QUIT command, the client tells the server that the connection should be terminated.

Fields

  • 0x01 : COM_QUIT

COM_SLEEP

This is an internal command used to represent a sleeping connection that is waiting for a new command from the client.

This command is used inside the server only.

Direction

Used inside server only.

COM_STATISTICS

This command retrieves a human-readable string containing internal server statistics like uptime and thread counts.

Get internal server statistics.

Fields

  • 0x09 : COM_STATISTICS header.

COM_STMT_FETCH

This command fetches rows from an existing result set of a prepared statement that was executed with a cursor.

Fetch rows from a prepared statement.

A with a non-zero cursor flag must have been successfully executed before any COM_STMT_FETCH commands can be executed.

Fields

  • 0x1C COM_STMT_FETCH header.

EOF_Packet

The EOF_Packet marks the end of a result set or a sequence of packets, containing warning counts and server status flags.

The EOF_Packet marks the end of a result set, and returns status and warnings.

When testing for an EOF packet, the packet size must be less than 9 bytes in length. The result set can send data that begin with a 0xfe byte, but then the packet length will be greater than 9.

Fields

LOCAL_INFILE_Packet

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.

If the client sends a LOAD DATA LOCAL INFILE statement via , the server responds with LOCAL_INFILE_Packet to tell the client to send a specified file to the server.

Fields

BEGIN_LOAD_QUERY_EVENT

Used during LOAD DATA INFILE operations, this event marks the beginning of the data load and contains the initial query information.

This event is written to the binary log file for events if the server variable binlog_mode is set to "STATEMENT".

Header

  • Event Type = 0x11.

4 - Server Response Packets

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 , .

  • Authentication

    SHA-2 encrypted password

    Encryption is XOR(SHA256(password), SHA256(seed, SHA256(SHA256(password)))).

    • byte<32> encrypted password.

    "fast" authentication result

    Result of fast authentication.

    • byte authentication result.

    0x03 value means success authentication. 0x04 value means continue.

    Client clear password answer

    • string password without encryption.

    Public key request

    Value send is not 0x01 like sha256_password use, but 0x02.

    • byte<1> fixed 0x02 value.

    Public key response

    • byte<1> fixed 0x01 value.

    • byte public key data.

    RSA encrypted password

    • 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

    sha256_password

    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

    Fixed length bytes

    The notation is "byte".

    A fixed-length byte stores the value in a series of n bytes.

    Length encoded 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.

    End of file length bytes

    The notation is "byte".

    Bytes whose length will be calculated by the packet remaining length.

    Fixed length integers

    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).

    Example

    An int<4> with value 2 is stored as02 00 00 00

    Length encoded integers

    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)

    Fixed-length strings

    The notation is "string".

    Fixed-length strings have a known hardcoded length.

    Null-terminated strings

    The notation is "string".

    Null-terminated strings have a variable size and are terminated by a 0x00 character

    Length-encoded strings

    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.

    Example

    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.

    End of File Length Strings

    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

    byte<1>

    Fixed-length bytes

    byte

    Length-encoded bytes

    header.

    Response

    EOF Packet.

    This page is licensed: CC BY-SA / Gnu FDL

    SUPER
    int<1>
    Header.
  • string schema name.

  • Response

    ERR_Packet or OK_Packet.

    Example

    This page is licensed: CC BY-SA / Gnu FDL

    int<1>
    06 00 00 00 02 74 65 73 74 63                    .....testc
    Response

    OK_Packet.

    This page is licensed: CC BY-SA / Gnu FDL

    int<1>

    int<4> process ID.

    Response

    OK Packet or ERR Packet.

    This page is licensed: CC BY-SA / Gnu FDL

    int<1>
    header.

    Response

    Server terminates connection.

    This page is licensed: CC BY-SA / Gnu FDL

    int<1>
    Fields
    • int<1> 0x00: COM_SLEEP header.

    Response

    ERR_Packet.

    This page is licensed: CC BY-SA / Gnu FDL

    Response

    string human-readable string.

    This page is licensed: CC BY-SA / Gnu FDL

    int<1>
  • int<4> statement id.

  • int<4> number of rows to fetch.

  • Response

    Returns one or more binary result set rows followed by an EOF packet.

    This page is licensed: CC BY-SA / Gnu FDL

    COM_STMT_EXECUTE
    int<1>

    int<1> 0xfe : EOF header.

  • int<2> warning count.

  • int<2> server status.

  • This page is licensed: CC BY-SA / Gnu FDL

    0xFB : LOCAL_INFILE header.
  • string filename.

  • Client Response

    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

    com_query
    int<1>
    Fields

    Fixed data part:

    • uint<4> The ID of the file.

    Variable data part:

    • byte Null terminated data block.

    Example

    This page is licensed: CC BY-SA / Gnu FDL

    LOAD DATA INFILE
    DROP SCHEMA
    DROP DATABASE

    0 - Packet

    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:

    Standard packet

    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.

    Packet Splitting

    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.

    Note that data of exact size 2^24 -1 byte must be sent in 2 packets, the first one with length prefix 0xffffff, and the second one with length 0 (length byte 0x000000, seqno incremented). Generally, if data length is an exact multiple of 2^24-1, it must always be followed by an empty packet.

    Compressed Packet

    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.

    Compression Packet Splitting

    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

    COM_FIELD_LIST

    This command retrieves a list of fields (columns) for a specified table, similar to the SHOW COLUMNS SQL statement.

    This command is deprecated and not used by MariaDB connectors any more. Use the SQL statements SHOW COLUMNS or SELECT FROM INFORMATION_SCHEMA.COLUMNS instead.

    Fields

    • 0x04 : COM_FIELD_LIST header.

    • Table name.

    • Optional field list.

    Response

    • zero or more .

    • .

    This page is licensed: CC BY-SA / Gnu FDL

    COM_PROCESSLIST

    This command retrieves a list of active threads and their current status, similar to the SHOW PROCESSLIST statement.

    This command is deprecated and not used by MariaDB connectors any more. Use the SQL statement SHOW PROCESSLIST instead.

    This page is licensed: CC BY-SA / Gnu FDL

    COM_STMT_CLOSE

    This command deallocates a prepared statement on the server, freeing up associated resources.

    Closes a previously prepared statement.

    Direction

    Client to server.

    Implemented by

    • .

    • .

    Fields

    • 0x19 COM_STMT_CLOSE header.

    • statement id.

    Example

    05 00 00 00 19 04 00 00 00

    Response

    No response from server.

    This page is licensed: CC BY-SA / Gnu FDL

    COM_STMT_RESET

    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.

    Fields

    • int<1> 0x1A COM_STMT_RESET header.

    • Statement ID.

    Response

    or .

    Example

    This page is licensed: CC BY-SA / Gnu FDL

    ERR_Packet

    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.

    Fields

    • 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.

    See also

    This page is licensed: CC BY-SA / Gnu FDL

    ANNOTATE_ROWS_EVENT

    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.

    The master server sends ANNOTATE_ROWS_EVENT events only if the replica server connects with the BINLOG_SEND_ANNOTATE_ROWS_EVENT flag (value is 2) in the COM_BINLOG_DUMP replica registration phase.

    Header

    • Event Type is 160 (0xa0).

    Fields

    • The SQL statement (not null-terminated).

    Complete Example with CRC32

    This page is licensed: CC BY-SA / Gnu FDL

    Connecting via sha256_password

    The sha256_password plugin manages authentication using SHA-256 encryption, supporting both clear text passwords over SSL and RSA encrypted password exchange.

    Overview

    SHA256 authentication possible exchanges:

    • if connection use SSL (SSLRequest Packet sent):

    COM_SET_OPTION

    This command configures client-specific options for the current connection, such as enabling or disabling multi-statements.

    Enables or disables server option.

    Fields

    • 0x1B COM_SET_OPTION .

    COM_STMT_SEND_LONG_DATA

    This command sends long data, such as BLOB or TEXT values, in chunks for a specific parameter of a prepared statement.

    When data for a specific column is large, it can be sent separately to avoid the limitation of max_allowed_packet (see ).

    Multiple COM_STMT_SEND_LONG_DATA commands with the same column ID append the data. COM_STMT_SEND_LONG_DATA is sent before .

    Fields

    COM_BINLOG_DUMP

    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.

    BINLOG_CHECKPOINT_EVENT

    A marker event indicating a checkpoint in the binary log, used to ensure consistency and safe rotation of log files.

    Binlog Checkpoint Event, Event Type is 161 (0xa1). This event specifies a binlog file such that XA crash recovery can start from that file.

    There can be more than one event in a binlog file.

    Header

    Fake GTID_LIST event

    A synthetic event sent by the master after the initial handshake to inform the replica of its current GTID state, it is not written to the binary log.

    This event is sent by master server to the registering replica. It is sent only once, after the .

    The fake GTID_LIST event is not written in the binlog file. It's created by the master and sent to newly connected replica before any "real" binlog event.

    INTVAR_EVENT

    This event records integer values for auto-increment columns or the LAST_INSERT_ID function, ensuring that these values are replicated deterministically.

    An INTVAR_EVENT is written every time a statement uses an auto increment column or the LAST_INSERT_ID() function.

    Header

    • Event Type is 5

    HEARTBEAT_LOG_EVENT

    A heartbeat event sent over the network by the master when there are no binlog events, ensuring the replica knows the connection is still active.

    This event does not appear in the . It's only sent over the network by a master to a replica server to let it know that the master is still alive, and is only sent when the master has no binlog events to send to replica servers.

    This event is never written to the binary log file.

    Header

    Fake ROTATE_EVENT

    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).

    The fake

    compressed body contains one or many standard packets but partial packets can also be sent:

    • one or many standard packets :

      • int<3> packet length

      • int<1> sequence number

      • packet body

    int<1>
    byte
    COM_PING
    OK_Packet
    max_allowed_packet
    handshake-response-packet
    int<3>
    int<1>
    int<3>
    byte
    COM_PING
    OK_Packet
    compress_packet

    string progress_info.

    string human-readable error message.

  • Else:

    • string human-readable error message.

  • int<2>
    error list
    int<1>
    int<1>
    int<3>
    string<1>
    string<5>

    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 , .

  • Authentication

    Client Clear Password Answer

    • string password without encryption.

    Public key request

    • byte<1> fixed 0x01 value.

    Public key response

    • byte<1> fixed 0x01 value.

    • byte public key data.

    RSA encrypted password

    • 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.

    Options

    Constant
    Value

    MYSQL_OPTION_MULTI_STATEMENTS_ON

    0

    MYSQL_OPTION_MULTI_STATEMENTS_OFF

    1

    Response

    EOF Packet on success or ERR packet.

    This page is licensed: CC BY-SA / Gnu FDL

    int<1>

    int<1> 0x18 COM_STMT_SEND_LONG_DATA header.

  • int<4> statement id.

  • int<2> parameter number.

  • byte data.

  • Response

    Server doesn't send response.

    This page is licensed: CC BY-SA / Gnu FDL

    packet splitting
    COM_STMT_EXECUTE

    uint<2> Flags.

  • uint<4> Slave server_id.

  • string The requested binlog file name.

  • Note

    • 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.

    Example of COM_BINLOG_DUMP

    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

    COM_REGISTER_SLAVE
    binlog events
    uint<1>
    uint<4>

    Event type is 161 (0xa1).

    Fields

    • uint<4> Log filename length.

    • string Log filename.

    Example Without CRC32

    Header, 19 Bytes

    • 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.

    Content, Variable Size

    • Filename length = 10 00 00 00 = >00 00 00 10 => 16.

    • Filename = mysql-bin.000062.

    This page is licensed: CC BY-SA / Gnu FDL

    Header
    • 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).

    Content

    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

    Format Description Event
    (
    0x05
    ).

    Fields

    • uint<1> Type.

    • uint<8> Value.

    Type

    0x00

    Invalid value.

    0x01

    LAST_INSERT_ID.

    0x02

    Insert id (auto_increment).

    Example From mysqlbinlog Utility, CRC32

    Example Event As It's Written In The Binlog File

    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).

  • Fields

    • string The current master binary log name.

    Example of Transmission (Without CRC32)

    Network Replication protocol, 5 bytes

    • packet size [3] = 23 00 00 => 00 00 23 => 35 (ok byte + event size).

    • pkt sequence [1] = 04.

    • OK indicator [1] = 0 (OK).

    Heartbeat Event

    Header

    • 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.

    Content, String

    • log-bin.1000139.

    This page is licensed: CC BY-SA / Gnu FDL

    binary log
    ROTATE_EVENT
    event is not written in the binlog file. It's created by the master and sent to newly connected replica before

    Header

    • Timestamp set to 0.

    • Event Tye is ROTATE_EVENT.

    • Next Pos is set to 0.

    • Flags are set to LOG_ARTIFICIAL_F (0x20).

    Content

    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

    ROTATE_EVENT
    FORMAT_DESCRIPTION_EVENT
    05 00 00 00 1A 04 00 00 00
    int<4>
    ERR_Packet
    OK_Packet
    string
    public key request
    public key response
    RSA encrypted password
    OK_Packet
    ERR_Packet
    int<1>
    string
    string
    result set rows
    EOF packet
    int<1>
    int<4>
    byte
    int<1>
    int
    string
    string
    string
    string

    COM_QUERY

    This 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.

    Fields

    • 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.

    Response

    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 ....

    Example

    This page is licensed: CC BY-SA / Gnu FDL

    COM_STMT_PREPARE

    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.

    Not all statements can be prepared. See PREPARE for a list of statements that can be prepared.

    Implemented by

    Fields

    • 0x16 COM_STMT_PREPARE header

    • SQL Statement

    Example

    Response

    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:

    COM_STMT_PREPARE_OK

    • 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

    1-Binlog Events

    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.

    Log File Structure

    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.

    Event Content

    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 first event, at 'position' 4, is a descriptor event that describes the format used to write events in the file.

    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).

    Example Binlog File (Hexdump -C $file_name)

    Example From

    This page is licensed: CC BY-SA / Gnu FDL

    3-Binlog Network Stream

    Describes the continuous packet stream format used to transmit binary log events from the primary server to the replica over the network.

    The binary log events stored in a binary log file can be sent over the network in order to replicate data changes from the master server (where data changes are written in binary logs) to replica servers which apply data changes to their own databases.

    The MariaDB replica replication protocol consists of:

    • A registration phase to master;

    • Events receiving (the master sending data when changes are available).

    This section is related to events sending only.

    Binlog Network streams are requested with COM_BINLOG_DUMP , and each Binlog Event is prepended with a status byte. The data sent over network is then MariaDB network protocol (4 bytes) + 1 byte status flag + event data.

    MariaDB 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.

    The remaining bytes of a large event transmission are always sent without a status flag and binlog event header, but rather just as network packet header + data.

    Example of an Event Transmission HEARTBEAT_LOG_EVENT

    Network Replication Protocol, 5 Bytes

    • 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

    COM_REGISTER_SLAVE

    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.

    Example of COM_REGISTER_SLA

    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

    ROTATE_EVENT

    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.

    Header

    • The Event Type is ROTATE_EVENT (0x4).

    Fields

    • 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.

    Example of Transmission With CRC32 (The Last 4 Bytes)

    This page is licensed: CC BY-SA / Gnu FDL

    XID_EVENT

    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.

    Header

    • Event Type is XID_EVENT (0x10).

    Fields

    The XID transaction number.

    Complete Example With CRC32

    Header, 19 Bytes

    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.

    Content, 8 Bytes

    • XID 66 00 00 00 00 00 00 00 => 102.

    CRC32, 4 Bytes

    • 09 30 45 a8 => a8 45 30 09 => 2823106569.

    This page is licensed: CC BY-SA / Gnu FDL

    XA_PREPARE_LOG_EVENT

    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.

    Header

    • Event Type is XA_PREPARE_LOG_EVENT (0x26).

    Fields

    • 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

    PACKET_BINDATA

    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.

    Field type
    Representation

    4-Semi-Sync Replication

    Explains the handshake and acknowledgement process for semi-synchronous replication, ensuring data is committed on at least one replica.

    is asynchronous. MariaDB also includes semi-synchronous Binlog Event.

    Event Header Changes

    If the user variable @rpl_semi_sync_slave is set, 2 extra bytes are added after the status byte of a and before the normal binlog event header.

    EXECUTE_LOAD_QUERY_EVENT

    This event is used for LOAD DATA INFILE operations, managing the execution phase similar to a QUERY_EVENT but with extra static fields for file handling.

    This event is written to the binary log file for events. The event format is similar to a , except that it has extra static fields.

    Header

    • Event Type = 0x12.

    START_ENCRYPTION_EVENT

    This event marks the beginning of encrypted data in the binary log, defining the encryption scheme and key version for subsequent events.

    The START_ENCRYPTION event is written to every file if is set to ON.

    This event is written just once, after the Format Description event (which is the first event of a binlog file at pos 4).

    The event has the 19 bytes event header with EventType set to value 164 (0xa4) + 17 bytes data.

    STOP_EVENT

    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 10
    ff ff ff 00 ...
    ff ff ff 01 ...
    02 00 80 02 ...
    01 00 00 00 00 00 00 01 00 00 00 10
    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.000034
    12 ad 26 5a a1 84 27 00  00 27 00 00 00 47 01 00  ..&Z..'..'...G..
    00 00 00 10 00 00 00 6d  79 73 71 6c 2d 62 69 6e  .......mysql-bin
    2e 30 30 30 30 36 32                              .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                                  1000139
    ee 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)mLB3

    3 - Binary Protocol (Prepared Statements)

    Understand the binary protocol for prepared statements. This section details how prepared statements are exchanged efficiently between client and server, optimizing performance and security.

    COM_CREATE_DB

    This command creates a new database on the server, functionally equivalent to the SQL statement CREATE DATABASE.

    This command is deprecated and not used by MariaDB connectors any more. Use the SQL statements CREATE SCHEMA or CREATE DATABASE instead.

    This page is licensed: CC BY-SA / Gnu FDL

    byte
    RSA encrypted password
    OK_Packet
    ERR_Packet
    ).
  • 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

    string
    OK Packet
    ERR_Packet
    OK_Packet
    LOCAL_INFILE Packet
    uint<1>
    uint<4>
    uint<4>
    uint<1>
    byte
    FORMAT_DESCRIPTION_EVENT
    ROTATE_EVENT
    STOP_EVENT
    mysqlbinlog
    network protocol
    uint<3>
    uint<1>
    uint<1>
    Heartbeat event
    password len [1] = 0 (not set).
  • slave port [2] = c9 5a => 23241.

  • rank [4] = 0.

  • master server id = 0.

  • uint<1>
    string
    uint<1>
    string
    uint<1>
    string
    uint<2>
    uint<4>
    uint<4>
    replication parameters
    uint<8>
    string
    Next Pos f2 0b 00 00 => 00 00 0b f2 => 3058.
  • Flags 00 00 = 0.

  • uint<8>

    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

    Fields

    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

    uint<1> semi-sync indicator, always 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.

    Semi Sync ACK Details

    This event is sent by the replica only if the semi-sync flag is set to 0x01.

    • uint<1> semi-sync indicator, always 0xef;

    • uint<8> the next position of received event;

    • string binlog file name.

    This packet sent by the replica never includes the CRC32.

    Sending an ACK when the semi-sync flag is set to 0x0 causes an error, and the connection is closed.

    Example of Heartbeat Event With Semi-Sync Protocol and CRC32

    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.

    Example of XID_EVENT, With CRC32

    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.

    Example of Semi-Sync ACK

    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

    Regular MariaDB replication
    semisynchronous replication
    binlog network stream
    Header
    • Event type is 164 (0xa4).

    Fields

    • uint<1> The Encryption scheme, always set to 1 for system files.

    • uint<4> The Encryption key version.

    • byte<12> Nonce (12 random bytes) of current binlog file.

    Decryption of Following Events

    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.

    Complete example with CRC32 from a binary log.

    Header, 19 Bytes

    • 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.

    Content, 17 Bytes

    • Enc scheme [1] = 01 => 1.

    • Enc key ver [4] = 01 00 00 00 => 00 00 00 01 => 1.

    • Nonce [12] = eWP&cY7F/;3#.

    CRC32, 4 bytes, of the Whole Event (Header[19] + Content[17])

    • 06 bb da 62 => 62 da bb 06 => 1658501894.

    This page is licensed: CC BY-SA / Gnu FDL

    binary log
    encrypt_binlog
    Header
    • Event header with EventType set to STOP_EVENT (0x03).

    • Event header NextPos set to EOF .

    • No special flags added.

    Fields

    • The event has no data.

    Example With CRC32 (Last 4 Bytes)

    Event size = header[19] + 0 bytes data + 4 CRC32 = 23.

    This page is licensed: CC BY-SA / Gnu FDL

    binary log
    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                                  1000139
    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: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=1
    T 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.000034
    b8 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#...b
    3a b8 15 5a 03 01 00 00  00 17 00 00 00 09 0c 00  ..Z............
    00 00 00 4e 99 ee 2c                              ...N..,
    column definition packet.
  • If !DEPRECATE_EOF eof_packet.

  • If prepared statement returns result set and number of result set columns > 0:

    • For each column:

      • column definition packet.

    • If !DEPRECATE_EOF .

  • int<2> number of prepared statement parameters ('?' placeholders).
  • string<1> -not used-

  • int<2> number of warnings.

  • int<1>
    string
    ERR_Packet
    COM_STMT_PREPARE_OK
    int<1>
    int<4>
    int<2>
    Fields

    Fixed Data Part

    • uint<4> The ID of the thread that issued this statement on the master.

    • uint<4> The time in seconds that the statement took to execute.

    • uint<1> The length of the name of the database which was the default database when the statement was executed. This name appears later, in the variable data part. It is necessary for statements such as INSERT INTO t VALUES(1) that don't specify the database and rely on the default database previously selected by USE.

    • The error code resulting from execution of the statement on the master.

    • The length of the status variable block.

    • 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).

    Variable Data Part

    • byte Zero or more status variables. Each status variable consists of one byte code identifying the variable stored, followed by the value of the variable. The format of the value is variable-specific. The number of bytes 'n' is the length of the status variable block (read in fixed data part)

    • string The default database name (null-terminated).

    • string The SQL statement. By subtraction the size of the statement can be known.

    Example

    This page is licensed: CC BY-SA / Gnu FDL

    LOAD DATA INFILE
    QUERY_EVENT

    COM_STMT_BULK_EXECUTE

    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).

    Direction

    Client to server.

    Fields

    • 0xfa : COM_STMT_BULK_EXECUTE header.

    • statement id.

    • .

    Flags

    Bulk Flags

    Flag
    Value
    Details

    Parameter Type Flag

    Parameter Indicator

    Response

    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

    FORMAT_DESCRIPTION_EVENT

    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.

    Header

    • The Event Type is 15 (0x0f).

    Fields

    • 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)

    Example FDE of MariaDB With CRC32

    Header, 19 Bytes

    • timestamp => 4d af 15 5a.

    • type = 0f => 15.

    • server_id = 1.

    • Event Size = fc => 252.

    Content, Variable Size Depending on MariaDB Versions

    • 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.

    Example FDE of MariaDB With CRC32

    Header, 19 Bytes

    • timestamp => 12 ad 26 5aa.

    • type = 0f => 15.

    • server_id = 84 27 00 00 => 00 00 27 84 => 10116.

    • Event Size = fc => 245.

    Content, Variable Size Depending on MariaDB Versions

    • 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

    RAND_EVENT

    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.

    It is written only before a QUERY_EVENT , and not used with row-based logging.

    Header

    • Event Type is 13 (0x0d).

    Fields

    • The value for the first seed.

    • The value for the second seed.

    Example From mysqlbinlog Utility, No CRC32

    Example Event as Written to the Binlog File

    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

    MariaDB Protocol Differences with MySQL

    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 Capabilities Extension

    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).

    COM_STMT_EXECUTE

    This command executes a prepared statement using parameter values provided in the binary protocol format.

    Executes a previously prepared statement.

    If specific data is large, it can be sent separately prior to this command (see ).

    If a statement is re-executed without changing the bind types, the types do not need to be sent to the server again.

    Direction

    Client to server.

    COM_RESET_CONNECTION

    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                                         ult
    uint<2>
    uint<2>
    uint<4>
    uint<4>
    uint<4>
    uint<1>
    If (
    bulk_flag
    &
    SEND_TYPES_TO_SERVER
    ):
    • For 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

    int<1>
    int<4>
    int<2>
    bulk flags
    ERR_Packet
    OK_packet
    Rset session variables.
  • Delete user variables.

  • Remove temporary tables.

  • Remove all PREPARE statements.

  • Database will NOT be reset to initial value.

    Fields

    • int<1> 0x1f : COM_RESET_CONNECTION Header

    Response

    ERR_Packet or OK_Packet

    This page is licensed: CC BY-SA / Gnu FDL

    uint<1> The header length. This length -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:

  • Next Pos = 00 01 00 00 => 00 00 01 00 => 256.
  • Flags = 00 => 0.

  • event_types array[252 - 19 - (2 + 50 + 4 +1) - 1 - 4] = 171 supported events.
  • checksum_algo = 01 => 1 (CRC32).

  • CRC32 bytes = d6 ce 13 e2.

  • Next Pos = f9 00 00 00 => 00 00 00 f9 => 249.
  • Flags = 00 => 0.

  • event_types array[245 - 19 - (2 + 50 + 4 +1) - 1 - 4] = 164 supported events.
  • checksum_algo = 0 => 0 (NONE).

  • CRC32 bytes = 2b 91 c2 91 (useless).

  • uint<2>
    string<50>
    uint<4>
    uint<1>
    uint<4>
    uint<8>
    uint<8>
    n = event_size - header length - offset (2 + 50 + 4 + 1) - checksum_algo - checksum
    4d 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 424
    Enhanced Capabilities
    • MARIADB_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 ).

    See Connection Capabilities.

    Prepare Statement Skipping Metadata

    This feature is available from MariaDB 10.6.0.

    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:

    Extended Column Information

    This feature is available from MariaDB 10.5.2.

    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'.

    Bulk

    This feature is available for unit results from or , respectively.

    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:

    Authentication Plugins

    MariaDB has specific authentication methods.

    • ED25519 plugin

    • PARSEC plugin (from MariaDB 11.6.1)

    • GSSAPI plugin

    Redirection

    This feature is available from MariaDB 11.3.1 or MaxScale 25.08.0, respectively.

    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."

    Zero-Configuration SSL

    This feature is available from MariaDB 11.4.1.

    A feature that enables TLS certificate validation without requiring client-side certificate configuration.

    Limitations

    • Requires a nonempty password.

    • Only supports the following authentication methods:

      • mysql_native_password

      • client_ed25519

      • parsec

    Operational Mechanism

    Server-Side Process

    1. When no SSL certificates are pre-configured, the server automatically generates a temporary self-signed certificate.

    2. During connection establishment, the server embeds a special validation hash in the connection's "OK_Packet" information field.

    Client-Side Process

    1. The client connector must postpone SSL error handling until the connection phase is complete.

    2. The client captures and stores the SHA256 fingerprint of the server's certificate.

    3. If SSL errors occur, the client can only use specific authentication plugins (mysql_native_password/ed25519/parsec) to prevent potential password exposure.

    4. At connection conclusion, the server sends an OK_Packet with a validation hash.

    5. 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.

    Password Hash Generation Methods

    • 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;

    Initial Session Tracking

    MySQL and MariaDB support session tracking when the CLIENT_SESSION_TRACK capability is set.

    One difference is that, since MariaDB 11.5.1, connection ending OK_Packet lists all the current variables of tracked variable.

    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".

    MySQL Features Not Supported

    • 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.

    TIPS

    Identifying MariaDB Server

    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.

    Pipelining Prepare Execute

    Connectors usually follow a two-step process for prepared statements:

    1. Prepare: Send a COM_STMT_PREPARE command to the server, receiving a statement ID in response.

    2. 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.

    Query Timeout

    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 ...

    Collations

    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

    Initial handshake packet
    Fields
    • int<1> 0x17 : COM_STMT_EXECUTE header.

    • int<4> statement ID.

    • int<1> flags.

    • Iteration count (always 1).

    • If (param_count > 0)

      • byte<(param_count + 7)/8> .

      • byte<1>: send type to server (0 / 1).

      • If (send type to server):

    Statement ID

    Statement ID is the identifier of the prepared statement (from COM_STMT_PREPARE answer).

    Specific "-1" statement id value

    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.

    Flag

    0

    no cursor

    1

    read only

    2

    cursor for update

    4

    scrollable cursor

    Cursors

    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 Flag

    Parameter type flag byte:

    128

    unsigned

    NULL-Bitmap

    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

    Binary Parameter Encoding

    The encoding of the COM_STMT_EXECUTE parameters are the same as the encoding of the binary result sets.

    Response

    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

    COM_STMT_SEND_LONG_DATA
    eof_packet

    USER_VAR_EVENT

    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.

    Header

    • Event Type is 14 (0x0e).

    Fields

    • The length of the user variable name.

    • The name of the user variable.

    • NULL indicator.

    • If (not null indicator):

    Variable type

    Value
    Type
    Example

    Flag

    Example for SET @foo:="bar" From mysqlbinlog Utility, CRC32

    Example Event as Written to the Binlog File

    This page is licensed: CC BY-SA / Gnu FDL

    COM_SHUTDOWN

    This command requests the server to shut down, it requires the SHUTDOWN privilege to be executed successfully.

    Shuts down the server. To execute this command, the SHUTDOWN privilege is required.

    Fields

    • int<1> 0x0A COM_SHUTDOWN

    • option

    Options

    Response

    or .

    This page is licensed: CC BY-SA / Gnu FDL

    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.

  • int<4>
    null bitmap

    Constant

    Value

    SHUTDOWN_DEFAULT

    0

    int<1>
    OK Packet
    ERR packet

    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

    uint<4>
    string
    uint<1>
    # 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.

  • COM_CHANGE_USER

    This command allows a connected client to re-authenticate as a different user without closing and reopening the connection.

    COM_CHANGE_USER resets the connection and re-authenticates with the given credentials. The packet is identical to the authentication packet in the connection handshake.

    Fields

    • 0x11 :

    COM_CHANGE_USER
    header.
  • string 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.

  • Response

    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

    int<1>

    OK_Packet

    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.

    Direction

    Server to client.

    Fields

    • 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.

    Server status flag

    Values of server status flags:

    Flag
    Value
    Details

    Session state info

    • While packet has remaining data:

      • .

      • If (session-change-type != SESSION_TRACK_STATE_CHANGE):

    Session change type

    Change Type
    Flag

    Session Data Change

    Each type of data has its own kind of format:

    SESSION_TRACK_SCHEMA

    • new current schema.

    SESSION_TRACK_SYSTEM_VARIABLES

    While there is remaining data:

    • variable data.

    For each variable data:

    • variable name.

    • variable value.

    Possible tracked variables list is tracked by special variable value description:

    • : format is mariadb/mysql:[<user>[:<password>]@]<host>[:<port>]/[<db>[?<opt1>=<value1>[&<opt2>=<value2>]]]. Possible options:

      • ttl

    SESSION_TRACK_STATE_CHANGE

    Indicates if session state changes occurred. The value is represented as 1.

    • 1 if session state tracking was enabled.

    SESSION_TRACK_GTIDS

    This tracker is not implemented by MariaDB.

    SESSION_TRACK_TRANSACTION_CHARACTERISTICS

    • 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:

    SESSION_TRACK_TRANSACTION_STATE

    • Transaction state string.

    The transaction state string is always 8 characters long. The characters, in order, are:

    1. No transaction: _ Explicit transaction: T Implicit transaction: I

    2. Transaction read safe: _ Transaction read unsafe: r

    3. Unknown transaction type: _

    Notes

    • 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

    2-Binlog Event Header

    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.

    Event Header Structure, 19 Bytes

    • Timestamp (creation time).

    • (type_code).

    • Server_id (server which created the event).

    • Event Length (header + data).

    Note: if CRC32 is in use, the Event Length is 4 bytes bigger in size. The 4 bytes CRC32 are written at the end of the event (just after the last 'data' byte).

    Encrypted Binlog Events

    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.

    Event Type

    Hex
    Event type description

    Fake Events

    These are generated on the fly, never written:

    Hex
    Event type description

    Event Flag

    Hex
    Event flag description

    Event Header Example of FORMAT_DESCRIPTION_EVENT

    This is the first event in the binlog file at pos 4:

    Interpretation of First 19 Bytes of the Event (the Event Header)

    • 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

    TABLE_MAP_EVENT

    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.

    Header

    • Event Type is 19 (0x13).

    Fields

    Fixed Data Part

    • The table ID.

    • Reserved for future use.

    Variable Data Part

    • Database name length.

    • The database name (null-terminated).

    • Table name length.

    • The table name (null-terminated).

    Metadata Block

    The metadata block contains type specific metadata information for each column.

    Type
    Length
    Description

    Optional Metadata Block

    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:

    Name
    Value
    Mode
    Description

    Example From mysqlbinlog

    Complete Event

    This page is licensed: CC BY-SA / Gnu FDL

    GTID_LIST_EVENT

    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 .

    In case of encrypted binlog files ( is set to ON), this event is written just after the .

    Header

    • Event type is 163 (0xa3).

    Fields

    • 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 .

    Example With 1 GTID, With CRC32

    From the utility:

    170824 9:52:04 server id 10124 end_log_pos 292 CRC32 0xb6d8f0a8 Gtid list [0-10124-3584]

    Header, 19 Bytes

    • 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].

    Content, Variable Size, is (4 + (4 + 4 + 8 ) * n_GTIDs) Bytes

    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

    GTID_EVENT

    The GTID_EVENT marks the start of a new transaction event group, associating it with a Global Transaction ID (GTID) and providing commit flags.

    For , used to start a new transaction event group, instead of the old BEGIN query event, and also to mark stand-alone (DDL).

    GTID_EVENT event type is 162 (0xa2).

    Event Header

    string
    string
    Authentication switch request
    Plugin
    OK_Packet
    ERR_Packet

    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

    uint<6>
    uint<2>
    uint<1>
    string
    uint<1>
    string
    byte<1>
    int
    byte

    Character set of columns, used if columns have different character sets. Returned as a sequence of collation numbers.

    uint<8> GTID sequence.

    Next Pos = 24 01 00 00 => 00 00 01 24 => 292.
  • Flags = 00 => 0.

  • crc32[4] = a8 f0 d8 b6 => b6 d8 f0 a8 => 0xb6d8f0a8.

    Format Description Event
    Start Encryption Event
    encrypt_binlog
    START_ENCRYPTION_EVENT
    uint<4>
    uint<4>
    uint<4>
    mysqlbinlog
    # at 847
    #171206 13:43:00 server id 10124  end_log_pos 892 CRC32 0xbe3c6b05 	Table_map: `test`.`t4` mapped to number 33
    # at 892
    d4 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> server status.

  • 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.

    int total length.
  • string session data change.

  • : cache timeout in ms to remember redirection, in order to reconnect directly to new host. 0=no caching
    Read-only transaction:
    R
  • Transaction 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

    int<1>
    int
    int
    SSL certificate verification signature
    int<1>
    session change type
    string
    string
    string
    string
    session_track_system_variables
    redirect_url
    string
    string
    string
    CLIENT_SESSION_TRACK

    uint<4> Next Event position.

  • uint<2> Event flags.

  • Copy the original event length back to its 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

    QUERY_EVENT

    0x03

    STOP_EVENT

    0x04

    ROTATE_EVENT

    0x10

    XID_EVENT

    0x0d

    RAND_EVENT

    0x0e

    USER_VAR_EVENT

    0x04

    FAKE_ROTATE_EVENT

    0xa3

    FAKE_GTID_LIST_EVENT

    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.

    uint<4>
    uint<1>
    Event Type
    uint<4>
    uint<4>
    residual block termination

    Type[1] = 0xa2.

  • Flags[2] = 08 00 => LOG_EVENT_SUPPRESS_USE_F.

  • Fields

    • uint<8> GTID sequence.

    • uint<4> Replication Domain ID.

    • uint<1> Flags.

    If flag & FL_GROUP_COMMIT_ID:

    • uint<8> commit_id.

    Else if flag & (FL_PREPARED_XA or FL_COMPLETED_XA):

    • uint<4> format_id.

    • uint<1> gtid_length.

    • uint<1> bqual_length.

    • byte xid, where n is sum of gtrid and bqual lengths.

    Else:

    • uint<6> 0.

    Flags

    Flag
    Value
    Details

    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.

    Transaction Example From mysqlbinlog Utility

    Standalone Event DDL (FLUSH TABLES) From mysqlbinlog Utility

    Example GTID_EVENT with DDL and CRC32

    Content

    • 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.

    Example GTID_EVENT With a Transaction and CRC32

    Content

    • 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

    global transaction ID

    5-Replica Registration

    Details the initialization phase where a replica connects to the primary, authenticates, sends capabilities, and registers for updates.

    Overview

    The replica server, when properly configured with CHANGE MASTER TO ... , can start MariaDB replication with the statement.

    After authentication, some packets are exchanged before sending and .

    The following COM_QUERY packets come from MariaDB 10.X replicas using :

    Result Set Row

    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.

    A result set row represents a database result set unit, which is usually generated by executing a statement that queries the database. Using , the result set row is in binary format, otherswise in text format.

    Text Result Set Row

    • For each column:

    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).

    string
    session state info
    long_query_time
    session change type
    FORMAT_DESCRIPTION_EVENT
    TABLE_MAP_EVENT
    HEARTBEAT_LOG_EVENT
    ANNOTATE_ROWS_EVENT
    BINLOG_CHECKPOINT_EVENT
    GTID_EVENT
    GTID_LIST_EVENT
    START_ENCRYPTION_EVENT
    QUERY_COMPRESSED_EVENT
    WRITE_ROWS_COMPRESSED_V1
    UPDATE_ROWS_COMPRESSED_V1
    DELETE_ROWS_COMPRESSED_V1
    WRITE_ROWS_V1
    UPDATE_ROWS_V1
    DELETE_ROWS_V1
  • 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.

    If semi-sync is in use, the request for the network protocol change is sent between COM_REGISTER_SLAVE and COM_BINLOG_DUMP.

    Example Using 'ngrep'

    COM_REGISTER_SLAVE, Semi-Sync and COM_BINLOG_DUMP:

    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.

    Complete Example with GTID Registration

    The example shows output up to COM_BINLOG_DUMP request, No Semi-Sync:

    Events Transmission After COM_BINLOG_DUMP

    The MariaDB master always sends this, after the COM_BINLOG_DUMP:

    • FAKE_ROTATE_EVENT;

    • 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.

    Complete Example of Event Transmission With CRC32

    We can see:

    1. FAKE_ROTATE_EVENT packet: 30 00 00 01 ... d5 3f ea d7

    2. 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)

    3. FAKE_GTID_LIST_EVENT packet : 3c 00 00 03 00 ... b6 33 8a 22

    4. BINLOG_CHECKPOINT EVENT packet: 2c 00 00 04 ... 16 1f fe 3f

    5. GTID_LIST_EVENT packet: 2c 00 00 05 ... 4a 01 94 22

    6. GTID_EVENT packet: 2b 00 00 06 ... 22 87 c0 61

    7. QUERY_EVENT packet: 4c 00 00 07 ... 6e c8 89 60

    This page is licensed: CC BY-SA / Gnu FDL

    START REPLICA
    COM_QUERY
    COM_REGISTER_SLAVE
    COM_BINLOG_DUMP
    GTID
    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.000034
    T 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.000034
    T 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.

    Binary Result Set Row

    • 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 :

    NULL-Bitmap Values

    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 Binary Encoding

    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 Binary Encoding

    DOUBLE is the IEEE 754 floating-point value in Little-endian format on 8 bytes.

    BIGINT Binary Encoding

    BIGINT is the value in Little-endian format on 8 bytes. Signed is defined by the Column field detail flag.

    INTEGER Binary Encoding

    INTEGER is the value in Little-endian format on 4 bytes. Signed is defined by the Column field detail flag.

    MEDIUMINT Binary Encoding

    MEDIUMINT is similar to INTEGER binary encoding, even if MEDIUM int is 3-bytes encoded server side. (Last byte will always be 0x00).

    FLOAT Binary Encoding

    FLOAT is the IEEE 754 floating-point value in Little-endian format on 4 bytes.

    SMALLINT Binary Encoding

    SMALLINT is the value in Little-endian format on 2 bytes. Signed is defined by the Column field detail flag.

    YEAR Binary Encoding

    YEAR uses the same format as SMALLINT.

    TINYINT Binary Encoding

    TINYINT is the value of 1 byte. Signed is defined by the Column field detail flag.

    DATE Binary Encoding

    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.

    TIMESTAMP Binary Encoding

    Data is encoded in 8 bytes without fractional seconds, 12 bytes with fractional seconds.

    Byte Position
    Description

    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)

    TIME Binary Encoding

    Data is encoded in 9 bytes without fractional seconds, 13 bytes with fractional seconds.

    • int<1> data length: 0 for special '00:00:00' value, 8 without fractional seconds, 12 with fractional seconds.

    • If data length > 0:

      • int<1> 0 for positive time, 1 for negative time.

      • days.

      • hours.

      • minutes.

      • seconds.

      • if data length > 8 :

        • microseconds.

    This page is licensed: CC BY-SA / Gnu FDL

    COM_STMT_EXECUTE

    QUERY_EVENT

    The QUERY_EVENT records text-based SQL statements for statement-based replication, capturing the query string and execution context like the default database.

    This event is written into the binary log file for:

    • Statement-based replication (updating statements).

    • DDLs.

    • COMMIT related to nontransactional engines (like MyISAM or BLACKHOLE).

    Header

    • Event Type = 0x02.

    Fields

    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.

    Status variables

    Q_FLAGS2_CODE (0x00)

    • bitmask.

    Value
    Option

    Q_SQL_MODE_CODE (0x01)

    • 8-byte bitmask.

    Value
    Option

    Q_CATALOG_NZ_CODE (0x02)

    • length

    • catalog name + '\0'

    Q_AUTO_INCREMENT (0x03)

    • auto_increment increment

    • auto_increment offset

    Q_CHARSET_CODE (0x04)

    • client character set

    • collation_connection

    • collation_server

    Q_TIMEZONE_CODE (0x05)

    • length

    • time zone

    Q_CATALOG_NZ_CODE (0x06)

    • length

    • catalog

    Q_LC_TIME_NAMES_CODE (0x07)

    • code The mapping between code and names are defined in sql_locale.cc.

    Q_CHARSET_DATABASE_CODE (0x08)

    • database collation

    Q_TABLE_MAP_FOR_UPDATE_CODE (0x09)

    • 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.

    Q_MASTER_DATA_WRITTEN_CODE (0x0A)

    • Not in use any more

    • original event length

    Q_INVOKER (0x0B|)

    • user name length

    • user name

    • host name length

    • host name

    Q_UPDATED_DB_NAMES (0x0C)

    • MySQL only

    • count

    • for (i=0;i < count; i++)

      • Null terminated database name

    Q_MICROSECONDS (0x0D)

    • MySQL only

    • microsecond part

    Q_HRNOW (0x80)

    • MariaDB only

    • microsecond part

    Q_XID (0x81)

    • MariaDB only

    • xid

    Example With CRC32

    Header, 19 Bytes

    • 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.

    Content, Variable Data

    • 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).

    CRC32, 4 Bytes

    • 4a 69 9e ed.

    Example With Default Database and CRC32

    Content, Variable Data

    • 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

    ROWS_EVENT_V1/V2, ROWS_COMPRESSED_EVENT_V1

    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.

    Event types

    Event
    Details

    Header

    • 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).

    Fields

    • The table id.

    • Flags.

    • If rows_event is version 2:

      • Extra data length.

    Table id

    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.

    Flags

    Flag
    Details

    Extra data length (version 2)

    The length of extra data.

    Extra data (version 2)

    Extra data, length is extra data length -2.

    Column Data Formats

    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.

    Simple Types

    Type
    Details

    MYSQL_TYPE_BLOB and other blob types

    Stored as a length-encoded string where the string is preceded by a variable-sized integer that stores the length of the blob. The size of the preceding integer in bytes is stored as a one byte integer in the table metadata that is a part of the table map event.

    For example, if the value 4 is stored in the table metadata, the length is stored as a 4 byte integer (for instance, uint32_t) followed by the data.

    The exact column_type can be determined by the metadata length:

    Length
    Type

    MYSQL_TYPE_STRING, MYSQL_TYPE_SET and MYSQL_TYPE_ENUM

    Stored as a fixed-length string with the length of the string stored in the second byte of the table metadata. All three of these types are stored as MYSQL_TYPE_STRING in the binlog and the real type of the field is stored in the first byte of the metadata.

    MYSQL_TYPE_VARCHAR and other variable length string types

    Stored as a length-encoded string where the string is preceded by a variable-sized integer that stores the length of the string. The field length is stored as a two byte integer in the table metadata.

    If the field length is larger than 255, the string length is stored as a two byte integer. If the value is equal to or less than 255, the string length is stored as a one byte integer.

    MYSQL_TYPE_DATETIME

    This field type is only used in MariaDB if the global variable mysql56_temporal_format is set to OFF. Stored as an 8 byte value with the values stored as multiples of 100. This means that the stored value is in the format YYYYMMDDHHMMSS , and can be extracted by repeatedly calculating the remainder of dividing the value by 100 and dividing the value by 100. The following pseudo-code demonstrates extracting the value.

    MYSQL_TYPE_TIME

    This field type is only used in MariaDB if the global variable mysql56_temporal_format is set to OFF. Stored as a 3 byte value with the values stored as multiples of 100. This means that the stored value is in the format HHMMSS and can be extracted in the same way a MYSQL_TYPE_DATETIME is extracted. The following pseudo-code demonstrates extracting the value.

    MYSQL_TYPE_DATETIME2

    Stored as 4-byte value The number of decimals for the fractional part is stored in the table metadata as a one byte value. The number of bytes that follow the 5 byte datetime value can be calculated with the following formula: (decimals + 1) / 2 .

    MYSQL_TYPE_TIME2

    Stored as 3-byte value. The number of decimals for the fractional part is stored in the table metadata as a one byte value. The number of bytes that follow the 3 byte time value can be calculated with the following formula: (decimals + 1) / 2 .

    MYSQL_TYPE_TIMESTAMP2

    Stored as a 4 byte UNIX timestamp (number of seconds since 00:00, Jan 1 1970 UTC) followed by the fractional second parts. The number of decimals for the fractional part is stored in the table metadata as a one byte value. The number of bytes that follow the 4 byte timestamp can be calculated with the following formula: (decimals + 1) / 2

    Microseconds for MYSQL_TYPE_DATETIME2, MYSQL_TYPE_TIME2 , and MYSQL_TYPE_TIMESTAMP .

    MYSQL_TYPE_DATE

    Stored as a 3 byte value where bits 1 to 5 store the day, bits 6 to 9 store the month and the remaining bits store the year.

    MYSQL_TYPE_TIMESTAMP

    Stored as a 4 byte UNIX timestamp (number of seconds since 00:00, Jan 1 1970 UTC).

    Example From mysqlbinlog Utility, CRC32

    Example Event as Written to the Binlog File

    This page is licensed: CC BY-SA / Gnu FDL

    Result Set Packets

    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 0
    If MYSQL_TYPE_MEDIUMINT type : MEDIUMINT Binary encoding
  • If 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)

    DOUBLE Binary encoding
    BIGINT Binary encoding
    INTEGER Binary encoding
    int<4>
    int<1>
    int<1>
    int<1>
    int<4>

    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

    Next Pos = 01 09 00 00 ===> 2305.
  • Flags = 00 00 => 0.

  • Status variable block len[2] = 1a 00 => 26.
  • Status variables[n] = 00 ... 08 00.

  • The default database[string] = 00 = 0 (no default database).

  • The SQL statement[string] = TRUNCATE TABLE test.t4.

  • 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

    uint<4>
    uint<4>
    uint<1>
    byte
    string
    string
    uint<4>
    uint<8>
    uint<1>
    string<length +1>
    uint<2>
    uint<2>
    uint<2>
    uint<2>
    uint<2>
    uint<1>
    string
    uint<1>
    string
    uint<2>
    uint<2>
    uint<8>
    uint<4>
    uint<1>
    string
    uint<1>
    string
    uint<1>
    string
    uint<3>
    uint<3>
    uint<8>

    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.

  • If (event_type == UPDATE_ROWS_EVENT_v1 ):

    • byte Null Bitmap_Update. n = (number_of_columns + 7)/8.

    • string Update Column data. The length needs to be calculated by checking the used colums bitmap and 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

    uint<6>
    uint<2>
    uint<2>
    TABLE_MAP_EVENT
    TABLE_MAP_EVENT

    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 % 100
    time_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                                   ....
    If not (MARIADB_CLIENT_CACHE_METADATA capability set) OR (send metadata == 1):
    • For each column (for instance, column_count times):

      • Column Definition packet.

  • If not (CLIENT_DEPRECATE_EOF capability set) EOF_Packet.

  • n result set row.

  • If error:

    • ERR_Packet.

  • 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.

    Column Count Packet

    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.

    Column Definition Packet

    A column definition packet describes a column in the result set. It uses the following format:

    • string catalog (always 'def').

    • string schema.

    • string table alias.

    • string table.

    • column alias.

    • column.

    • If extended type supported (see MARIADB_CLIENT_EXTENDED_METADATA ):

      • .

    • length of fixed fields (=0xC).

    • character set number.

    • max. column size.

    • .

    • .

    • decimals.

    • - unused -

    Field types

    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.

    Value
    Protocol Column Type
    Encoding

    0

    MYSQL_TYPE_DECIMAL

    1

    MYSQL_TYPE_TINY

    2

    MYSQL_TYPE_SHORT

    3

    MYSQL_TYPE_LONG

    Field Details Flag

    The column details flag describes certain column attributes and whether certain column options are set.

    It is a bitmask with the following flags:

    Flag Value
    Flag Name
    Flag Description

    1

    NOT_NULL

    field cannot be null

    2

    PRIMARY_KEY

    field is a primary key

    4

    UNIQUE_KEY

    field is unique

    8

    MULTIPLE_KEY

    The BLOB flag cannot be used to determine if a column has binary data, because BINARY and VARBINARY columns are treated as strings, instead of blobs.

    The BINARY_COLLATION flag can be used to determine if a string column has binary data.

    Extended Metadata

    This extended column type information can be used to find out more specific details about the column type:

    • For a POINT column, the column type field is MYSQL_TYPE_GEOMETRY, but the extended type indicates 'point'.

    • For a JSON column, the column type field is MYSQL_TYPE_STRING, but the extended type indicates 'json'.

    • While string has data:

      • data type: 0x00:type, 0x01: format.

      • value.

    This page is licensed: CC BY-SA / Gnu FDL

    Result set metadata
    column count packet

    INTEGER Binary encoding

    4

    MYSQL_TYPE_FLOAT

    FLOAT Binary encoding

    5

    MYSQL_TYPE_DOUBLE

    DOUBLE Binary encoding

    6

    MYSQL_TYPE_NULL

    Not used, nullness is indicated by the NULL-bitmap in the result

    7

    MYSQL_TYPE_TIMESTAMP

    TIMESTAMP Binary encoding

    8

    MYSQL_TYPE_LONGLONG

    BIGINT Binary encoding

    9

    MYSQL_TYPE_INT24

    INTEGER Binary encoding

    10

    MYSQL_TYPE_DATE

    TIMESTAMP Binary encoding

    11

    MYSQL_TYPE_TIME

    TIME Binary encoding

    12

    MYSQL_TYPE_DATETIME

    TIMESTAMP Binary encoding

    13

    MYSQL_TYPE_YEAR

    SMALLINT Binary encoding

    14

    MYSQL_TYPE_NEWDATE

    byte encoding

    15

    MYSQL_TYPE_VARCHAR

    byte encoding

    16

    MYSQL_TYPE_BIT

    byte encoding

    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

    byte encoding

    247

    MYSQL_TYPE_ENUM

    byte encoding

    248

    MYSQL_TYPE_SET

    byte encoding

    249

    MYSQL_TYPE_TINY_BLOB

    byte encoding

    250

    MYSQL_TYPE_MEDIUM_BLOB

    byte encoding

    251

    MYSQL_TYPE_LONG_BLOB

    byte encoding

    252

    MYSQL_TYPE_BLOB

    byte encoding

    253

    MYSQL_TYPE_VAR_STRING

    byte encoding

    254

    MYSQL_TYPE_STRING

    byte encoding

    255

    MYSQL_TYPE_GEOMETRY

    byte encoding

    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

    EOF_Packet
    string
    string
    string
    extended metadata
    int
    int<2>
    int<4>
    int<1>
    Field types
    int<2>
    Field detail flag
    int<1>
    int<2>
    int<1>
    string
    byte encoding
    TINYINT Binary encoding
    SMALLINT Binary encoding

    Connecting

    The connection phase involves an initial handshake where the client and server exchange capabilities, default settings, and authentication data to establish a session.

    Overview

    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:

    Initial Handshake Packet

    • 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.

    Client Handshake Response

    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.

    SSLRequest Packet

    • client capabilities.

    • max packet size.

    • client's default character set and collation.

    • reserved.

    Zero-Configuration SSL Encryption

    Automatic Encrypted Connections (+):

    Previously, failed SSL connections due to self-signed certificates prevented communication. + introduces a secondary validation method that works for all servers.

    What Happens When SSL Validation Fails?

    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.

    Verifying a Secure Connection:

    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.

    Shared Secret for Secure Connection:

    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.

    Server 11.4+ Confirmation Details:

    For servers running or later, the final confirmation packet contains:

    • encryption (actually only 0x01 = SHA256 encryption)

    • shared secret.

    Matching the 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.

    Handshake Response Packet

    • client capabilities.

    • max packet size.

    • client's default character set and collation.

    • reserved.

    Server Response to Handshake Response Packet

    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.

    Authentication Switch Request

    (If client and server support CLIENT_AUTH capability):

    • 0xFE : Authentication switch request header.

    • authentication plugin name.

    • authentication plugin data.

    Plugin List

    mysql_old_password Plugin

    deprecated — send an 8 byte encrypted password.

    Authentication plugin data format:

    • 8-byte seed.

    Client response:

    • old format encrypted password.

    mysql_clear_password Plugin

    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.

    mysql_native_password Plugin

    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 ) ) ) .

    dialog Plugin (PAM)

    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 .

    auth_gssapi_client Plugin

    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.

    client_ed25519 Plugin

    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.

    parsec Plugin

    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.

    Capabilities

    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).

    Capability
    Value
    Details

    Native Password Authentication

    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

    mysql_stmt_close()
    mysql_stmt_prepare()
    mariadb_stmt_execute_direct()
    mysql_stmt_prepare()

    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 & CLIENT_SECURE_CONNECTION):

    • string authentication plugin data 2nd part. Length = max(12, plugin data length - 9).

    • string<1> reserved byte.

  • 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

    ERR_Packet
    Initial handshake packet
    SSLRequest packet
    int<1>
    string
    int<4>
    int<4>
    int<1>
    string<19>
    int<1>
    byte
    int<4>
    int<4>
    int<1>
    string<19>
    OK_packet
    ERR_packet
    int<1>
    string
    byte
    byte<8>
    string
    string
    string
    byte
    byte<1>
    string
    string
    EOF_Packet
    OK_Packet
    ERROR_packet
    string
    string
    GSSAPI
    plugin description
    byte
    byte
    string<32>
    string<1>
    byte<1>
    byte
    byte<32>
    byte<64>
    Initial handshake packet
    SHA1( passwd) ^ SHA1( seed + SHA1( SHA1( passwd ) ) )
    Client sends 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.

  • int<1>
    string
    string
    string
    string
    LOAD DATA LOCAL
    "Progress reporting"
    MariaDB 10.5.2
    MariaDB 10.2.0
    MariaDB 11.5.1
    MariaDB 11.5.1
    MariaDB 11.0
    MariaDB 11.4
    MariaDB 11.4
    MariaDB 11.4
    MariaDB 10.2