What are Query Rules?
Query rules are the most powerful feature of ProxySQL. They are regex-based rules that inspect incoming SQL queries and route them to specific hostgroups, modify them, block them, or cache them. Rules are evaluated in order by rule_id — the first matching rule wins (unless apply=0).
Basic Query Rules — Read/Write Split
SQL — Basic Read/Write Rules
-- Rule 1: Route SELECT FOR UPDATE to master (writes inside transactions)
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: Route all other SELECTs to replicas
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES
(2, 1, '^SELECT', 20, 1, 'All SELECTs → Replicas');
-- Apply
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; mysql_query_rules — Column Reference
| Column | Description |
|---|---|
| rule_id | Unique ID — rules evaluated in ascending order |
| active | 1 = rule is active; 0 = disabled |
| username | Match only queries from this MySQL user (NULL = all users) |
| schemaname | Match only queries for this schema/database |
| client_addr | Match queries from this client IP |
| proxy_addr | Match queries arriving on this ProxySQL IP |
| proxy_port | Match queries arriving on this ProxySQL port |
| digest | Match by query digest hash |
| match_digest | Regex match against query digest (normalized query) |
| match_pattern | Regex match against full query text |
| negate_match_pattern | 1 = invert the regex match |
| re_modifiers | Regex modifiers (e.g. CASELESS) |
| flagIN | Rule only applies if connection flag equals this |
| flagOUT | Set connection flag to this value after match |
| apply | 1 = stop processing after this rule; 0 = continue to next |
| destination_hostgroup | Route to this hostgroup |
| cache_ttl | Cache result for this many ms (NULL = no cache) |
| cache_empty_result | Cache even empty resultsets |
| reconnect | Force reconnect to new backend |
| timeout | Query-specific timeout in ms |
| retries | Number of retries on failure |
| delay | Delay query execution by this many ms |
| mirror_hostgroup | Mirror query to this hostgroup (traffic shadowing) |
| mirror_flagOUT | Set flag for mirrored connection |
| error_msg | Return this error message instead of routing |
| OK_msg | Return success without executing query |
| sticky_conn | Keep connection sticky to same backend |
| multiplex | Override multiplexing for this rule |
| log | Log matching queries to query log |
| replace_pattern | Replace matched part of query with this string |
| comment | Free text description |
Advanced Query Rule Examples
Route specific table to master
SQL — Table-specific Routing
-- Always read from master for orders table (consistency)
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES
(10, 1, 'orders', 10, 1, 'All queries on orders table → Master');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Route specific user to specific hostgroup
SQL — User-based Routing
-- All queries from reports_user go to replica hostgroup
INSERT INTO mysql_query_rules
(rule_id, active, username, destination_hostgroup, apply, comment)
VALUES
(20, 1, 'reports_user', 20, 1, 'reports_user always → Replicas');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Block dangerous queries
SQL — Query Firewall
-- Block DROP TABLE
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, error_msg, apply, comment)
VALUES
(100, 1, '^DROP TABLE', 'Query blocked by ProxySQL firewall', 1, 'Block DROP TABLE');
-- Block queries without WHERE clause on large tables
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, error_msg, apply, comment)
VALUES
(101, 1, '^DELETE FROM .* WHERE', NULL, 1, 'Allow DELETE with WHERE');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Query rewriting
SQL — Query Rewriting
-- Add SQL_NO_CACHE hint to all SELECTs
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, replace_pattern, apply, comment)
VALUES
(200, 1, '^(SELECT)', '\1 SQL_NO_CACHE', 1, 'Disable query cache');
-- Rewrite old table name to new table name
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, replace_pattern, apply, comment)
VALUES
(201, 1, 'old_table_name', 'new_table_name', 0, 'Table rename rewrite');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Monitoring Query Rule Hits
SQL — Rule Stats
-- See which rules are being hit and how many times
SELECT rule_id, match_pattern, destination_hostgroup,
cache_ttl, apply, hits, comment
FROM stats.stats_mysql_query_rules
ORDER BY hits DESC;
-- View query digest (normalized queries and their stats)
SELECT hostgroup, schemaname, username,
digest_text, count_star, sum_time, min_time, max_time
FROM stats.stats_mysql_query_digest
ORDER BY count_star DESC
LIMIT 20;💡 Note: Use match_digest instead of match_pattern when possible — it is faster because ProxySQL normalizes the query once and matches against the digest.
Rule Ordering Best Practice
- Most specific rules first (lowest rule_id)
- SELECT FOR UPDATE before generic SELECT
- User-specific rules before generic rules
- Table-specific rules before generic rules
- Catch-all rules last (highest rule_id)
