Prometheus MySQL exporter init script for SysV init system

Here at neveropen, 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.

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

# Prometheus mysqld_exporter defaults
# See https://github.com/prometheus/mysqld_exporter
ARGS="--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.info_schema.tables \
--collect.global_variables \
--collect.info_schema.query_response_time \
--collect.info_schema.userstats \
--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"

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

• Monitoring MySQL / MariaDB with Prometheus in five minutes
• Monitoring Ceph Cluster with Prometheus and Grafana
• How to Monitor Redis Server with Prometheus and Grafana in 5 minutes
• How to Monitor Linux Server Performance with Prometheus and Grafana in 5 minutes
• How to Monitor BIND DNS server with Prometheus and Grafana