MySQL Server exporter

Installation of MySQL Server Exporter in production environment consists from following high level steps:

Install exporter;
Configure authentication;
Configure firewall;
Configure service for autostart and restart;
Check access to exporter from I&B monitoring platform.

1. Install Prometheus mysqld_exporter.

Check latest version, available platforms of MySQL Server Exporter GitHub page and make necessary changes in variables on next step.

Prepare environment variables to simplify installation and configuration process.

Bash

export EXPORTER_VERSION=0.17.2
export EXPORTER_PLATFORM=linux-amd64

Download exporter

Bash

wget https://github.com/prometheus/mysqld_exporter/releases/download/v$EXPORTER_VERSION/mysqld_exporter-$EXPORTER_VERSION.$EXPORTER_PLATFORM.tar.gz

Create directory /usr/local/bin/mysqld_exporter and untar exporter into it.

Bash

sudo mkdir -p /usr/local/bin/mysqld_exporter
sudo tar xvfz mysqld_exporter-$EXPORTER_VERSION.$EXPORTER_PLATFORM.tar.gz -C /usr/local/bin/mysqld_exporter --strip-components=1

Create prometheus user. If it already exist - go to step 6.

Fedora (RHEL, CentOS)

Bash

sudo useradd -M -s /sbin/nologin prometheus

-M (or --no-create-home): This option prevents the creation of a home directory for the new user;
-s /sbin/nologin: This option sets the user's login shell to /sbin/nologin. This effectively prevents the user from interactively logging into the system. While the user won't have a password set, this further ensures they cannot log in.

Debian (Ubuntu)

Bash

sudo useradd -s /sbin/nologin prometheus --no-create-home

Make prometheus user owner of mysqld_exporter directory

Bash

sudo chown -hR prometheus:prometheus /usr/local/bin/mysqld_exporter

2. Configure firewall

Configure firewall on resource

MySQL Server Exporter uses default port 9104, so allow it in firewall on resource.

Fedora (RHEL, CentOS)

Bash

sudo firewall-cmd --zone=public --add-port=9104/tcp --permanent
sudo systemctl reload firewalld

Check firewall configuration

Bash

sudo firewall-cmd --list-all

You should see in output:

Bash

ports: 9104/tcp

Debian (Ubuntu)

Bash

sudo ufw allow 9104

You should see in output:

Bash

Rules updated
Rules updated (v6)

Check firewall configuration

Bash

sudo ufw status

You should see in output:

Bash

To                             Action          From
--                             ------          ----
9104                           ALLOW           Anywhere

3. Configure authentication for exporter

Do the steps described in Exporters configuration → Authentication section.

4. Check configuration

To connect a database you need to create user with rights PROCESS, REPLICATION CLIENT and SELECT for all tables in needed database. An example command for creating and configuring permissions is presented below.

Bash

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

If The MySQL server configured with SSL, you may need to specify a CA truststore to verify the server's chain-of-trust. You may also need to specify a SSL keypair for the client side of the SSL connection. To configure the mysqld exporter to use a custom CA certificate, and specify the client SSL keypair, add the following to the mysql cnf file.

Bash

ssl-ca=/path/to/ca/file
ssl-key=/path/to/ssl/client/key
ssl-cert=/path/to/ssl/client/cert

if SSL isn't configured this strings not needed.

Create file .my.cnf with user credentials

Bash

nano /usr/local/bin/mysqld_exporter/.my.cnf

And paste folfowing text into it

Bash

[client]
user = exporter
password = XXXXXXXX
ssl-ca=/path/to/ca/file
ssl-key=/path/to/ssl/client/key
ssl-cert=/path/to/ssl/client/cert

Run mysqld_exporter

Bash

/usr/local/bin/mysqld_exporter/mysqld_exporter --mysqld.username=exporter --web.config.file=/usr/local/bin/mysqld_exporter/web-config.yml --mysqld.address=127.0.0.1:3306

In this string --mysqld.username=exporter, exporter is username to connect to database, --web.config.file=/usr/local/bin/mysqld_exporter/web-config.yml sets path to your web-config file, --mysqld.address=127.0.0.1:3306 sets address:port to connect to the database

In output you should see:

Bash

... msg="TLS is enabled." ...

Check with browser if it is accessible at:

https://resource-hostname-or-ip:9104/metrics

After entering exporter’s user name and password you should see page like this:

Bash

# HELP go_gc_duration_seconds A summary of the wall-time pause (stop-the-world) duration in garbage collection cycles.
# TYPE go_gc_duration_seconds summary
go_gc_duration_seconds{quantile="0"} 8.8199e-05
...

Stop mysqld exporter with Ctrl-c in terminal where you run it.

Make mysqld_exporter service for autostart and restart.

Create file with your preferred text editor, for example nano …

Bash

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

… and paste following text into it.

Bash

[Unit]
Description=mysqld Exporter

[Service]
User=prometheus
ExecStart=/usr/local/bin/mysqld_exporter/mysqld_exporter --mysqld.username=exporter --web.config.file=/usr/local/bin/mysqld_exporter/web-config.yml --mysqld.address=127.0.0.1:3306

[Install]
WantedBy=multi-user.target

Save file and exit editor.

Start service …

Bash

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

… and check

Bash

sudo systemctl status mysqld_exporter

You should see following output similar to:

In case of any problem, you should view log entries for service:

Bash

journalctl -u mysqld_exporter.service -e

Now MySQL Server Exporter is ready to accept data and then expose it to Prometheus.