Building a High‑Performance MySQL Monitoring Platform with Prometheus and Grafana

Overview – The author describes a MySQL monitoring stack based on Prometheus and Grafana, explaining why this combination meets daily operational needs and linking to an introductory installation guide.

Exporter deployment

1. Install the mysqld_exporter binary:

# https://github.com/prometheus/mysqld_exporter/releases/download/v0.10.0/mysqld_exporter-0.10.0.linux-amd64.tar.gz
# tar -xf mysqld_exporter-0.10.0.linux-amd64.tar.gz

2. Create a MySQL user for the exporter:

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'exporter'@'%' IDENTIFIED BY 'localhost';
flush privileges;

3. Write a minimal .my.cnf for the exporter:

[client]
user=exporter
password=123456

4. Define a systemd service mysql_exporter.service that starts the exporter with the required collectors:

[Unit]
Description=mysql Monitoring System
Documentation=mysql Monitoring System

[Service]
ExecStart=/opt/mysqld_exporter-0.10.0.linux-amd64/mysqld_exporter \
    -collect.info_schema.processlist \
    -collect.info_schema.innodb_tablespaces \
    -collect.info_schema.innodb_metrics \
    -collect.perf_schema.tableiowaits \
    -collect.perf_schema.indexiowaits \
    -collect.perf_schema.tablelocks \
    -collect.engine_innodb_status \
    -collect.perf_schema.file_events \
    -collect.info_schema.processlist \
    -collect.binlog_size \
    -collect.info_schema.clientstats \
    -collect.perf_schema.eventswaits \
    -config.my-cnf=/opt/mysqld_exporter-0.10.0.linux-amd64/.my.cnf

[Install]
WantedBy=multi-user.target

5. Add the exporter job to prometheus.yml:

- job_name: 'mysql'
  static_configs:
    - targets: ['192.168.1.11:9104','192.168.1.12:9104']

6. Verify that metrics are exposed (e.g., http://192.168.1.12:9104/metrics) and that mysql_up returns 1.

Key monitoring metrics

Replication health: mysql_slave_status_slave_sql_running, mysql_slave_status_seconds_behind_master Throughput: mysql_global_status_questions (queries), global_status_commands_total (command breakdown)

Slow queries: mysql_global_status_slow_queries Connection counts: mysql_global_status_threads_connected, mysql_global_status_threads_running, mysql_global_status_aborted_connects InnoDB buffer pool: mysql_global_variables_innodb_buffer_pool_size, mysql_global_status_innodb_buffer_pool_read_requests, mysql_global_status_innodb_buffer_pool_reads Alert rules (Grafana/Prometheus)

groups:
- name: MySQL-rules
  rules:
  - alert: MySQL Status
    expr: up == 0
    for: 5s
    labels:
      severity: warning
    annotations:
      summary: "{{ $labels.instance }}: MySQL has stopped!"
      description: "Detect MySQL database running status"

  - alert: MySQL Slave IO Thread Status
    expr: mysql_slave_status_slave_io_running == 0
    for: 5s
    labels:
      severity: warning
    annotations:
      summary: "{{ $labels.instance }}: MySQL Slave IO Thread has stopped!"
      description: "Detect MySQL slave IO thread status"

  - alert: MySQL Slave SQL Thread Status
    expr: mysql_slave_status_slave_sql_running == 0
    for: 5s
    labels:
      severity: warning
    annotations:
      summary: "{{ $labels.instance }}: MySQL Slave SQL Thread has stopped!"
      description: "Detect MySQL slave SQL thread status"

  - alert: MySQL Slave Delay Status
    expr: mysql_slave_status_sql_delay == 30
    for: 5s
    labels:
      severity: warning
    annotations:
      summary: "{{ $labels.instance }}: MySQL Slave delay > 30s!"
      description: "Detect MySQL replication lag"

  - alert: Mysql Too Many Connections
    expr: rate(mysql_global_status_threads_connected[5m]) > 200
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "{{ $labels.instance }}: Too many connections"
      description: "Current value is {{ $value }}"

  - alert: Mysql Too Many Slow Queries
    expr: rate(mysql_global_status_slow_queries[5m]) > 3
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "{{ $labels.instance }}: Slow queries rate high"
      description: "Current value is {{ $value }}"

These rules are referenced in the Prometheus configuration via rule_files: ["rules/*.yml"] and become visible in the Grafana web UI.

Conclusion – The author confirms that the MySQL monitoring stack is fully operational and encourages readers to extend the setup with additional metrics and alerts tailored to their production environments.