Traffic Mirroring in ProxySQL
ProxySQL can mirror (shadow) queries to a secondary hostgroup while still serving the original query normally. The application gets its response from the primary backend — the mirrored query runs asynchronously on the mirror backend without affecting response time.
Traffic mirroring is perfect for:
- Testing a new MySQL version with production traffic
- Validating a new schema or index before cutover
- Load testing a new server with real query patterns
- A/B testing query optimizations
- Warming up a new replica before adding it to the read pool
How Mirroring Works
Application sends: SELECT * FROM products WHERE id=1 ProxySQL: ├── Primary: Sends to HG20 (Replica) → returns result to app ✓ └── Mirror: Sends copy to HG30 (Test server) async → result discarded App only sees result from HG20. HG30 gets the query load.
Setup — Add Mirror Backend Server
SQL — Add Mirror Server
-- Add new/test MySQL server in its own hostgroup (HG30)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, comment)
VALUES (30, '192.168.1.200', 3306, 'ONLINE', 'Mirror/Test MySQL Server');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
-- Make sure app user can connect to HG30 as well
-- (ProxySQL uses same credentials for mirror connections) Setup — Create Mirroring Query Rule
SQL — Basic Mirroring Rule
-- Mirror ALL SELECT queries to HG30 while serving from HG20
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup,
mirror_hostgroup, apply, comment)
VALUES
(500, 1, '^SELECT', 20, 30, 1,
'Serve reads from HG20, mirror to HG30');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Selective Mirroring
SQL — Selective Mirroring
-- Mirror only specific table queries
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup,
mirror_hostgroup, apply, comment)
VALUES
(501, 1, 'SELECT .* FROM orders', 20, 30, 1,
'Mirror orders queries to test server');
-- Mirror queries from specific user only
INSERT INTO mysql_query_rules
(rule_id, active, username, match_pattern, destination_hostgroup,
mirror_hostgroup, apply, comment)
VALUES
(502, 1, 'testuser', '^SELECT', 20, 30, 1,
'Mirror testuser queries to HG30');
-- Mirror only 10% of traffic using flagIN/flagOUT chaining
-- (ProxySQL does not natively support % mirroring but you can use
-- application-side sampling or route specific users)
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Monitor Mirror Traffic
SQL — Monitor Mirroring
-- Check if mirror hostgroup is receiving queries
SELECT hostgroup, srv_host, Queries, Bytes_data_sent
FROM stats.stats_mysql_connection_pool
WHERE hostgroup=30;
-- Check query digest on mirror hostgroup
SELECT hostgroup, digest_text, count_star
FROM stats.stats_mysql_query_digest
WHERE hostgroup=30
ORDER BY count_star DESC LIMIT 20;
-- Check rule hits for mirror rule
SELECT rule_id, hits, mirror_hostgroup, comment
FROM stats.stats_mysql_query_rules
WHERE mirror_hostgroup IS NOT NULL; Disabling Mirroring
SQL — Disable Mirroring
-- Disable the mirror rule without deleting it
UPDATE mysql_query_rules SET active=0 WHERE rule_id=500;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- Or remove mirror_hostgroup to stop mirroring but keep routing
UPDATE mysql_query_rules SET mirror_hostgroup=NULL WHERE rule_id=500;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;💡 Note: Mirrored queries are fire-and-forget — ProxySQL does not wait for mirror results. If the mirror server is slow or down, it does not affect your application response time.
⚠ Warning: Mirrored write queries (INSERT/UPDATE/DELETE) will actually EXECUTE on the mirror server. Use mirroring with SELECT-only rules unless you want data changes on the mirror.
