Comprehensive Guide to MySQL Monitoring with Prometheus, Grafana, and mysqld_exporter - ITFROMZERO - Share tobe shared! A comprehensive guide to building a deep MySQL monitoring system using Prometheus, Grafana, and mysqld_exporter, complete with production-ready configs, optimized PromQL queries, and bottleneck troubleshooting strategies.

Table of Contents

Context & Why In-Depth MySQL Monitoring Matters

Have you ever found yourself hunched over your laptop at 2 AM because your application threw a 504 Gateway Timeout? Frantically typing SHOW PROCESSLIST; without knowing what happened just five minutes earlier is a nightmare. The traditional approach usually involves enabling the slow query log or parsing log files manually. This method is not only slow, but it also leaves you completely reactive.

Once your users table crosses 10 million rows, slow queries start piling up. Adding indexes only scratches the surface. The root issue lies in gaining full observability across your infrastructure: How much space is left in the InnoDB Buffer Pool? How many IOPS is your disk handling? When did connection counts hit the max_connections ceiling?

The power trio of mysqld_exporter + Prometheus + Grafana solves this problem completely:

mysqld_exporter: An independent service running alongside MySQL that queries internal performance tables and exposes metrics via an HTTP endpoint in Prometheus format.
Prometheus: A time-series database that periodically pulls data from exporters, stores it, and continuously evaluates alert rules.
Grafana: A visualization dashboard that turns millions of data points into intuitive charts and dispatches real-time alerts to Telegram or Slack.

Setting Up the Monitoring Stack

Rule number one: never use MySQL’s root user for the exporter. Always grant minimal privileges to a dedicated user to mitigate security risks.

1. Create a Dedicated MySQL User for mysqld_exporter

CREATE USER 'mysqld_exporter'@'127.0.0.1' IDENTIFIED BY 'MatKhauPhucTap@2026' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON performance_schema.* TO 'mysqld_exporter'@'127.0.0.1';
FLUSH PRIVILEGES;

Important Note: The WITH MAX_USER_CONNECTIONS 3 flag acts as a critical safety barrier. Even if the exporter gets stuck on a query or hangs, it will never consume more than 3 connections, preserving resources for your primary application.

2. Install mysqld_exporter on the Database Server

Download the binary directly from the official Prometheus GitHub repository:

cd /tmp
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar -xvf mysqld_exporter-0.15.1.linux-amd64.tar.gz
sudo mv mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter /usr/local/bin/
sudo useradd -rs /bin/false prometheus_exporter

Store the database credentials in a secure configuration file at /etc/.mysqld_exporter.cnf:

[client]
user=mysqld_exporter
password=MatKhauPhucTap@2026
host=127.0.0.1
port=3306

Restrict file permissions to ensure only the service user can read it:

sudo chown prometheus_exporter:prometheus_exporter /etc/.mysqld_exporter.cnf
sudo chmod 600 /etc/.mysqld_exporter.cnf

3. Create a Systemd Service

Create the unit file at /etc/systemd/system/mysqld_exporter.service:

[Unit]
Description=Prometheus MySQL Exporter
After=network.target mysql.service

[Service]
User=prometheus_exporter
Group=prometheus_exporter
Type=simple
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/.mysqld_exporter.cnf \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.processlist \
  --collect.global_status \
  --collect.global_variables
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

Enable the service and verify the connection:

sudo systemctl daemon-reload
sudo systemctl enable --now mysqld_exporter

# Verify metric output
curl -s http://localhost:9104/metrics | grep -E '^mysql_up'

An output of mysql_up 1 confirms that the exporter has successfully connected to the database.

Prometheus Scraping & Alert Rules Configuration

Once the metric endpoint is running, the next step is configuring scrape targets and defining alert rules on your Prometheus server.

1. Define the Scrape Job in prometheus.yml

Add the target configuration to /etc/prometheus/prometheus.yml:

scrape_configs:
  - job_name: 'mysql_production'
    scrape_interval: 15s
    scrape_timeout: 10s
    static_configs:
      - targets: ['192.168.1.50:9104']
        labels:
          environment: 'production'
          cluster: 'core-database'

Production Tip: A scrape_interval: 15s strike the ideal balance. Scraping too aggressively (e.g., every 1–2 seconds) creates unnecessary query overhead against database metadata, particularly during peak load.

2. Configure Production Alert Rules

Create /etc/prometheus/alert_rules_mysql.yml with battle-tested thresholds:

groups:
  - name: mysql_critical_alerts
    rules:
      # Alert: MySQL instance is down
      - alert: MySQLInstanceDown
        expr: mysql_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MySQL instance {{ $labels.instance }} is down"
          description: "Failed to connect to the MySQL instance for more than 1 minute."

      # Alert: Connection pool reaching critical threshold
      - alert: MySQLTooManyConnections
        expr: (mysql_global_status_threads_connected / mysql_global_variables_max_connections) * 100 > 85
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "MySQL connection pool overloaded: {{ $value | printf '%.2f' }}%"
          description: "Active connections exceeded 85% of the configured max_connections limit."

      # Alert: InnoDB Buffer Pool Hit Ratio too low
      - alert: MySQLInnoDBBufferPoolHitRatioLow
        expr: 100 - (rate(mysql_global_status_innodb_buffer_pool_reads[5m]) / rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]) * 100) < 95
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Low InnoDB cache hit ratio: {{ $value | printf '%.2f' }}%"
          description: "Hit ratio dropped below 95% for 5 minutes. The buffer pool may lack RAM or full table scans are occurring repeatedly."

Visualization & Monitoring via Grafana Dashboard

With metrics flowing into Prometheus, it is time to turn them into intuitive visual dashboards in Grafana.

1. Connect Data Source and Import a Pre-built Dashboard

Access Grafana in your browser (default port 3000).
Navigate to Connections > Data Sources > Select Prometheus > Enter your Prometheus server URL (e.g., http://prometheus-server:9090).
Rather than building panels from scratch, go to Dashboards > New > Import, enter dashboard ID 7362 (a popular community dashboard for mysqld_exporter), and select your Prometheus data source.

2. Four Critical Metrics to Watch

While dashboards include dozens of panels, you only need to focus on four core metrics during an incident:

Threads Running vs. Threads Connected: If threads_connected reaches 500 while threads_running stays below 15, your database is healthy because most connections are merely idle pool connections. However, if threads_running exceeds your CPU core count (e.g., jumping to 40 on an 8 vCPU server), the system is choking on locks or query backlogs.
InnoDB Buffer Pool Hit Ratio: This metric should consistently stay at 99% or higher. If it drops to 92–94%, your innodb_buffer_pool_size is insufficient to hold the hot working set. The database will fall back to reading from disk, causing I/O pressure and latency spikes from a few milliseconds to hundreds of milliseconds.
Queries per Second (QPS) & Questions: Helps map out traffic curves by hour. You will clearly see peak traffic windows (e.g., 8 PM – 10 PM), allowing you to avoid scheduling heavy cron jobs or backups during high-load periods.
InnoDB Log File Syncs: Measures how frequently transaction redo logs flush to disk. If this metric surges alongside disk I/O queue spikes, consider adjusting innodb_flush_log_at_trx_commit from 1 to 2 (trading up to 1 second of transactions during a sudden power outage to significantly relieve disk write pressure).

3. Testing Real-World Alerting Scenarios

To confirm that your alerting pipeline works as expected, simulate an outage on your staging environment using sudo systemctl stop mysql. Exactly one minute later, check the Alerting tab in Grafana or Prometheus to ensure the MySQLInstanceDown rule transitions to the Firing state. Once notifications arrive in your configured Telegram or Slack channels, you can safely deploy the setup to production.