ProxySQL Overview: Core Features, Configuration Layers, and Practical Use Cases

ProxySQL is a high‑performance, protocol‑aware MySQL proxy that provides read/write splitting, dynamic routing, query caching, connection pooling, and high‑availability features.

Request flow : client → ProxySQL processing → backend MySQL → ProxySQL frontend connection → client.

Core functions include read/write splitting, simple sharding via regex, connection‑pool management, multiplexing, traffic control (kill connection/query, whitelist), query caching, dynamic configuration, traffic mirroring, and SQL rewriting.

Configuration system consists of three layers:

RUNTIME layer – active configuration loaded into memory (cannot be edited directly).

MEMORY layer – editable configuration stored in memory, loaded into RUNTIME with load ... to runtime .

DISK/CONFIG FILE layer – persistent storage (SQLite files) loaded into MEMORY on startup.

Typical commands to move configurations between layers are shown in the following snippet:

# Load changes to runtime
load mysql servers to runtime;
# Save runtime to memory
save mysql servers to memory;
# Load from config file
load mysql servers from config;

First startup reads proxysql.cnf to initialize MEMORY and RUNTIME, then persists to SQLite. Using --initial forces a fresh initialization.

Key tables :

global_variables – stores admin, MySQL, and monitor parameters (modifiable via SQL UPDATE or SET ).

mysql_servers – defines backend MySQL instances (hostgroup, hostname, port, status, weight, max_connections, max_replication_lag).

mysql_users – ProxySQL client users (username, password, default_hostgroup, active, transaction_persistent, etc.).

mysql_replication_hostgroups – maps writer and reader hostgroups for async/semisync replication.

mysql_query_rules – routing rules (match_digest, destination_hostgroup, flagIN/OUT, log, error_msg, apply).

Read‑write splitting examples :

Separate ports (6401 for writes, 6402 for reads) with routing rules based on proxy_port .

Regex‑based rules: ^SELECT.*FOR UPDATE$ → writer hostgroup, ^SELECT → reader hostgroup.

Testing shows that queries are correctly routed according to the configured ports or regex patterns, and highlights limitations when the application cannot use multiple entry ports.

Hostgroup failure scenario : if all servers in a read hostgroup become SHUNNED , queries routed to that group fail with a timeout error, demonstrating the need for fail‑over strategies such as placing both master and slave in the read group with lower master weight.

Weight tuning : increasing a server’s weight raises its selection probability in the random‑weighted load‑balancing algorithm; tests show near‑100 % traffic to a server when its weight is set to the maximum value.

Replication‑lag handling uses max_replication_lag and monitoring variables ( monitor_replication_lag_use_percona_heartbeat , monitor_slave_lag_when_null ) to mark lagging replicas as SHUNNED . The article demonstrates behavior with both SHOW SLAVE STATUS and Percona heartbeat.

Blacklist/whitelist is implemented via mysql_query_rules (error_msg for blacklist) and chainable rules for whitelist based on client_addr . Tests verify that only whitelisted IPs can execute queries, while others receive a custom error.

Firewall feature (available from ProxySQL 2.0.9) simplifies IP‑based access control and SQL‑fingerprint whitelisting using two tables:

mysql_firewall_whitelist_users – defines users, client IPs, and mode (OFF, DETECTING, PROTECTING).

mysql_firewall_whitelist_rules – stores allowed SQL digests per user/IP.

In DETECTING mode, unknown queries generate warnings; in PROTECTING mode, they are blocked. The article notes that the firewall currently does not support subnet masks.

Overall, ProxySQL offers powerful, easily configurable capabilities to improve MySQL performance, availability, and security.