Monitoring MySQL and MariaDB Droplets Using Prometheus MySQL Exporter

Introduction

Effective monitoring of MySQL and MariaDB databases is essential for maintaining optimal performance, identifying potential bottlenecks, and ensuring overall system reliability. Prometheus MySQL Exporter is a robust tool that offers detailed insights into database metrics that are crucial for proactive management and troubleshooting.

In this tutorial, you will learn how to set up Prometheus MySQL Exporter to monitor MySQL and MariaDB databases using manual configuration and script-based automation. These methods offer flexibility to select the best deployment strategy and operational workflow approach.

By the end, you’ll have an operational monitoring system using Prometheus and Grafana for real-time performance analysis.

Prerequisites

Before you begin, make sure you have the following prerequisites in place:

• You’ll need to have MySQL or MariaDB database servers set up and running on a Ubuntu Droplet. You can refer to our tutorials on How to Install MariaDB on Ubuntu and How to Install MySQL on Ubuntu. The servers you want to monitor should be accessible from the machine where you plan to install Prometheus MySQL Exporter.

• You’ll need SSH access to the MySQL or MariaDB Ubuntu Droplets to install and configure Prometheus MySQL Exporter.

• Prometheus MySQL Exporter integrates with Prometheus for metrics scraping and Grafana for visualization. Here, you will be using Prometheus and Grafana images from DigitalOcean Marketplace to monitor the database Droplets.

Method 1-Manual Configuration

Let’s proceed with the manual configuration method in this section.

Add Prometheus System User and Group

In this step, you will configure the Target Droplets. Let’s Create a system user and group named “prometheus” to manage the exporter service.

sudo groupadd --system prometheus

sudo useradd -s /sbin/nologin --system -g prometheus prometheus

Download and Install Prometheus MySQL Exporter

In this step, you will download the latest release of Prometheus MySQL Exporter from GitHub, extract the downloaded files, and move the binary to the /usr/local/bin/ directory.

curl -s https://api.github.com/repos/prometheus/mysqld_exporter/releases/latest | grep browser_download_url | grep linux-amd64 | cut -d '"' -f 4 | wget -qi -

tar xvf mysqld_exporter*.tar.gz

sudo mv mysqld_exporter-*.linux-amd64/mysqld_exporter /usr/local/bin/

sudo chmod +x /usr/local/bin/mysqld_exporter

Create Prometheus Exporter Database User

Log in to MySQL as root and create a dedicated user for the exporter with the necessary privileges.

mysql -u root -p

Enter the root password, if required, and you will see the MySQL/MariaDB prompt. Then execute the following commands (replace giveapassword in the below command with a new password for the mysqld_exporter database user).

Create a new MySQL user named mysqld_exporter with a password giveapassword for local connections.

CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'giveapassword';

Grant the mysqld_exporter user permissions to view process lists, client replication status, and query data from all databases.

GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';

Reload the grant tables to apply the changes made to user permissions.

FLUSH PRIVILEGES;

Exit the MySQL command-line interface.

EXIT

Configure Database Credentials

Create a configuration file to store the MySQL exporter credentials.

sudo vi /etc/.mysqld_exporter.cnf

Add the following user credentials to the file:

[client]
user=mysqld_exporter
password=giveapassword

Set ownership permissions for the configuration file:

sudo chown root:prometheus /etc/.mysqld_exporter.cnf

Create systemd Unit File

Create a systemd service unit file for managing the MySQL Exporter service:

sudo vi /etc/systemd/system/mysql_exporter.service

Add the following data to the file:

[Unit]
Description=Prometheus MySQL Exporter
After=network.target
User=prometheus
Group=prometheus

[Service]
Type=simple
Restart=always
ExecStart=/usr/local/bin/mysqld_exporter \
--config.my-cnf /etc/.mysqld_exporter.cnf \
--collect.global_status \
--collect.info_schema.innodb_metrics \
--collect.auto_increment.columns \
--collect.info_schema.processlist \
--collect.binlog_size \
--collect.info_schema.tablestats \
--collect.global_variables \
--collect.info_schema.query_response_time \
--collect.info_schema.userstats \
--collect.info_schema.tables \
--collect.perf_schema.tablelocks \
--collect.perf_schema.file_events \
--collect.perf_schema.eventswaits \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tableiowaits \
--collect.slave_status \
--web.listen-address=0.0.0.0:9104

[Install]
WantedBy=multi-user.target

Reload systemd and Start MySQL Exporter Service

Reload systemd configuration and start the MySQL Exporter service:

sudo systemctl daemon-reload

sudo systemctl enable mysql_exporter

sudo systemctl start mysql_exporter

Configuring the Prometheus Droplet

Let’s start by taking a backup of the prometheus.yml file:

cp /etc/prometheus/prometheus.yml /etc/prometheus/prometheus.yml-$(date +'%d%b%Y-%H:%M')

Now log in to your Prometheus server and add the MySQL Exporter endpoints to be scraped.

Replace the IP addresses and ports with your MySQL Exporter endpoints (9104 is the default port for Prometheus MySQLd exporter).

vi /etc/prometheus/prometheus.yml

scrape_configs:
  - job_name: server1_db
    static_configs:
      - targets: ['10.10.1.10:9104']
        labels:
          alias: db1

  - job_name: server2_db
    static_configs:
      - targets: ['10.10.1.11:9104']
        labels:
          alias: db2

Method 2-Configuring Using Scripts

You can also achieve this by running two scripts - one for the target droplets and the other for the Prometheus droplet.

Let’s start by configuring the Target Droplets.

Step 1: SSH into the Target Droplet.

Step 2: Download the Target Configuration script by using the following command:

wget https://solutions-files.ams3.digitaloceanspaces.com/MySQL-MariaDB-Monitoring/DO_MySQL_MariaDB_Target_Config.sh

Step 3: Once the script is downloaded, ensure it has executable permissions by running:

chmod +x DO_MySQL_MariaDB_Target_Config.sh

Step 4: Execute the script by running:

./DO_MySQL_MariaDB_Target_Config.sh

Step 5: Enter a new password for the mysql_exporter user, and then enter the MySQL root password:

The configuration is complete.

Configuring the Prometheus Droplet (Script Method)

Step 1: SSH into the Prometheus Droplet.

Step 2: Download the Prometheus Configuration script by using the following command:

wget https://solutions-files.ams3.digitaloceanspaces.com/MySQL-MariaDB-Monitoring/DO_MySQL_MariaDB_Prometheus_Config.sh

This bash script automates updating the Prometheus configuration file to add new server endpoints. It starts by backing up the current prometheus.yml file with a timestamped filename.

The script then prompts the user to specify the number of servers to be added and validates this input.

For each server, it requests a hostname and IP address, ensuring that each IP address is unique within the configuration file. Valid server configurations are appended to the prometheus.yml file. After updating the configuration, the script restarts the Prometheus service and checks its status to confirm that the changes have been applied.

Finally, it waits for user input before exiting.

Step 3: Once the script is downloaded, ensure it has executable permissions by running:

chmod +x DO_MySQL_MariaDB_Prometheus_Config.sh

Step 4: Execute the script by running:

./DO_MySQL_MariaDB_Prometheus_Config.sh

Step 5: Enter the number of Droplets to add to monitoring.

Step 6: Enter the hostnames and IP addresses.

Step 7: The configuration is complete.

Step 8: Once added, check whether the targets are updated by accessing the URLprometheushostname:9090/targets.

Configuring Grafana

Step 1: Log into the Grafana dashboard by visiting Grafana-IP:3000 on a browser.

Step 2: Go to Configuration > Data Sources.

Step 3: Click on Add data source.

Step 4: Search and Select Prometheus.

Step 5: Enter Name as Prometheus, and URL (Prometheushostname:9090) and click Save & Test. If you see “Data source is working”, you have successfully added the data source. Once done, go to Create > Import.

Step 6: You can manually configure the dashboard or import the dashboard by uploading the JSON file. A JSON template for MySQL and MariaDB monitoring can be found in the below link:

https://solutions-files.ams3.digitaloceanspaces.com/MySQL-MariaDB-Monitoring/DO_Grafana-Mysql-MariaDB_Monitoring.json

Step 7: Fill in the fields and Import.

Step 8: The Grafana dashboard is ready. Select the host and check if the metrics are visible. You can modify and edit the dashboard as needed.

Conclusion

In this tutorial, you learned to automate the setup process for deploying Prometheus MySQL Exporter on your servers. By following the steps, you can efficiently install and configure the exporter to collect MySQL metrics for monitoring.

By automating these steps, you can streamline the deployment process, reduce the risk of human error, and ensure consistent setup across multiple servers. This tutorial is a reliable and efficient solution for deploying Prometheus MySQL Exporter in your monitoring infrastructure.