How to Build a MySQL Monitoring Platform with Prometheus and Grafana

Overview

There are many ways to build a MySQL monitoring platform, such as TianTu‑based solutions or custom Zabbix integrations. The author chose a Prometheus + Grafana stack for production use because it meets daily operational needs.

Exporter Deployment

Install the MySQL exporter:

[root@controller2 opt]# https://github.com/prometheus/mysqld_exporter/releases/download/v0.10.0/mysqld_exporter-0.10.0.linux-amd64.tar.gz
[root@controller2 opt]# tar -xf mysqld_exporter-0.10.0.linux-amd64.tar.gz

Create a MySQL account for the exporter:

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

Write a minimal configuration file (

.my.cnf

) for the exporter:

[client]
user=exporter
password=123456

Create a systemd service unit (

mysql_exporter.service

) to run the exporter:

[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

Add the exporter to the Prometheus server configuration:

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

Test the endpoint (e.g.,

http://192.168.1.12:9104/metrics

) and verify that

mysql_up

returns

1

when the exporter is working.

#HELP mysql_up Whether the MySQL server is up.
#TYPE mysql_up gauge
mysql_up 1

Key Monitoring Metrics

When monitoring MySQL, focus on the following indicators:

Master‑slave replication health (

Slave_IO_Running

,

Slave_SQL_Running

)

Replication lag (

Seconds_Behind_Master

)

Query throughput (

Questions

and

Com_*

counters)

Slow query count (

Slow_queries

)

Connection usage (

Threads_connected

,

Threads_running

,

max_connections

)

InnoDB buffer pool size and activity (

innodb_buffer_pool_size

,

Innodb_buffer_pool_read_requests

,

Innodb_buffer_pool_reads

)

Examples of metric queries:

# HELP mysql_slave_status_slave_sql_running Generic metric from SHOW SLAVE STATUS.
# TYPE mysql_slave_status_slave_sql_running untyped
mysql_slave_status_slave_sql_running{channel_name="",connection_name="",master_host="172.16.1.1",master_uuid=""} 1

# HELP mysql_slave_status_seconds_behind_master Generic metric from SHOW SLAVE STATUS.
# TYPE mysql_slave_status_seconds_behind_master untyped
mysql_slave_status_seconds_behind_master{channel_name="",connection_name="",master_host="172.16.1.1",master_uuid=""} 0

# HELP mysql_global_status_questions Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_questions untyped
mysql_global_status_questions 13253

# HELP mysql_global_status_slow_queries Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_slow_queries untyped
mysql_global_status_slow_queries 0

# HELP mysql_global_status_threads_connected Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_threads_connected untyped
mysql_global_status_threads_connected 41

# HELP mysql_global_variables_innodb_buffer_pool_size Generic gauge metric from SHOW GLOBAL VARIABLES.
# TYPE mysql_global_variables_innodb_buffer_pool_size gauge
mysql_global_variables_innodb_buffer_pool_size 1.34217728e+08

Simple Alert Rules

groups:
- name: MySQL-rules
  rules:
  - alert: MySQL Status
    expr: up == 0
    for: 5s
    labels:
      severity: warning
    annotations:
      summary: "{{$labels.instance}}: MySQL has stop !!!"
      description: "检测MySQL数据库运行状态"

  - 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 stop !!!"
      description: "检测MySQL主从IO线程运行状态"

  - 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 stop !!!"
      description: "检测MySQL主从SQL线程运行状态"

  - alert: MySQL Slave Delay Status
    expr: mysql_slave_status_sql_delay == 30
    for: 5s
    labels:
      severity: warning
    annotations:
      summary: "{{$labels.instance}}: MySQL Slave Delay has more than 30s !!!"
      description: "检测MySQL主从延时状态"

  - alert: Mysql_Too_Many_Connections
    expr: rate(mysql_global_status_threads_connected[5m]) > 200
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "{{$labels.instance}}: 连接数过多"
      description: "{{$labels.instance}}: 连接数过多，请处理 ,(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}}: 慢查询有点多，请检查处理"
      description: "{{$labels.instance}}: Mysql slow_queries is more than 3 per second ,(current value is: {{ $value }})"

Finally, add the rule file to Prometheus (

rule_files: - "rules/*.yml"

) and reload the UI to see alerts in action.

Conclusion

The MySQL monitoring setup described above is ready for production use. Users can extend it with additional metrics and dashboards as needed.