ProxySQL Read/Write Splitting for MySQL Replication: Complete Setup Guide

When a MySQL primary starts spending 70% of its CPU serving SELECT queries that a replica could answer just as well, that is not a scaling problem — it is a routing problem. ProxySQL solves it transparently: your application connects to one endpoint, sends the same mix of reads and writes it always has, and ProxySQL decides in real time whether each query goes to the primary or a replica. No application code changes, no ORM rewrites, no connection string gymnastics. Getting the configuration right — hostgroups, query rules, lag detection, and connection routing — is the work. This guide covers every piece of it.

ProxySQL Architecture for Read/Write Splitting

ProxySQL sits between application servers and MySQL, operating as a full MySQL protocol proxy. It maintains two pools of connections: one to the primary, one to the replicas. Application servers connect to ProxySQL on port 6033 (by default) using standard MySQL credentials. ProxySQL's admin interface runs on port 6032 and accepts MySQL protocol commands — all configuration is done via SQL INSERT, UPDATE, and LOAD ... TO RUNTIME statements against an in-memory SQLite-backed configuration layer.

The core concept is the hostgroup. A hostgroup is a named pool of MySQL servers that share the same routing role. The writer hostgroup contains only the primary. The reader hostgroup contains the replicas. When ProxySQL evaluates an incoming query, it matches the query against a list of rules and assigns it to a hostgroup. ProxySQL then picks a server from that hostgroup's connection pool, executes the query, and returns the result — all transparent to the application.

The Configuration Layer

ProxySQL uses a three-layer configuration model: the in-memory runtime layer (what is actually running), the in-memory configuration tables (where you make changes), and the disk layer (what persists across restarts). To activate any change, you must run LOAD ... TO RUNTIME. To persist it across a restart, you run SAVE ... TO DISK. Forgetting to save to disk is the most common ProxySQL configuration error in production.

-- After any configuration change:
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

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

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

Configuring Hostgroups (Writer Group vs Reader Group)

Hostgroups for MySQL replication are declared in the mysql_replication_hostgroups table. This table tells ProxySQL which hostgroup is the writer group and which is the reader group. ProxySQL uses this mapping to monitor replication status on each server: it periodically runs SHOW SLAVE STATUS (or SHOW REPLICA STATUS in MySQL 8.0+) on servers in the reader hostgroup to check lag and replication health.

-- Connect to the ProxySQL admin interface (port 6032)
-- mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- Define the writer hostgroup (10) and reader hostgroup (20)
INSERT INTO mysql_replication_hostgroups
  (writer_hostgroup, reader_hostgroup, comment)
VALUES
  (10, 20, 'MySQL primary-replica replication setup');

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

The integers 10 and 20 are arbitrary — they just need to be consistent across your server entries and query rules. Some teams use 1/2, others use 100/200. The values themselves do not matter; what matters is that you use the same integers everywhere.

Adding MySQL Servers to ProxySQL

Servers are registered in the mysql_servers table. Each row represents one MySQL instance. The hostgroup_id column assigns it to a role. The primary goes in hostgroup 10; replicas go in hostgroup 20. You can add multiple replicas — ProxySQL load-balances across them using a round-robin or least-connections algorithm depending on the weight values you assign.

-- Add the primary to the writer hostgroup
INSERT INTO mysql_servers
  (hostgroup_id, hostname, port, weight, max_connections, comment)
VALUES
  (10, '10.0.1.10', 3306, 1000, 200, 'MySQL Primary');

-- Add replica 1 to the reader hostgroup
INSERT INTO mysql_servers
  (hostgroup_id, hostname, port, weight, max_connections, max_replication_lag, comment)
VALUES
  (20, '10.0.1.11', 3306, 1000, 200, 10, 'MySQL Replica 1');

-- Add replica 2 to the reader hostgroup
INSERT INTO mysql_servers
  (hostgroup_id, hostname, port, weight, max_connections, max_replication_lag, comment)
VALUES
  (20, '10.0.1.12', 3306, 1000, 200, 10, 'MySQL Replica 2');

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

Key Column Definitions

• weight: Relative traffic weight for load balancing across servers in the same hostgroup. Equal weights distribute reads evenly. Give a beefier replica a higher weight to send it more traffic.
• max_connections: Maximum number of server-side connections ProxySQL will open to this MySQL instance. Set this below the MySQL instance's max_connections to leave headroom for direct admin access.
• max_replication_lag: Replication lag threshold in seconds. If a replica's Seconds_Behind_Master exceeds this value, ProxySQL marks it SHUNNED and stops routing queries to it until it catches up.

Adding a MySQL Monitoring User

ProxySQL must be able to connect to each MySQL server to run health checks and replication status queries. Create a dedicated monitoring user on MySQL and configure it in ProxySQL:

-- Run on MySQL primary (replicates to replicas):
CREATE USER 'proxysql_monitor'@'%' IDENTIFIED BY 'StrongMonitorPass123!';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'proxysql_monitor'@'%';
FLUSH PRIVILEGES;

-- Run on ProxySQL admin interface:
SET mysql-monitor_username = 'proxysql_monitor';
SET mysql-monitor_password = 'StrongMonitorPass123!';

-- Tuning monitor intervals (milliseconds)
SET mysql-monitor_connect_interval     = 60000;
SET mysql-monitor_ping_interval        = 10000;
SET mysql-monitor_read_only_interval   = 1500;
SET mysql-monitor_replication_lag_interval = 2000;

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Query Rules for Read/Write Routing

Query rules are the heart of read/write splitting. Each rule contains a SQL pattern (regex or exact match) and a destination hostgroup. Rules are evaluated in ascending rule_id order. The first rule that matches a query determines its routing — unless that rule has apply=0, in which case evaluation continues to the next matching rule.

Basic Read/Write Split Rules

-- Rule 1: Route explicit writes to the writer hostgroup
-- Catches INSERT, UPDATE, DELETE, REPLACE, CALL
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES
  (1, 1, '^SELECT.*FOR UPDATE', 10, 1, 'SELECT FOR UPDATE → writer'),
  (2, 1, '^SELECT.*FOR SHARE',  10, 1, 'SELECT FOR SHARE → writer');

-- Rule 2: Route all other SELECT queries to the reader hostgroup
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES
  (3, 1, '^SELECT', 20, 1, 'Generic SELECT → readers');

-- Rule 3: Route everything else (INSERT, UPDATE, DELETE, etc.) to the writer
-- This is the default_hostgroup fallback — see mysql_users below
-- But an explicit rule makes the intent clear:
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES
  (4, 1, '.*', 10, 1, 'All non-SELECT → writer (catch-all)');

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

Default Hostgroup via mysql_users

Every ProxySQL user entry has a default_hostgroup. Any query that does not match any query rule is routed to this hostgroup. Set it to the writer hostgroup as a safe default — this ensures writes always land on the primary even if your query rules have a gap.

-- Add the application user (must exist on MySQL too)
INSERT INTO mysql_users
  (username, password, default_hostgroup, transaction_persistent, comment)
VALUES
  ('appuser', 'AppUserPass456!', 10, 1, 'Application user — default to writer');

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

The transaction_persistent=1 setting is critical: it ensures that once a connection enters a transaction (after a BEGIN or START TRANSACTION), all subsequent queries within that transaction go to the same server — even queries that would otherwise match the reader rule. Without this, a SELECT inside a transaction could be routed to a replica and read data that has not yet replicated from the primary, causing logic errors and phantom reads.

Handling Stored Procedures and Multi-Statement Queries

Stored procedure calls and queries containing semicolons (multi-statement) should always go to the writer hostgroup. Add explicit rules for these:

INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES
  (5, 1, '^CALL',  10, 1, 'Stored procedures → writer'),
  (6, 1, ';',      10, 1, 'Multi-statement queries → writer');

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

Replica Lag Detection and Routing

ProxySQL's monitor thread continuously checks replication lag on every server in the reader hostgroup. When a replica's Seconds_Behind_Master exceeds the max_replication_lag threshold you configured in mysql_servers, ProxySQL changes that server's status from ONLINE to SHUNNED and stops routing queries to it. When the replica catches up and falls back below the threshold, it is automatically reinstated. This is the mechanism that prevents stale reads without any application-level awareness of replication lag.

Verifying Lag Detection is Working

-- Check the current status of all monitored servers
SELECT hostgroup_id, hostname, port, status, weight, max_replication_lag
FROM mysql_servers;

-- Check the monitor log for replication lag readings
SELECT hostname, port, time_start_us, replication_lag, error
FROM monitor.mysql_server_replication_lag_log
ORDER BY time_start_us DESC
LIMIT 20;

-- See which servers are currently SHUNNED due to lag
SELECT * FROM runtime_mysql_servers
WHERE status = 'SHUNNED';

Tuning the Lag Threshold

The right value for max_replication_lag depends on your application's tolerance for stale reads. A value of 5 seconds is appropriate for most OLTP workloads where eventual consistency within a few seconds is acceptable. Lower it to 1–2 seconds if you route reads to replicas but frequently query data that was just written. Set it to 0 to disable lag-based shunning entirely — but only do this if your replicas are typically at sub-second lag and you have alerting in place for lag spikes.

-- Update lag threshold live (no restart required)
UPDATE mysql_servers
SET max_replication_lag = 5
WHERE hostgroup_id = 20;

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

What Happens When All Replicas Are Lagging

If every server in the reader hostgroup is simultaneously shunned due to lag, ProxySQL has no healthy server to route read queries to. In this scenario, ProxySQL falls back to the writer hostgroup for those queries — the primary absorbs the read traffic until replicas recover. This fallback behavior is automatic and requires no configuration. It means your application continues to function correctly during a replication lag spike, at the cost of increased load on the primary.

Testing and Verifying the Setup

Verification should happen in three stages: confirm that ProxySQL can see all servers as healthy, confirm that individual queries route to the expected hostgroup, and confirm that traffic splits as expected under load.

Stage 1: Check Server Health

-- Connect to ProxySQL admin (port 6032)
SELECT hostgroup_id, hostname, port, status, ConnUsed, ConnFree, ConnOK, ConnERR
FROM stats_mysql_connection_pool
ORDER BY hostgroup_id, hostname;

All servers should show ONLINE status. ConnERR > 0 indicates connection failures — check the MySQL user credentials and network connectivity. ConnUsed + ConnFree shows total connections opened per server; this should grow as load is applied.

Stage 2: Verify Query Routing

-- Connect to ProxySQL on port 6033 as the application user
-- mysql -u appuser -pAppUserPass456! -h 127.0.0.1 -P 6033

-- This SELECT should be routed to a replica (reader hostgroup)
-- Each replica has a different @@hostname — run this several times
-- to confirm round-robin across replicas
SELECT @@hostname, @@server_id;

-- This should always return the primary's hostname (writer hostgroup)
SELECT @@hostname FROM DUAL WHERE 1=0 FOR UPDATE;

-- Writes always go to the primary
CREATE TABLE IF NOT EXISTS proxysql_test (id INT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO proxysql_test (id) VALUES (1);
-- The INSERT should route to the primary

Stage 3: Verify Transaction Stickiness

-- Confirm that SELECT inside a transaction goes to the writer
START TRANSACTION;
SELECT @@hostname;  -- Should return primary hostname, not a replica
INSERT INTO proxysql_test (id) VALUES (2);
SELECT @@hostname;  -- Still primary — transaction_persistent is working
COMMIT;

Monitoring with stats_mysql_query_digest

ProxySQL records every query that passes through it in the stats_mysql_query_digest table. This table is invaluable for verifying that read/write splitting is working, identifying which queries consume the most server time, and confirming that the reader and writer hostgroups are receiving the expected traffic mix.

-- See query routing distribution across hostgroups
SELECT hostgroup, digest_text,
       count_star      AS executions,
       sum_time / 1000 AS total_ms,
       min_time / 1000 AS min_ms,
       max_time / 1000 AS max_ms,
       sum_rows_sent   AS rows_returned
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 20;

Look at the hostgroup column: 10 means the query was routed to the writer; 20 means it went to a replica. If you see a large volume of SELECT queries in hostgroup 10 instead of 20, a query rule is not matching as expected. Compare the digest_text against your rule patterns to find the gap.

Identifying Queries That Are Not Being Split

-- Find SELECT queries that are routing to the writer instead of readers
SELECT hostgroup, digest_text, count_star
FROM stats_mysql_query_digest
WHERE hostgroup = 10
  AND digest_text LIKE 'SELECT%'
  AND digest_text NOT LIKE '%FOR UPDATE%'
  AND digest_text NOT LIKE '%FOR SHARE%'
ORDER BY count_star DESC
LIMIT 10;

Common causes for misrouted SELECTs include queries beginning with a comment (/* ... */ SELECT does not match ^SELECT), queries using multi-line formatting, or queries that begin with a whitespace character that the regex does not account for. Fix these by updating the match pattern in the relevant query rule or adding a new rule with higher priority.

-- Handle SELECTs prefixed with comments (common with ORMs)
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES
  (2, 1, '^(/\*.*?\*/\s*)?SELECT', 20, 1, 'SELECT with optional comment → readers');

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

Resetting the Query Digest

-- Clear accumulated stats to get a fresh measurement window
SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;

Working with JusDB on ProxySQL and MySQL Replication

JusDB configures ProxySQL read/write splitting for MySQL teams who need to scale reads without application changes. Our DBAs handle hostgroup design, query rule tuning, and lag-aware routing — so your replicas are fully utilized and your primaries aren't overloaded.

Explore JusDB MySQL Management →  |  Talk to a DBA

Related reading:

• ProxySQL: MySQL Load Balancing Guide
• MySQL Replication Lag: Causes and Fixes
• Read Replicas vs Caching: When to Use Each