ProxySQL — REST API & Prometheus Monitoring

-- Connect to ProxySQL Admin

-- Enable the REST API
UPDATE global_variables SET variable_value='true'
WHERE variable_name='admin-restapi_enabled';

-- Set REST API port (default 6070)
UPDATE global_variables SET variable_value='6070'
WHERE variable_name='admin-restapi_port';

LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

-- Verify REST API is enabled
SELECT variable_name, variable_value
FROM global_variables
WHERE variable_name LIKE '%restapi%';

# Test basic connectivity
curl http://127.0.0.1:6070/

# Get Prometheus metrics
curl http://127.0.0.1:6070/metrics

# Get metrics in JSON format
curl http://127.0.0.1:6070/metrics/json

# Specific metric categories
curl http://127.0.0.1:6070/metrics | grep proxysql_connection_pool
curl http://127.0.0.1:6070/metrics | grep proxysql_mysql_status
curl http://127.0.0.1:6070/metrics | grep proxysql_query_cache

# In prometheus.yml
cat >> /etc/prometheus/prometheus.yml << EOF
scrape_configs:
  - job_name: 'proxysql'
    static_configs:
      - targets: ['192.168.1.10:6070']
    scrape_interval: 15s
    scrape_timeout: 10s
    metrics_path: /metrics
EOF

# Reload Prometheus
systemctl reload prometheus

# Verify ProxySQL target is UP
# Open: http://prometheus:9090/targets

# Import community ProxySQL dashboard
# Dashboard ID: 11204 (ProxySQL Overview)
# Or: Dashboard ID: 14098

# In Grafana:
# 1. Go to Dashboards → Import
# 2. Enter Dashboard ID: 11204
# 3. Select your Prometheus datasource
# 4. Click Import

# Key panels to monitor:
# - Client connections (connected vs max)
# - Backend connection pool usage
# - Query rate (questions/second)
# - Cache hit ratio
# - Slow query rate
# - Backend server latency
# - Hostgroup query distribution

# Sample Prometheus alerting rules for ProxySQL
cat > /etc/prometheus/rules/proxysql.yml << EOF
groups:
  - name: proxysql_alerts
    rules:
      - alert: ProxySQLHighClientConnections
        expr: proxysql_mysql_status_client_connections_connected > 8000
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "ProxySQL high client connections"
          description: "ProxySQL has {{ dollar }}value client connections (threshold: 8000)"

      - alert: ProxySQLBackendDown
        expr: proxysql_connection_pool_status == 3
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "ProxySQL backend server offline"
          description: "Backend {{ dollar }}labels.srv_host is OFFLINE"

      - alert: ProxySQLHighSlowQueries
        expr: rate(proxysql_mysql_status_slow_queries[5m]) > 10
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "ProxySQL slow query rate high"
          description: "Slow query rate is {{ dollar }}value per second"
EOF

systemctl reload prometheus

# Simple bash monitoring script
#!/bin/bash
ADMIN="mysql -u admin -padmin -h 127.0.0.1 -P 6032 -N -s"

echo "=== ProxySQL Health Check ==="
echo "Client Connections:"
$ADMIN -e "SELECT variable_value FROM stats.stats_mysql_global WHERE variable_name='Client_Connections_connected';"

echo "Backend Pool Status:"
$ADMIN -e "SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree FROM stats.stats_mysql_connection_pool;"

echo "Top 5 Queries:"
$ADMIN -e "SELECT digest_text, count_star, avg_time FROM stats.stats_mysql_query_digest ORDER BY count_star DESC LIMIT 5;"

echo "Slow Queries:"
$ADMIN -e "SELECT variable_value FROM stats.stats_mysql_global WHERE variable_name='Slow_queries';"