$ guides / proxysql / proxysql-galera-group-replication-hostgroups ▌

Operations Guides

ProxySQL Galera and Group Replication hostgroups: automatic writer/reader routing

ProxySQL’s mysql_galera_hostgroups and mysql_group_replication_hostgroups tables automate writer/reader hostgroup assignment for synchronous replication clusters. Instead of manually assigning nodes to writer and reader hostgroups in mysql_servers, you define the hostgroup IDs and let the monitor module reassign nodes based on live cluster topology. When a Galera cluster’s active writer shifts during a split-brain resolution, or a Group Replication member wins a primary election, ProxySQL re-routes traffic without operator intervention.

Both cluster types expose their own state. Galera nodes report wsrep_local_state, wsrep_desync, and wsrep_reject_queries. Group Replication nodes report membership state and role through performance_schema.replication_group_members (MySQL 8.0+) or a user-installed sys.gr_member_routing_candidate_status view on older versions. ProxySQL polls these on its monitor interval and moves nodes between writer, reader, backup, and offline hostgroups. This differs from async replication read/write splitting, where ProxySQL relies on read_only checks against statically configured backends. With these tables, the cluster’s own state source is authoritative.

What it is and why it matters

Both mysql_galera_hostgroups and mysql_group_replication_hostgroups share the same table schema. You configure a row that maps four hostgroup IDs to their roles:

Column	Purpose
`writer_hostgroup`	Where write traffic goes. Only nodes the monitor confirms as the cluster primary or a synced writer land here.
`backup_writer_hostgroup`	Overflow for writer-eligible nodes when `max_writers` is exceeded.
`reader_hostgroup`	Where read traffic goes. Populated from SYNCED or SECONDARY nodes.
`offline_hostgroup`	Where nodes go when they are not viable (donor, RECOVERING, UNREACHABLE, desynced).
`active`	Enables or disables the row.
`max_writers`	Maximum number of nodes that can simultaneously occupy `writer_hostgroup`.
`writer_is_also_reader`	Controls whether writers also serve reads (0, 1, or 2).
`max_transactions_behind`	Lag threshold. Nodes exceeding this are SHUNNED or moved offline, depending on version.
`comment`	Free text.

Once you insert a row and load it to runtime, the monitor takes over hostgroup management for the referenced nodes. The initial placement in mysql_servers does not matter. ProxySQL reads the cluster state variables and reassigns nodes to the correct hostgroup.

You still need entries in mysql_servers for each backend node, assigned to one of the four hostgroup IDs. The monitor module moves them between hostgroups at runtime based on cluster state. The mysql_servers entry is the registration; the hostgroup table tells the monitor how to interpret each node’s role.

How it works

The monitor module runs Galera and Group Replication checks on separate intervals from the generic ping, connect, and read-only checks. Each module reads cluster-specific state variables and makes assignment decisions.

flowchart TD
    A["Monitor reads cluster state"] --> B{"Galera or\nGroup Replication?"}
    B -->|"Galera: wsrep_local_state"| C{"Synced and\nnot desynced?"}
    B -->|"GR: performance_schema"| D{"Member ONLINE\nwith PRIMARY role?"}
    C -->|"Yes"| E["Candidate for writer"]
    C -->|"No (donor/SST)"| F["offline_hostgroup"]
    D -->|"Yes"| E
    D -->|"SECONDARY"| G["reader_hostgroup"]
    D -->|"RECOVERING/UNREACHABLE"| F
    E --> H{"Active writers\nbelow max_writers?"}
    H -->|"Yes"| I["writer_hostgroup"]
    H -->|"No"| J["backup_writer_hostgroup"]

Galera state checks

For each node in a Galera cluster, the monitor reads three variables:

wsrep_local_state: The node’s Galera sync state. Value 4 means Synced. Value 2 means Donor/Desynced, typically because the node is serving a State Snapshot Transfer (SST) or Incremental State Transfer (IST) to another node. Values 1 (Joining) and 3 (Joined but not yet synced) indicate the node is not yet fully operational.
wsrep_desync: When set to ON, the node is intentionally removed from the cluster’s flow control. ProxySQL treats this as non-viable for writes.
wsrep_reject_queries: When set to a non-zero value, the node rejects all incoming queries. ProxySQL moves it to the offline hostgroup.

Group Replication state checks

A member with state ONLINE and role PRIMARY goes to the writer hostgroup. A member with state ONLINE and role SECONDARY goes to the reader hostgroup. Members in RECOVERING, ERROR, or UNREACHABLE states go to the offline hostgroup.

Recent ProxySQL versions support replica autodiscovery for Group Replication via performance_schema.replication_group_members, reducing manual configuration when nodes are added or removed from the group.

max_writers and backup_writer_hostgroup

When max_writers is set to 1 (the common single-primary case), only one node occupies the writer hostgroup at a time. If the monitor detects multiple synced primary-eligible nodes, it promotes one and moves the excess to backup_writer_hostgroup.

When max_writers is greater than 1 (multi-writer Galera), multiple nodes can simultaneously occupy the writer hostgroup. This is valid for Galera’s multi-primary mode but requires application-level conflict handling for concurrent writes to the same rows.

writer_is_also_reader

This setting controls whether writer nodes also appear in the reader hostgroup:

0: Writers are excluded from the reader hostgroup. All read traffic goes to reader-only nodes.
1: Writers are also placed in the reader hostgroup. Read traffic can hit the writer.
2: Only nodes in backup_writer_hostgroup are also placed in the reader hostgroup. The active writer is excluded.

Setting writer_is_also_reader=2 with max_writers=1 means the active writer is excluded from reads, and only backup writers serve reads. If all backup writers become non-viable, the cluster ends up with no readers. Plan your reader capacity accordingly.

Lag-based behavior

The max_transactions_behind column sets the lag threshold. When a node exceeds this, ProxySQL takes action. The behavior is version-dependent:

Group Replication: Before v2.3.0, lagging nodes were moved to the offline hostgroup. v2.3.0 and later SHUNNED instead of moved offline. For Group Replication, v2.3.0 introduced a two-stage shunning process: nodes are first SHUNNED when transactions behind exceed the threshold, then existing connections are terminated if the lag persists and doubles.
Galera: Lagging nodes (those with WSREP_LOCAL_RECV_QUEUE above max_transactions_behind) are moved to the offline_hostgroup, dropping existing connections.

SHUNNED is graceful: new queries avoid the node, but in-flight queries complete. Moving to offline_hostgroup is disruptive: connections are killed mid-query.

The Group Replication monitor in recent versions also preserves OFFLINE_SOFT status set by an operator. In older versions, the monitor would override OFFLINE_SOFT on the next state check, making graceful draining impossible during GR hostgroup management.

Where it shows up in production

Galera SST during backups. When a new node joins a Galera cluster or a node restarts, it receives an SST from a donor. The donor node’s wsrep_local_state changes to 2 (Donor/Desynced). ProxySQL moves the donor to the offline hostgroup, removing it from both writer and reader rotation. If the donor was the active writer, there is a detection gap before the monitor promotes a replacement from backup_writer_hostgroup. Schedule SST operations with this in mind.

With xtrabackup-based SST, the donor node may remain capable of serving queries, but ProxySQL still moves it offline based on wsrep_local_state=2. This can reduce capacity unnecessarily during backups.

Group Replication primary failover. When the GR primary fails, the group elects a new primary. ProxySQL’s monitor detects the role change on its next check interval and moves the new primary to the writer hostgroup. The old primary, if it returns as ONLINE with role SECONDARY, goes to the reader hostgroup. During the election window, the writer hostgroup may briefly have no viable nodes.

Node desync for maintenance. Setting wsrep_desync=ON on a Galera node causes ProxySQL to move it to the offline hostgroup. This is useful for planned maintenance, but the node will not receive traffic until wsrep_desync is set back to OFF and the monitor re-checks on its next interval.

Lag spikes during bulk operations. A large INSERT, ALTER TABLE, or batch job on the primary causes replication lag on followers. If lag exceeds max_transactions_behind, ProxySQL shuns the lagging nodes (v2.3.0+) or moves them offline (pre-v2.3.0). Read traffic concentrates on remaining healthy readers, which may also be approaching the threshold.

Tradeoffs and when to use it

Use these tables only with actual Galera or Group Replication clusters. For async replication (primary-replica), use the standard read/write split with read_only checks in mysql_servers and mysql_replication_hostgroups instead. The Galera and GR tables expect cluster-specific state variables and will not function correctly without them.

max_writers=1 creates a hard writer dependency. With a single writer, any issue on that node stops all writes. Failover depends on the monitor detecting the change and promoting a backup writer. Monitor interval length determines detection latency.

writer_is_also_reader=2 can leave you without readers. If all backup writers become non-viable, no nodes serve reads. With writer_is_also_reader=1 or 0, the reader hostgroup has dedicated reader nodes that are not subject to writer-hostgroup churn.

The promotion order is deterministic but not always intuitive. When weights are equal, hostname and port decide which node becomes the writer. A DNS change or a node replacement with a different hostname can shift which node is promoted, even if the previous writer is still healthy.

Configuration sync conflicts in ProxySQL Cluster. When multiple ProxySQL instances sync configuration via proxysql_servers, the Galera or GR hostgroup auto-assignment can conflict with cluster config propagation. If one peer reassigns a node while another is pushing a config update, runtime state may diverge. Monitor stats_proxysql_servers_checksums for divergence when using both ProxySQL Cluster and Galera or GR hostgroups together.

Signals to watch

Signal	Why it matters	Warning sign
`runtime_mysql_servers` status per node	Shows which hostgroup each node is in and its status (ONLINE, SHUNNED, OFFLINE_SOFT, OFFLINE_HARD).	A node stuck in the wrong hostgroup, or the writer hostgroup with zero ONLINE nodes.
`Servers_table_version` in `stats_mysql_global`	Increments when the hostgroup manager reassigns nodes.	Rapid increments indicate cluster instability or monitor oscillation.
`MySQL_Monitor_connect_check_OK` / `ERR`	Whether the monitor can reach backends at all.	ERR rate rising means network issues between ProxySQL and cluster nodes.
`MySQL_Monitor_ping_check_OK` / `ERR`	Whether backends respond to pings.	ERR rate rising on a specific node precedes SHUNNED transitions.
`ConnERR` per backend in `stats_mysql_connection_pool`	Failed connection attempts to specific backends.	Rising ConnERR on a node that the monitor reports as ONLINE suggests a credential or firewall mismatch between data-plane and monitor connections.
`backend_lagging_during_query`	Queries that hit a node after it was detected as lagging.	Any non-zero rate means queries were routed to a node that was already behind.
`backend_offline_during_query`	Queries that were in flight when a backend went offline.	Spikes correlate with cluster topology changes (failover, SST, desync).
Per-hostgroup query distribution	Whether traffic is balanced across the reader hostgroup.	All read traffic on one reader suggests others are SHUNNED or offline.

How Netdata helps

Netdata collects ProxySQL’s internal stats at per-second granularity. During a Galera or GR failover, the metrics that matter are:

runtime_mysql_servers transitions. Netdata captures each node’s hostgroup and status changes, so you can pinpoint exactly when a node moved between writer, reader, and offline, and correlate it with system-level events on the backend host.
Monitor check failure rates. Spikes in MySQL_Monitor_connect_check or MySQL_Monitor_ping_check errors that precede SHUNNED transitions indicate monitor-driven routing changes, not data-plane failures.
Connection pool saturation during failover. When the writer hostgroup loses its active writer, remaining readers absorb redirected traffic. Netdata surfaces ConnUsed, ConnFree, and ConnPool_get_conn_failure per backend, so you can catch pool exhaustion before it becomes a client-visible timeout.
Multi-proxy divergence. If you run multiple ProxySQL instances, Netdata lets you compare backend status and routing decisions across peers side by side, catching checksum divergence before it causes split-brain routing.