Split MySQL Reads and Writes with ProxySQL (2026)

ProxySQL is the secret sauce that lets you scale MySQL reads and writes independently, not by magically making MySQL faster, but by intelligently routing traffic to multiple MySQL instances.

Let’s see it in action. Imagine you have a single MySQL server, and your application is pounding it with both reads and writes. As traffic grows, writes start to block reads, and reads start to hog resources, making everything slow.

Here’s a basic ProxySQL setup:

+-----------------+      +---------------+      +-----------------+
|   Application   |----->|   ProxySQL    |<---->| MySQL Master    |
+-----------------+      +---------------+      +-----------------+
                               |
                               |      +-----------------+
                               +----->| MySQL Read Slave|
                                      +-----------------+

In this setup, ProxySQL intercepts all incoming MySQL connections. It’s configured with rules that say: "If the query is a SELECT (or anything that looks like a read), send it to the read slave. If it’s an INSERT, UPDATE, or DELETE (or anything that looks like a write), send it to the master."

The magic happens in ProxySQL’s configuration. You define "hostgroups" which are just collections of MySQL servers.

Hostgroup 10:

• mysql_hostgroup_id: 10
• mysql_host: 192.168.1.100:3306 (Your Master)
• mysql_user: proxyuser
• mysql_password: proxypass
• weight: 1000 (Higher weight for writes, usually)
• max_connections: 2000

Hostgroup 20:

• mysql_hostgroup_id: 20
• mysql_host: 192.168.1.101:3306 (Your Read Slave)
• mysql_user: proxyuser
• mysql_password: proxypass
• weight: 100 (Lower weight for reads, as we might have multiple slaves)
• max_connections: 2000

Then, you define "query rules" to tell ProxySQL how to route.

Query Rule 1 (Writes):

• rule_id: 1
• active: 1
• rule_type: MATCH_SQLSMT
• pattern: (?i)^(INSERT|UPDATE|DELETE|REPLACE|TRUNCATE)
• destination_hostgroup: 10 (Send to the Master)

Query Rule 2 (Reads):

• rule_id: 2
• active: 1
• rule_type: MATCH_SQLSMT
• pattern: (?i)^SELECT
• destination_hostgroup: 20 (Send to the Read Slave)

When your application connects to ProxySQL_IP:3306, ProxySQL receives the query. It checks rule_id=1. If it’s a write, it sends it to Hostgroup 10 (the master). If it’s not a write, it checks rule_id=2. If it’s a SELECT, it sends it to Hostgroup 20 (the read slave). This way, reads and writes are handled by separate sets of servers, preventing them from blocking each other.

The real power comes when you add more read replicas. You just add more servers to Hostgroup 20. ProxySQL will load balance the read queries across all servers in that hostgroup.

One thing that trips people up is how ProxySQL handles transactions. By default, if a transaction starts on a read slave (because of a SELECT in the middle of a transaction), and then a write occurs, ProxySQL will automatically reroute that entire transaction to the master. This is crucial to maintain data consistency and avoid split-brain scenarios where a read slave might have stale data by the time a write needs to happen. You don’t have to do anything special in your application for this to work; ProxySQL handles the transaction management based on the query type and its internal state.

The next step is to explore how ProxySQL can automatically failover to a healthy replica when a primary instance goes down.