ProxySQL Query Routing: Moving Read Traffic Without Touching Application Code

What ProxySQL Does and Why It Matters Here

ProxySQL is a high-performance MySQL-compatible proxy that sits between your application and your database. Applications connect to ProxySQL as if it were MySQL. ProxySQL inspects each query, applies routing rules, and forwards the query to the appropriate backend — writer or reader — based on rules you define in its internal configuration database.

The critical property for read/write splitting: ProxySQL operates at the query text level. It can look at every SQL statement and decide where to route it before the statement reaches any database server. Your application sees a single MySQL endpoint. ProxySQL does the traffic distribution invisibly.

This is different from application-level read/write splitting (configuring two connection pools in your ORM) or AWS Aurora's reader endpoint (which load-balances connections but can't inspect individual query intent). ProxySQL routes at the query level, which means you can express sophisticated routing rules: route all SELECTs to replicas, route SELECTs inside transactions to the writer (to avoid reading stale data after a write), route specific heavy analytical queries to a dedicated replica, and keep all writes on the primary.

ProxySQL Architecture Basics

ProxySQL organizes backends into host groups. Each host group is a pool of servers that queries can be routed to. The standard read/write split setup uses two host groups:

• Host group 10 (writer): The MySQL primary / Aurora writer. All writes go here.
• Host group 20 (readers): MySQL replicas or Aurora reader instances. Read traffic goes here, load-balanced across all members.

-- Connect to the ProxySQL admin interface
-- ProxySQL exposes a MySQL-compatible admin interface on port 6032
mysql -h 127.0.0.1 -P 6032 -u admin -padmin

-- Define the backend servers
-- Writer (primary)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (10, 'primary.cluster.us-east-1.rds.amazonaws.com', 3306, 1);

-- Readers (replicas) -- add all replica endpoints
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES
  (20, 'replica-1.cluster.us-east-1.rds.amazonaws.com', 3306, 1),
  (20, 'replica-2.cluster.us-east-1.rds.amazonaws.com', 3306, 1);

-- Define a MySQL user that ProxySQL will use for backend connections
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', 'password_here', 10);
-- default_hostgroup=10 means writes go to the primary by default

-- Load changes to runtime and persist to disk
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Writing Query Routing Rules

Query routing rules are the core of ProxySQL's value. Each rule has a match condition (a regex or exact match against the query text) and a destination host group. Rules are evaluated in order by rule_id — the first matching rule wins.

-- Basic read/write split: route SELECTs to readers, everything else to writer

-- Rule 1: Route SELECTs to the reader host group (hostgroup 20)
INSERT INTO mysql_query_rules (
  rule_id, active, match_pattern, destination_hostgroup, apply
) VALUES (
  1, 1, '^SELECT', 20, 1
);

-- Rule 2: Keep SELECTs inside transactions on the writer
-- (prevents reading stale data after a write within the same transaction)
INSERT INTO mysql_query_rules (
  rule_id, active, match_pattern, destination_hostgroup, apply,
  multiplex
) VALUES (
  2, 1, '^SELECT', 10, 1, 0  -- route to writer when in_transaction=1
);
-- Note: use the transaction_sticky_max_age parameter instead of
-- a blanket rule -- see below for the better approach

-- Apply and persist
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

The transaction-within-read-routing problem is the most common source of bugs when implementing ProxySQL routing. If your application opens a transaction, writes a row, then SELECTs from the same table within the same transaction, routing that SELECT to a replica returns stale data — the write hasn't replicated yet. ProxySQL handles this with the transaction_persistent user setting:

-- Enable transaction-sticky routing for the application user
-- This ensures all queries within a transaction go to the same hostgroup
-- (whichever hostgroup handled the first query of the transaction)
UPDATE mysql_users
SET transaction_persistent = 1
WHERE username = 'appuser';

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- With transaction_persistent=1:
-- BEGIN; UPDATE orders ...; SELECT * FROM orders ...; COMMIT;
-- All three statements route to the writer (hostgroup 10)
-- because the transaction started with a write
--
-- BEGIN; SELECT * FROM products ...; SELECT * FROM inventory ...; COMMIT;
-- Both SELECTs route to a reader (hostgroup 20)
-- because the transaction started with a read

Advanced Routing: Pinning Specific Queries

Beyond basic read/write splitting, ProxySQL routing rules let you pin specific query patterns to specific host groups. Common use cases:

Route heavy analytical queries to a dedicated replica:

-- Add a dedicated analytics replica to a separate host group
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (30, 'analytics-replica.cluster.us-east-1.rds.amazonaws.com', 3306, 1);

-- Route specific slow queries to the analytics replica
-- Match queries from the reporting schema or containing specific patterns
INSERT INTO mysql_query_rules (
  rule_id, active, username, match_pattern,
  destination_hostgroup, apply, comment
) VALUES
  (10, 1, 'appuser', 'SELECT.*FROM.*monthly_revenue.*',
   30, 1, 'Heavy reporting query to analytics replica'),
  (11, 1, 'appuser', 'SELECT.*FROM.*user_activity_log.*GROUP BY',
   30, 1, 'Aggregation on activity log to analytics replica');

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Add query caching for repetitive reads:

-- Cache specific query results in ProxySQL's internal query cache
-- Useful for near-static reference data (product catalog, config tables)
INSERT INTO mysql_query_rules (
  rule_id, active, match_pattern, cache_ttl,
  destination_hostgroup, apply, comment
) VALUES (
  20, 1, '^SELECT.*FROM product_catalog WHERE id = ',
  30000,  -- cache for 30 seconds (milliseconds)
  20, 1, 'Cache product catalog lookups'
);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Monitoring Routing Effectiveness

ProxySQL maintains detailed statistics in its internal stats schema. After enabling routing, verify that traffic is distributing as expected:

-- Connect to ProxySQL admin to check routing stats
-- (port 6032)

-- Query digest: what queries are running and where they're going
SELECT
  hostgroup,
  schemaname,
  username,
  LEFT(digest_text, 80) AS query_pattern,
  count_star AS total_executions,
  sum_time / count_star / 1000 AS avg_latency_ms,
  sum_rows_sent / count_star AS avg_rows_returned
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 20;

-- Connection pool health per host group
SELECT
  hostgroup,
  srv_host,
  status,
  ConnUsed,
  ConnFree,
  ConnOK,
  ConnERR,
  Queries,
  Bytes_data_sent,
  Bytes_data_recv
FROM stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;

-- Verify read/write distribution
SELECT
  hostgroup,
  SUM(Queries) AS total_queries
FROM stats_mysql_connection_pool
GROUP BY hostgroup;
-- hostgroup 10 (writer): should be writes + transaction-sticky reads
-- hostgroup 20 (readers): should be the bulk of SELECT traffic

Rollback: The Operational Advantage

One underappreciated property of the ProxySQL approach: rolling back is instantaneous. If you route reads to replicas and discover that your application has read-after-write consistency requirements you didn't account for, reverting is a single SQL statement against the ProxySQL admin interface:

-- Immediately route all traffic back to the writer (emergency rollback)
UPDATE mysql_query_rules SET active = 0 WHERE destination_hostgroup = 20;
LOAD MYSQL QUERY RULES TO RUNTIME;
-- No application restart, no code change, no deployment
-- Takes effect for the next query that arrives at ProxySQL

-- Or more surgically: disable only specific rules
UPDATE mysql_query_rules SET active = 0 WHERE rule_id = 1;
LOAD MYSQL QUERY RULES TO RUNTIME;

Compare this to the rollback procedure for application-level connection pool changes: revert the code, build, test, deploy, restart. The ProxySQL rollback is faster than any application deployment pipeline.

ProxySQL on AWS: Deployment Considerations

On AWS, deploy ProxySQL on EC2 instances in the same VPC as your RDS or Aurora cluster. ProxySQL must be able to reach all backend servers over the internal VPC network. Place ProxySQL instances in the same Availability Zone as your application servers where possible — cross-AZ traffic for every query adds latency and data transfer cost.

For high availability, run two ProxySQL instances behind a Network Load Balancer. ProxySQL itself is stateless — both instances use the same configuration, and the NLB distributes application connections between them. If one ProxySQL instance fails, the NLB removes it and routes to the healthy instance within seconds.

# ProxySQL HA setup with NLB (Terraform sketch)
# Two ProxySQL EC2 instances, one NLB, one target group

resource "aws_lb" "proxysql" {
  name               = "proxysql-nlb"
  internal           = true
  load_balancer_type = "network"
  subnets            = var.private_subnet_ids
}

resource "aws_lb_target_group" "proxysql" {
  name     = "proxysql-tg"
  port     = 6033  # ProxySQL MySQL port
  protocol = "TCP"
  vpc_id   = var.vpc_id

  health_check {
    protocol = "TCP"
    port     = 6033
    interval = 10
  }
}

resource "aws_lb_listener" "proxysql" {
  load_balancer_arn = aws_lb.proxysql.arn
  port              = 3306
  protocol          = "TCP"
  default_action {
    type             = "forward"
    target_group_arn = aws_lb_target_group.proxysql.arn
  }
}

Your application connects to the NLB DNS name on port 3306 — same as connecting to MySQL directly. ProxySQL handles the routing. The application has no awareness of the proxy layer.