Understanding the MySQL Client/Server Protocol: Connection and Replication

MySQL client and server communicate over a TCP‑based protocol. The article first describes the connection phase, where the client initiates a TCP connection, the server sends an Initial Handshake Packet, optional SSL negotiation, the client replies with a Handshake Response Packet containing username and encrypted password, and the server confirms with an OK_Packet or ERR_Packet.

Each packet consists of a 4‑byte header (3‑byte payload length and 1‑byte sequence ID) followed by the payload. The payload size cannot exceed 2^24‑1 bytes (16 MB); larger data are split across multiple packets.

Initial Handshake Packet

The server’s Initial Handshake Packet includes protocol version, server version string, thread ID, authentication seed (auth‑plugin‑data‑part‑1 and part‑2), capability flags, character set, status flags, and the name of the authentication plugin (e.g., mysql_native_password ).

The default authentication algorithm for MySQL 5.x is:

SHA1(password) XOR SHA1("20‑bytes random data from server"
SHA1(SHA1(password)))

Capabilities Flags are a 4‑byte bitmap indicating supported features such as CLIENT_PROTOCOL_41, CLIENT_PLUGIN_AUTH, and CLIENT_SECURE_CONNECTION.

Handshake Response Packet

The client replies with a packet that must set CLIENT_PROTOCOL_41 and, for the default authentication, also CLIENT_PLUGIN_AUTH and CLIENT_SECURE_CONNECTION. The packet contains capability flags, max packet size, character set, username, optional auth‑response length, and the encrypted password.

OK and ERR Packets

After authentication the server sends an OK_Packet (or ERR_Packet on failure). Since MySQL 5.7.5 the OK packet may also include an EOF packet for warnings and status.

Replication

To become a replication slave the client sends COM_REGISTER_SLAVE , receives an OK or ERR, then sends COM_BINLOG_DUMP to request binlog data. The server streams binlog events or returns an error.

COM_REGISTER_SLAVE

The packet consists of a 1‑byte command (0x15), a 4‑byte server‑id, and optional fields; the user and password fields are identifiers for the slave, not MySQL login credentials.

COM_BINLOG_DUMP

The packet includes a 1‑byte command (0x12), binlog position, flags, server‑id, and binlog filename. If the flags contain 0x01, the server sends an EOF packet when the binlog is exhausted.

Binlog Events

Binlog events have a common header and optional post‑header and payload. Event types include management events (START_EVENT_V3, FORMAT_DESCRIPTION_EVENT, ROTATE_EVENT, etc.), statement‑based events (QUERY_EVENT, INTVAR_EVENT, RAND_EVENT, USER_VAR_EVENT, XID_EVENT), and row‑based events (TABLE_MAP_EVENT, DELETE_ROWS_EVENTv0/v1/v2, UPDATE_ROWS_EVENTv0/v1/v2, WRITE_ROWS_EVENTv0/v1/v2). Replication can be asynchronous or semi‑synchronous, and binlog formats may be statement, row, or mixed.

Understanding these packet structures enables implementation of a custom MySQL client library that can register as a slave and parse binlog streams.