Client/Server Protocol Overview

Client/Server Protocol Documentation

Replication Protocol Documentation

List of Possible Types

Unknown type:

Integer type:

String type:

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 :

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

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 Initial handshake packet (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).

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

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

See Connection Capabilities.

Prepare Statement Skipping Metadata

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:

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();
}

Results with metadata caching:

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

The same, without metadata caching:

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

Extended Column Information

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

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:

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();
}

Client send :

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

Server response:

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

Authentication Plugins

MariaDB has specific authentication methods.

• ED25519 plugin

• PARSEC plugin (from MariaDB 11.6.1)

• GSSAPI plugin

Redirection

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

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.

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;

    • Salt;

    • Raw public key.

Initial Session Tracking

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

This is useful for connectors which have a method to set the transaction type, retrieving database for example to always have the server current value when changed. This permit to avoid executing some queries when not needed

Example of ending connection OK_Packet :

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

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}

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;

• int<1> sequence number;

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

byte[0] + (byte[1]<<8) + (byte[2]<<16)

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 COM_PING packet COM_PING body has only one byte (0x10):

01 00 00 00 10

The server will then return an OK_Packet 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:

ff ff ff 00 ...

Second packet:

ff ff ff 01 ...

Third packet:

02 00 80 02 ...

The client must be aware of the max_allowed_packet 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.

Compressed Packet

For slow connections, the packet can be compressed. This is activated after the handshake-response-packet 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.

• int<3> compressed packet length;

• int<1> compression protocol sequence number;

• int<3> uncompress body length;

• byte compressed body;

  • 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

      • byte packet 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 COM_PING packet COM_PING body when COMPRESS is enabled. This is 1 byte of data that has then no reason to be compressed, so:

01 00 00 00 00 00 00 01 00 00 00 10

The server returns an OK_Packet 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}

Overview

Connection is done by many exchanges:

• (Create socket)

• If first byte from server is 0xFF:

  • Packet is an ERR_Packet, socket has to be closed.

• Else:

  • Packet is an Initial handshake packet.

  • If SSL/TLS connection:

    • Client sends SSLRequest packet and switches to SSL mode for sending and receiving the following messages:

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

    • Authentication switch:

      • If the client or server doesn't have PLUGIN_AUTH capability:

        • Server sends 0xFE byte.

        • Client sends old_password.

      • Else:

        • Server sends Authentication switch request.

        • Client may have many exchange with the server according to the Plugin.

      • Authentication switch ends with server sending either OK_Packet or ERR_Packet.

Initial Handshake Packet

• int<1> protocol version.

• string server version

  • MariaDB Server 10.X versions are by default prefixed "5.5.5-".

  • MariaDB 11.0 and later versions do not have a "5.5.5-" default prefix.

• 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 /* MariaDB 10.2 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.

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

• int<4> client capabilities.

• int<4> max packet size.

• int<1> client's default character set and collation.

• string<19> reserved.

• If not (server_capabilities & CLIENT_MYSQL):

  • int<4> extended client capabilities

• Else:

  • string<4> reserved.

ZERO-CONFIGURATION SSL ENCRYPTION

Automatic Encrypted Connections (MariaDB 11.4+):

Previously, failed SSL connections due to self-signed certificates prevented communication. MariaDB 11.4+ 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 MariaDB 11.4 or later, the final confirmation packet contains:

• int<1> encryption (actually only 0x01 = SHA256 encryption)

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

• int<4> client capabilities.

• int<4> max packet size.

• int<1> client's default character set and collation.

• string<19> 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):

    • int<1> length of authentication response.

    • string authentication response (length is indicated by previous field).

  • Else:

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

    • string key.

    • string value.

Server Response to Handshake Response Packet

The server responds with an OK_packet, an ERR_packet, or an Authentication Switch Request packet.

Authentication Switch Request

(If client and server support CLIENT_AUTH capability):

• int<1> 0xFE : Authentication switch request header.

• string authentication plugin name.

• byte authentication plugin data.

Plugin List

mysql_old_password Plugin

deprecated — send a 8 byte encrypted password.

Authentication plugin data format :

• byte<8> 8-byte seed.

Client response:

• string old format encrypted password.

mysql_clear_password Plugin

Send clear password to server.

Client response:

• string password without encryption.

mysql_native_password Plugin

SHA-1 encrypted password with server seed.

Authentication plugin data format:

• string seed.

Client response:

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

• byte<1> password type.

• string 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 string 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 EOF_Packet, OK_Packet or ERROR_packet.

auth_gssapi_client Plugin

GSSAPI implementation.

Authentication plugin data format :

• string serverPrincipalName (UTF-8 format).

• string mechanisms (UTF-8 format).

Client must exchange packet with server until having a mutual GSSAPI 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 plugin description.

The server sends a random nonce that the client signs.

authentication plugin data format :

• byte seed.

Client response :

• byte ed25519 encrypted password.

parsec Plugin

Authentication plugin data format:

• string<32> server nonce.

Client has to send an empty packet to request "ext-salt".

Format of ext-salt is:

• string<1> 'P' (denotes KDF algorithm = PBKDF2).

• byte<1> iteration factor. number of iterations correspond to 1024 << iteration factor (0x0 means 1024, 0x1 means 2048, etc.).

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

• byte<32> client nonce.

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

Native Password Authentication

The 20 byte string 'seed' is calculated by concatenating scramble first part (8 bytes) and scramble second part from Initial handshake packet. After that, the client calculates a password hash using the password and seed by using ^ (bitwise xor), + (string concatenation) and SHA1 as follows:

SHA1( passwd) ^ SHA1( seed + SHA1( SHA1( passwd ) ) )

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

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

Caching SHA256 authentication possible exchanges:

• 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 public key request.

      • Server sends a public key response.

    • Client sends an RSA encrypted password.

    • Ends with server sending either OK_Packet , ERR_Packet.

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

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

Overview

SHA256 authentication possible exchanges:

• if connection use SSL (SSLRequest Packet sent):

  • 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 RSA encrypted password.

  • Ends with server sending either OK_Packet , ERR_Packet.

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}

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

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

    • string key.

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

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}

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

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

Fields

• int<1> 0xOD : COM_DEBUG header.

Response

EOF Packet.

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

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

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

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

Fields

• int<1> 0x02 : COM_INIT_DB Header.

• string schema name.

Response

ERR_Packet or OK_Packet.

Example

06 00 00 00 02 74 65 73 74 63                    .....testc

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

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

Fields

• int<1> 0x0e : COM_PING header.

Response

OK_Packet.

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

Forces the server to terminate a specified connection.

Fields

• int<1> 0xC COM_PROCESS_KILL.

• int<4> process ID.

Response

OK Packet or ERR Packet.

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

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

With the COM_QUERY command, the client sends the server an SQL statement to be executed immediately.

Fields

• int<1> 0x03 : COM_QUERY header.

• string 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 OK Packet 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).

• Null character (0x00).

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 - ERR_Packet if any error occurs.

• 0x00 - OK_Packet when query execution works without result set.

• 0xFB - LOCAL_INFILE Packet if the query was LOCAL INFILE ....

• Or a result set, when the query returns results (in case of a SELECT query, for example).

Example

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

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

Fields

• int<1> 0x01 : COM_QUIT header.

Response

Server terminates connection.

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

COM_RESET_CONNECTION resets a connection without reauthentication.

The command does this:

• Roll back any open transactions.

• Reset transaction isolation level.

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

Enables or disables server option.

Fields

• int<1> 0x1B COM_SET_OPTION .

• int<2> option.

Options

Response

EOF Packet on success or ERR packet.

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

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

Fields

• int<1> 0x0A COM_SHUTDOWN

• int<1> option

Options

Response

OK Packet or ERR packet.

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

This command is used inside the server only.

Direction

Used inside server only.

Fields

• int<1> 0x00: COM_SLEEP header.

Response

ERR_Packet.

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

Get internal server statistics.

Fields

• int<1> 0x09 : COM_STATISTICS header.

Response

string human-readable string.

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

Closes a previously prepared statement.

Direction

Client to server.

Implemented by

• mysql_stmt_close().

• mysql_stmt_prepare().

Fields

• int<1> 0x19 COM_STMT_CLOSE header.

• int<4> 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}

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

• int<1> 0xfa : COM_STMT_BULK_EXECUTE header.

• int<4> statement id.

• int<2> bulk flags.

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

Flags

Bulk Flags

Parameter Type Flag

Parameter Indicator

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.

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

Executes a previously prepared statement.

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

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.

Fields

• int<1> 0x17 : COM_STMT_EXECUTE header.

• int<4> statement ID.

• int<1> flags.

• int<4> Iteration count (always 1).

• If (param_count > 0)

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

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

  • If (send type to server):

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

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

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:

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}

Fetch rows from a prepared statement.

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

Fields

• int<1> 0x1C COM_STMT_FETCH header.

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

Prepares a statement on the server.

Implemented by

• mariadb_stmt_execute_direct()

• mysql_stmt_prepare()

Fields

• int<1> 0x16 COM_STMT_PREPARE header

• string SQL Statement

Example

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

Response

If anything goes wrong, the server will send an ERR_Packet. If the command succeeds, different packets are received:

• COM_STMT_PREPARE_OK.

• If number of parameters (count of '?' placeholders) > 0:

  • For each parameter:

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

COM_STMT_PREPARE_OK

• int<1> 0x00 COM_STMT_PREPARE_OK header.

• int<4> statement ID.

• int<2> number of columns in the returned result set (or 0 if statement does not return result set).

• int<2> number of prepared statement parameters ('?' placeholders).

• string<1> -not used-

• int<2> number of warnings.

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

Resets a prepared statement on the client and server to state after preparing.

Fields

• int<1> 0x1A COM_STMT_RESET header.

• int<4> Statement ID.

Response

ERR_Packet or OK_Packet.

Example

05 00 00 00 1A 04 00 00 00

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

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

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

Fields

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

In contrast to the text protocol, the binary protocol transfers data according to the format of the field types returned in PACKET_METADATA.

Fields

(column_count + 7)/8   null bitmap
while (!eof) {
  for (i=0; i < column_count; i++)
  {
    data (length depends on the data type)
  }
}

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

MariaDB Server sends the following packets as part of a result set.

A result set consists of different packets:

• Result set metadata.

  • 1 column count packet.

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

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.

• string column alias.

• string column.

• If extended type supported (see MARIADB_CLIENT_EXTENDED_METADATA ):

  • string extended metadata.

• int length of fixed fields (=0xC).

• int<2> character set number.

• int<4> max. column size.

• int<1> Field types.

• int<2> Field detail flag.

• int<1> decimals.

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

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:

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:

  • int<1> data type: 0x00:type, 0x01: format.

  • string value.

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

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

Text Result Set Row

• For each column:

  • 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 : DOUBLE Binary encoding

    • If MYSQL_TYPE_LONGLONG type : BIGINT Binary encoding

    • If MYSQL_TYPE_INTEGER type : INTEGER Binary encoding

    • 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

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:

06 45 49 53 46 53 48      . - 1 5 . 5 0

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.

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.

  • int<4> days.

  • int<1> hours.

  • int<1> minutes.

  • int<1> seconds.

  • if data length > 8 :

    • int<4> microseconds.

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

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

• int<1> 0x00 : OK_Packet header or (0xFE if CLIENT_DEPRECATE_EOF is set).

• int affected rows.

• int last insert id.

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

    • string session state info.

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 SSL certificate verification signature. For the following OK_Packets it contains (if present) various human-readable information.

Server status flag

Values of server status flags:

Session state info

• While packet has remaining data:

  • int<1> session change type.

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

    • int total length.

  • string session data change.

Session change type

Session Data Change

Each type of data has its own kind of format:

SESSION_TRACK_SCHEMA

• string new current schema.

SESSION_TRACK_SYSTEM_VARIABLES

While there is remaining data:

• string variable data.

For each variable data:

• string variable name.

• string variable value.

SESSION_TRACK_STATE_CHANGE

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

• string 1 if session state tracking was enabled.

SESSION_TRACK_GTIDS

This tracker is not implemented by MariaDB.

SESSION_TRACK_TRANSACTION_CHARACTERISTICS

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

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

SESSION_TRACK_TRANSACTION_STATE

• string 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: _ Read-only transaction: R

4. Transaction write safe: _ Transaction write unsafe: w

5. Unknown transaction type: _ Read-write transaction: W

6. Transaction statement safe: _ Transaction statement unsafe: s

7. Transaction does not have resultsets: _ Transaction with result sets: S

8. No locked tables: _ Tables have been locked: L

Notes

• To determine if session tracking is enabled, check if the CLIENT_SESSION_TRACK flag is set in server_capabilities.

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

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

• int<1> 0xfe : EOF header.

• int<2> warning count.

• int<2> server status.

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

ERR_Packet indicates that an error occurred.

Fields

• int<1> ERR_Packet header = 0xFF.

• int<2> error code. see error list.

• If (error code == 0xFFFF) /* progress reporting */:

  • int<1> stage.

  • int<1> max_stage.

  • int<3> progress.

  • string progress_info.

• Else:

  • If (next byte = '#'):

    • string<1> sql state marker '#'.

    • string<5> sql state.

    • string human-readable error message.

  • Else:

    • string human-readable error message.

See also

"Progress reporting"

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

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

Fields

• int<1> 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}

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 remaining events are interpreted according to the version.

The final event is usually a log-rotation event ROTATE_EVENT that specifies the next binary log filename or a STOP_EVENT written during server shutdown.

Note: in case of a server crash there is no terminating event (no ROTATE nor STOP).

Example Binlog File (Hexdump -C $file_name)

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

Example From mysqlbinlog

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

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

All the binlog events stored in a binary log file have a common structure:

• An event header;

• Event data.

Event Header Structure, 19 Bytes

• uint<4> Timestamp (creation time).

• uint<1> Event Type (type_code).

• uint<4> Server_id (server which created the event).

• uint<4> Event Length (header + data).

• uint<4> Next Event position.

• uint<2> Event flags.

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;

• Copy the original event length back to its position.

Regardless of the cipher used to encrypt the binlogs, the encrypted data are the same size as the original unencrypted event. For events that are encrypted in CBC mode, and whose length is not a multiple of the cipher block size, the final partial block is encrypted using a form of residual block termination:

• Encrypt the current IV of the binlog file in ECB mode;

• XOR the remaining bytes with the encrypted IV.

Event Type

Fake Events

These are generated on the fly, never written:

Event Flag

Event Header Example of FORMAT_DESCRIPTION_EVENT

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

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

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

• f9 00 00 00 [4] Next Event pos => 00 00 00 f9 => 249 (pos 4 + event size)

• 00 00 [2] Event flags = 0

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

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

The MariaDB replica replication protocol consists of:

• A registration phase to master;

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

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

MariaDB network protocol 4 bytes are:

• uint<3> packet length (the sent binlog event can be up to 2^24 - 1 - 1 data bytes).

• uint<1> packet sequence byte<1>(0 to 255).

Replication protocol status byte:

• uint<1> OK (0) or ERR (ff) or End of File, EOF, (fe).

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.

Example of an Event Transmission HEARTBEAT_LOG_EVENT

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

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

• Content, string.

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

Regular MariaDB replication is asynchronous. MariaDB also includes semisynchronous replication 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 binlog network stream and before the normal binlog event header.

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

SET @rpl_semi_sync_slave=1

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.

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.

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

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.

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

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.

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

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

Overview

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

After authentication, some COM_QUERY packets are exchanged before sending COM_REGISTER_SLAVE and COM_BINLOG_DUMP.

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

• SELECT UNIX_TIMESTAMP();

• SHOW VARIABLES LIKE 'SERVER_ID';

• SET @master_heartbeat_period= 30000001024;

• SET @master_binlog_checksum= @@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.

Example Using 'ngrep'

COM_REGISTER_SLAVE, Semi-Sync and COM_BINLOG_DUMP:

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

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:

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