Monitoring MySQL/MariaDB With MySQL Exporter Prometheus Grafana

Monitoring MySQL/MariaDB dengan Mysql Exporter Prometheus Grafana

Artikel Installasi Node Exporter Prometheus Grafana

  • Pastikan sudah install prometheus dan grafana
  • Download MySQL exporter versi terbaru
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 -
  • Extract mysql exporter, masuk ke directory mysqld_exporter lalu copy ke /usr/local/bin
tar xvf mysqld_exporter*.tar.gz
cd mysqld_exporter*
cp mysqld_exporter /usr/local/bin
  • Cek versi mysqld exporter
mysqld_exporter  --version

mysqld_exporter, version 0.13.0 (branch: HEAD, revision: ad2847c7fa67b9debafccd5a08bacb12fc9031f1)
  build user:       root@e2043849cb1f
  build date:       20210531-07:30:16
  go version:       go1.16.4
  platform:         linux/amd64
  • Login ke dalam database mysql/mariadb, > buat user mysqld_exporter pada database mysql/mariadb dengan akses PROCESS, SELECT, REPLICATION CLIENT
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'Password_MySQLD' WITH MAX_USER_CONNECTIONS 5;
Query OK, 0 rows affected (0.019 sec)

MariaDB [(none)]> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> EXIT
Bye
  • Buat file untuk database credential /etc/.mysqld_exporter.cnf
nano /etc/.mysqld_exporter.cnf
[client]
user=mysqld_exporter
password=Password_MySQLD
  • Set permission file /etc/.mysqld_exporter.cnf
chown root:prometheus /etc/.mysqld_exporter.cnf
  • Tambahkan file konfigurasi systemd service untuk mysqld_exporter di /etc/systemd/system/mysql_exporter.service dengan isi file seperti berikut:
nano /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=icmp.my.id:9104

[Install]
WantedBy=multi-user.target
  • Reload system daemon dan jalankan service mysqld_exporter
sudo systemctl daemon-reload
sudo systemctl enable mysql_exporter
sudo systemctl start mysql_exporter
  • Cek service mysqld_exporter nya apakah sudah running atau belum
 sudo systemctl status mysql_exporter

● mysql_exporter.service - Prometheus MySQL Exporter
   Loaded: loaded (/etc/systemd/system/mysql_exporter.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2021-10-07 21:38:39 WIB; 30min ago
 Main PID: 57332 (mysqld_exporter)
   CGroup: /system.slice/mysql_exporter.service
           └─57332 /usr/local/bin/mysqld_exporter --config.my-cnf /etc/.mysqld_exporter.cnf --collect.global_status -...

Oct 07 22:06:24 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:06:24.583Z caller=exporter.go:174...ion"
Oct 07 22:06:39 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:06:39.586Z caller=exporter.go:174...ion"
Oct 07 22:06:54 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:06:54.585Z caller=exporter.go:174...ion"
Oct 07 22:07:09 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:07:09.587Z caller=exporter.go:174...ion"
Oct 07 22:07:24 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:07:24.586Z caller=exporter.go:174...ion"
Oct 07 22:07:39 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:07:39.589Z caller=exporter.go:174...ion"
Oct 07 22:07:54 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:07:54.598Z caller=exporter.go:174...ion"
Oct 07 22:08:09 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:08:09.594Z caller=exporter.go:174...ion"
Oct 07 22:08:24 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:08:24.583Z caller=exporter.go:174...ion"
Oct 07 22:08:39 icmp.my.id mysqld_exporter[57332]: level=error ts=2021-10-07T15:08:39.588Z caller=exporter.go:174...ion"
Hint: Some lines were ellipsized, use -l to show in full
  • Pastikan port 9104 sudah di allow oleh firewall, akses mysql exporter dengan url: http://ip_server_mysql_exporter:9104, sampai di sini installasi mysql exporter sudah selesai.

  • Setting pada prometheus
  • Edit file /etc/prometheus/prometheus.yml ganti pada bagian targets, tambahkan baris berikut pada bagian paling bawah:
  - job_name: server1_db
    static_configs:
      - targets: ['icmp.my.id:9104']
        labels:
          alias: db-server1
  • Restart service promethues
systemctl restart prometheus

  • Import dashboard pada grafana, dashboard yang akan digunakan adalah nomor 7362 (https://grafana.com/grafana/dashboards/7362).
  • Masukan nomor dashboard 7362 > Load > Edit nama dashboard > Pilih Metrics: Prometheus > Import
  • Tampilan dashboard mysql exporter prometheus grafana

herdiana3389

A system administrator with skills in system administration, virtualization, linux, windows, networking, cloud computing, container, etc.