How to Monitor MySQL Deployments with Prometheus & Grafana at MysQL Overview

Introduction

In this article, I will explain to you how to monitoring mysql with prometheus & grafana at Mysql overview, This is motivated by the fact that one moment I had to create a dashboard whose function was to monitor mysql or the database, and the tools that I might be able to use prometheus & grafana, with service exporter. first I will explain toplogy or concept about grafana prometheus on this picture.

Prometheus is the tool that we will use to store mySQL metrics, from one or more exporters periodically and while Grafana as the UI display, and below are the steps to install and configure prometheus & grafana

Step Installation 1 – Installing & Configuring the Prometheus n grafana server

1. I have bash script for the installation and configuration of Prometheus grafana, so all you have to do is clone this repo

https://github.com/gunawan-d/grafana-prometheus.git

script bash like a this

#!/bin/bash

echo "=============================="
echo "====Installing Grafana========"
echo "=============================="
apt-get update -y
apt-get install -y gnupg2 curl software-properties-common
curl https://packages.grafana.com/gpg.key | sudo apt-key add -
add-apt-repository "deb https://packages.grafana.com/oss/deb stable main"
apt update
apt -y install grafana
systemctl start grafana-server
systemctl enable grafana-server
systemctl status grafana-server

echo "====Success & run nginx reverse proxy======"


echo "=============================="
echo "====Installing Prometheus====="
echo "=============================="

wget https://github.com/prometheus/prometheus/releases/download/v2.27.1/prometheus-2.27.1.linux-amd64.tar.gz
tar xvf prometheus-2.27.1.linux-amd64.tar.gz
cd prometheus-2.27.1.linux-amd64
mkdir -p /etc/prometheus
mkdir -p /var/lib/prometheus

#move prometheus
mv prometheus promtool /usr/local/bin/
mv consoles/ console_libraries/ /etc/prometheus/
mv prometheus.yml /etc/prometheus/prometheus.yml
prometheus --version
promtool --version

#create group
groupadd --system prometheus
useradd -s /sbin/nologin --system -g prometheus prometheus
chown -R prometheus:prometheus /etc/prometheus/  /var/lib/prometheus/
chmod -R 775 /etc/prometheus/ /var/lib/prometheus/
touch /etc/systemd/system/prometheus.service
cat > /etc/systemd/system/prometheus.service<<EOF
[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
Group=prometheus
Restart=always
Type=simple
ExecStart=/usr/local/bin/prometheus \
    --config.file=/etc/prometheus/prometheus.yml \
    --storage.tsdb.path=/var/lib/prometheus/ \
    --web.console.templates=/etc/prometheus/consoles \
    --web.console.libraries=/etc/prometheus/console_libraries \
    --web.listen-address=0.0.0.0:9090

[Install]
WantedBy=multi-user.target
EOF


#check service
systemctl start prometheus
systemctl enable prometheus
systemctl status prometheus

2. Testing access your host with a localhost or IP like a ” http://your_ip:9000″ for Prometheus & http://your_ip:3000 for grafana”

Enjoy like this

Step Installation 2 – Installing & Configuring the Prometheus n grafana server

Prometheus requires exporters to fetch the MySQL server metric data, and install it on the prometheus host server, so whatever service it can be passed using the metrics read by prometheus

1. Download & Install Prometheus MySQL Exporter

$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

2. Create Prometheus Exporter Database User to Access the Database for metrics

Create a user password with privileges that can access our database, and also provide a maximum of 2 database connections only

CREATE USER 'mysqld_exporter'@'<PrometheusHostIP>' IDENTIFIED BY 'Password' WITH MAX_USER_CONNECTIONS 2;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'<PrometheusHostIP>';
FLUSH PRIVILEGES;

3. Configure Database Credentials

Create file exporter in file directory
touch /etc/.mysqld_exporter.cnf

And add username & password for communication prometheus to MySQL server want yo monitor

$nano /etc/.mysqld_exporter.cnf

[client]
user=user_exporter
password=Password
host=IP-database

Set ownership permissions for exporter

chown root:prometheus /etc/.mysqld_exporter.cnf

4. Create systemd unit file

Create a new service file on prometheus host

nano /etc/systemd/system/mysql_exporter.service

files in it

[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

NB : address = 0.0.0.0:9104 specifies that the server can accept all incoming IPs to the prometheus host, if you have a public or private network, it is recommended to replace IP 0.0.0.0:9104 with a private IP, for example 192.168.70.40:9104

5. Configure MySQL endpoint to be included in prometheus monitoring, using yaml language, you can learn yaml learning in this link

Edit or create a new file in your prometheus.yml file, which is located in /etc/prometheus/prometheus.yml

scrape_configs:
  - job_name: prometheus
    static_configs:
      - targets: ['localhost:9090']
        labels:
          alias: prometheus

  - job_name: server_dbprod
    static_configs:
      - targets: ['192.168.10.xx:9104']
        labels:
          alias: db_productionapp

Monitoring multiple exporter or aplication Hosts form a central prometheus host

After that restart activate the mysql exporter service, then restart the prometheus service because earlier we added jobs or hosts that will be sourced by prometheus :

reload daemon, kemudian reload systemd & start mysql_exporter servicenya

$systemctl daemon-reload
$systemctl enable mysql_exporter
$systemctl start mysql_exporter

reload your service prometheus

systemctl reload prometheus

After everything is done, we can access the logging UI and check the targets menu, the mysql metric is detected

http://<PrometheusHostIP>:9090

Step Installation 3 – Creating Dashboards & Add Data Source

After we finish installing & setting prometheus grafana, the next step is to add the data source and also display the display to grafana.

Access grafana url : http://<GrafanaHostIP>:3000

Note: if prometheus is not active then we cannot add the data source so that between grafana & prometheus is mutually sustainable

this is an example of a grafana dashboard taken from MySQL using “mySQL overview” , for a dashboard/template you can import it from the official grafana documentation: https://grafana.com/grafana/dashboards.

The above Grafana dashboard displays MySQL Select Types, MySQL Client Thread Activity, MySQL Network Usage Hourly, and MySQL Table Locks metrics visualized in the charts, and the below Grafana dashboard displays MySQL Top Command Counters and MySQL Top Command Counters Hourly.

Finish & goodluck