Configure Prometheus MySQL Exporter on Ubuntu 18.04 / CentOS 7

By
Thapelo Manthata
-
0
18

Prometheus MySQL Exporter is a client application used to get MySQL metrics and export to Prometheus server. The installation and usage of Prometheus MySQL Exporter to monitor MySQL/MariaDB servers were covered in Monitoring MySQL / MariaDB with Prometheus in five minutes.

In this article, I’ll summarise the guide for guys whose interest is just to install Prometheus MySQL exporter.

Step 1: Add Prometheus system user and group:

Create dedicated Prometheus user and group.

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

This user will manage the exporter service.

Step 2: 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:

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 -

Extract the file after downloading:

tar xvf mysqld_exporter-*.linux-amd64.tar.gz

Give extracted file executable bits and move it to /usr/local/bin directory.

cd mysqld_exporter-*.linux-amd64/

Clean installation by removing the tarball and extraction directory:

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

Check the version of the software installed.

$ 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

Step 3: Create Prometheus exporter database user

Login to MySQL server shell as root user:

$ mysql -u root -p

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.

Step 4: 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

Step 5: 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=<strong>0.0.0.0:9104</strong>

[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

If your system doesn’t support systemd init, use

If you’re interested in knowing how to add targets to Prometheus server and start visualizing metrics with Grafana, then you need to check our guide how to: