Of course! A Python MySQL Proxy is an excellent project for understanding networking, protocols, and database interaction. It acts as a middleman between your application (the client) and the MySQL server.

The core idea is simple:

1. Client connects to the proxy (thinking it's the MySQL server).
2. Proxy accepts the connection, establishes its own connection to the real MySQL Server.
3. Proxy blindly forwards packets from the client to the server, and from the server back to the client.

The real power comes from what you do before forwarding the packets. You can inspect, modify, log, or even block queries.

Let's build a basic, functional proxy from scratch using only Python's built-in socket library. Then, we'll look at more advanced approaches.

The Core Concept: The Proxy Loop

The proxy's main job is to manage two sockets and shuttle data between them.

+----------+      +-----------+      +----------+
| Client   | <---> | Proxy     | <---> | MySQL    |
| (Python  |      | (Our      |      | Server   |
|  App)    |      | Script)   |      |          |
+----------+      +-----------+      +----------+

The logic for each client connection is a loop:

1. Receive data from the client.
2. Send that data to the MySQL server.
3. Receive data from the MySQL server.
4. Send that data back to the client.
5. Repeat until one of the connections is closed.

Building a Basic Proxy from Scratch

This proxy will be transparent. It won't modify any data; it will just forward it. This is a great starting point.

The Code: basic_proxy.py

import socket
import threading
import sys
# --- Configuration ---
PROXY_HOST = '0.0.0.0'  # Listen on all available interfaces
PROXY_PORT = 3307       # A port different from the default MySQL port (3306)
MYSQL_HOST = '127.0.0.1' # The address of your actual MySQL server
MYSQL_PORT = 3306        # The default MySQL port
def handle_client(client_socket):
    """
    Handles a single client connection by forwarding traffic to the MySQL server.
    """
    print(f"[+] Accepted connection from {client_socket.getpeername()}")
    # Connect to the actual MySQL server
    try:
        mysql_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        mysql_socket.connect((MYSQL_HOST, MYSQL_PORT))
        print(f"[+] Connected to MySQL server at {MYSQL_HOST}:{MYSQL_PORT}")
    except Exception as e:
        print(f"[-] Failed to connect to MySQL server: {e}")
        client_socket.close()
        return
    try:
        # Start the forwarding loop
        while True:
            # Receive data from the client
            client_data = client_socket.recv(4096)
            if not client_data:
                print("[+] Client closed the connection.")
                break
            # Forward data to the MySQL server
            mysql_socket.sendall(client_data)
            # Receive data from the MySQL server
            server_data = mysql_socket.recv(4096)
            if not server_data:
                print("[+] MySQL server closed the connection.")
                break
            # Forward data back to the client
            client_socket.sendall(server_data)
    except Exception as e:
        print(f"[!] An error occurred during forwarding: {e}")
    finally:
        # Clean up sockets
        print("[*] Closing connections.")
        client_socket.close()
        mysql_socket.close()
def start_proxy():
    """
    Starts the proxy server, listening for client connections.
    """
    server = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    server.bind((PROXY_HOST, PROXY_PORT))
    server.listen(5)
    print(f"[*] Proxy server listening on {PROXY_HOST}:{PROXY_PORT}")
    try:
        while True:
            client_socket, addr = server.accept()
            # Handle each client in a new thread
            client_handler = threading.Thread(target=handle_client, args=(client_socket,))
            client_handler.start()
    except KeyboardInterrupt:
        print("\n[*] Server shutting down.")
    finally:
        server.close()
if __name__ == "__main__":
    start_proxy()

How to Run It:

1. Prerequisites: You need a MySQL server running (e.g., on your local machine at 0.0.1:3306).

2. Save the code: Save the code above as basic_proxy.py.

   
   （图片来源网络，侵删）

3. Run the proxy:

   python basic_proxy.py

   You should see: [*] Proxy server listening on 0.0.0.0:3307

4. Connect your application to the proxy: Configure your Python application (or any MySQL client) to connect to the proxy instead of the direct server.

   • Host: 0.0.1 (or localhost)
   • Port: 3307 (the port the proxy is listening on)

   Example using mysql-connector-python:

   import mysql.connector
   # Connect to the PROXY, not the direct server
   db = mysql.connector.connect(
       host="127.0.0.1",
       port=3307,  # <-- Proxy port
       user="your_mysql_user",
       password="your_mysql_password",
       database="your_database"
   )
   cursor = db.cursor()
   cursor.execute("SELECT VERSION()")
   version = cursor.fetchone()
   print(f"MySQL Version (via proxy): {version[0]}")
   db.close()

Level Up: A Query-Inspecting Proxy

Now for the fun part! Let's modify the proxy to inspect the queries. We'll use the mysql-connector-python library on the proxy side to parse the MySQL protocol packets into a readable format.

This allows us to see the exact query being executed.

The Code: inspecting_proxy.py

import socket
import threading
import sys
import mysql.connector
from mysql.connector import errorcode
# --- Configuration ---
PROXY_HOST = '0.0.0.0'
PROXY_PORT = 3308
MYSQL_HOST = '127.0.0.1'
MYSQL_PORT = 3306
def inspect_query(packet):
    """
    Attempts to parse a MySQL packet to extract the query string.
    This is a simplified approach and might not work for all packets.
    """
    try:
        # Use mysql-connector to parse the packet
        # This is much more robust than manual parsing
        proto = mysql.connector.protocol.Protocol()
        proto.parse(packet)
        # We are interested in the 'query' message type
        if proto.messages and isinstance(proto.messages[0], mysql.connector.connection.MySQLQuery):
            query = proto.messages[0].query
            return query.strip()
    except Exception as e:
        # Parsing can fail for many reasons (handshake, auth, etc.)
        # print(f"Could not parse packet: {e}")
        return None
    return None
def handle_client(client_socket):
    print(f"[+] Accepted connection from {client_socket.getpeername()}")
    try:
        mysql_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        mysql_socket.connect((MYSQL_HOST, MYSQL_PORT))
        print(f"[+] Connected to MySQL server.")
    except Exception as e:
        print(f"[-] Failed to connect to MySQL server: {e}")
        client_socket.close()
        return
    try:
        while True:
            # 1. Receive from Client
            client_data = client_socket.recv(4096)
            if not client_data:
                print("[+] Client closed connection.")
                break
            # 2. Inspect Client Data (Query)
            query = inspect_query(client_data)
            if query:
                print(f"\n--- [QUERY INSPECTOR] ---")
                print(f"Executing Query: {query}")
                print(f"--- [/QUERY INSPECTOR] ---\n")
            # 3. Forward to Server
            mysql_socket.sendall(client_data)
            # 4. Receive from Server
            server_data = mysql_socket.recv(4096)
            if not server_data:
                print("[+] MySQL server closed connection.")
                break
            # 5. Forward back to Client
            client_socket.sendall(server_data)
    except Exception as e:
        print(f"[!] An error occurred: {e}")
    finally:
        print("[*] Closing connections.")
        client_socket.close()
        mysql_socket.close()
def start_proxy():
    server = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    server.bind((PROXY_HOST, PROXY_PORT))
    server.listen(5)
    print(f"[*] Inspecting Proxy server listening on {PROXY_HOST}:{PROXY_PORT}")
    try:
        while True:
            client_socket, addr = server.accept()
            client_handler = threading.Thread(target=handle_client, args=(client_socket,))
            client_handler.start()
    except KeyboardInterrupt:
        print("\n[*] Server shutting down.")
    finally:
        server.close()
if __name__ == "__main__":
    # You need to install mysql-connector-python for this version
    # pip install mysql-connector-python
    start_proxy()

How to Run It:

1. Install the library:

   pip install mysql-connector-python

2. Run the proxy:

   python inspecting_proxy.py

   Output: [*] Inspecting Proxy server listening on 0.0.0.0:3308

3. Connect your application to the new proxy port (3308).

Now, when you run your application, the proxy's console will print out the queries as they are executed.

Advanced Proxy Features & Production-Ready Solutions

Building a robust, high-performance proxy is complex. Handling connection pooling, SSL, the full MySQL protocol, and preventing memory leaks from large result sets is non-trivial.

For production use, consider these established solutions:

SQLProxy (SQLProxy)

This is a powerful, open-source proxy written in Python. It's designed for high performance and includes many useful features out of the box.

• GitHub: https://github.com/julien-duponchelle/python-sql-proxy
• Features:
  • Query logging and analysis.
  • Query rewriting and blocking.
  • Performance metrics.
  • Load balancing.
  • Can be extended with custom Python scripts.

ProxySQL

While not written in Python, ProxySQL is the industry-standard for high-performance MySQL proxying. It's incredibly fast and feature-rich. You can configure it using a simple web interface or its SQL-like admin interface.

• Website: https://proxysql.com/
• Use Case: When you need raw performance, read/write splitting, query caching, and advanced routing, ProxySQL is the go-to choice. You can even use external scripts (including Python) to make routing decisions.

Galera Cluster

For high availability, solutions like Galera Cluster (e.g., Percona XtraDB Cluster) use a proxy-like mechanism to manage traffic across multiple database nodes, automatically handling failover.

Summary