Monitoring MySQL Metrics on Grafana via Prometheus

Table of Contents

1. Introduction

2. Step-by-Step Guide

   • Install and Configure the MySQL Exporter

   • Configure Prometheus to Scrape MySQL Exporter

   • Import MySQL Dashboard into Grafana

   • Verify Metrics

3. Optional Enhancements

4. Conclusion

   
   

Introduction

Monitoring database performance is critical for maintaining system reliability and optimizing resource usage. This guide outlines how to monitor MySQL metrics in Grafana using Prometheus, ensuring comprehensive insight into database performance.

Step-by-Step Guide

1. Install and Configure the MySQL Exporter

The MySQL Exporter collects metrics from your MySQL server and makes them accessible to Prometheus.

Steps to Install:

1. Download and extract the exporter:

   
   

   wget https://github.com/prometheus/mysqld_exporter/releases/latest/download/mysqld_exporter-<version>.linux-amd64.tar.gz tar -xvzf mysqld_exporter-<version>.linux-amd64.tar.gz cd mysqld_exporter-<version>.linux-amd64

   
   

2. Move the binary to a system path:

sudo mv mysqld_exporter /usr/local/bin/

Configure MySQL User:

1. Create a dedicated monitoring user in MySQL:

   
   

   CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password'; GRANT PROCESS, REPLICATION CLIENT, SELECT ON . TO 'exporter'@'localhost'; FLUSH PRIVILEGES;

1. Store credentials securely in a .my.cnf file:

   
   

[client] user=exporter password=password

Run the Exporter:

Start the exporter:

mysqld_exporter --config.my-cnf=/path/to/.my.cnf

2. Configure Prometheus to Scrape MySQL Exporter

Edit the Prometheus configuration file (prometheus.yml) to add a scrape job for the MySQL Exporter:

scrape_configs: - job_name: 'mysql' static_configs: - targets: ['<exporter-host>:9104']

sudo systemctl restart prometheus

3. Import MySQL Dashboard into Grafana

1. Log in to Grafana and navigate to Dashboards > Import.

2. Use Dashboard ID 7362 (MySQL Overview) from the Grafana Library.

3. Select Prometheus as the data source and import.

   
   

4. Verify Metrics

1. Confirm Prometheus is collecting metrics by checking for mysql_up and other MySQL metrics.

2. Open the Grafana dashboard and verify that MySQL metrics are displayed in real time.

   
   

Optional Enhancements

1. Set Alerts in Grafana: Configure alerts for high query times, low connections, or other key metrics.

2. Secure the Exporter : Restrict access to sensitive files such as .my.cnf:

chmod 600 /path/to/.my.cnf

1. Automate Exporter Management: Use systemd to manage the MySQL Exporter:

   
   

sudo nano /etc/systemd/system/mysqld_exporter.service

1. Example service file:

[Unit] Description=Prometheus MySQL Exporter After=network.target [Service] User=exporter ExecStart=/usr/local/bin/mysqld_exporter --config.my-cnf=/path/to/.my.cnf [Install] WantedBy=multi-user.target

Conclusion

Integrating MySQL metrics into Grafana using Prometheus provides a robust solution for monitoring database health and performance. With dashboards, real-time metrics, and alerting capabilities, you can proactively manage MySQL resources, ensure system reliability, and optimize query performance.

This step-by-step guide ensures a seamless setup process. For advanced configurations or troubleshooting, feel free to reach out for additional assistance.