ProxySQL Hostgroups: Routing MySQL Traffic the Right Way

The first time you open ProxySQL's admin interface, you see hostgroup_id everywhere: in mysql_servers, in mysql_users, in mysql_query_rules. It looks like a minor config detail. It isn't. Hostgroups are the core idea in ProxySQL. Once you understand them, read/write splitting, failover, maintenance windows, and multi-DC setups all follow from them.

In the previous ProxySQL post we used ProxySQL as a connection pooler. This post goes one level deeper: what hostgroups are, why they exist, how people use them, where they go wrong, and how they work with Galera clusters and ProxySQL instances spread across data centers.

What Is ProxySQL?

ProxySQL is a SQL-aware proxy for MySQL (and MySQL-compatible databases like Percona Server, MariaDB, and Galera-based clusters). It speaks the MySQL protocol, so your application connects to ProxySQL exactly as it would connect to MySQL, by default on port 6033. You manage it through a separate admin interface on port 6032, which is also MySQL-protocol. You configure it with plain SQL against in-memory tables.

Unlike a TCP load balancer such as HAProxy, ProxySQL parses every query. That gives it the information it needs to:

• Pool and multiplex connections
• Route each query to a backend based on rules
• Health-check backends and take bad ones out of rotation
• Cache, rewrite, mirror, or block queries
• Record per-query-digest statistics

Configuration lives in three layers, which matters for everything below:

You edit MEMORY, push to RUNTIME to apply, and SAVE ... TO DISK to make it survive a restart.

Why Do We Need Something Like ProxySQL?

Once your database is more than one server, your application has to decide which server gets each query. Without a proxy, that logic ends up in application code:

// Every service re-implements this, slightly differently
$writer = new PDO('mysql:host=db-primary;...');
$reader = new PDO('mysql:host=db-replica-' . rand(1, 3) . ';...');
 
// What if db-replica-2 is down? What if it's 30 seconds behind?
// What if the primary fails over to db-replica-1?

Every service needs to know the topology, handle failover, check replication lag, and be redeployed when a server is added. In a company with ten services in three languages, that's ten slightly different, slightly buggy versions of the same logic.

A proxy moves that logic into one place:

Applications connect to one endpoint. The topology can change underneath (a failover, a new replica, a node in maintenance) without anyone redeploying anything. Hostgroups are how ProxySQL models that topology.

What Is a Hostgroup?

A hostgroup is a named (well, numbered) set of backend servers that ProxySQL treats as interchangeable for routing.

That's the whole definition. A hostgroup is just an integer ID. ProxySQL gives it no built-in meaning. "Hostgroup 10 is writers, 20 is readers" is a convention you pick.

You put servers into hostgroups through the mysql_servers table:

-- Admin interface: mysql -u admin -p -h 127.0.0.1 -P 6032
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES
  (10, 'db-primary',   3306, 1000, 200),   -- writer hostgroup
  (20, 'db-replica-1', 3306, 1000, 200),   -- reader hostgroup
  (20, 'db-replica-2', 3306, 1000, 200),
  (20, 'db-primary',   3306,   10, 200);   -- primary also takes a small share of reads
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

A few things to notice:

• The primary key is (hostgroup_id, hostname, port). The same server can be in several hostgroups, each with its own weight and max_connections.
• weight controls load balancing within a hostgroup. Above, db-primary receives about 10 / 2010 ≈ 0.5% of reads.
• Each entry has a status: ONLINE, SHUNNED (temporarily avoided after errors), OFFLINE_SOFT (no new traffic, existing transactions finish), or OFFLINE_HARD (removed immediately).

How Traffic Reaches a Hostgroup

Your application never names a hostgroup. ProxySQL decides per query, in this order:

1. mysql_users.default_hostgroup: where a user's queries go if nothing else matches.
2. mysql_query_rules.destination_hostgroup: overrides the default when a rule matches (by regex, digest, username, schema, client address, and more).
3. mysql_users.transaction_persistent: when 1, every query inside a transaction stays in the hostgroup where the transaction started.

-- Writes by default
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('app', 'app_password', 10, 1);
 
-- Plain SELECTs go to readers; SELECT ... FOR UPDATE stays on the writer
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
  (100, 1, '^SELECT .* FOR UPDATE', 10, 1),
  (200, 1, '^SELECT',               20, 1);
 
LOAD MYSQL USERS TO RUNTIME;       SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

What Is the Purpose of Hostgroups?

Hostgroups give you a layer of indirection: routing rules point at roles, not at machines.

Your query rules say "SELECTs go to hostgroup 20." They don't say "SELECTs go to db-replica-1." That separation is what makes everything else possible:

Better still, ProxySQL can move servers between hostgroups for you. With the special "hostgroup manager" tables (mysql_replication_hostgroups, mysql_galera_hostgroups, mysql_group_replication_hostgroups, mysql_aws_aurora_hostgroups), ProxySQL's monitor watches each backend's state and reassigns it automatically. When a replica gets promoted, it moves to the writer hostgroup and your query rules don't change.

For classic async replication, that looks like this:

-- ProxySQL checks @@read_only on every server in hostgroups 10 and 20:
--   read_only=0 -> writer hostgroup (10)
--   read_only=1 -> reader hostgroup (20)
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES (10, 20, 'read_only', 'main cluster');
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

ProxySQL doesn't perform the failover (that's Orchestrator, MHA, or your own tooling). It follows it. When read_only flips on the new primary, ProxySQL moves traffic within seconds.

Common Use Cases

1. Read/Write Splitting

The classic case, shown above: a writer hostgroup, a reader hostgroup, and query rules sending SELECTs to readers. A tip: instead of routing every SELECT to readers, start by sending everything to the writer, then use stats_mysql_query_digest to find your heaviest read queries and route only those to readers. That keeps read-your-own-write surprises to a minimum.

-- Find the top read digests by total time
SELECT digest, SUBSTR(digest_text, 1, 80) AS query, count_star, sum_time
FROM stats_mysql_query_digest
WHERE digest_text LIKE 'SELECT%'
ORDER BY sum_time DESC
LIMIT 10;
 
-- Route one specific digest to readers
INSERT INTO mysql_query_rules (rule_id, active, digest, destination_hostgroup, apply)
VALUES (300, 1, '0x3A6B9F2C1D4E5A7B', 20, 1);

2. Isolating Heavy Workloads

Reporting jobs, analytics dashboards, and batch exports can starve your OLTP traffic. Give them a dedicated replica in their own hostgroup and route by username:

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (30, 'db-reporting', 3306);
 
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('reporting', 'reporting_password', 30);

The reporting team's 40-second queries can no longer touch production replicas.

3. Sharding by Schema or Tenant

If different schemas live on different clusters, route by schemaname:

INSERT INTO mysql_query_rules (rule_id, active, schemaname, destination_hostgroup, apply) VALUES
  (400, 1, 'tenant_eu', 40, 1),
  (401, 1, 'tenant_us', 50, 1);

The application connects to a single ProxySQL endpoint and only has to USE the right schema.

4. Zero-Downtime Maintenance

Drain a server before upgrading it:

UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = 'db-replica-2';
LOAD MYSQL SERVERS TO RUNTIME;
 
-- Wait until ConnUsed reaches 0, then do the maintenance
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree
FROM stats_mysql_connection_pool
WHERE srv_host = 'db-replica-2';

5. Canary Upgrades and Migrations

Upgrading MySQL 8.0 → 8.4? Add the new server to the reader hostgroup with a low weight, watch error rates and latency, then increase the weight step by step. You can also use mirror_hostgroup on a query rule to send a copy of real traffic to a new server without returning its results to clients:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, mirror_hostgroup, apply)
VALUES (500, 1, '^SELECT', 20, 60, 1);   -- hostgroup 60 = the new MySQL 8.4 server

Common Pitfalls

1. Replication Lag and Read-Your-Own-Writes

A user saves their profile (writer), the page reloads (reader), and the old data comes back because the replica is two seconds behind. This is the most common complaint after enabling read/write splitting.

Mitigations:

• Set max_replication_lag on reader entries in mysql_servers. ProxySQL shuns a replica that falls too far behind.
• For Galera, use max_transactions_behind in mysql_galera_hostgroups (covered below).
• Keep consistency-sensitive reads on the writer. Route by digest, not with a blanket ^SELECT.
• For async replication with GTID, ProxySQL 2.x supports causal reads (gtid_from_hostgroup in query rules plus the ProxySQL binlog reader), but it adds moving parts.

UPDATE mysql_servers SET max_replication_lag = 5 WHERE hostgroup_id = 20;
LOAD MYSQL SERVERS TO RUNTIME;

2. Readers With Nowhere to Go

If every replica is shunned (lagging or down) and the writer isn't in the reader hostgroup, reads have no destination and fail. Decide on purpose: either add the writer to the reader hostgroup with a low weight as a fallback, or accept failing reads over overloading the writer. Just don't find out during an incident.

3. Query Rule Ordering and apply

Rules run in rule_id order. A rule with apply=1 stops evaluation. A rule with apply=0 sets attributes and keeps going. Put ^SELECT before ^SELECT .* FOR UPDATE and your locking reads go to a replica. They "work" there, but lock nothing on the primary. Always put specific rules before general ones, and check stats_mysql_query_rules to see which rules actually match:

SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;

4. Forgetting LOAD ... TO RUNTIME (or SAVE ... TO DISK)

You edit mysql_servers, nothing changes, and you spend twenty minutes debugging. Or it works, then ProxySQL restarts and your change is gone. Make LOAD + SAVE a reflex. When debugging, compare mysql_servers with runtime_mysql_servers.

5. Fighting the Hostgroup Manager

When mysql_replication_hostgroups or mysql_galera_hostgroups is active, ProxySQL owns server placement. If you manually set a node to OFFLINE_SOFT or move it between hostgroups, the monitor may move it right back on the next check. For Galera, drain nodes from the database side (SET GLOBAL pxc_maint_mode = 'MAINTENANCE' on Percona XtraDB Cluster, or SET GLOBAL wsrep_desync = ON / wsrep_reject_queries) and let ProxySQL react.

6. Monitor User Missing or Under-Privileged

The hostgroup managers depend on the monitor user being able to connect and read status variables. If its password is wrong or it lacks privileges, checks fail, servers get shunned, or failover just doesn't happen. Check the monitor logs:

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 5;
SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 5;

7. Hostgroup ID Collisions

Every hostgroup listed in a hostgroup manager row (writer, reader, backup writer, offline) must be unique and not reused by another manager row. Pick a numbering scheme and write it down, for example 10x for cluster A and 20x for cluster B.

Using Hostgroups With a Galera Cluster

Galera (and Percona XtraDB Cluster, MariaDB Galera Cluster) is multi-master: every node accepts writes. That's tempting, but writing to several nodes at once causes certification conflicts. Two nodes modify the same row at the same time, one transaction loses at commit, and the application sees a deadlock error. Most production setups write to one node at a time and use the others for reads and as hot standbys.

ProxySQL 2.x has native Galera support through mysql_galera_hostgroups, which uses four hostgroups:

Configuration:

-- Put all nodes in the writer hostgroup; ProxySQL redistributes them.
-- The highest weight wins the writer role.
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
  (10, 'galera-node1', 3306, 1000),
  (10, 'galera-node2', 3306,  100),
  (10, 'galera-node3', 3306,  100);
 
INSERT INTO mysql_galera_hostgroups
  (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup,
   active, max_writers, writer_is_also_reader, max_transactions_behind, comment)
VALUES
  (10, 12, 20, 9999, 1, 1, 2, 100, 'galera main');
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

What the key settings mean:

• max_writers = 1: only one node in the writer hostgroup. Extra writer candidates go to the backup writer hostgroup. This is how you avoid certification conflicts.
• writer_is_also_reader:
  • 0: the writer serves no reads
  • 1: the writer and backup writers are all in the reader hostgroup
  • 2: only backup writers are readers (a common choice: the writer handles writes, the other nodes handle reads)
• max_transactions_behind: if a node's receive queue (wsrep_local_recv_queue) goes over this value, ProxySQL takes it out of the reader hostgroup until it catches up. This is the Galera version of max_replication_lag.

The monitor checks each node's Galera state (wsrep_local_state must be 4/Synced, plus read_only, wsrep_desync, wsrep_reject_queries, and on PXC pxc_maint_mode). If node1 dies or leaves the Primary component, it moves to the offline hostgroup, the next-highest-weight node becomes writer, and the application only sees a short blip.

Check what ProxySQL decided:

SELECT hostgroup_id, hostname, status, weight
FROM runtime_mysql_servers
ORDER BY hostgroup_id, weight DESC;
 
SELECT hostname, primary_partition, read_only, wsrep_local_recv_queue, error
FROM monitor.mysql_server_galera_log
ORDER BY time_start_us DESC
LIMIT 6;

The same idea applies to other clusters. Use mysql_group_replication_hostgroups for MySQL Group Replication / InnoDB Cluster and mysql_aws_aurora_hostgroups for Aurora. Each manager table knows how to read its cluster's health and maps it onto the same writer/reader/offline hostgroups.

Multiple ProxySQL Instances Across Data Centers

A single ProxySQL is a single point of failure, so production setups run several. Once those instances span data centers, there are three questions: where do they run, how do they stay in sync, and how do they route by locality?

Where to Run ProxySQL

Both work. Sidecar is common for smaller fleets. A dedicated tier is common when you have hundreds of app hosts.

Keeping Instances in Sync: ProxySQL Cluster

ProxySQL has built-in clustering. Instances listed in proxysql_servers watch each other's config checksums and pull changes. Edit one, and the rest follow:

-- On every ProxySQL instance
UPDATE global_variables SET variable_value = 'cluster_admin'
  WHERE variable_name = 'admin-cluster_username';
UPDATE global_variables SET variable_value = 'cluster_secret'
  WHERE variable_name = 'admin-cluster_password';
-- The cluster user must also be in admin-admin_credentials, e.g. 'admin:admin_pw;cluster_admin:cluster_secret'
 
INSERT INTO proxysql_servers (hostname, port, weight, comment) VALUES
  ('proxysql-dc1-a', 6032, 0, 'dc1'),
  ('proxysql-dc1-b', 6032, 0, 'dc1'),
  ('proxysql-dc2-a', 6032, 0, 'dc2');
 
LOAD ADMIN VARIABLES TO RUNTIME;  SAVE ADMIN VARIABLES TO DISK;
LOAD PROXYSQL SERVERS TO RUNTIME; SAVE PROXYSQL SERVERS TO DISK;

ProxySQL Cluster syncs mysql_servers, mysql_users, mysql_query_rules, the hostgroup manager tables, and mysql_variables. Note that it's eventually consistent with no leader: the last change wins, so make changes from one place (an admin node or your config management), not by hand on random instances.

Routing by Locality

This is where it gets interesting. Picture a Galera cluster stretched across two DCs:

You want reads to stay local and all writes to go to the same single node, no matter which DC they come from. Some design rules:

1. Every ProxySQL instance must agree on the writer. This is the big one. Each instance runs its own monitor and makes its own decisions. If ProxySQL DC1 picks node1 as writer while ProxySQL DC2 picks node3, you're effectively multi-master across a WAN, and certification conflicts will follow. Make the writer choice deterministic: identical mysql_servers weights on every instance (ProxySQL Cluster helps here), with one clearly highest-weight node in the primary DC.

2. Local reads need DC-specific configuration. Because ProxySQL Cluster syncs mysql_servers, "prefer local nodes" can't be a weight that differs per DC inside one synced cluster. Two practical options:

• One ProxySQL Cluster per DC. Each DC's cluster has its own mysql_servers, with high weights for local readers and low weights for remote readers as a fallback. Keep writer weights identical across DCs with config management (Ansible, Terraform, etc.).
• One global cluster with DC-specific hostgroups. Define reader hostgroups per DC (e.g., 21 for DC1 readers, 22 for DC2 readers), and give apps in each DC their own ProxySQL user (app_dc1, app_dc2) with query rules that match on username and route to the local reader hostgroup.

The first option is simpler to reason about. The second keeps a single source of truth. Pick one and document it.

3. Expect cross-DC write latency. Galera commits are synchronous across the cluster, so every commit pays at least one WAN round trip. Use Galera segments (gmcast.segment, one per DC) to cut cross-DC replication traffic, and keep transactions small.

4. Plan for a network partition. If DC1 and DC2 lose contact, Galera keeps only the side with quorum as the Primary component. With two equal DCs, neither side has a majority and the whole cluster stops accepting writes. Run an odd number of sites, often with a lightweight garbd arbitrator in a third location. On the minority side, nodes go non-Primary, ProxySQL there moves them to the offline hostgroup, and that DC's writes fail fast instead of causing split-brain. That's the behavior you want, but your application should handle it gracefully.

5. Watch the monitor load. Each ProxySQL instance health-checks every backend on its own. Twenty sidecars × six DB nodes × checks every second adds up. Tune mysql-monitor_*_interval values, and prefer a dedicated tier over sidecars as the fleet grows.

Summary

Hostgroups look like a small config detail, but they are the core of how ProxySQL works:

✅ A hostgroup is just a numbered set of servers; the meaning (writer, reader, reporting) is up to you
✅ Traffic reaches a hostgroup through default_hostgroup, query rules, and transaction persistence
✅ Routing rules point at roles, so failover and maintenance never touch your rules
✅ Hostgroup managers (mysql_replication_hostgroups, mysql_galera_hostgroups, ...) move servers for you
✅ For Galera, use max_writers = 1, writer_is_also_reader = 2, and max_transactions_behind
✅ Watch for replication lag, rule ordering, and manual edits fighting the monitor
✅ Across DCs, every ProxySQL must agree on the writer, while reads stay local

Get the hostgroup design right, and the rest of ProxySQL (pooling, routing, failover) becomes configuration instead of firefighting.

Related: ProxySQL: Reduce PHP-MySQL Connections at Scale
Related: Galera Cluster Architecture: Synchronous Multi-Master Replication
Series: MySQL High Availability: Galera, Percona & ProxySQL Roadmap