ProxySQL OFFLINE_SOFT vs OFFLINE_HARD vs SHUNNED: what each backend status means

ProxySQL tracks four backend statuses: ONLINE, SHUNNED, OFFLINE_SOFT, and OFFLINE_HARD. OFFLINE_SOFT and OFFLINE_HARD are operator-controlled drain states. SHUNNED is automatic and monitor-driven. The distinction determines who sets the state, how it recovers, and which table shows the truth.

A backend that is OFFLINE_SOFT is deliberately draining and will not recover until you change it back. A backend that is SHUNNED is temporarily avoided by the monitor and self-corrects. Conflating the two leads to unnecessary intervention (restarting backends that would recover on their own) or dangerous neglect (assuming a draining backend will fix itself during a failover).

The four backend statuses at a glance

SHUNNED is the only state the operator does not control. The monitor decides when to shun and when to retry. OFFLINE_SOFT and OFFLINE_HARD are always operator-initiated through mysql_servers.

How each status actually works

ONLINE

The default healthy state. The backend receives new connections and serves queries.

SHUNNED (automatic, monitor-driven)

ProxySQL’s Monitor Module shuns a backend when it detects too many connection errors or replication lag exceeding the threshold configured in mysql_servers.max_replication_lag.

Two global variables control shunning:

• mysql-shun_on_failures (default 5): connection errors tolerated before the monitor shuns the backend
• mysql-shun_recovery_time_sec (default 10): seconds before ProxySQL retries a shunned server

When a backend is shunned, no new connections are created toward it. Existing connections are not immediately killed. This is ProxySQL’s protection mechanism: it temporarily avoids a backend that appears unhealthy without severing active sessions.

SHUNNED is transient by design. After the recovery timer expires, ProxySQL probes the backend during normal traffic routing. If the probe succeeds, the backend returns to ONLINE. If the problem persists, it stays SHUNNED.

OFFLINE_SOFT (operator-set draining)

The graceful drain state. Set it in mysql_servers for planned maintenance: OS patches, MySQL upgrades, schema changes, capacity removal.

Behavior when loaded to runtime:

• No new backend connections created toward this server
• Existing connections kept until returned to the pool or destructed
• Queries already in flight complete normally
• The server remains in the hostgroup but receives no new traffic

OFFLINE_HARD (operator-set immediate removal)

The force-removal state. Use it when a backend must be removed immediately: a compromised server, a replica returning wrong data, or an emergency failover where you cannot wait for sessions to drain.

Behavior when loaded to runtime:

• No new connections created
• Existing free (idle) connections immediately dropped
• Active session connections dropped when the client next tries to use them
• Functionally equivalent to removing the server from rotation

OFFLINE_HARD causes query failures for sessions that were using that backend. The client receives an error on its next query. Only use this when the cost of keeping the backend in rotation exceeds the cost of dropping active sessions.

mysql_servers vs runtime_mysql_servers: which table tells the truth

ProxySQL’s three-layer configuration model creates two tables that operators routinely confuse:

• mysql_servers is the staging table. It reflects what the operator has configured but not what is live at runtime. Changes here require LOAD MYSQL SERVERS TO RUNTIME to take effect.
• runtime_mysql_servers is the live truth. It shows the actual operational state of every backend, including status changes made by the monitor.

SHUNNED only appears in runtime_mysql_servers and stats_mysql_connection_pool, never in mysql_servers. The monitor operates at the runtime layer. If you query mysql_servers and see a backend as ONLINE, it may be SHUNNED at runtime.

# Check the live runtime truth, not the config table
# ProxySQL admin interface (default credentials shown; change in production)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup_id, hostname, port, status FROM runtime_mysql_servers ORDER BY hostgroup_id;"

# Cross-reference with connection pool stats for per-backend detail
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree FROM stats_mysql_connection_pool ORDER BY hostgroup;"

If you set a backend to OFFLINE_SOFT in mysql_servers but forget LOAD MYSQL SERVERS TO RUNTIME, the backend remains ONLINE at runtime and the drain never starts.

State transitions and who controls them

The diagram below shows all valid transitions. The key asymmetry: the monitor can move a backend from ONLINE to SHUNNED, but only the operator can move it to OFFLINE_SOFT or OFFLINE_HARD.

stateDiagram-v2
    ONLINE --> SHUNNED: monitor detects failures or lag
    SHUNNED --> ONLINE: recovery timer expires, traffic retry succeeds
    ONLINE --> OFFLINE_SOFT: operator sets drain
    OFFLINE_SOFT --> ONLINE: operator restores
    ONLINE --> OFFLINE_HARD: operator force-removes
    OFFLINE_HARD --> ONLINE: operator restores
    OFFLINE_SOFT --> OFFLINE_HARD: operator escalates

An operator can override SHUNNED by setting OFFLINE_SOFT or OFFLINE_HARD in mysql_servers and loading to runtime. The operator-set OFFLINE states take precedence over the monitor’s SHUNNED. The reverse is not true: the monitor cannot shun a backend that the operator has set to OFFLINE_SOFT or OFFLINE_HARD.

Draining a backend safely for maintenance

The safe drain procedure uses OFFLINE_SOFT. The goal: stop new traffic without disrupting sessions already in flight.

# Step 1: Set the backend to OFFLINE_SOFT in the config table
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='10.0.1.50' AND port=3306;"

# Step 2: Load to runtime so the drain actually starts
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "LOAD MYSQL SERVERS TO RUNTIME;"

After loading, monitor the connection pool for that backend. ConnUsed tracks connections currently executing queries or pinned to sessions. ConnFree tracks idle connections available for reuse.

# Step 3: Watch ConnUsed drop to zero
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree FROM stats_mysql_connection_pool WHERE srv_host='10.0.1.50';"

When ConnUsed reaches zero and ConnFree also drops (idle connections reclaimed by the pool), the backend is fully drained. You can then perform maintenance safely.

To bring the backend back after maintenance:

# Step 4: Set back to ONLINE and persist
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "UPDATE mysql_servers SET status='ONLINE' WHERE hostname='10.0.1.50' AND port=3306;"

# Step 5: Load to runtime and save to disk
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"

Drain time depends on how long existing sessions take to finish. Long-running transactions or persistent connections can keep ConnUsed above zero for minutes or longer. If you cannot wait, escalate to OFFLINE_HARD, but expect query failures for sessions on that backend.

Always run SAVE MYSQL SERVERS TO DISK after loading to runtime. Without it, a ProxySQL restart reverts the change. This is particularly dangerous if you drained a backend, then ProxySQL restarts (OOM, upgrade, host reboot), and the backend comes back ONLINE mid-maintenance.

SHUNNED recovery: why it needs traffic

A shunned backend does not automatically return to ONLINE based on monitor health checks alone.

The recovery mechanism is passive. After mysql-shun_recovery_time_sec expires, ProxySQL retries the backend the next time it routes a query to that hostgroup. If the retry succeeds, the backend returns to ONLINE. If there is no client traffic hitting the hostgroup, the retry never happens. The node stays SHUNNED.

In low-traffic environments or during off-peak hours, a shunned backend may appear stuck in SHUNNED even though the underlying problem (network blip, temporary replication lag) has resolved. This is not a bug. Send a test query through the proxy to the relevant hostgroup to trigger the retry.

One exception: if the shunned server is the only backend in its hostgroup and mysql-shun_recovery_time_sec is greater than 1, ProxySQL brings it back after 1 second regardless of the configured value. This prevents a single-backend hostgroup from being unreachable for too long.

Common operator mistakes

Treating SHUNNED as always critical. SHUNNED is often transient and self-correcting. Paging on every SHUNNED event causes alert fatigue. The correct page condition is zero ONLINE backends in a hostgroup with active traffic. A single backend going SHUNNED and recovering in 10 seconds is normal operation.

Querying mysql_servers instead of runtime_mysql_servers. The config table does not reflect monitor-driven status changes. Always check the runtime table or stats_mysql_connection_pool for live state.

Forgetting LOAD MYSQL SERVERS TO RUNTIME. Setting OFFLINE_SOFT or OFFLINE_HARD in mysql_servers without loading to runtime means the change has no effect. The backend stays ONLINE. This is the most common reason a planned drain does not work.

Forgetting SAVE MYSQL SERVERS TO DISK after a drain. Without saving, a ProxySQL restart reverts to the previous disk configuration and the drained backend comes back ONLINE.

Using OFFLINE_HARD when OFFLINE_SOFT would work. OFFLINE_HARD severs active connections and causes client-visible errors. If the backend is not actively harmful, OFFLINE_SOFT gives sessions time to complete without errors.

Expecting SHUNNED to self-heal without traffic. In low-traffic or idle environments, a shunned backend stays shunned until traffic triggers a retry. If you see a persistent SHUNNED with no apparent backend problem, check whether traffic is actually flowing to that hostgroup.

Signals to watch in production

Monitoring these states with Netdata

Per-second collection matters for ProxySQL backend states. SHUNNED events often last 10-15 seconds and are invisible to 60-second pollers.

• Connection pool stats per second. ConnUsed, ConnFree, and ConnERR per backend let you watch drains complete in real time and catch error spikes that precede shun events.
• SHUNNED transition visibility. Sub-second status changes are captured, so transient shuns and flapping patterns are visible.
• Monitor check trends. Rising error rates on ping and connect checks are the leading indicator before a shun event.
• Correlation across the stack. When a backend goes SHUNNED, you can correlate timing with system-level signals (CPU, network, disk I/O) and MySQL-level signals (replication lag, connection errors) without switching tools.

Related guides

• ProxySQL backend SHUNNED: why a healthy backend gets pulled out of rotation
• How ProxySQL actually works in production: a mental model for operators
• ProxySQL monitoring checklist: the signals every production proxy needs
• ProxySQL monitoring maturity model: from survival to expert