Configuring MySQL Read/Write Splitting with ProxySQL: Offload Read Queries Without Touching Application Code - ITFROMZERO - Share tobe shared! MySQL Master hitting 100% CPU while replicas sit idle? A step-by-step guide to configuring automatic Read/Write Splitting with ProxySQL to save production without touching application code.

Table of Contents

2:15 AM and the PagerDuty Alarm: When Replicas Are Just for “Show”

At 2:15 AM, the phone vibrated violently with a PagerDuty alert. The Grafana dashboard was glowing bright red: the MySQL Master node’s CPU hit a 100% ceiling, and Threads_running surged from 15 to over 350. The API gateway began spitting out mass 504 Gateway Timeout errors. Meanwhile, two 8-core 32GB RAM Replica nodes sitting alongside were practically idling, with CPU usage below 3%.

This scenario is all too familiar. The Master-Slave replication cluster was configured textbook-perfect. Yet, every microservice still pointed to a single Master connection string. Every single read and write query piled onto one instance. Without implementing Read/Write Splitting overnight, the upcoming 9:00 AM flash sale would certainly crash and burn.

Weighing Three Approaches to Read/Write Splitting

There are typically three ways to tackle this problem:

1. Splitting Connection Pools at the Application Layer (Application Routing)

The backend defines two separate DataSources: db_master for writes and db_replica for reads. Developers must manually wrap queries or configure annotations (such as @Transactional(readOnly = true) in Spring Boot or read-replica pools in Laravel/Django).

2. Using HAProxy with Port Splitting

Set up HAProxy with port 3306 forwarding to the Master and port 3307 load-balancing across the Replica cluster. The application still has to manage the logic to connect to the appropriate port.

3. Placing an Intelligent Database Proxy (ProxySQL) in Between

ProxySQL acts as a single point of entry (port 6033). It parses SQL queries in real time (Layer 7): routing SELECT statements to Replicas while directing INSERT/UPDATE statements to the Master. The application remains completely oblivious to any underlying architectural changes.

Why ProxySQL Wins in Production Firefighting

Each approach has its merits depending on system maturity:

Application code changes: The ideal long-term architectural solution. However, ripping open 15 microservices in the middle of the night to reconfigure connection pools, audit transaction contexts, and deploy is suicide. Miss just one read-your-own-writes query inside a transaction, and you’ll immediately introduce data inconsistency.
HAProxy: Extremely lightweight and battle-tested for throughput (Layer 4). Unfortunately, HAProxy cannot inspect SQL payload contents. It cannot distinguish between a SELECT and an UPDATE, shifting the entire routing burden back onto the application.
ProxySQL: Understands the MySQL wire protocol deeply at Layer 7. Beyond automatic regex-based routing, ProxySQL features powerful connection pooling, in-memory query caching, and replication lag monitoring. Best of all: the application only needs a single-line update to its host and port.

Architecture Overview

The reference infrastructure for this guide consists of three nodes:

MySQL Master (Writer): 192.168.1.10:3306
MySQL Replica (Reader): 192.168.1.11:3306
ProxySQL Node: 192.168.1.5 (Client port: 6033, Admin port: 6032)

Step-by-Step Implementation of ProxySQL Read/Write Splitting

Step 1: Install ProxySQL on the Intermediate Server

Install ProxySQL 2.5.x on an Ubuntu/Debian node:

sudo apt-get update
sudo apt-get install -y lsb-release curl apt-transport-https
curl -fsSL https://repo.proxysql.com/repo/proxysql.repo.gpg | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/proxysql.gpg
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt-get update
sudo apt-get install -y proxysql
sudo systemctl enable --now proxysql

Step 2: Create a Monitoring User on the MySQL Master

ProxySQL requires a dedicated user for health checks and tracking replication lag. Run the following commands on the Master (they will automatically replicate to the Replica):

CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonitorPass123!';
GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

Step 3: Configure Backend Servers into Hostgroups in ProxySQL

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

Divide servers into two pools: Hostgroup 10 for Writers (Master) and Hostgroup 20 for Readers (Replica):

-- 1. Configure monitoring user
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='MonitorPass123!' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- 2. Add database nodes to hostgroups
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, weight, max_replication_lag) VALUES 
(10, '192.168.1.10', 3306, 1000, 1, 0),
(20, '192.168.1.11', 3306, 1000, 1, 10);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

The max_replication_lag = 10 parameter is invaluable. If a Replica falls more than 10 seconds behind the Master, ProxySQL immediately stops routing read traffic to that node to prevent stale reads.

Step 4: Sync Application Users into ProxySQL

Define the credentials that your backend uses to connect to the database:

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) 
VALUES ('app_user', 'SecretAppPass', 10, 1);

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

The transaction_persistent = 1 flag is critical. It guarantees that any query (even a SELECT) executed within an open transaction (BEGIN ... COMMIT) is pinned to the Master. This completely avoids dirty reads or race conditions caused by replication delay.

Step 5: Configure Automatic Query Routing Rules

This is the core of the routing mechanism. The rule priority (rule_id) determines data consistency:

-- Rule 1: Keep SELECT ... FOR UPDATE on Master for safe row locking
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1);

-- Rule 2: Route all remaining SELECT statements to Replicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT', 20, 1);

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

The apply = 1 attribute in Rule 1 is essential: when a query matches the FOR UPDATE pattern, ProxySQL immediately assigns it to Hostgroup 10 and stops evaluating subsequent rules. This prevents the query from accidentally being routed to a Replica by Rule 2.

Step 6: Verification and Load Inspection

Update the connection string on your backend from the old Master IP to 192.168.1.5:6033. Open the ProxySQL admin interface to verify how queries are routed:

SELECT hostgroup, schemaname, digest_text, count_star 
FROM stats_mysql_query_digest 
ORDER BY count_star DESC LIMIT 10;

Under the hostgroup column, SELECT statements are evenly distributed to Hostgroup 20, while INSERT/UPDATE queries go straight to Hostgroup 10. Roughly 75% of read traffic was offloaded to the Replica nodes. The Master node’s CPU cooled down rapidly from 100% to around 25%, and Threads_running dropped back to a safe 18 threads. System stability was restored without modifying a single line of application code.