Implementing a Raw TCP MySQL Connection in Python

When a Python program uses a MySQL driver (e.g., mysql‑connector‑python or pymysql), the driver simply creates a TCP connection to the MySQL server and follows the MySQL client‑server protocol.

The protocol begins with the server sending a handshake packet that contains the server version, default authentication plugin, and a password salt. The client then replies with a handshake response packet that includes the username, encrypted password, client capabilities, and optional attributes. After the handshake, the server sends a response indicating whether authentication succeeded.

The article lists the packet flow:

1. Server → client: handshake packet.

2. Client → server: optional SSL request.

3. Client → server: handshake response (username, password, attributes, etc.).

4. Server → client: authentication result.

To demonstrate that the driver is just a TCP client, a full Python script is provided that implements the same steps using only the standard socket library. Key parts of the code are shown below.

#!/usr/bin/env python3
import os, ssl, sys, time, socket, struct, logging, argparse
from plugins import get_auth_plugin

class MySQLTcpSocket(object):
    """A synchronous TCP socket that talks MySQL protocol."""
    def __init__(self, host=None, port=3306, user=None, password=None):
        self._host = host
        self._port = port
        self._user = user
        self._password = password
        self._packet_number = 0
        # Resolve and connect
        for info in socket.getaddrinfo(self._host, self._port, socket.AF_INET, socket.SOCK_STREAM, 0):
            try:
                family, sock_type, proto, _, addr = info
                self._sock = socket.socket(family, sock_type, proto)
                self._sock.settimeout(3)
                self._sock.connect(addr)
                break
            except IOError as err:
                logging.exception(str(err))
                if hasattr(self._sock, 'close'):
                    self._sock.close()
                sys.exit(1)

The class includes methods to read and write MySQL packets, handle SSL upgrade, and build authentication packets. A companion MySQLProtocol class parses the server handshake and assembles client response packets, including support for the caching_sha2_password plugin.

The main function creates a MySQLTcpSocket, receives the handshake, parses it, sends an SSL request, switches to SSL mode, builds the authentication response, and finally logs the server’s OK packet. Sample log output shows a successful connection and authentication.

Running the script against a MySQL instance also reveals the client connection attributes stored in performance_schema.session_connect_attrs, confirming that custom attributes (e.g., process ID, platform, client name) are transmitted correctly.

The article concludes that while writing a raw TCP client requires hundreds of lines of code, it is valuable for building custom middleware such as read‑write splitters or for deep debugging of the MySQL protocol.

Additional resources include the official MySQL client‑server protocol documentation and a GitHub repository containing related protocol packet utilities.