(Last Updated On: September 14, 2018)

Prometheus MySQL Exporter is a client application used to get MySQL metrics and exporte 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:

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:

export VER=0.11.0
wget https://github.com/prometheus/mysqld_exporter/releases/download/v${VER}/mysqld_exporter-${VER}.linux-amd64.tar.gz
tar xvf mysqld_exporter-${VER}.linux-amd64.tar.gz
sudo mv  mysqld_exporter-${VER}.linux-amd64/mysqld_exporter /usr/local/bin/
sudo chmod +x /usr/local/bin/mysqld_exporter

Clean installation by removing the tarball and extraction directory.

rm -rf mysqld_exporter-${VER}.linux-amd64
rm mysqld_exporter-${VER}.linux-amd64.tar.gz

Step 3: 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.

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=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

If your system doesn’t support systemd init, use

Prometheus MySQL exporter init script for SysV init system

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 Monitoring MySQL / MariaDB with Prometheus in five minutes.