DEV Community

Cover image for Monitor MySQL DB using Promethus-Grafana/Mysqld exporter
Kannan
Kannan

Posted on

Monitor MySQL DB using Promethus-Grafana/Mysqld exporter

  • Create a traget machine to install Mysql server
    Here I have created a MySQL target machine using VM (ubuntu 22.04)

  • Lets Install Mysql server
    apt update
    apt install mysql-server
    systemctl start mysql
    systemctl enable mysql
    systemctl status mysql

  • Add prometheus user in prometheus group

useradd --no-create-home --shell /bin/false prometheus
Enter fullscreen mode Exit fullscreen mode
groupadd --system prometheus

Enter fullscreen mode Exit fullscreen mode
useradd -s /sbin/nologin --system -g prometheus prometheus

Enter fullscreen mode Exit fullscreen mode
  • Downloading latest Mysqld-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 -
Enter fullscreen mode Exit fullscreen mode
  • Extract the downloaded file
tar xvf mysqld_exporter*.tar.gz
Enter fullscreen mode Exit fullscreen mode
root@mysql-2:~# tar xvf mysqld_exporter*.tar.gz
mysqld_exporter-0.15.0.linux-amd64/
mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter
mysqld_exporter-0.15.0.linux-amd64/NOTICE
mysqld_exporter-0.15.0.linux-amd64/LICENSE

Enter fullscreen mode Exit fullscreen mode

Move the mysqld-exporter to /usr/local/bin

mv  mysqld_exporter-*.linux-amd64/mysqld_exporter /usr/local/bin/
Enter fullscreen mode Exit fullscreen mode
  • giving permission to mysqld-exporter
chmod +x /usr/local/bin/mysqld_exporter
Enter fullscreen mode Exit fullscreen mode
  • verify the mysqld-exporter version

mysqld_exporter --version

root@mysql-2:~# mysqld_exporter  --version
mysqld_exporter, version 0.15.0 (branch: HEAD, revision: 6ca2a42f97f3403c7788ff4f374430aa267a6b6b)
  build user:       root@c4fca471a5b1
  build date:       20230624-04:09:04
  go version:       go1.20.5
  platform:         linux/amd64
  tags:             netgo

Enter fullscreen mode Exit fullscreen mode
  • Creating MySQL user and DB for mysqld-exporter
mysql -u root -p
Enter fullscreen mode Exit fullscreen mode
CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'StrongPassword';
Enter fullscreen mode Exit fullscreen mode
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';
Enter fullscreen mode Exit fullscreen mode
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode
EXIT
Enter fullscreen mode Exit fullscreen mode
root@mysql-2:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> EXIT
Bye

Enter fullscreen mode Exit fullscreen mode
  • Configure MySQL DB credentials

vim /etc/.mysqld_exporter.cnf

root@mysql-2:~# cat /etc/.mysqld_exporter.cnf
[client]
user=mysqld_exporter
password=******

Enter fullscreen mode Exit fullscreen mode
  • providing ownership

chown root:prometheus /etc/.mysqld_exporter.cnf

  • Create systemmd unit file

vim /etc/systemd/system/mysql_exporter.service

root@mysql-2:~# cat /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

Enter fullscreen mode Exit fullscreen mode
  • Reload the daemon and start,enable,status of the service

systemctl daemon-reload
systemctl enable mysql_exporter
systemctl start mysql_exporter
systemctl status mysql_exporter

  • Already we have created a prometheus server machine and done with the installation of(prometheus,grafana,alertmanager,node-exporter)

  • Adding scrape config file to communicate with db

vim /etc/prometheus/prometheus.yml

- job_name: 'server1_db'
    scrape_interval: 5s
    static_configs:
      - targets: ['server_ip:9104']
Enter fullscreen mode Exit fullscreen mode
root@prometheus-2:~# cat etc/prometheus/prometheus.yml
cat: etc/prometheus/prometheus.yml: No such file or directory
root@prometheus-2:~# cat /etc/prometheus/prometheus.yml

global:
  scrape_interval: 10s

scrape_configs:
  - job_name: 'prometheus'
    scrape_interval: 5s
    static_configs:
      - targets: ['localhost:9090']

  - job_name: 'prometheus_server'
    scrape_interval: 5s
    static_configs:
      - targets: ['192.168.122.138:9100']

  - job_name: 'server1_db'
    scrape_interval: 5s
    static_configs:
      - targets: ['192.168.122.137:9104']

Enter fullscreen mode Exit fullscreen mode
  • Adding Alert rules for msqld-exporter

vim /etc/prometheus/rules/alert-rules.yml

alertmanager rules:
- alert: MysqlDown
    expr: mysql_up == 0
    for: 2m
    labels:
      severity: critical
    annotations:
      summary: MySQL down (instance {{ $labels.instance }})
      description: "MySQL instance is down on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
Enter fullscreen mode Exit fullscreen mode
  • Restart and verify the status of all services (prometheus,grafana,node_exporter,alertmanager)

systemctl restart prometheus
systemctl status prometheus
systemctl restart grafana
systemctl status grafana
systemctl restart node_exporter
systemctl status node_exporter
systemctl restart alertmanager
systemctl status alertmanager

  • Need to import the JASON file at the Grafana dashboard

  • find the below link to get the JASON file.

[(https://github.com/prometheus/mysqld_exporter/blob/main/mysqld-mixin/dashboards/mysql-overview.json#L3)]

  • Copy the mysql-overview.jason file from the above link and paste under "import via dashboard JASON model"

Image description

  • Name the Dashboard and keep time sync "every 5 minutes" and the save the dashboard.

Image description

Now we able to Monitor the MySQL DB using Prometheus-Grafana/mysqld-exporter.

Heroku

This site is built on Heroku

Join the ranks of developers at Salesforce, Airbase, DEV, and more who deploy their mission critical applications on Heroku. Sign up today and launch your first app!

Get Started

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs