What are Hostgroups?
A hostgroup is a logical collection of MySQL backend servers in ProxySQL. Instead of routing to individual servers, ProxySQL routes traffic to hostgroups — and then load balances across all servers within that group. This abstraction makes it easy to add/remove servers without changing routing rules.
Hostgroup Design Patterns
1Simple Read/Write Split
| Hostgroup | Role | Contains |
|---|---|---|
| 10 | Writers (Master) | MySQL Master only |
| 20 | Readers (Replicas) | All MySQL Replicas |
2Master in Both Groups
| Hostgroup | Role | Contains |
|---|---|---|
| 10 | Writers | MySQL Master |
| 20 | Readers | MySQL Master + All Replicas |
💡 Note: Adding master to reader hostgroup ensures reads still work even if all replicas fail.
3Per-Application Hostgroups
| Hostgroup | Role |
|---|---|
| 10 | App1 Writers |
| 11 | App1 Readers |
| 20 | App2 Writers |
| 21 | App2 Readers |
mysql_replication_hostgroups
This table tells ProxySQL which hostgroup is the writer and which is the reader for MySQL traditional replication. ProxySQL monitors the read_only variable on all servers and automatically moves them between hostgroups.
SQL — Configure Replication Hostgroups
-- Configure Read/Write split hostgroups
INSERT INTO mysql_replication_hostgroups
(writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES
(10, 20, 'read_only', 'MySQL Replication RW Split');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
-- Verify
SELECT * FROM mysql_replication_hostgroups;
SELECT * FROM runtime_mysql_replication_hostgroups;| Column | Description |
|---|---|
| writer_hostgroup | Hostgroup for servers where read_only=OFF (master) |
| reader_hostgroup | Hostgroup for servers where read_only=ON (replicas) |
| check_type | read_only (standard) or innodb_read_only or super_read_only |
| comment | Free text |
mysql_group_replication_hostgroups
For MySQL Group Replication or InnoDB Cluster, use this table instead:
SQL — Group Replication Hostgroups
-- For MySQL Group Replication
INSERT INTO mysql_group_replication_hostgroups
(writer_hostgroup, backup_writer_hostgroup, reader_hostgroup,
offline_hostgroup, active, max_writers, writer_is_also_reader,
max_transactions_behind, comment)
VALUES
(10, 30, 20, 40, 1, 1, 1, 0, 'Group Replication Setup');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;| Column | Description |
|---|---|
| writer_hostgroup | Primary/writer node hostgroup |
| backup_writer_hostgroup | Backup writers (multi-primary mode) |
| reader_hostgroup | Read replicas hostgroup |
| offline_hostgroup | Servers that are offline/recovering go here |
| max_writers | Maximum number of writer nodes (usually 1) |
| writer_is_also_reader | 1 = writer also serves reads; 0 = writer only does writes |
| max_transactions_behind | Max allowed transactions behind primary before SHUNNED |
Checking Hostgroup Assignment
SQL — Check Hostgroup Status
-- See which servers are in which hostgroup RIGHT NOW (runtime)
SELECT hostgroup_id, hostname, port, status
FROM runtime_mysql_servers
ORDER BY hostgroup_id, hostname;
-- Check which servers ProxySQL thinks are read_only
SELECT hostname, port, time_start_us, success_time_us, read_only, error
FROM monitor.mysql_server_read_only_log
ORDER BY time_start_us DESC
LIMIT 20;