MySQL Protocol

This document descibes the MySQL network protocol version 10, as of October 2002 (MySQL 3.23.52). It has been produced by reading libmysql.c, net.c and sql_show.cc in the MySQL source.

The protocol requires first that a client and server negotiate a connection, including agreeing about protocol features and authentication. This is followed by a series of requests from the client and reponses from the server, until either the client issues a QUIT request or the connection is broken.

MySQL uses either a Unix domain socket (usually /tmp/mysql.sock or /var/run/mysqld/mysql.sock), an MS Windows named pipe (usually \\hostname\pipe\mysql) or a TCP port (usually 3306). It treats all communications methods identically.

Each client message is sent as a single MySQL 'packet'; a server response may consist of many packets. These packets are not related to the underlying network layer packets, and indeed many response packets may be contained in a single IP datagram.

Each packet begins with a 4-byte header. The first three header bytes give the body length as a Little-Endian integer (all MySQL integers are Little-Endian unless described otherwise). The fourth gives a packet number for this transaction, starting from 0, and including both client and server messages. The body of the message then follows.

All example packets are taken from a real system.

Connection

Once the transport-level connection is established, the connection negotiation starts with the server identifying itself.

Server greeting

The host sends an initial greeting similar to:

The first byte of the message body is the protocol version, currently 10; the current client is capable of connecting to protocol 9 or 10 servers. The protocol version is followed by a version string for the server (here 3.23.52-log), null-terminated - the -log suffix means logging is enabled. Following this is a 4-byte integer describing the thread ID of the server thread handling the request. Finally an 8-byte 'scramble string' salt for encryption, again null-terminated.

Newer servers follow this by a 2-byte integer describing the server's capabilities - this server can do LONG_FLAG and CONNECT_WITH_DB, as well as the more recent COMPRESS and TRANSACTIONS. Even newer servers follow this with a single byte giving the server character set and two bytes describing the server status.

Client authentication

The client now sends a request to the server, similar to:

This is the client's turn to send its capabilities in two bytes. These will always include LONG_PASSWORD, LONG_FLAG and TRANSACTIONS for modern clients, and in this case include INTERACTIVE and LOCAL_FILES as well, as this is the standard MySQL command-line client.

At this point the client will switch to SSL on the communications channel if it has set the SSL capability bit - which it will not do if the server has not also set that bit.

This is followed by three bytes giving the maximum allowed packet length. A value of 0 here means 2^24=16MB, and will be used by modern clients. This is followed by the username, null-terminated, and the eight characters of scrambled password (the example given here is not a real password) if a password is provided.

If the client is trying to connect to a particular database and the server is capable of CONNECT_WITH_DB, it will add the database name as a null-terminated string.

The client will now expect the server to send an OK simple response or an error. After an OK response, the flow of client commands can begin; after an error response the client closes the connection.

If the client and server are both capable of compression, and the client wants to use it (it usually has to be explicitly enabled), the client will now switch to using it. It follows the standard packet header with a three-byte compressed length. The compression mechanism used is standard zlib.

Simple responses

Most commands produce a simple 'OK' response. This will usually be of the form:

In the case of an OK response to the initial authentication, of course, the packet number will be 2.

For an error, the response will be more like the following:

The first byte of the error response body is set to 255.

If the body length is at least 4, then the packet contains a 2-byte error code, followed by the error message, of up to 200 characters. The error code is absent in protocol 9 servers.

There are 218 error codes, ranging from HASHCHK (1000) to ROW_IS_REFERENCED (1217).

Commands

A simple command is sent in the form:

The first byte is the command code, followed by an optional argument. Any command not explicitly listed, including any SQL command, is sent as QUERY, as in the example.

Disconnection

When the client wishes to disconnect, it sends a QUIT simple command and closes its end of the connection, as follows

01 00 00 00	Body Length=1, packet=0
01	Command=QUIT

The server will close its end of the connection without further response.

Field-length encoding

When data is sent in tabular form, each field is preceded by its field length, encoded in the following manner:

This mechanism is also used for encoding other integers efficiently.

Tabular response

When returning data as a table, as a response to a QUERY, FIELD_LIST or PROCESS_INFO command, MySQL first describes the fields in each row, then sends the rows.

There is one logical packet per field description. Each field description contains five parts, each preceded by their length in field_length encoding: the table name, the field name, the maximum length (a 3 byte integer), the field type and 2 bytes of flags followed by one byte of decimals.

In a client or server without the LONG_FLAG capability, there is only one byte of field flags.

If default values are provided (this will usually just be for the FIELD_LIST command), there is a sixth part to the field description: default value.

After all the field description packets, a packet with just a single byte of value 254 indicates the start of the rows.

Each row is in a separate packet, with the field values preceded by a field length. All non-binary fields values are sent as text - in particular, all numeric fields appear as ASCII, and timestamps as YYYY-MM-DD HH:MM:SS. Trailing spaces are removed from char and varchar fields.

After the last row, another packet holding just the byte 254 indicates the end of the data.

Complex Responses

There are four commands that can return complex responses.

STATISTICS

The STATISTICS command response is simply a string describing the current server statistics.

FIELD_LIST

The FIELD_LIST command response is the same as the first part of a tabular response (the field descriptions) - with the exception that a default value may be attached to each field description.

PROCESS_INFO

The PROCESS_INFO command response is first a packet containing a field count, in field_length encoding, followed by a tabular response.

QUERY

The QUERY command handles all SQL commands, as well as almost all client commands.

There are three forms of response. The simplest is where no tabular data needs to be returned:

Here, the first byte is 0 and the following two entries are field_length encoded. If the server is capable of transactions, two bytes of server status will follow.

Finally, some additional information may be provided as a string, preceded by its field length.

If a file is to be sent to the server, the following response is generated:

The file is sent by the client as a series of ordinary packets, with the final packet empty.

Otherwise, the result contains a field count, followed by some extra information (possibly a row count), both field_length encoded:

This is then followed by the tabular data.

Semantics

Many MySQL clients and drivers expect certain behaviour from the server, and perform queries behind the scenes. The important responses are shown below.

Getting current database and user

The mysql command-line client issues the request
select DATABASE(),USER()
when the user asks for the current status. It will expect the following response:

The DATABASE() field may be empty. The USER() field is of the form user@host.

Status enquiry

The current status response is similar to:

Uptime: 129  Threads: 1  Questions: 6  Slow queries: 0  
Opens: 6  Flush tables: 1  Open tables: 0 Queries per second avg: 0.047

The mysql client assumes the number following the first space is a time and formats it appropriately.

Show status

Show databases

Show tables

Show fields

The Null field takes values '' or 'YES' - this is presumably a bug. The Key field is 'PRI', 'UNI', 'MUL'or ''.

The same output is produce by show columns, desc or describe.

Show processlist

Show processlist full removes the size limit on the Info column.

Password algorithm

The protocol 9 and protocol 10 password algorithms are different. In both cases, a seed is provided by the server and a password is provided by the user, and together they are used to generate a scrambled message of the same length as the seed.

Hashing mechanism

A string is hashed to a pair of unsigned longs using the following algorithm:

Only the bottom 31 bits of the unsigned longs are used.

Protocol 10

First the password, then the salt are hashed to give two pairs of longs: p₁, p₂, s₁ and s₂. These are then used to generate the two seeds of a random number generator, which takes values between 0 and 2³⁰-2.

seed₁ = p₁ EOR s₁
seed₂ = p₂ EOR s₂

The random number generator algorithm is:

seed₁ = (seed₁ * 3 + seed₂) mod 2³⁰ - 1
seed₂ = (seed₁ + seed₂ + 33) mod 2³⁰ - 1
return seed₁ / (2³⁰ - 1)

The random number generator is called once for each byte of the salt, and the number given is multiplied by 31 and added to 64 to give an ASCII character between '@' and '^'.

msg[i] = chr(64 + rng()*31)

The string just given then has each character exclusive-ored with the random number generator multiplied by 31.

msg[i] = msg[i] eor(rng()*31)

Protocol 9

Here only p₁ and s₁ are used, 2³⁰ is replaced by 2²⁹ and seed₂ is initially set to seed₁ / 2. The final exclusive-or pass to modify the message is also skipped.

Magic numbers

MySQL uses the following codes:

Note that the NUM flag is set automatically by the client, based on the field type.

This document is in the public domain, and you may do anything with it you wish. The author takes no responsibility for the accuracy of its contents. Some of the terms in this document are trademarks of MySQL AB and other companies. No trade secrets or other privileged information has been used in its compilation, and the author has no relationship with MySQL.