MySQL read/write splitting with ProxySQL: hostgroups, query rules, lag-aware routing and multiplexing traps

#Goal

Send writes to the primary and safe reads to replicas without changing application code, take replicas out of rotation when they lag, and follow the writer automatically after a failover. ProxySQL does this with hostgroups, a read_only monitor and query rules. The hard part is not the routing; it is knowing which reads are safe to move and which session features break connection multiplexing.

Use 2.6.2 or later if your users authenticate with caching_sha2_password (the MySQL 8.x default); older 2.x releases only handle it on the backend side.

#Architecture

• Hostgroup 10: writer. Hostgroup 20: readers.
• mysql_replication_hostgroups maps the pair. ProxySQL's monitor checks read_only on each backend and moves servers between 10 and 20 itself.
• Your failover tool (Orchestrator, MySQL Shell, a script) promotes a replica and sets read_only=OFF on it. ProxySQL follows; it does not perform failover.
• Run at least two ProxySQL instances (one per app host, or a pair behind a load balancer) with ProxySQL Cluster or config management keeping them in sync.

#Step 1: monitoring user on MySQL

CREATE USER 'proxysql_mon'@'10.0.%' IDENTIFIED BY 'use-a-secret-store';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'proxysql_mon'@'10.0.%';

REPLICATION CLIENT lets the monitor read replica status for lag checks. Every replica must have read_only=ON and super_read_only=ON in its config file so a restarted replica never comes up writable.

#Step 2: servers and hostgroups

Connect to the admin interface (port 6032 by default) and configure:

-- mysql -h 127.0.0.1 -P 6032 -u admin -p
UPDATE global_variables SET variable_value = 'proxysql_mon'
 WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = 'use-a-secret-store'
 WHERE variable_name = 'mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_replication_lag, max_connections, comment)
VALUES (10, 'db1.internal', 3306, 1000, 0, 500, 'primary'),
       (20, 'db2.internal', 3306, 1000, 10, 500, 'replica'),
       (20, 'db3.internal', 3306, 1000, 10, 500, 'replica');

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES (10, 20, 'read_only', 'main cluster');

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

max_replication_lag = 10 shuns a reader when its reported lag exceeds 10 seconds and brings it back when it catches up. 0 disables the check. The monitor password must be stored in clear text in ProxySQL.

Every change follows the same pattern: edit the main tables, LOAD ... TO RUNTIME to apply, SAVE ... TO DISK to persist. Forgetting SAVE is the most common reason config disappears after a restart.

Check what the monitor sees:

SELECT hostname, port, read_only, error
FROM   monitor.mysql_server_read_only_log
ORDER  BY time_start_us DESC LIMIT 10;

SELECT hostname, port, repl_lag, error
FROM   monitor.mysql_server_replication_lag_log
ORDER  BY time_start_us DESC LIMIT 10;

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

#Step 3: application users

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent, active)
VALUES ('app', 'app-password', 10, 1, 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

• default_hostgroup = 10 means anything without a matching rule goes to the writer. This is the safe default.
• transaction_persistent = 1 keeps every statement inside an explicit transaction on the hostgroup where it started. Leave it on.

#Step 4: route reads by digest, not by regex on SELECT

The tempting rule ^SELECT → readers breaks applications: SELECT ... FOR UPDATE, reads immediately after a write, and reads inside stored procedures all move to a replica. Start with everything on the writer, then move specific, proven-safe queries.

Find the top read digests:

SELECT digest, SUBSTR(digest_text, 1, 80) AS query, count_star,
       sum_time / 1000 AS total_ms, hostgroup
FROM   stats_mysql_query_digest
WHERE  digest_text LIKE 'SELECT%'
ORDER  BY sum_time DESC
LIMIT  20;

Review each with the owning team: can this query tolerate up to max_replication_lag seconds of staleness? Route the approved ones:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply, comment)
VALUES (100, 1, '^SELECT .* FOR UPDATE', 10, 1, 'locking reads stay on writer');

INSERT INTO mysql_query_rules (rule_id, active, digest, destination_hostgroup, apply, comment)
VALUES (200, 1, '0x3B2A9F1C7D5E4A10', 20, 1, 'product catalogue listing');

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

match_digest is a regex against the normalised query text; the digest column matches one exact query fingerprint (the value above is an example; take yours from stats_mysql_query_digest). Rules are evaluated in rule_id order and apply = 1 stops processing. Put the "stay on writer" rules first.

Confirm rules are hit:

SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;

#Step 5: understand multiplexing

ProxySQL reuses backend connections across client sessions. Some statements tie a client to one backend connection, which disables multiplexing for that session and can exhaust backend connections:

Watch the effect:

SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR, Queries, Latency_us
FROM   stats_mysql_connection_pool
ORDER  BY hostgroup, srv_host;

ConnUsed close to max_connections with modest query rates means sessions are pinned. Find which client patterns cause it and fix them in the app (for example, replace temp tables with CTEs).

Warning

Read-after-write inconsistency is the main production risk. A user saves a form, the next page read goes to a replica that is 2 seconds behind, and the change looks lost. Route those reads to the writer, or keep them inside the write transaction.

#Step 6: test failover

1. Note runtime_mysql_servers.
2. Promote db2 with your failover tool; make sure it sets read_only=OFF on db2 and read_only=ON on the old primary (if it is reachable).
3. Within a few monitor intervals db2 should appear in hostgroup 10. Check monitor.mysql_server_read_only_log.
4. Run a write through port 6033 and confirm it lands on db2 (SELECT @@hostname; inside a transaction).

Caution

If two backends report read_only=OFF, ProxySQL puts both in the writer hostgroup and spreads writes across them. That is split brain. The failover tool must fence the old primary; ProxySQL cannot.

#Prevent problems next time

• Keep super_read_only=ON in every replica's config; it is the signal ProxySQL trusts.
• Alert on servers with status SHUNNED or OFFLINE_SOFT in runtime_mysql_servers for more than a few minutes.
• Review stats_mysql_query_digest monthly for new heavy reads worth moving, and reset it with SELECT * FROM stats_mysql_query_digest_reset; after reviewing.
• Store ProxySQL config in version control and apply it with automation; avoid ad hoc admin edits that differ between instances.
• Load test with the real application before go-live; multiplexing problems only show under realistic concurrency.