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.

Top comments (0)