# Monitoring MySQL Metrics on Grafana via Prometheus

By Abhishek, 2024-11-23. Canonical: https://www.va2pt.com/post/monitoring-mysql-metrics-on-grafana-via-prometheus

> The client wants to monitor MySQL metrics in Grafana using Prometheus. This requires collecting and visualizing metrics such as database health, query…

![Monitoring MySQL Metrics on Grafana via Prometheus](/blog/monitoring-mysql-metrics-on-grafana-via-prometheus/img-1.webp)

## Problem Statement
The client wants to monitor MySQL metrics in **Grafana** using **Prometheus**. This requires collecting and visualizing metrics such as database health, query performance, and resource usage in real time.

## Solution Overview
To achieve this, you need to:

1.  Install and configure the **MySQL Exporter**, which acts as a bridge between Prometheus and the MySQL database.

2.  Configure **Prometheus** to scrape metrics from the MySQL Exporter.

3.  Import a pre-built MySQL dashboard into **Grafana** for visualization.

## Step-by-Step Implementation
### 1\. Install and Configure the MySQL Exporter
**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 sudo mv mysqld_exporter /usr/local/bin/
```

#### Create a Monitoring User in MySQL

-   Log into MySQL as a root user:

```
mysql -u root -p
```

-   Create a user with appropriate permissions:

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

#### Run MySQL Exporter
-   Start the exporter with connection details:

```
mysqld_exporter --mysql.user=exporter --mysql.password=password
```

-   Alternatively, create a .my.cnf file with credentials:

```
[client] user=exporter password=password
```

-   Then run:

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

### 2\. Configure Prometheus to Scrape MySQL Metrics
-   Edit the **Prometheus configuration file** (prometheus.yml):

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

-   Restart Prometheus to apply changes:

```bash
sudo systemctl restart prometheus
```

### 3\. Import MySQL Dashboard into Grafana
1.  Log into **Grafana**.

2.  Navigate to **Dashboards > Import**.

3.  Enter the dashboard ID for a pre-built MySQL dashboard, e.g., **7362 (MySQL Overview)**.

4.  Set the Prometheus data source.

5.  Import and verify that the dashboard displays real-time MySQL metrics.

## Optional Enhancements
-   **Configure Alerts in Grafana**: Set up alerts for metrics like high query latency, high memory usage, or connection saturation.

-   **Secure MySQL Exporter**: Use a .my.cnf file with restricted permissions to store credentials securely:

```
chmod 600 /path/to/.my.cnf
```

-   **Optimize Prometheus Queries**: Reduce query overhead by configuring Prometheus rules for aggregating common metrics.

-   **Enable TLS Encryption**: Secure the Prometheus and Grafana communication using SSL/TLS certificates.

## Conclusion
By setting up the **MySQL Exporter**, configuring **Prometheus** to scrape metrics, and importing a pre-built **Grafana dashboard**, you can effectively monitor MySQL performance.

Optional enhancements further secure and optimize the setup for enterprise-grade reliability.

Would you like assistance with automating this process or creating custom dashboards?

---

Published by VA2PT (https://www.va2pt.com). Related service: https://www.va2pt.com/24x7-sre-noc