ProxySQL sits between your application and MySQL, routing queries intelligently based on rules you define. Here is how to configure it for read/write splitting, query mirroring, and connection pooling.
Why ProxySQL?
- Transparent read/write splitting without application changes
- Connection multiplexing — thousands of app connections map to tens of MySQL connections
- Query caching and rewriting
- Seamless failover integration with Orchestrator
Installation
bash
# Ubuntu/Debian
apt-get install -y proxysql2
# Start and connect to admin interface
systemctl start proxysql
mysql -u admin -padmin -h 127.0.0.1 -P 6032Add MySQL Servers
sql
-- hostgroup 10 = writes (primary), hostgroup 20 = reads (replicas)
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
(10, 'primary.db.internal', 3306),
(20, 'replica1.db.internal', 3306),
(20, 'replica2.db.internal', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;Read/Write Split Query Rules
sql
-- Route SELECTs not in transactions to replicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT.*FOR UPDATE', 10, 1),
(2, 1, '^SELECT', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Warning:
SELECT ... FOR UPDATE must go to the primary. Always add that rule before the generic SELECT rule.Connection Pooling Config
sql
UPDATE global_variables SET variable_value = 200
WHERE variable_name = 'mysql-max_connections';
UPDATE global_variables SET variable_value = 4
WHERE variable_name = 'mysql-connection_max_age_ms';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;Monitor Replication Lag
ProxySQL can automatically remove lagging replicas from the read pool:
sql
UPDATE mysql_servers SET max_replication_lag = 10
WHERE hostgroup_id = 20;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;Query Stats and Slow Query Analysis
sql
-- Top 10 slowest query digest
SELECT digest_text, count_star, avg_time, sum_time
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 10;Key Takeaways
- Use hostgroup 10 for writes, 20+ for reads to clearly separate traffic
- Always route
SELECT ... FOR UPDATEto the write hostgroup - Set
max_replication_lagto automatically pull lagging replicas - Use
stats_mysql_query_digestfor ongoing query analysis
JusDB Can Help
ProxySQL misconfiguration is a common cause of production incidents. Contact JusDB to review your ProxySQL setup and query routing rules.