ProxySQL MySQL MySQL · DBA · Beginner

ProxySQL — Introduction & Architecture

Understand what ProxySQL is, how it works, its architecture, key components, and why it is used with MySQL in production environments.

What is ProxySQL

ProxySQL is a high-performance, open-source MySQL proxy that sits between your application and MySQL servers. It understands the MySQL protocol and acts as an intelligent middleware — routing queries, load balancing connections, enforcing rules, and caching results — all transparently to your application.

It was created by René Cannaò and is widely used in production environments at companies like GitHub, Booking.com, and Percona. ProxySQL does not store data itself — it only proxies and manages traffic to real MySQL backends.

Architecture

ProxySQL sits between the application layer and MySQL backend servers:

  [ Application / App Servers ]
           |
           ▼
     [ ProxySQL :6033 ]          ← MySQL Protocol Port (clients connect here)
     [ Admin    :6032 ]          ← Admin Interface (DBA manages ProxySQL here)
           |
     ┌─────┴──────┐
     ▼            ▼
[ MySQL Master ] [ MySQL Replica ]
  (Hostgroup 10)  (Hostgroup 20)

Key Components

MySQL Port (6033): Application connects to this port using standard MySQL client/driver.
Admin Port (6032): DBA connects here to manage ProxySQL using SQL commands.
Hostgroups: Logical groups of MySQL backend servers (e.g. writers = 10, readers = 20).
Query Rules: Regex-based rules that route queries to the correct hostgroup.
MySQL Users: ProxySQL maintains its own user table to authenticate and route connections.
Connection Pool: ProxySQL multiplexes thousands of application connections into a smaller pool of backend connections.

Three-Layer Configuration

ProxySQL uses three configuration layers. Changes flow from Runtime → Memory → Disk:

Layer	Description	Persistent?
RUNTIME	Currently active config used by ProxySQL threads	No — lost on restart
MEMORY	Staging area — changes made here first via Admin interface	No — lost on restart
DISK	SQLite database on disk — survives restarts	Yes

💡 Note: Always run LOAD ... TO RUNTIME and SAVE ... TO DISK after making changes so they take effect and persist.

Why Use ProxySQL with MySQL?

Read/Write Splitting: Automatically send SELECT to replicas, writes to master.
Load Balancing: Distribute queries across multiple MySQL servers.
Connection Pooling: Reduce connection overhead on MySQL backend.
Query Routing: Route specific queries to specific servers based on regex rules.
Query Caching: Cache query results in memory with configurable TTL.
Query Rewriting: Modify queries on the fly before sending to backend.
Failover: Automatically remove unhealthy servers from rotation.
Query Firewall: Block or flag dangerous queries.
Traffic Mirroring: Mirror production traffic to a test server.
Multiplexing: 10,000 app connections → handled with far fewer backend connections.

ProxySQL vs MySQL Router vs HAProxy

Feature	ProxySQL	MySQL Router	HAProxy
MySQL Protocol Aware	✓ Yes	✓ Yes	✗ No
Query Routing by Regex	✓ Yes	✗ No	✗ No
Connection Pooling	✓ Advanced	Limited	✗ No
Query Cache	✓ Yes	✗ No	✗ No
Query Rewriting	✓ Yes	✗ No	✗ No
Admin Interface	SQL-based	Config file	Web UI
Performance	Very High	High	High (TCP level)

Default Ports

Port	Purpose
6033	MySQL client connections (applications connect here)
6032	ProxySQL Admin interface (management)
6070	REST API / Prometheus metrics endpoint