3. MySQL Prometheus Exporter Setup

Prometheus must have implemented a MySQL Exporter in order to fetch the metrics from the MySQL systems.

3.1 Install MySQL Prometheus Exporter

3.1.1 Download the latest version of MySQL Exporter from their official repository:

Releases · prometheus/mysqld_exporterGitHub

root@deploymentmachine:/home# curl -s https://github.com/prometheus/mysqld_exporter/releases | grep latest
<div class="release pt-2 pt-md-0 pb-3 pb-md-0 clearfix label-latest">
            <a class="border-0 Label--outline-green" href="/prometheus/mysqld_exporter/releases/latest">Latest release</a>
              <a class="border-0 Label--outline-green" href="/prometheus/mysqld_exporter/releases/latest">Latest release</a>
root@deploymentmachine:/home#

... better use github endoipoints...

root@deploymentmachine:/home# curl -s https://api.github.com/repos/prometheus/mysqld_exporter/releases/latest  |\
grep browser_download_url |\
grep linux-amd64.tar.gz |\
awk '{gsub(/"/, "", $2); print $2}' | wget -qi -

root@deploymentmachine:/home# ls -ltr my*
-rw-r--r-- 1 root root 7121565 Jul 29  2019 mysqld_exporter-0.12.1.linux-amd64.tar.gz

3.2 ... and untar for extracting files:

root@deploymentmachine:/home# tar xvf mysqld_exporter-0.12.1.linux-amd64.tar.gz
mysqld_exporter-0.12.1.linux-amd64/
mysqld_exporter-0.12.1.linux-amd64/NOTICE
mysqld_exporter-0.12.1.linux-amd64/mysqld_exporter
mysqld_exporter-0.12.1.linux-amd64/LICENSE
root@deploymentmachine:/home# 
root@deploymentmachine:/home# 
root@deploymentmachine:/home# ls my*
mysqld_exporter-0.12.1.linux-amd64.tar.gz

mysqld_exporter-0.12.1.linux-amd64:
LICENSE  NOTICE  mysqld_exporter

3.3 Move mysqld_exporter under /usr/local/bin, and make it executable:

 root@deploymentmachine:/home# mv  my*.linux-amd64/mysqld_exporter /usr/local/bin/
 root@deploymentmachine:/home# chmod +x /usr/local/bin/mysqld_exporter

3.2 Configure MySQL Prometheus Exporter

3.2.1 Create Exporter user to access databases

If you require details on how to login to OCI Mysql using mysql-shell, check tutorial "Access MySQL Instance"

We have deployed 3 MySQL systems with following private IPs: 10.0.0.28, 10.0.0.30, 10.0.0.32

We need to create an Exporter user on every MySQL data, and grant it permissions:

a) On MySQL system 10.0.0.28 (log into the system using mysqlsh)

root@deploymentmachine:~# mysqlsh usertest@10.0.0.28:3306
Please provide the password for 'usertest@10.0.0.28:3306': ***********
 MySQL  10.0.0.28:3306 ssl  JS >
 MySQL  10.0.0.28:3306 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  10.0.0.28:3306 ssl  SQL >
 MySQL  10.0.0.28:3306 ssl  SQL > CREATE USER 'user_exporter'@'%' IDENTIFIED BY 'Passw0rdStr0nk' WITH MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.0201 sec)
 MySQL  10.0.0.28:3306 ssl  SQL > GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'user_exporter'@'%';
Query OK, 0 rows affected (0.0034 sec)
 MySQL  10.0.0.28:3306 ssl  SQL >
 MySQL  10.0.0.28:3306 ssl  SQL > \exit
 Bye!

b) On MySQL system 10.0.0.30 (log into the system using mysqlsh)

root@deploymentmachine:~# mysqlsh usertest@10.0.0.30:3306
Please provide the password for 'usertest@10.0.0.30:3306': ***********
 MySQL  10.0.0.30:3306 ssl  JS >
 MySQL  10.0.0.30:3306 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  10.0.0.30:3306 ssl  SQL >
 MySQL  10.0.0.30:3306 ssl  SQL > CREATE USER 'user_exporter'@'%' IDENTIFIED BY 'Passw0rdStr0nk' WITH MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.0201 sec)
 MySQL  10.0.0.30:3306 ssl  SQL >
 MySQL  10.0.0.30:3306 ssl  SQL > GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'user_exporter'@'%';
Query OK, 0 rows affected (0.0034 sec)
 MySQL  10.0.0.30:3306 ssl  SQL >
 MySQL  10.0.0.30:3306 ssl  SQL > \exit
 Bye!

c) On MySQL system 10.0.0.32 (log into the system using mysqlsh)

root@deploymentmachine:~# mysqlsh usertest@10.0.0.32:3306
Please provide the password for 'usertest@10.0.0.32:3306': ***********
 MySQL  10.0.0.32:3306 ssl  JS >
 MySQL  10.0.0.32:3306 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  10.0.0.32:3306 ssl  SQL >
 MySQL  10.0.0.32:3306 ssl  SQL > CREATE USER 'user_exporter'@'%' IDENTIFIED BY 'Passw0rdStr0nk' WITH MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.0201 sec)
 MySQL  10.0.0.32:3306 ssl  SQL >
 MySQL  10.0.0.32:3306 ssl  SQL > GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'user_exporter'@'%';
Query OK, 0 rows affected (0.0034 sec)
 MySQL  10.0.0.32:3306 ssl  SQL >
 MySQL  10.0.0.32:3306 ssl  SQL > \exit
 Bye!

3.2.2 Configure Exporter files on the host where Prometheus is installed

Since we have 3 hosts, each host needs its own configuration file for an Exporter service. (this seems to be a normal behavior for MySQL Exporter )

For the configuration files (/etc/.mysqld_exporter.cnf, for instance), you need to provide same MySQL user (along with its password) that you have provided at step 3.2.1. Also, the host is the private IP of the database:

root@deploymentmachine:/home# more /etc/.mysqld_exporter.cnf
[client]
user=user_exporter
password=Passw0rdStr0nk
host=10.0.0.28
root@deploymentmachine:/home# more /etc/.mysqld_exporter_1.cnf
[client]
user=user_exporter
password=Passw0rdStr0nk
host=10.0.0.30
root@deploymentmachine:/home# more /etc/.mysqld_exporter_2.cnf
[client]
user=user_exporter
password=Passw0rdStr0nk
host=10.0.0.32
root@deploymentmachine:/home#

Once you have created all three configuration files, change the ownership:

root@deploymentmachine:/home# chown root:prometheus /etc/.mysqld_exporter.cnf
root@deploymentmachine:/home# chown root:prometheus /etc/.mysqld_exporter_1.cnf
root@deploymentmachine:/home# chown root:prometheus /etc/.mysqld_exporter_2.cnf

3.2.3. Configure Systemd for Exporter

At this step, as you have guessed it, we need three different configuration files for Systemd. Pay attention to ports. The port for Exporter is 9104. Since we need 3 different Exporter services, we will provide three different ports in every mysql_exporter.service file: 9104, 9105, 9106

• Systemd file that will use /etc/.mysqld_exporter.cnf && port 9104

root@deploymentmachine:/home# more /etc/systemd/system/mysql_exporter.service
[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

• Systemd file that will use /etc/.mysqld_exporter_1.cnf && port 9105

root@deploymentmachine:/home# more /etc/systemd/system/mysql_exporter_1.service
[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_1.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:9105

 [Install]
 WantedBy=multi-user.target

• Systemd file that will use /etc/.mysqld_exporter_2.cnf && port 9106

root@deploymentmachine:/home# more /etc/systemd/system/mysql_exporter_2.service
[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_2.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:9106

 [Install]
 WantedBy=multi-user.target

3.3 Before loading and enabling Systemd Exporter

This has two parts - networking changes and Prometheus changes

3.3.1 Networking changes

Since we are using new ports, we need to make a few changes:

3.3.1.1 In the Cloud

Add following ingress rules for ports 9104, 9105, respectively 9106:

... and the final result

3.3.1.2 On the host

Make sure you open the ports for listening:

root@deploymentmachine:/home# firewall-cmd --add-port=9104/tcp --permanent
success
root@deploymentmachine:/home# firewall-cmd --add-port=9105/tcp --permanent
success
root@deploymentmachine:/home# firewall-cmd --add-port=9106/tcp --permanent
success
root@deploymentmachine:/home# firewall-cmd --reload
success
root@deploymentmachine:/home#

3.3.1 Prometheus changes

Add the following changes for scrape_configs field in the /etc/prometheus/prometheus.yml file:

scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: 'mysql_server1'

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
    - targets: ['localhost:9104']

  - job_name: 'mysql_server2'
    static_configs:
    - targets: ['localhost:9105']

  - job_name: 'mysql_server3'
    static_configs:
    - targets: ['localhost:9106']

  - job_name: 'prometheus'
    static_configs:
    - targets: ['localhost:9090']

Load changes and restart prometheus service:

root@deploymentmachine:/home# systemctl daemon-reload
root@deploymentmachine:/home# systemctl restart prometheus

Now we are ready to start the three Exporter services

3.3 Start Exporter services and perform checking:

root@deploymentmachine:/home# systemctl daemon-reload
root@deploymentmachine:/home# systemctl enable mysql_exporter.service
root@deploymentmachine:/home# systemctl enable mysql_exporter_1.service
root@deploymentmachine:/home# systemctl enable mysql_exporter_2.service
root@deploymentmachine:/home# systemctl start mysql_exporter.service
root@deploymentmachine:/home# systemctl start mysql_exporter_1.service
root@deploymentmachine:/home# systemctl start mysql_exporter_2.service

Check if MySQL Exporter is listening on the three ports:

root@deploymentmachine:/home# lsof -i :9104  | grep -i listen
COMMAND     PID       USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld_ex 20292       root    3u  IPv6 234242      0t0  TCP *:9104 (LISTEN)
root@deploymentmachine:/home# lsof -i :9105  | grep -i listen 
COMMAND     PID       USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld_ex 20308       root    3u  IPv6 234288      0t0  TCP *:9105 (LISTEN)
root@deploymentmachine:/home# lsof -i :9106  | grep -i listen
COMMAND     PID       USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld_ex 20752       root    3u  IPv6 239749      0t0  TCP *:9106 (LISTEN)

Check in browser if mysql metric names are showing up in Prometheus: