ProxySQL: Load Balancing MySQL Traffic

ProxySQL is a high-performance MySQL proxy that sits between your application and your database servers. Unlike a simple TCP load balancer, it understands the MySQL protocol — which means it can make routing decisions based on the actual query, not just the connection. That's what makes read/write splitting, connection multiplexing, and query caching possible without any application changes.

Why ProxySQL

When you have a primary-replica setup, applications typically need to send writes to the primary and reads to one of the replicas. Without a proxy, this routing logic lives in your application or ORM — which creates tight coupling and makes it hard to change your database topology without a code deploy. ProxySQL solves this transparently:

Connection pooling — thousands of application threads share a much smaller pool of backend connections, reducing connection overhead on MySQL significantly.
Read/write splitting — non-locking SELECT statements are automatically routed to replicas; writes and locking reads go to the primary.
Failover awareness — ProxySQL can remove a failed backend and redistribute traffic automatically.
Query rules — route, rewrite, mirror, or block specific queries without touching application code.

Installation

# Debian / Ubuntu
apt install -y lsb-release gnupg2
wget https://github.com/sysown/proxysql/releases/download/v2.6.3/proxysql_2.6.3-debian12_amd64.deb
dpkg -i proxysql_2.6.3-debian12_amd64.deb
systemctl enable --now proxysql

# RHEL / AlmaLinux / Rocky
cat > /etc/yum.repos.d/proxysql.repo << 'EOF'
[proxysql_repo]
name=ProxySQL repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key
EOF
dnf install -y proxysql
systemctl enable --now proxysql

ProxySQL listens on two ports: 6033 for MySQL client connections and 6032 for its admin interface.

The Admin Interface

All ProxySQL configuration is done through its admin interface, which speaks MySQL protocol. Connect with:

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt "ProxySQL> "

Change the default admin credentials immediately in production: UPDATE global_variables SET variable_value='admin:new_password' WHERE variable_name='admin-admin_credentials'; LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK;

Adding Backend Servers

ProxySQL organizes backends into hostgroups. By convention, hostgroup 10 is the writer (primary) and hostgroup 20 is the reader pool (replicas).

-- Add the primary (writer)
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (10, '10.0.1.100', 3306);

-- Add replicas (readers)
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (20, '10.0.1.101', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (20, '10.0.1.102', 3306);

-- Apply and persist
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Read/Write Splitting with Query Rules

Query rules match incoming SQL using regex and route matched queries to a hostgroup. The simplest read/write split sends SELECT statements to readers and everything else to the writer:

-- Route locking reads to the writer (must come first, lower rule_id wins)
-- Covers: SELECT ... FOR UPDATE, SELECT ... FOR SHARE, SELECT ... LOCK IN SHARE MODE
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, re_modifiers, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*(FOR UPDATE|FOR SHARE|LOCK IN SHARE MODE)', 'CASELESS', 10, 1);

-- Route all other SELECTs to the reader pool
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, re_modifiers, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 'CASELESS', 20, 1);

-- Everything else goes to the writer (default_hostgroup handles this)
-- Set the default hostgroup in the mysql_users table

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Create a MySQL user in ProxySQL that maps to a real MySQL account:

INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', 'app_password', 10);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

The default_hostgroup = 10 means non-matched queries (writes, DDL) go to the primary.

Monitoring and Stats

ProxySQL's stats schema gives you real-time visibility into what's happening:

-- Query digest: most frequent queries, latency, error rate
SELECT hostgroup, digest_text, count_star, sum_time/count_star AS avg_us
FROM stats.stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 10;

-- Connection pool health
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, Latency_us
FROM stats.stats_mysql_connection_pool;

-- Which servers are currently UP
SELECT hostgroup_id, hostname, port, status
FROM runtime_mysql_servers;

Conclusion

ProxySQL is one of the most impactful additions you can make to a MySQL architecture. Once it's in place, adding read replicas, performing primary failovers, and tuning connection pools become operational tasks that don't require application changes. Start with basic read/write splitting and connection pooling — those two features alone will improve throughput and reduce load on your primary significantly.

Questions about ProxySQL configuration or a tricky query routing scenario? Reach out.