ProxySQL MySQL MySQL · DBA · Monitoring

ProxySQL — MySQL Monitor User Setup

Configure the ProxySQL monitor module — create monitor user, set check intervals, view health check logs and understand auto-failover actions.

ProxySQL Monitor — Overview

The ProxySQL Monitor module continuously checks the health of all backend MySQL servers. It performs connection checks, ping checks, read_only checks, and replication lag checks. Based on these checks, ProxySQL automatically moves servers between hostgroups or marks them SHUNNED.

Step 1 — Create Monitor User in MySQL

SQL — Create Monitor User

-- Run on MySQL Master (replicates to replicas automatically)
CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonitorPass123!';
GRANT SELECT, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

-- Verify
SHOW GRANTS FOR 'monitor'@'%';

💡 Note: REPLICATION CLIENT privilege is required for ProxySQL to check replication lag using SHOW SLAVE STATUS or SHOW REPLICA STATUS.

Step 2 — Configure Monitor in ProxySQL

SQL — Set Monitor Credentials

-- Set monitor username and password
UPDATE global_variables SET variable_value='monitor'
WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='MonitorPass123!'
WHERE variable_name='mysql-monitor_password';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Monitor Interval Variables

SQL — Monitor Intervals

-- How often to test if ProxySQL can CONNECT to backend (ms)
UPDATE global_variables SET variable_value='60000'
WHERE variable_name='mysql-monitor_connect_interval';

-- Timeout for connect check (ms)
UPDATE global_variables SET variable_value='10000'
WHERE variable_name='mysql-monitor_connect_timeout';

-- How often to PING backends (ms)
UPDATE global_variables SET variable_value='10000'
WHERE variable_name='mysql-monitor_ping_interval';

-- Timeout for ping (ms)
UPDATE global_variables SET variable_value='1000'
WHERE variable_name='mysql-monitor_ping_timeout';

-- How often to check read_only variable (ms)
UPDATE global_variables SET variable_value='1500'
WHERE variable_name='mysql-monitor_read_only_interval';

-- Timeout for read_only check (ms)
UPDATE global_variables SET variable_value='500'
WHERE variable_name='mysql-monitor_read_only_timeout';

-- How long to keep monitor history (ms)
UPDATE global_variables SET variable_value='600000'
WHERE variable_name='mysql-monitor_history';

-- How often to check replication lag (ms)
UPDATE global_variables SET variable_value='1000'
WHERE variable_name='mysql-monitor_replication_lag_interval';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

All Monitor Variables Reference

Variable	Default	Description
mysql-monitor_username	monitor	MySQL user for all health checks
mysql-monitor_password	monitor	Password for monitor user
mysql-monitor_enabled	true	Enable/disable entire monitor module
mysql-monitor_connect_interval	60000	Connect check interval (ms)
mysql-monitor_connect_timeout	10000	Connect check timeout (ms)
mysql-monitor_ping_interval	10000	Ping interval (ms)
mysql-monitor_ping_timeout	1000	Ping timeout (ms)
mysql-monitor_read_only_interval	1500	read_only check interval (ms)
mysql-monitor_read_only_timeout	500	read_only check timeout (ms)
mysql-monitor_replication_lag_interval	1000	Replication lag check interval (ms)
mysql-monitor_replication_lag_timeout	1000	Replication lag check timeout (ms)
mysql-monitor_history	600000	How long to keep monitor logs (ms)
mysql-monitor_writer_is_also_reader	0	Master also serves reads

Viewing Monitor Logs

SQL — Monitor Logs

-- Connection check results
SELECT hostname, port, time_start_us, connect_success, error
FROM monitor.mysql_server_connect_log
ORDER BY time_start_us DESC
LIMIT 20;

-- Ping check results
SELECT hostname, port, time_start_us, ping_success, error
FROM monitor.mysql_server_ping_log
ORDER BY time_start_us DESC
LIMIT 20;

-- read_only check results (key for auto-failover)
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;

-- Replication lag results
SELECT hostname, port, time_start_us, replication_lag, error
FROM monitor.mysql_server_replication_lag_log
ORDER BY time_start_us DESC
LIMIT 20;

Understanding Monitor Actions

Check	Failure Action
Connect check fails	Server marked SHUNNED temporarily
Ping check fails	Server marked SHUNNED temporarily
read_only = ON detected	Server moved to reader_hostgroup automatically
read_only = OFF detected	Server moved to writer_hostgroup automatically
Replication lag > max_replication_lag	Server SHUNNED until lag reduces

Disabling Monitor Temporarily

SQL — Disable/Enable Monitor

-- Disable monitor (useful during maintenance)
UPDATE global_variables SET variable_value='false'
WHERE variable_name='mysql-monitor_enabled';

LOAD MYSQL VARIABLES TO RUNTIME;

-- Re-enable monitor
UPDATE global_variables SET variable_value='true'
WHERE variable_name='mysql-monitor_enabled';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;