Build a Powerful MySQL Monitoring Platform with Prometheus and Grafana

Overview

For MySQL monitoring, many solutions exist (e.g., TianTu, Zabbix). This article uses Prometheus + Grafana, which are already deployed in the production environment.

Exporter Deployment

1. Install the MySQL exporter

# 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. Add a MySQL account for the exporter

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

3. Create the exporter configuration file

# cat /opt/mysqld_exporter-0.10.0.linux-amd64/.my.cnf
[client]
user=exporter
password=123456

4. Create a systemd service

# cat /etc/systemd/system/mysql_exporter.service
[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 job to the Prometheus server

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

6. Test the endpoint

http://192.168.1.12:9104/metrics

Check the mysql_up metric to verify the exporter is working:

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

Monitoring Metrics

When monitoring a service, you need to know which indicators reflect its health. For MySQL, common metrics include replication status, query throughput, slow queries, connection count, buffer‑pool usage, and query execution performance.

Replication Status

1. Master‑slave thread monitoring

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000045
Read_Master_Log_Pos: 72904854
Relay_Log_File: mariadb-relay-bin.000127
Relay_Log_Pos: 72905142
Relay_Master_Log_File: mysql-bin.000045
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

The exporter exposes mysql_slave_status_slave_sql_running to indicate health of the replication cluster.

# 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

2. Replication lag (Seconds_Behind_Master)

# 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

Query Throughput

The Questions status variable counts every client query. It can be queried with: SHOW GLOBAL STATUS LIKE "Questions"; The exporter provides mysql_global_status_questions:

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

PromQL can calculate the rate over a short interval, e.g.:

rate(mysql_global_status_questions[2m])

Slow Query Performance

MySQL tracks slow queries with the Slow_queries counter. The threshold is controlled by long_query_time (default 10 s). SHOW VARIABLES LIKE 'long_query_time'; Adjust the threshold if needed: SET GLOBAL long_query_time = 5; Exporter metric:

# 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

Rate of slow queries can be monitored with:

rate(mysql_global_status_slow_queries[5m])

Connection Monitoring

Maximum connections (default 151):

SHOW VARIABLES LIKE 'max_connections';

# HELP mysql_global_variables_max_connections Generic gauge metric from SHOW GLOBAL VARIABLES.
# TYPE mysql_global_variables_max_connections gauge
mysql_global_variables_max_connections 151

Current connections:

SHOW GLOBAL STATUS LIKE 'Threads_connected';

# 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

Active connections:

SHOW GLOBAL STATUS LIKE 'Threads_running';

# HELP mysql_global_status_threads_running Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_threads_running untyped
mysql_global_status_threads_running 1

Aborted connections (connection errors):

# HELP mysql_global_status_aborted_connects Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_aborted_connects untyped
mysql_global_status_aborted_connects 31

Available connections can be calculated in PromQL:

mysql_global_variables_max_connections - mysql_global_status_threads_connected

InnoDB Buffer Pool

Buffer pool size (default 128 MiB):

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

# 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

Read requests from the buffer pool:

# HELP mysql_global_status_innodb_buffer_pool_read_requests Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_innodb_buffer_pool_read_requests untyped
mysql_global_status_innodb_buffer_pool_read_requests 2.7711547168e+10

Disk reads (when the buffer pool cannot satisfy a request):

# HELP mysql_global_status_innodb_buffer_pool_reads Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_innodb_buffer_pool_reads untyped
mysql_global_status_innodb_buffer_pool_reads 138

PromQL can monitor the growth rate of disk reads:

rate(mysql_global_status_innodb_buffer_pool_reads[2m])

Grafana Dashboards

Master‑slave monitoring (template 7371)

MySQL status monitoring (template 7362)

Buffer pool status (template 7365)

Alert Rules

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_seconds_behind_master > 30
    for: 5s
    labels:
      severity: warning
    annotations:
      summary: "{{ $labels.instance }}: MySQL Slave delay exceeds 30 s"
      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: "Connection count exceeds threshold (current value: {{ $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: "Slow query count exceeds 3 per second (current value: {{ $value }})"

Conclusion

The MySQL monitoring platform is now operational. Extend it by adding more metrics as needed; the presented setup reflects a production‑grade configuration.