ProxySQL — Troubleshooting Common Issues

# Test admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032

# Test MySQL interface
mysql -u appuser -pAppPass -h 127.0.0.1 -P 6033

# Check if ProxySQL is running
systemctl status proxysql

# Check listening ports
ss -tlnp | grep proxysql

# Check error log
tail -100 /var/lib/proxysql/proxysql_errorlog

# Check firewall rules
firewall-cmd --list-ports
iptables -L -n | grep 6032
iptables -L -n | grep 6033

-- Check user exists in ProxySQL
SELECT username, password, default_hostgroup, active
FROM mysql_users
WHERE username='appuser';

-- Check user is loaded to RUNTIME
SELECT username, default_hostgroup FROM runtime_mysql_users;

-- If user missing from runtime:
LOAD MYSQL USERS TO RUNTIME;

-- Check user exists in MySQL backend
-- (Connect directly to MySQL and run:)
-- SELECT user, host, authentication_string FROM mysql.user WHERE user='appuser';

-- Check password matches
-- ProxySQL stores MySQL native hash — verify it matches MySQL

-- Check active query rules in RUNTIME
SELECT rule_id, active, match_pattern, destination_hostgroup, apply
FROM runtime_mysql_query_rules
ORDER BY rule_id;

-- If runtime rules differ from memory — reload:
LOAD MYSQL QUERY RULES TO RUNTIME;

-- Check query digest to see where queries are going
SELECT hostgroup, digest_text, count_star
FROM stats.stats_mysql_query_digest
ORDER BY count_star DESC LIMIT 20;

-- Check rule hit counts
SELECT rule_id, hits, match_pattern, destination_hostgroup
FROM stats.stats_mysql_query_rules
ORDER BY rule_id;

-- Rules with 0 hits may have wrong regex — test pattern:
-- SELECT 'SELECT * FROM users' REGEXP '^SELECT';  -- in MySQL

-- Check server status in RUNTIME
SELECT hostgroup_id, hostname, port, status, ConnUsed, ConnFree
FROM runtime_mysql_servers;

-- Server SHUNNED? Check why:
SELECT hostname, port, ping_success, error
FROM monitor.mysql_server_ping_log
WHERE ping_success=0
ORDER BY time_start_us DESC LIMIT 10;

-- Check connect errors
SELECT hostname, port, connect_success, error
FROM monitor.mysql_server_connect_log
WHERE connect_success=0
ORDER BY time_start_us DESC LIMIT 10;

-- Check read_only detection
SELECT hostname, port, read_only, error
FROM monitor.mysql_server_read_only_log
ORDER BY time_start_us DESC LIMIT 10;

-- Manually bring server back online
UPDATE mysql_servers SET status='ONLINE'
WHERE hostname='192.168.1.101';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Monitor cannot connect to backend?
-- Check monitor user exists in MySQL:
-- SHOW GRANTS FOR 'monitor'@'%';

-- Check monitor is enabled
SELECT variable_name, variable_value
FROM global_variables
WHERE variable_name LIKE '%monitor%';

-- Check monitor logs for errors
SELECT hostname, port, error
FROM monitor.mysql_server_connect_log
WHERE error IS NOT NULL
ORDER BY time_start_us DESC LIMIT 10;

-- Common fix: wrong monitor credentials
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;

-- Find slowest queries
SELECT digest_text, count_star, avg_time, max_time
FROM stats.stats_mysql_query_digest
ORDER BY avg_time DESC LIMIT 20;

-- Check connection pool - are backends saturated?
SELECT hostgroup, srv_host, ConnUsed, ConnFree, MaxConnUsed, Latency_us
FROM stats.stats_mysql_connection_pool
ORDER BY ConnUsed DESC;

-- Check for connection errors
SELECT variable_name, variable_value
FROM stats.stats_mysql_global
WHERE variable_name IN (
  'ConnPool_get_conn_failure',
  'Server_Connections_aborted',
  'Client_Connections_aborted'
);

-- Check active queries (anything stuck?)
SELECT user, hostgroup, command, time_ms, info
FROM stats.stats_mysql_processlist
WHERE time_ms > 5000
ORDER BY time_ms DESC;