Prometheus MySQL exporter init script for SysV init [Guide]

Here at computingforgeeks, we have a comprehensive guide on Monitoring MySQL / MariaDB with Prometheus in five minutes. The initial guide was written for systemd servers, but you may be having MySQL / MariaDB servers running on SysV/Upstart init system. This system requires a compatible init script to manage the service. I’ll share a Prometheus MySQL exporter init script for SysV init system I wrote.

Original content from computingforgeeks.com - post 3619

Follow steps below to configure MySQL exporter init script.

Step 1: Install Prometheus server and daemonize

You need to have installed and configured Prometheus server before you can start creating the script on your MySQL server. Check:

How to Install Prometheus Server on CentOS / Ubuntu

Another requirement is the installation of daemonize package which will be used to run the process in the background

For Ubuntu / Debian systems, install it using:

sudo apt update && sudo apt install daemonize

For CentOS 6.x, use

sudo yum -y install daemonize

Step 2: Create system user used to run the script

It is not recommended to run init script as a root user, unless necessary. Let’s create user and group named prometheus

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

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

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 -
tar xvf mysqld_exporter*.tar.gz
sudo mv  mysqld_exporter-*.linux-amd64/mysqld_exporter /usr/local/bin/
sudo chmod +x /usr/local/bin/mysqld_exporter

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

Create daemon configuration file

sudo vim /etc/sysconfig/mysqld_exporter

Add the following contents. You can edit accordingly, all supported options are listed here.

# Prometheus mysqld_exporter defaults
# See https://github.com/prometheus/mysqld_exporter
ARGS="--config.my-cnf /etc/.mysqld_exporter.cnf \
--web.listen-address=0.0.0.0:9104"

Step 6: Create the init script file

Now create the init script file. It is available on my Github page.

Prometheus MySQL Exporter script github

Download the script and place it on /etc/init.d

git clone https://github.com/jmutai/prometheus-mysqld-exporter-init-script.git
cd prometheus-mysqld-exporter-init-script
chmod +x mysqld_exporter.init
sudo mv mysqld_exporter.init /etc/init.d/mysqld_exporter

To start the service, just run:

sudo /etc/init.d/mysqld_exporter start

Set it to start on boot

sudo chkconfig mysqld_exporter on
sudo chkconfig --list | grep mysqld_exporter

For configuration of MySQL endpoint to be scraped by Prometheus Server, check the guide