Monitoring MySQL / MariaDB with Prometheus in five minutes

Prometheus is a leading time series database and monitoring solution
that is open source. Prometheus collects metrics from configured targets
at given intervals, evaluates rule expressions, displays the results,
and can trigger alerts if some condition is observed to be true.

Here we will look at how to configure Prometheus MySQL exporters on
database servers, both MySQL MariaDB and visualizing data with Grafana.
This will enable you to have a good view of database performance and
know where to check whenever you have issues.  The configuration of
alerting rules is beyond the scope of this guide, but I’ll try to cover
it in the next guides.

This guide will have three main steps

1. Installation and configuration of Prometheus server
2. Installation and configuration of MySQL Prometheus exporter on database servers
3. Creating / Importing MySQL Grafana dashboards – We will use readily baked dashboards by Percona.

Step 1: Install and Configure Prometheus server

I had written a comprehensive guide on how to install and configure Prometheus server. The guide was titled for Ubuntu and CentOS 7 but it should work for any other systemd server.

• How to Install Prometheus Server on CentOS / Ubuntu

Follow the guide and you should have a working Prometheus server at the end.

Step 2: Install and Configure Prometheus MySQL Exporter

Once you have installed Prometheus server, you need to
install Prometheus exporter for MySQL server metrics. Note that the
supported MySQL versions is 5.5 and up.

Add Prometheus system user and group:

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

This user will manage the exporter service.

Download and install Prometheus MySQL Exporter:

This should be done on MySQL / MariaDB servers, both slaves and master servers. You may need to check Prometheus MySQL exporter releases page for the latest release, then export the latest version  to VER variable as shown below:

Download latest release of MySQL exporter:

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

Confirm installation by checking version of mysqld_exporter:

$ mysqld_exporter  --version
mysqld_exporter, version 0.14.0 (branch: HEAD, revision: ca1b9af82a471c849c529eb8aadb1aac73e7b68c)
  build user:       root@401d370ca42e
  build date:       20220304-16:25:15
  go version:       go1.17.8
  platform:         linux/amd64

Create Prometheus exporter database user

The user should have PROCESS, SELECT, REPLICATION CLIENT grants:

CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'StrongPassword' WITH MAX_USER_CONNECTIONS 2;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';
FLUSH PRIVILEGES;
EXIT

If you have a Master-Slave database architecture, create user on the master servers only.

WITH MAX_USER_CONNECTIONS 2 is used to set a max connection limit for the user to avoid overloading the server with monitoring scrapes under heavy load.

Configure database credentials

Create database credentials file:

sudo vim /etc/.mysqld_exporter.cnf

Add correct username and password for user create

[client]
user=mysqld_exporter
password=StrongPassword

Set ownership permissions:

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

Create systemd unit file ( For Systemd systems )

This is for systemd servers, for SysV init system, use Prometheus MySQL exporter init script for SysV init system

Create a new service file:

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

Add the following content

[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

If your server has a public and private network, you may need to replace 0.0.0.0:9104 with private IP, e.g. 192.168.4.5:9104

When done, reload systemd and start mysql_exporter service.

sudo systemctl daemon-reload
sudo systemctl enable mysql_exporter
sudo systemctl start mysql_exporter

Configure MySQL endpoint to be scraped by Prometheus Server

Login to your Prometheus server and Configure endpoint to scrape. Below is an example for two MySQL database servers.

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

The first server has the IP address 10.10.1.10 and the second one is 10.10.1.11. Add other targets using the similar format. Job names should be unique for each target.

Note: Prometheus Server should be able to reach the
targets over the network. Ensure you have correct network/firewall
configurations.

Step 3: Creating / Importing MySQL Grafana dashboards

Now that we have the targets configured and agents to be monitored,
we should be good to add Prometheus data source to Grafana so that we
can do metrics visualization. If you don’t have a ready Grafana server,
use any of the guides below to install Grafana:

• Install Grafana and InfluxDB on CentOS 7
• How to Install Grafana on Ubuntu and Debian

When installed, login to admin dashboard and add Datasource by navigating to Configuration > Data Sources.

Name: Prometheus
Type: Prometheus
URL: http://localhost:9090

If Prometheus server is not on the same host as Grafana, provide IP address of the server.

Create / Import Grafana Dashboard for MySQL Prometheus exporter

If you don’t have all the golden time to create your own dashboards, you can use one created by Percona, they are Open source.

Let’s download MySQL_Overview dashboard which has a good overview of database performance.

mkdir ~/grafana-dashboards
cd ~/grafana-dashboards
wget https://raw.githubusercontent.com/percona/grafana-dashboards/main/dashboards/MySQL/MySQL_Instances_Overview.json

Upload Prometheus MySQL dashboard(s) to grafana

Go to Dashboards > Import > Upload .json file

Locate the directory with dashboard file and import.

Metrics collected should start showing.

If you wish to import all Percona dashboards for Prometheus, install them on Grafana server.

git clone https://github.com/percona/grafana-dashboards.git
cp -r grafana-dashboards/dashboards /var/lib/grafana/

You need to restart Grafana server to import these dashboards.

sudo systemctl restart grafana-server
sudo service grafana-server restart

You can then start using the dashboards on Grafana. I’ll do a guide
for how to Monitor Linux server with Prometheus, for OS metrics, before
then, check similar guides below:

• Monitor Linux systems with Grafana, telegraf, and InfluxDB.
• Monitor Linux Server Performance with Prometheus and Grafana in 5 minutes
• Monitor Apache Web Server with Prometheus and Grafana in 5 minutes