ProxySQL is the workhorse of MySQL routing — connection pool, query router, failover detector, and operational lever all in one daemon. This article is the install and bring-up we follow when a client needs ProxySQL in front of MySQL or MariaDB to spread reads across replicas, hide failover from the application, and give the operator a place to throttle, mirror, or rewrite queries without redeploying code. We will install the package, define hostgroups, configure users, and verify a query actually lands on the host we expect.
How to verify
ProxySQL exposes two interfaces — the admin interface on 6032 for configuration via SQL, and the client interface on 6033 for traffic. Both speak the MySQL protocol.
sudo systemctl status proxysql
ss -ltnp | grep -E ':(6032|6033)\b'
mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "SELECT * FROM runtime_mysql_servers;"
mysql -h 127.0.0.1 -P 6033 -u app -p -e "SELECT @@hostname;"
mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "SELECT * FROM stats_mysql_connection_pool;"
A backend in ONLINE state with non-zero ConnUsed is serving traffic. OFFLINE_SOFT is shedding new connections gracefully; SHUNNED is hard-quarantined after repeated failures.
What’s happening
ProxySQL’s data model lives in three layers. Memory is what you edit interactively — INSERT, UPDATE, DELETE against the mysql_servers, mysql_users, mysql_query_rules tables. Runtime is what the proxy actually uses to make decisions — promoted from memory by LOAD MYSQL SERVERS TO RUNTIME. Disk is what survives restart — flushed by SAVE MYSQL SERVERS TO DISK. A change you forget to LOAD does not take effect; a change you forget to SAVE evaporates on restart. Every config change is a two-step.
Backends live in hostgroups. A hostgroup is a numbered pool of MySQL servers — by convention, hostgroup 0 is the writeable primary, hostgroup 1 is the read replicas. A query rule matches incoming SQL and routes it to a hostgroup; a rule that matches ^SELECT and routes to hostgroup 1 is the entire read/write split in one line. Users authenticate against ProxySQL using credentials defined in mysql_users; the user has a default_hostgroup which is where queries land in the absence of a more specific rule.
For replication topology awareness, ProxySQL ships mysql_replication_hostgroups — declare which hostgroup is “writer” and which is “reader”, and ProxySQL queries each backend’s read_only variable on a schedule. A backend that flips to read_only=ON moves to the reader hostgroup; the one that flips to OFF moves to the writer hostgroup. The proxy hides the failover from the client.
The procedure
-
Install ProxySQL from the official repository — the 2.x line is the production target.
curl -fsSL https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key | sudo apt-key add - echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/$(lsb_release -sc)/ ./" \ | sudo tee /etc/apt/sources.list.d/proxysql.list sudo apt update sudo apt install -y proxysql sudo systemctl enable proxysql sudo systemctl start proxysql -
Change the admin password before anything else — the default
admin:adminis in every script kiddie’s toolkit.-- mysql -h 127.0.0.1 -P 6032 -u admin -padmin UPDATE global_variables SET variable_value='admin:STRONG_ADMIN_PASS' WHERE variable_name='admin-admin_credentials'; LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK; -
Define the backend hostgroups — writers in 10, readers in 20.
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES (10, '10.0.0.11', 3306, 200), (20, '10.0.0.12', 3306, 200), (20, '10.0.0.13', 3306, 200); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; -
Declare the replication hostgroup pair so ProxySQL handles failover automatically based on
read_only.INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES (10, 20, 'production replication'); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; -
Create the monitor user on the backend with
REPLICATION CLIENTso ProxySQL can readread_only.-- on the MySQL primary CREATE USER 'monitor'@'%' IDENTIFIED BY 'MON_PASS'; GRANT REPLICATION CLIENT, SELECT ON *.* TO 'monitor'@'%';Then tell ProxySQL about it:
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; UPDATE global_variables SET variable_value='MON_PASS' WHERE variable_name='mysql-monitor_password'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; -
Add the application user and let it default to the writer hostgroup — query rules will peel reads off later.
INSERT INTO mysql_users (username, password, default_hostgroup, max_connections) VALUES ('app', 'APP_PASS', 10, 1000); LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; -
Add the read/write split rule — any
SELECTnot inside a transaction goes to readers.INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (10, 1, '^SELECT.*FOR UPDATE', 10, 1), (20, 1, '^SELECT', 20, 1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; -
Confirm reads land on a replica and writes land on the primary.
mysql -h 127.0.0.1 -P 6033 -u app -pAPP_PASS -e "SELECT @@hostname;" mysql -h 127.0.0.1 -P 6033 -u app -pAPP_PASS -e "INSERT INTO test.routing(t) VALUES(NOW());" mysql -h 127.0.0.1 -P 6032 -u admin -pSTRONG_ADMIN_PASS \ -e "SELECT hostgroup, srv_host, ConnUsed, Queries FROM stats_mysql_connection_pool;"
Common pitfalls
- Forgetting
LOAD ... TO RUNTIME— yourINSERTran fine and changed nothing. ProxySQL reads from runtime, not memory. - Default admin password in production — change it before exposing 6032 to anything beyond localhost.
- Replication hostgroups without a monitor user — ProxySQL cannot detect
read_onlychanges, both backends sit in the writer hostgroup, and replicas can take writes the application sends them. - Routing transactions to readers —
SET autocommit=0followed bySELECTis a write in everything but name; the rule forFOR UPDATEis necessary but not sufficient. Sticky transactions (transaction_persistent=1on the user) keep multi-statement transactions on the writer. - No
mysql_query_rules.apply=1— withoutapply=1, the rule routes but ProxySQL keeps evaluating later rules and the destination can be overridden by a less specific match.
ProxySQL gives the operator a SQL-level handle on a database fleet — the place we add rate limits, mirror traffic to a canary, throttle one tenant’s noisy query, all without an application deploy. In the engagements we run, ProxySQL sits under our managed operations practice: hostgroups, rules, and users are tracked in version control, the admin interface is locked down to the management network, and stats_mysql_query_digest is mined for the queries worth shaping. For the broader pattern of operating database fleets like this, see /en/services/managed-operations/.