Read/Write Split — Complete Setup
This page walks through a complete, production-ready Read/Write split configuration with ProxySQL and MySQL replication. By the end, all SELECT queries go to replicas and all writes (INSERT/UPDATE/DELETE) go to the master — automatically.
Environment
| Server | IP | Role | ProxySQL Hostgroup |
|---|---|---|---|
| proxysql | 192.168.1.10 | ProxySQL | — |
| mysql-master | 192.168.1.100 | MySQL Master | 10 (Writers) |
| mysql-replica1 | 192.168.1.101 | MySQL Replica | 20 (Readers) |
| mysql-replica2 | 192.168.1.102 | MySQL Replica | 20 (Readers) |
Step 1 — Create Monitor User in MySQL
Create this user on the MySQL Master — it replicates to replicas automatically.
SQL — Create Monitor User (MySQL Master)
-- Run on MySQL Master
CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonitorPass123!';
GRANT SELECT, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES; Step 2 — Create App User in MySQL
SQL — Create App User (MySQL Master)
-- Run on MySQL Master
CREATE USER 'appuser'@'%' IDENTIFIED BY 'AppPass123!';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES; Step 3 — Configure ProxySQL Monitor
SQL — Configure Monitor (ProxySQL Admin)
-- Connect to ProxySQL Admin: mysql -u admin -padmin -h 127.0.0.1 -P 6032
-- Set monitor credentials
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';
-- Set monitor intervals
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_connect_interval';
UPDATE global_variables SET variable_value='1000' WHERE variable_name='mysql-monitor_ping_interval';
UPDATE global_variables SET variable_value='1500' WHERE variable_name='mysql-monitor_read_only_interval';
UPDATE global_variables SET variable_value='500' WHERE variable_name='mysql-monitor_read_only_timeout';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK; Step 4 — Add Backend Servers
SQL — Add Backend Servers
-- Add MySQL Master to writer hostgroup
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, max_connections, comment)
VALUES (10, '192.168.1.100', 3306, 'ONLINE', 1, 500, 'MySQL Master');
-- Add Replicas to reader hostgroup
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, max_connections, comment)
VALUES (20, '192.168.1.101', 3306, 'ONLINE', 1, 500, 'MySQL Replica 1');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, max_connections, comment)
VALUES (20, '192.168.1.102', 3306, 'ONLINE', 1, 500, 'MySQL Replica 2');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK; Step 5 — Configure Replication Hostgroups
SQL — Replication Hostgroups
-- Tell ProxySQL: HG10 = writers, HG20 = readers
-- ProxySQL will auto-move servers based on read_only variable
INSERT INTO mysql_replication_hostgroups
(writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES (10, 20, 'read_only', 'Master-Replica R/W Split');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK; Step 6 — Add App User to ProxySQL
SQL — Add App User
INSERT INTO mysql_users
(username, password, default_hostgroup, active, transaction_persistent)
VALUES
('appuser', 'AppPass123!', 10, 1, 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; Step 7 — Add Query Routing Rules
SQL — Query Routing Rules
-- Rule 1: SELECT FOR UPDATE → Master (consistency for locking reads)
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 → Master');
-- Rule 2: All other SELECTs → Replicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES (2, 1, '^SELECT', 20, 1, 'All SELECTs → Replicas');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Step 8 — Verify Everything Works
SQL — Verify Setup
-- Test: connect as app user through ProxySQL
-- mysql -u appuser -pAppPass123! -h 192.168.1.10 -P 6033
-- Run on ProxySQL Admin to see routing in action
SELECT hostgroup, digest_text, count_star
FROM stats.stats_mysql_query_digest
ORDER BY count_star DESC;
-- Check connection pool
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, Queries
FROM stats.stats_mysql_connection_pool;
-- Check server health
SELECT hostname, port, time_start_us, connect_success, ping_success
FROM monitor.mysql_server_connect_log
ORDER BY time_start_us DESC LIMIT 10;
-- Verify read_only detection
SELECT hostname, port, read_only
FROM monitor.mysql_server_read_only_log
ORDER BY time_start_us DESC LIMIT 10;✓ At this point, all SELECTs go to replicas and all writes go to master. ProxySQL automatically handles failover — if a replica goes down, it is removed from the reader hostgroup automatically.
Troubleshooting
| Problem | Check | Fix |
|---|---|---|
| All queries going to master | Query rules not loaded | LOAD MYSQL QUERY RULES TO RUNTIME |
| Replica not receiving traffic | Server status in runtime_mysql_servers | Check monitor logs for errors |
| Authentication failed | User exists in mysql_users | LOAD MYSQL USERS TO RUNTIME |
| Monitor errors | monitor.mysql_server_connect_log | Verify monitor user exists in MySQL |
| Stale reads | max_replication_lag setting | Set max_replication_lag on replica servers |
