- 15 MySQL Client/Server Protocol
- 15.1 Overview
- 15.2 Connection Phase
- 15.3 Authentication Method
- 15.4 Compression
- 15.5 SSL
- 15.6 Text Protocol
- 15.7 Prepared Statements
- 15.8 Stored Procedures
- 15.9 Replication Protocol
- 15.10 Row Based Replication
- 15.11 Semi-Sync Replication
- 15.12 Protocol Examples
- 15.13 Source Code Locations
[+/-]
The MySQL protocol is used between MySQL Clients and a MySQL Server. It is implemented by
the Connectors (Connector/C, ...)
MySQL Proxy
the MySQL Server itself for the slaves
It supports:
transparent encryption via SSL
transparent compression via Compression
a Connection Phase where capabilities and authentication data are exchanged
and a Command Phase which supports needs of Prepared Statements and Stored Procedures
The documentation is based on the source files of the MySQL Server like:
-
sql/sql_parse.cc for the protocol basics
-
sql/sql_prepare.cc for the prepared statement protocol
-
sql/sql_repl.cc for the binlog protocol
sql/protocol.cc for the value and type encoding
The protocol has a few very basic types that are used throughout the protocol:
integers and
strings
The MySQL Protocol has a set of possible encodings for integers:
fixed length intergers
length encoded integers
- Protocol::LengthEncodedInteger:
-
A integer than consumes 1, 3, 4 or 9 bytes depending on its numeric valuer
To convert a number value into a length-encoded integer:
If the value is < 251, it is stored as a 1-byte integer.
If the value is ≥ 251 and < (2^16), it is stored as
fc+ 2-byte integer.If the value is ≥ (2^16) and < (2^24), it is stored as
fd+ 3-byte integer.If the value is ≥ (2^24) and < (2^64) it is stored as
fe+ 8-byte integer.
NoteUp to MySQL 3.22,
0xfewas followed by a 4-byte integer.To convert a length-encoded integer into its numeric value, check the first byte:
If it is < 0xfb, treat it as a 1-byte integer.
If it is 0xfc, it is followed by a 2-byte integer.
If it is 0xfd, it is followed by a 3-byte integer.
If it is 0xfe, it is followed by a 8-byte integer.
CautionIf the first byte of a packet is a length-encoded integer and its byte value is
0xfe, you must check the length of the packet to verify that it has enough space for a 8-byte integer.If not, it may be a
EOF_Packetinstead.Depending on the context, the first byte may also have other meanings:
If it is 0xfb, it is represents a NULL in a
ProtocolText::ResultsetRow.If it is 0xff and is the first byte of a
ERR_Packet
Caution0xff as 1st byte of length-encoded integers is undefined.
- Implemented by
- Example
-
fa -- 250 fc fb 00 -- 251
Strings are sequences of bytes and appear in a few forms in the protocol:
- Protocol::FixedLengthString:
-
Fixed length strings have a known, hardcoded length.
An example is the sql-state of the
ERR_Packetwhich is always 5 byte long.- Implemented by
- Protocol::LengthEncodedString:
-
A length encoded string is a string that is prefixed with length encoded integer describing the length of the string.
It is a special case of
Protocol::VariableLengthString- Fields
length (lenenc_int) -- length of the string
string (string.fix_len) -- [len=$length] string
- Implemented by
In this document we describe each packet by first defining its
payload and provide a
example showing each packet
that is sent include its packet header:
<packetname>
<description>
direction: client -> server
response: <response>
payload:
<type> <description>
Example:
01 00 00 00 01
The <type> describes the sequence of bytes of the packet:
- type 1
1 byte
Protocol::FixedLengthInteger
- type 2
2 byte
Protocol::FixedLengthInteger
- type 3
3 byte
Protocol::FixedLengthInteger
- type 4
4 byte
Protocol::FixedLengthInteger
- type 6
6 byte
Protocol::FixedLengthInteger
- type 8
8 byte
Protocol::FixedLengthInteger
Some packets have optional fields or a different layout
depending on the
Protocol::CapabilityFlags
that are sent as part of the
Protocol::HandshakeResponse
packet.
If a field has a fixed value its description will show it as hex value in brackets like [00].
If a MySQL client or server wants to send data, it:
Splits the data into packets of size (224–1) bytes
Prepends to each chunk a packet header
- Protocol::Packet:
-
Data between client and server is exchanged in packets of max 16MByte size.
- Payload
-
3 payload length 1 sequence id string[len] payload
- Fields
length (3): Length of the payload. The number of bytes in the packet beyond the initial 4 bytes that make up the packet header.
sequence_id (1): Sequence ID
payload (string.fix_len): [len=length] payload of the packet
- Example
-
A
COM_QUITlooks like this:01 00 00 00 01 -- length=0x01, sequence_id=0x00, command-byte=0x01
If the payload is larger than or equal to 2^24-1 bytes the
length is set to 2^24-1 (ff ff
ff) and a additional packets are sent with the rest
of the payload until the payload of a packet is less than
2^24-1 bytes.
Sending a payload of 16 777 215 (2^16-1) bytes looks like:
ff ff ff 00 ... 00 00 00 01
The sequence-id is incremented with each packet and may wrap around. It starts at 0 and is reset to 0 when a new command begins in the Command Phase.
For most of the commands the client sends to the server one of two packets is returned as response:
- OK_Packet:
-
OK packet is sent from the server to the client to signal successful completion of a command.
If
CLIENT_PROTOCOL_41is set it contains a warning count.- Payload
-
1 [00] the OK header lenenc-int affected rows lenenc-int last-insert-id if capabilities & CLIENT_PROTOCOL_41 { 2 status_flags 2 warnings } elseif capabilities & CLIENT_TRANSACTIONS { 2 status_flags } string[EOF] info - Fields
header (1) -- OK header indicator
affected_rows (lenenc_int) -- rows affected by the command
last_insert_id (lenenc_int) -- last insert-id generated by the command
status_flags (2) -- status flags
warnings (2) -- number of warnings generated by the command
info (string.EOF) -- human readable info about the query
- Example
-
07 00 00 02 00 00 00 02 00 00 00 ...........
- ERR_Packet:
-
It contains a SQL-state if
CLIENT_PROTOCOL_41is enabled.- Payload
-
1 [ff] the ERR header 2 error code if capabilities & CLIENT_PROTOCOL_41 { string[1] '#' the sql-state marker string[5] sql-state } string[EOF] error-message - Fields
header (1) -- ERR Packet indicator
error_code (2) -- error-code
sql_state_marker (string.fix_len) -- [len = 1], #
sql_state (string.fix_len) -- [len = 5], SQL State of this error
error_message (string.EOF) -- human readable error message
- Example
-
17 00 00 01 ff 48 04 23 48 59 30 30 30 4e 6f 20 .....H.#HY000No 74 61 62 6c 65 73 20 75 73 65 64 tables used
- EOF_Packet:
-
If
CLIENT_PROTOCOL_41is enabled the EOF packet will contain a warning count and status flags.Cautionthe EOF packet may appear in places where a
Protocol::LengthEncodedIntegermay appear. You have to check the packet length is less then 9 to make sure it is a EOF packet.- Payload
-
1 [fe] the EOF header if capabilities & CLIENT_PROTOCOL_41 { 2 warning count 2 status flags } - Fields
header (1) -- EOF Packet indicator
warning_count (2) -- number of warnings
status_flags (2) -- status of the statement, see Status Flags
- Example
-
05 00 00 05 fe 00 00 02 00
MySQL has a very flexible character set support as documented in:
The list of character sets and their IDs can be queried with:
SELECT id, collation_name FROM information_schema.collations ORDER BY id; +----+-------------------+ | id | collation_name | +----+-------------------+ | 1 | big5_chinese_ci | | 2 | latin2_czech_cs | | 3 | dec8_swedish_ci | | 4 | cp850_general_ci | | 5 | latin1_german1_ci | | 6 | hp8_english_ci | | 7 | koi8r_general_ci | | 8 | latin1_swedish_ci | | 9 | latin2_general_ci | | 10 | swe7_swedish_ci | +----+-------------------+
A few common ones are:
number |
hex |
character set name |
|---|---|---|
8 |
0x08 |
latin1_swedish_ci |
33 |
0x21 |
utf8_general_ci |
63 |
0x3f |
binary |
- Protocol::CharacterSet:
-
a character is defined in the protocol as a integer
- Fields
charset_nr (2) -- number of the character set and collation
In the command phase the client sends a command packet with the sequence-id [00]:
13 00 00 00 03 53 ...
01 00 00 00 01
^^- command-byte
^^---- sequence-id == 0
The first byte of the payload describes the command-type like:
hex |
constant name |
|---|---|
00 |
|
01 |
|
02 |
|
03 |
|
04 |
|
05 |
|
06 |
|
07 |
|
08 |
|
09 |
|
0a |
|
0b |
|
0c |
|
0d |
|
0e |
|
0f |
|
10 |
|
11 |
|
12 |
|
13 |
|
14 |
|
15 |
|
16 |
|
17 |
|
18 |
|
19 |
|
1a |
|
1b |
|
1c |
|
1d |
|
1e |
The commands belong to
