Automatic Failover in ProxySQL
ProxySQL provides automatic failover for MySQL backend servers through its monitor module. When a backend server becomes unavailable, ProxySQL detects this through health checks and automatically removes it from the active server pool — without any manual intervention or application changes.
How Failover Detection Works
- Monitor module pings all backend servers at mysql-monitor_ping_interval
- If ping fails mysql-monitor_ping_max_failures times in a row → server marked SHUNNED
- ProxySQL stops routing new connections to SHUNNED server
- Existing connections complete or are killed (OFFLINE_SOFT vs OFFLINE_HARD)
- Monitor continues checking — when server recovers, it returns to ONLINE
Failover Detection Variables
SQL — Failover Variables
-- Max consecutive ping failures before server is SHUNNED
UPDATE global_variables SET variable_value='3'
WHERE variable_name='mysql-monitor_ping_max_failures';
-- How fast to detect failure (check every 1 second)
UPDATE global_variables SET variable_value='1000'
WHERE variable_name='mysql-monitor_ping_interval';
-- Timeout per ping (ms)
UPDATE global_variables SET variable_value='500'
WHERE variable_name='mysql-monitor_ping_timeout';
-- How long a SHUNNED server waits before retry
UPDATE global_variables SET variable_value='100'
WHERE variable_name='mysql-shun_recovery_time_sec';
-- Max replication lag before replica is SHUNNED
UPDATE global_variables SET variable_value='5'
WHERE variable_name='mysql-monitor_replication_lag_interval';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK; Master Failover Scenario
When the MySQL Master fails and a replica is promoted:
SQL — Manual Master Failover
-- Scenario: Master at 192.168.1.100 dies
-- Replica at 192.168.1.101 is promoted to Master
-- Step 1: ProxySQL automatically detects master is gone (SHUNNED)
-- Check current server status:
SELECT hostgroup_id, hostname, port, status
FROM runtime_mysql_servers;
-- Step 2: Manually update ProxySQL to point writes to new master
-- (Or use orchestrator/ProxySQL with mysql_replication_hostgroups for auto-detection)
-- Remove old master
UPDATE mysql_servers SET status='OFFLINE_HARD'
WHERE hostname='192.168.1.100' AND hostgroup_id=10;
-- Add new master (promoted replica)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, max_connections, comment)
VALUES (10, '192.168.1.101', 3306, 'ONLINE', 1, 1000, 'New MySQL Master');
-- Update old replica entry (remove from reader hostgroup since it is now master)
DELETE FROM mysql_servers WHERE hostname='192.168.1.101' AND hostgroup_id=20;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK; Auto-Failover with mysql_replication_hostgroups
If you configure mysql_replication_hostgroups, ProxySQL monitors the read_only variable on all servers and automatically moves them between writer and reader hostgroups. When a replica is promoted to master (read_only=OFF), ProxySQL automatically routes writes to it.
SQL — Auto-Failover Setup
-- Setup: add ALL servers to writer hostgroup
-- ProxySQL will auto-sort them based on read_only
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status)
VALUES (10, '192.168.1.100', 3306, 'ONLINE'); -- Master (read_only=OFF)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status)
VALUES (10, '192.168.1.101', 3306, 'ONLINE'); -- Replica (read_only=ON → moved to HG20)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status)
VALUES (10, '192.168.1.102', 3306, 'ONLINE'); -- Replica (read_only=ON → moved to HG20)
-- Tell ProxySQL: HG10=writers, HG20=readers, use read_only to decide
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type)
VALUES (10, 20, 'read_only');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
-- Now when a replica is promoted (read_only=OFF), ProxySQL auto-moves it to HG10
-- When old master recovers (read_only=ON), ProxySQL auto-moves it to HG20 Monitor Failover Events
SQL — Monitor Failover Events
-- Check current server status
SELECT hostgroup_id, hostname, port, status, ConnUsed, ConnFree
FROM runtime_mysql_servers ORDER BY hostgroup_id;
-- Check ping failures
SELECT hostname, port, ping_success, error
FROM monitor.mysql_server_ping_log
WHERE ping_success=0
ORDER BY time_start_us DESC LIMIT 20;
-- Check connect failures
SELECT hostname, port, connect_success, error
FROM monitor.mysql_server_connect_log
WHERE connect_success=0
ORDER BY time_start_us DESC LIMIT 20;
-- Check read_only changes (shows failover events)
SELECT hostname, port, read_only, time_start_us
FROM monitor.mysql_server_read_only_log
ORDER BY time_start_us DESC LIMIT 30; Integration with Orchestrator
For fully automated master failover, ProxySQL is commonly integrated with Orchestrator (MySQL topology manager). Orchestrator detects master failure, elects a new master, and updates ProxySQL via its API:
BASH — Orchestrator Integration
# Orchestrator can call ProxySQL hooks on failover
# Example: orchestrator-client command to update ProxySQL
orchestrator-client -c relocate-replicas -i old-master:3306 -d new-master:3306
# Or use Orchestrator hooks to run SQL against ProxySQL admin
mysql -u admin -padmin -h proxysql -P 6032 -e "
UPDATE mysql_servers SET status='OFFLINE_HARD'
WHERE hostname='old-master' AND hostgroup_id=10;
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status)
VALUES (10, 'new-master', 3306, 'ONLINE');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
"