Writing MySQL Proxy in GO for self-learning: Part 2 — decoding handshake packet

MySQL connections threads

Each client connection to MySQL Server handled by a thread. MySQL is portable, so the underhood threads implementation is system dependent (Windows, macOS, and Linux have their own threads implementation).

MySQL Protocol Basics

The basic unit of communication is the application-layer packet. The packets can be compressed, or/and transmitted over the SSL layer. The decision about using compression or SSL is made during the handshake stage and depends on the capabilities and settings of both the client and the server.

  • Splits the data into packets of size ²²⁴ bytes (16mb)
  • Prepends to each chunk a packet header
Packet: 01 00 00 00 01+------------+-----------------+-------------+
| Length = 1 | Sequence ID = 0 | Payload = 1 |
| 01 00 00 | 00 | 01 |

The Handshake Stage

MySQL has a special connection phase, which performs these tasks:

  • Exchange the capabilities of client and server
  • Setup SSL communication channel if requested
  • Authenticate the client against the server
Initial Handshake starts with server sending the Initial Handshake Packet. After this, optionally, client can request an SSL connection to be established with SSL Connection Request Packet, and then client sends the Handshake Response Packet.

Sniffing MySQL traffic

Let’s “feel” the data flow between the client and MySQL Server.

  1. brew install ngrep
sudo ngrep -x -q -d lo0 '' 'port 3306'
MySQL Protocol Communication Steps

Exploring the handshake packet

This is the definition of Protocol::HandshakeV10, the first packet the MySQL Server send to the client right after the TCP connection has been established.

1              [0a] protocol version
string[NUL] server version
4 connection id
string[8] auth-plugin-data-part-1
1 [00] filler
2 capability flags (lower 2 bytes)
if more data in the packet:
1 character set
2 status flags
2 capability flags (upper 2 bytes)
if capabilities & CLIENT_PLUGIN_AUTH {
1 length of auth-plugin-data
} else {
1 [00]
string[10] reserved (all [00])
if capabilities & CLIENT_SECURE_CONNECTION {
string[$len] auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))
if capabilities & CLIENT_PLUGIN_AUTH {
string[NUL] auth-plugin name
Handshake Packet

Decoding Integers

The MySQL Protocol has a set of possible encodings for integers:

  • Fixed-length integers
  • Length-encoded integers
uint16(b[0]) | uint16(b[1])<<8
b[0]                = 00001111 (8 bit integer)
uint16(b[0]) = 0000000000001111 (we created 16 bit integer)
They are both still represent the same value (15 in decimal)b[1] = 10000001 (8 bit integer)
uint16(b[1]) = 0000000010000001 (we created 16 bit integer)
But the value of second byte alone doesn't make sense to us. So we move it to be in the "right place" by shifting it by 8 bits.uint16(b[1])<<8 = 1000000100000000 (shifting left 8 times)and finally we use bitwise sumuint16(b[0]) | uint16(b[1])<<8 = 1000000100001111 (33039 in decimal)We successfully reconstructed the original value.Just remind you how the binary sum works:0000000000001111
b[0]                = 00001111 (8 bit integer)
uint16(b[0]) = 0000000000001111 (we created 16 bit integer)
uint16(b[0])<<8 = 0000111100000000
We shifted the byte at index 0 by 8 bits, cause Big Endian says this byte is the most significant byte, so we move it to the right position.b[1] = 10000001 (8 bit integer)
uint16(b[1]) = 0000000010000001 (we created 16 bit integer)
And finally apply bitwise sum operatoruint16(b[0]) | uint16(b[1])<<8 = 0000111110000001This number is 3969 in decimal, but the original number was 33039

Decoding Strings

MySQL Protocol supports 5 types of string encodings. You can read more in the docs.



MySQL Protocol uses bitmask, a well known trick to implement flags. Each flag is an unsigned integer with a single bit ON in its binary representation. It allows to encode 8 different flags with a single byte.

The code

This is what was done as part of this second article:

  • Data read/write directly from the socket for handshake only
  • Catch ^C signal, shut down connections and exit (uses context)
  • Structs created (PacketHeader, InitialHandshakePacket, CapabilityFlag)
index := bytes.IndexByte(payload, byte(0x00))

Try it :)

What next?

It was a great experience! The hardest thing is the writing this article =)

  • Introduce state machine abstraction to handle connections life cycle
  • Implement decoding/encoding of the all MySQL Packets
  • Implement query/response data buffering
  • Implement efficient decoding/encoding by reusing buffers
  • Connection pool

Helpful resources

A MySQL specific information you can find in the MySQL Client/Server Protocol, Client/Server Protocol documentation. Also, you can read more about Endianness and Bitwise operations on wiki.





Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alexander Ravikovich

Alexander Ravikovich

In GO we trust. Software Engineer. @Isreal