Setting Up MySQL Monitoring with Prometheus (mysqld_exporter)

Setting Up MySQL Monitoring with Prometheus (mysqld_exporter)

Imagine this: at 3 AM, MySQL crashes due to connection pool exhaustion. You find out about it in the morning from clients. We deploy automatic monitoring that alerts you 5 minutes before the outage. MySQL generates tons of diagnostic information: SHOW STATUS, SHOW ENGINE INNODB STATUS, information_schema, performance_schema. Going through thousands of lines manually is pointless. We automate metric collection with mysqld_exporter, turning them into clear graphs and alerts in Grafana. Over 10 years, we've set up monitoring for 50+ projects. The cost savings from preventing just one night-time incident can be substantial.

Why mysqld_exporter?

It extracts everything from MySQL: from InnoDB cache hit ratio to replication lag. Without it, you learn about problems when the database is already down. With it, you get a warning 5 minutes before failure. As noted in Prometheus documentation, mysqld_exporter collects over 200 MySQL metrics.

Which metrics are critical?

InnoDB buffer pool. mysql_global_status_innodb_buffer_pool_reads and mysql_global_status_innodb_buffer_pool_read_requests. Calculate hit ratio: 1 - (rate(innodb_buffer_pool_reads[5m]) / rate(innodb_buffer_pool_read_requests[5m])). If below 99%, the buffer pool is too small. At 95%, urgently increase innodb_buffer_pool_size. On a project with 10k req/s, hit ratio dropped to 97%. We increased buffer pool and ratio returned to 99.9%.

Threads and connections. mysql_global_status_threads_connected / mysql_global_variables_max_connections. When more than 80% is used, a "Too many connections" error is imminent. Typical cause: connection leaks in the application.

Slow queries. rate(mysql_global_status_slow_queries[5m]) > 1 indicates a steady flow of slow queries. Check slow query log or performance_schema.events_statements_summary_by_digest.

Replication lag. mysql_slave_status_seconds_behind_master > 30 — lag over 30 seconds breaks read-heavy applications.

How to set up alerts?

We use standard Prometheus rules (mysql_up == 0, threads_connected / max_connections > 0.8, etc.). Our ready-to-use set covers all key scenarios. Order a comprehensive setup—we provide a complete set of rules in one day. Additional savings: a comprehensive setup avoids costly downtime.

How to deploy mysqld_exporter?

1. Create a user with minimal privileges:

CREATE USER 'exporter'@'%' IDENTIFIED BY 'exporter_secret' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
GRANT SELECT ON performance_schema.* TO 'exporter'@'%';
FLUSH PRIVILEGES;

2. Run the container:

services:
  mysqld-exporter:
    image: prom/mysqld-exporter:v0.15.1
    command:
      - "--mysqld.username=exporter"
      - "--mysqld.address=mysql:3306"
    ports:
      - "9104:9104"
    environment:
      MYSQLD_EXPORTER_PASSWORD: "exporter_secret"

3. Verify metric collection by querying http://localhost:9104/metrics.

MAX_USER_CONNECTIONS 3 is important: the exporter creates a separate connection per scrape; without a limit, an aggressive scrape_interval can exhaust max_connections.

Which collectors to enable?

Default collectors are enabled. For production, additionally add:

--collect.global_status
--collect.global_variables
--collect.slave_status
--collect.info_schema.innodb_metrics
--collect.info_schema.tables
--collect.perf_schema.eventsstatements
--collect.perf_schema.eventsstatementssum

perf_schema.eventsstatements is expensive—it reads performance_schema.events_statements_summary_by_digest. On loaded servers, set a separate job with scrape_interval: 60s.

Which MySQL metrics are key?

InnoDB buffer pool. mysql_global_status_innodb_buffer_pool_reads vs mysql_global_status_innodb_buffer_pool_read_requests — this is InnoDB cache hit ratio. Calculate as:

1 - (rate(innodb_buffer_pool_reads[5m]) / rate(innodb_buffer_pool_read_requests[5m])) 

If ratio drops below 99%, either the buffer pool is too small for the working set, or load has changed. A value of 95% is a serious signal.

Threads and connections. mysql_global_status_threads_connected and mysql_global_status_max_used_connections. When threads_connected approaches max_connections, MySQL starts refusing connections with "Too many connections". Classic story: developers don't close connections, a leak accumulates, and at 3 AM the database dies.

Slow queries. mysql_global_status_slow_queries — a monotonically increasing counter. rate(mysql_global_status_slow_queries[5m]) > 1 means a constant flow of slow queries. This signals checking the slow query log or performance_schema.events_statements_summary_by_digest.

Replication lag. On replicas, mysql_slave_status_seconds_behind_master shows lag in seconds. Values above 30 seconds cause issues for read-heavy applications using the replica.

Alerts in Prometheus

groups:
  - name: mysql
    rules:
      - alert: MySQLDown
        expr: mysql_up == 0
        for: 1m
        labels:
          severity: critical
      - alert: MySQLTooManyConnections
        expr: |
          mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL is using {{ $value | humanizePercentage }} connections"
      - alert: MySQLHighReplicationLag
        expr: mysql_slave_status_seconds_behind_master > 30
        for: 5m
        labels:
          severity: warning
      - alert: MySQLInnoDBBufferPoolLowHitRatio
        expr: |
          1 - (rate(mysql_global_status_innodb_buffer_pool_reads[5m]) / rate(mysql_global_status_innodb_buffer_pool_read_requests[5m])) < 0.99
        for: 10m
        labels:
          severity: warning

Grafana dashboard

Dashboard ID 7362 (MySQL Overview by Percona) is one of the best ready-made options. Covers InnoDB metrics, query analytics, connections, replication. Requires perf_schema collectors.

What's included in the setup?

• Installation and configuration of mysqld_exporter
• Setup of scrape jobs in Prometheus
• Basic alerts (5 rules) and Grafana dashboard
• Operation documentation and access details
• 2 months of warranty support

How we work

We start with a check: is performance_schema enabled in the MySQL config (performance_schema = ON in my.cnf), what is the current slow_query_log_min_time, are there replicas. On older MySQL versions (5.6 and below), some collectors aren't available—we explicitly limit the exporter's flags.

Deploying the exporter, setting up scrapes, basic alerts—2–3 hours. A complete alert set with replication, query analytics, and custom metrics for a specific database schema—up to 2 days.

Contact us for a consultation. We guarantee stable alert operation and complete metric transparency. Order the setup and forget about night-time incidents.