How to install and configure Prometheus mysql exporter in linux

The Prometheus Mysql exporter is a tool that periodically runs configured queries against a Mysql Server and exports the result as prometheus gauge metrics. It can be configured to collect MySQL metrics like queries per second (QPS) and InnoDB buffer pool size MySQL.

Prometheus is an open-source software application used for event monitoring and alerting. It can be used along with a visualization tool like Grafana to easily create and edit dashboards, query, visualize, alert on, and understand your metrics.

We will configure Prometheus to scrape MySQL Exporter metrics and optionally ship them.

# Ensure that the Prometheus Server is installed

Before proceeding, you need to have a working prometheus server. If you do not have one, checkout these guides on getting up and running with prometheus:

# Install and configure Prometheus mysql exporter

The prometheus mysql exporter is available as a separate installable binary. The official binary can be retrieved from the releases page of the product here.

Download the binary and move it to the bin directory:

# Download the binary
curl -LO https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz

# Then extract
tar xvf mysqld_exporter*.tar.gz

# Finally move to the binary directory and ensure that it is executable
sudo mv  mysqld_exporter-*.linux-amd64/mysqld_exporter /usr/local/bin/

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

Confirm the installation by checking mysqld_exporter version:

$ 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

# Add Prometheus system user and group:

Let us also create a dedicated user that will be used by the service:

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

This user will manage the exporter service.

# Creating Mysql User

Create a mysql user that can query the metrics:

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

The WITH MAX_USER_CONNECTIONS 3 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=exporter
password=xxxxxx
host=127.0.0.1

Set ownership permissions:

sudo chown mysql_exporter:mysql_exporter /etc/mysqld_exporter.cnf

# Create a Systemd Unit file

Create a new service file:

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

Add the following content

[Unit]
Description=Prometheus MySQL Exporter Service

After=network.target
User=mysql_exporter
Group=mysql_exporter

[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

The configs on ExecStart directive above instructs systemd to start the service with the configuration supplied while collecting all the specified stats. It will listen on the web.listen-address=0.0.0.0:9104.

Finally reload systemd units and enable the service on boot before starting it.

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

You can confirm that it is running as expected:

$ sudo systemctl status mysql_exporter
● mysql_exporter.service - Prometheus MySQL Exporter Service
     Loaded: loaded (/etc/systemd/system/mysql_exporter.service; enabled; vendor preset: disabled)
     Active: active (running) since Tue 2022-07-05 19:51:57 UTC; 26s ago
   Main PID: 113899 (mysqld_exporter)
      Tasks: 4 (limit: 49928)
     Memory: 2.7M
        CPU: 8ms
     CGroup: /system.slice/mysql_exporter.service
             └─113899 /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.pr>

Jul 05 19:51:57 kip-alma9srv.citizix.com mysqld_exporter[113899]: ts=2022-07-05T19:51:57.949Z caller=mysqld_exporter.go:293 level=info msg="Scraper enabled" scraper=perf_schema.eventswaits
Jul 05 19:51:57 kip-alma9srv.citizix.com mysqld_exporter[113899]: ts=2022-07-05T19:51:57.950Z caller=mysqld_exporter.go:303 level=info msg="Listening on address" address=0.0.0.0:9104

# Configure MySQL Endpoint to be Scraped by Prometheus

Make changes like below to the prometheus.yml file:

global:
  scrape_interval: 15s

scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: citizix_db
    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.
    static_configs:
      - targets: ['127.0.0.1:9104']
        labels:
          alias: salesdb

In the above config, we are targeting the localhost for scrape. If your prometheus server is running on a remote host from the mysql host, input it’s IP. Prometheus Server should be able to reach the targets over the network. Ensure you have correct network/firewall configurations.

# Configuring dashboard in Grafana

We can use grafana to create dashboards for our data. We can add our prometheus as a data source then use the data collected for visualization.

Checkout these guides on how to setup Grafana:

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.

Once added, you can create dashboard from the metrics.

Feel free to also check this Percona project for grafana dashboards.