What are Backend Servers?
Backend servers are the actual MySQL instances that ProxySQL routes traffic to. You register them in ProxySQL's mysql_servers table and assign them to hostgroups. ProxySQL then manages connections, health checks, and routing to these servers.
Adding MySQL Backend Servers
SQL — Add Backend Servers
-- Add a MySQL Master (Hostgroup 10 = Writers)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, max_connections, comment)
VALUES (10, '192.168.1.100', 3306, 'ONLINE', 1, 1000, 'MySQL Master');
-- Add a MySQL Replica (Hostgroup 20 = Readers)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, max_connections, comment)
VALUES (20, '192.168.1.101', 3306, 'ONLINE', 1, 1000, 'MySQL Replica 1');
-- Add another Replica
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, max_connections, comment)
VALUES (20, '192.168.1.102', 3306, 'ONLINE', 1, 1000, 'MySQL Replica 2');
-- Apply changes
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK; mysql_servers — Column Reference
| Column | Type | Description |
|---|---|---|
| hostgroup_id | INT | Logical group this server belongs to |
| hostname | VARCHAR | IP or hostname of MySQL server |
| port | INT | MySQL port (default 3306) |
| gtid_port | INT | Port for GTID tracking (0 = disabled) |
| status | ENUM | ONLINE / OFFLINE_SOFT / OFFLINE_HARD / SHUNNED |
| weight | INT | Load balancing weight (higher = more traffic) |
| compression | INT | Enable compression (0/1) |
| max_connections | INT | Max backend connections from ProxySQL |
| max_replication_lag | INT | Max allowed replication lag in seconds |
| use_ssl | INT | Use SSL for backend connection (0/1) |
| max_latency_ms | INT | Max ping latency — server shunned if exceeded |
| comment | VARCHAR | Free text description |
Server Status Values
| Status | Meaning |
|---|---|
| ONLINE | Server is healthy and accepting connections |
| OFFLINE_SOFT | No new connections, existing connections allowed to finish |
| OFFLINE_HARD | Server immediately removed — all connections dropped |
| SHUNNED | Temporarily removed due to too many connection errors or high replication lag (auto-managed) |
Weight-based Load Balancing
Weight determines how much traffic a server receives relative to others in the same hostgroup. Higher weight = more connections routed to that server.
SQL — Weight-based Balancing
-- Add 3 replicas with different weights
-- Replica 1 gets 50% traffic, Replica 2 gets 30%, Replica 3 gets 20%
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (20, '192.168.1.101', 3306, 5); -- 50%
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (20, '192.168.1.102', 3306, 3); -- 30%
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (20, '192.168.1.103', 3306, 2); -- 20%
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK; Modifying and Removing Servers
SQL — Modify / Remove Servers
-- Take a server offline gracefully (drain connections)
UPDATE mysql_servers
SET status = 'OFFLINE_SOFT'
WHERE hostname = '192.168.1.101' AND hostgroup_id = 20;
-- Take a server offline immediately
UPDATE mysql_servers
SET status = 'OFFLINE_HARD'
WHERE hostname = '192.168.1.101' AND hostgroup_id = 20;
-- Bring server back online
UPDATE mysql_servers
SET status = 'ONLINE'
WHERE hostname = '192.168.1.101' AND hostgroup_id = 20;
-- Remove a server entirely
DELETE FROM mysql_servers
WHERE hostname = '192.168.1.101' AND hostgroup_id = 20;
-- Apply all changes
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK; Viewing Server Status
SQL — View Server Status
-- View configured servers
SELECT hostgroup_id, hostname, port, status, weight, max_connections, comment
FROM mysql_servers
ORDER BY hostgroup_id, hostname;
-- View RUNTIME servers (currently active)
SELECT hostgroup_id, hostname, port, status, weight, ConnUsed, ConnFree, ConnOK, ConnERR, MaxConnUsed, Latency_us
FROM runtime_mysql_servers
ORDER BY hostgroup_id;
-- View connection pool stats
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnOK, ConnERR, Queries, Bytes_data_sent, Bytes_data_recv
FROM stats.stats_mysql_connection_pool
ORDER BY hostgroup; Replication Lag Control
SQL — Replication Lag Control
-- Set max replication lag — server gets SHUNNED if lag exceeds this
UPDATE mysql_servers
SET max_replication_lag = 10
WHERE hostgroup_id = 20;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
-- Check current replication lag detected by monitor
SELECT hostname, port, time_start_us, replication_lag
FROM monitor.mysql_server_replication_lag_log
ORDER BY time_start_us DESC
LIMIT 20;💡 Note: max_replication_lag only works when the monitor user is configured and ProxySQL is actively checking replication lag. See the Monitoring page for setup.
