How to Set Up MariaDB/MySQL Exporter on Galera Cluster Nodes for Prometheus Monitoring

Ali Ghaemi — Sun, 06 Oct 2024 08:25:51 +0000

Hello, dev.to community! 👋

In this post, I’ll walk you through the steps I took to set up the MariaDB/MySQL Exporter on Galera Cluster nodes for monitoring with Prometheus. In my case, I’m running a MariaDB Galera Cluster on my cloud server provider with three nodes and using Prometheus + Grafana to monitor my setup. The Galera Cluster setup and monitoring tools have been incredibly useful, but getting the right metrics from MariaDB wasn’t straightforward, so I decided to document my solution.

To monitor MariaDB and Galera Cluster metrics such as the number of queries per second, connections, replication status, etc., you need to use a MySQL Exporter in combination with Prometheus. This exporter will expose database-specific metrics, including Galera and MariaDB statistics.

Steps to Set Up MariaDB/MySQL Exporter on Galera Cluster Nodes:

Prerequisites

A MariaDB Galera Cluster with multiple nodes.
A Prometheus server up and running.
Grafana for visualizing metrics (optional but recommended).
Access to the nodes to install the exporter.

Note: You can use already Prometheus Grafana's machins on marketplace of your cloud server provider. Hetzner, Digitalocean

Step 1: Install MySQL/MariaDB Exporter

On each Galera node, we need to install the MySQL Exporter, which collects metrics and exposes them for Prometheus. Here’s how you can do that:

Download and install the exporter from the official source

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz

Extract the files

tar xvfz mysqld_exporter-0.15.1.linux-amd64.tar.gz

Move the binary to a system path

sudo cp mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter /usr/local/bin/

Add a system user for the exporter

sudo useradd -rs /bin/false exporter

Step 2: Configure Exporter with MySQL/MariaDB Credentials

The exporter needs access to the MariaDB metrics. Create a .my.cnf file in the exporter’s home directory to store the connection credentials:

sudo mkdir /etc/.mysqld_exporter
sudo nano /etc/.mysqld_exporter/.my.cnf

Add the following to the file:

[client]
user=exporter
password=yourpassword

Make sure the user has the proper permissions to access the necessary metrics:

to access use mysql -u root or mariadb -u root

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

Step 3: Set Up the Exporter as a Service

To ensure the MySQL Exporter starts on boot, let’s configure it as a systemd service:

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

Add the following content to the file:

[Unit]
Description=Prometheus MySQL Exporter
Wants=network-online.target
After=network-online.target

[Service]
User=mysqld_exporter
Group=mysqld_exporter
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/.mysqld_exporter/.my.cnf

[Install]
WantedBy=multi-user.target

Save the file and reload systemd:

sudo systemctl daemon-reload
sudo systemctl start mysqld_exporter
sudo systemctl enable mysqld_exporter

Step 4: Add Exporter Targets to Prometheus

Next, update your Prometheus configuration to scrape the metrics from the nodes. Add the following to your prometheus.yml file:

Before that you can check if MySQL Exporter is running by visiting:

curl http://<MariaDB-nodes-ip>:9104/metrics

to access this file for example prometheus.yml use:
sudo nano /opt/containers/prometheus-grafana/prometheus/prometheus.yml

Add inside scrape_configs::

- job_name: 'mariadb-galera'
  static_configs:
    - targets: ['node1-ip:9104', 'node2-ip:9104', 'node3-ip:9104']

replease you ip of your database nodes

Then, reload Prometheus:

sudo systemctl reload prometheus

Step 5: Visualize in Grafana

If you’re using Grafana, you can import pre-built dashboards for MySQL/MariaDB monitoring. One popular dashboard ID for MySQL/MariaDB metrics is 7362.

To import:

Open Grafana.
Navigate to Dashboards > Import.
Enter 7362 in the "Grafana.com Dashboard" field, then click Load.

You should now see detailed metrics from your MariaDB Galera Cluster nodes in your Grafana dashboards!

Conclusion

With the MariaDB Exporter set up on each Galera Cluster node, Prometheus can now scrape and monitor key database metrics. This setup provides better insight into your cluster’s health and performance, making it easier to troubleshoot and optimize as needed.

Feel free to leave any questions or comments below if you're setting up something similar or have any suggestions. Thanks for reading!

Hello World

Ali Ghaemi — Sun, 06 Oct 2024 06:51:32 +0000

Hello! 👋

This is my very first post here, and I'm excited to start sharing my journey. I plan to document the technical challenges I encounter and the solutions I come up with. These challenges might have already been solved by others, or they might be very specific and even seem simple to some, but my goal is to create a public record of them for myself and for anyone else who might benefit.

By sharing, I hope to:

Keep track of my own learning and problem-solving.
Help others who might be facing similar challenges.
Get feedback and learn new approaches from the dev community.

I’ll be posting about a variety of technical topics as they come up, whether it’s related to development, server setup, databases, or anything else I’m working on. Stay tuned for more updates!

Thanks for reading, and I’m looking forward to being a part of this amazing community!

DEV Community: Ali Ghaemi