ProxySQL Basics

Introduction¶

ProxySQL is a high-performance MySQL protocol proxy. It pools connections, routes queries to the right server (writer vs readers), survives backend failover transparently, and can cache query results — all configurable at runtime without restarts.

The three config layers¶

ProxySQL config exists in three places, and you move settings between them:

• RUNTIME — what's live right now. Not directly editable.
• MEMORY — your working copy. You edit here (SQL against the admin interface).
• DISK — persisted across restarts.

-- connect to the admin interface (default port 6032)
mysql -u admin -padmin -h 127.0.0.1 -P6032

-- edit MEMORY, then:
LOAD MYSQL SERVERS TO RUNTIME;    SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;      SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;  SAVE MYSQL VARIABLES TO DISK;

Forgetting LOAD ... TO RUNTIME after an edit is the #1 "my change did nothing".

Hostgroups¶

A hostgroup is a set of backends ProxySQL treats as interchangeable for a purpose:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES
  (10, '10.0.2.11', 3306, 200),   -- writer hostgroup
  (20, '10.0.2.12', 3306, 200),   -- reader hostgroup
  (20, '10.0.2.13', 3306, 200);

Replication awareness¶

ProxySQL monitors backends and moves them between writer/reader hostgroups automatically:

-- for classic async replication + read_only flag
UPDATE global_variables SET variable_value='10' WHERE variable_name='mysql-monitor_writer_is_also_reader';
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type)
VALUES (10, 20, 'read_only');
LOAD MYSQL SERVERS TO RUNTIME;

Now ProxySQL polls read_only on each server: read_only=0 → writer hostgroup 10, read_only=1 → reader hostgroup 20. On failover (a replica promoted, its read_only set to 0), ProxySQL moves it to hostgroup 10 within a monitor interval — the app's connection string never changes. For Group Replication or InnoDB Cluster, use mysql_group_replication_hostgroups instead.

Read/write splitting with query rules¶

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
  (100, 1, '^SELECT.*FOR UPDATE',      10, 1),   -- SELECT ... FOR UPDATE -> writer
  (200, 1, '^SELECT',                  20, 1),   -- other SELECTs -> readers
  (300, 1, '.*',                       10, 1);   -- everything else -> writer
LOAD MYSQL QUERY RULES TO RUNTIME;

Caveat: naive ^SELECT → reader breaks read-after-write consistency (write to the primary, then immediately read your own data from a lagging replica). Options: route SELECTs inside a transaction to the writer (transaction_persistent=1 on the user), route by comment hint (/* proxysql:writer */), or accept eventual consistency where it's fine.

Connection multiplexing¶

ProxySQL reuses backend connections across many frontend connections — thousands of app connections share a small pool to MySQL. Multiplexing is disabled per connection when the session has state that must stick to one backend: an open transaction, SET of a session var, LOCK TABLES, user variables, temp tables, prepared statements (pre-2.x). Minimise per-session SETs to keep multiplexing working.

Query cache¶

-- add a cache_ttl (ms) to a query rule
UPDATE mysql_query_rules SET cache_ttl=5000 WHERE rule_id=200;

Caches result sets in ProxySQL for TTL ms — good for hot, rarely-changing SELECTs. It's a simple TTL cache, no invalidation on write.

Verification and troubleshooting¶

-- on the admin interface (6032)
SELECT * FROM runtime_mysql_servers;
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, Queries FROM stats_mysql_connection_pool;
SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;
SELECT * FROM mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
SELECT * FROM stats_mysql_query_rules;   -- hits per rule

• All queries going to the writer — query rules not loaded to runtime, rules don't match (match_digest is a regex over the normalized query — check stats_mysql_query_digest), or apply=0 so evaluation continues to a catch-all.
• Access denied — the frontend user must exist in mysql_users in ProxySQL and on the backend MySQL with the same password; default_hostgroup set. LOAD MYSQL USERS TO RUNTIME.
• Backend shows SHUNNED — ProxySQL detected errors and temporarily removed it; check mysql_server_connect_log / mysql_server_ping_log. It re-adds after mysql-shun_recovery_time_sec.
• Read-after-write bugs — SELECTs hitting a lagging replica. Route transactional reads to the writer, or use transaction_persistent.
• Multiplexing not happening (many backend connections) — session state: SET statements, user variables, prepared statements. stats_mysql_processlist and check for hostgroup stickiness.
• Failover didn't reroute — mysql_replication_hostgroups not configured, or the promoted replica's read_only wasn't set to 0, or the monitor user lacks REPLICATION CLIENT. Check mysql-monitor_* variables and the monitor logs.
• Config lost after restart — you did LOAD ... TO RUNTIME but not SAVE ... TO DISK.

Related tools and reading¶

• On-site: Port Checker.
• Related posts: MySQL replication basics, Connection pooling explained.