MODOP – Grafana/Promotheus pour Cluster MySQL/Percona

La machine sera une RockyLinux 8.4

1°) Prérequis

Un cluster Mysql/Percona
MODOP – Installation CLUSTER MySQL Percona et HA Proxy

Inscription de toutes les machines au DNS de l’infrastructure
MODOP – Configurer un DNS Primaire et Secondaire

2°) Spécification machine

Machine Grafana02
IP : 192.168.1.4 (vSwitch vmbr0)
Host : grafana.house.cpb
IP : 172.16.185.24 (vSwitch vmbr1)
Host : grafana02.house.cpb
Disque 1 – Système 20Go
RAM 2G

3°) Installation de Middleware

[root@grafana02 ~]# dnf update
[root@grafana02 ~]# dnf -y install vim bash-completion nmap net-tools wget

4°) Suppression du selinux

[root@grafana02 ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
[root@grafana02 ~]# reboot

5°) Désactivation IPv6

[root@node01-ceph-admin ~]# vi /etc/sysctl.conf

net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.all.autoconf = 0
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv6.conf.default.autoconf = 0

[root@node01-ceph-admin ~]# sysctl -p

6°)  Installation Promotheus

Création User Promotheus

[root@grafana02 chris]# useradd --no-create-home --shell /bin/false prometheus

Récupération de promotheus

[root@grafana02 tmp]# wget https://github.com/prometheus/prometheus/releases/download/v2.8.1/prometheus-2.8.1.linux-amd64.tar.gz
[root@grafana02 tmp]# tar xzvf prometheus-2.8.1.linux-amd64.tar.gz
[root@grafana02 tmp]# mv prometheus-2.8.1.linux-amd64 prometheuspackage

Création structure Promotheus

[root@grafana02 tmp]# mkdir /etc/prometheus
[root@grafana02 tmp]# mkdir /var/lib/prometheus
[root@grafana02 tmp]# chown prometheus:prometheus /etc/prometheus
[root@grafana02 tmp]# chown prometheus:prometheus /var/lib/prometheus

Copier les binaires sur la structure Promotheus

[root@grafana02 tmp]# cp prometheuspackage/prometheus /usr/local/bin/
[root@grafana02 tmp]# cp prometheuspackage/promtool /usr/local/bin/
[root@grafana02 tmp]# chown prometheus:prometheus /usr/local/bin/prometheus
[root@grafana02 tmp]# chown prometheus:prometheus /usr/local/bin/promtool

Copier les fichiers conf sur la structure Promotheus

[root@grafana02 tmp]# cp -r prometheuspackage/consoles /etc/prometheus
[root@grafana02 tmp]# cp -r prometheuspackage/console_libraries /etc/prometheus
[root@grafana02 tmp]# chown -R prometheus:prometheus /etc/prometheus/consoles
[root@grafana02 tmp]# chown -R prometheus:prometheus /etc/prometheus/console_libraries

Création du service Promotheus

[root@grafana02 tmp]# vi /etc/systemd/system/prometheus.service

[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target
<[Service]
User=prometheus
Group=prometheus
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
[Install]
WantedBy=multi-user.target

Configurer Promotheus

[root@grafana02 chris]# vi /etc/prometheus/prometheus.yml

global:
 scrape_interval: 10s

scrape_configs:
 - job_name: 'Percona'
 static_configs:
 - targets: ['localhost:9090', 'node01-sql:9104']

Démarrage du service Promotheus

[root@grafana02 tmp]# systemctl daemon-reload
[root@grafana02 tmp]# systemctl start prometheus
[root@grafana02 tmp]# systemctl status prometheus

Régle de Firewall – Promotheus

[root@grafana02 ~]#firewall-cmd --zone=public --add-port={9090,9104}/tcp --permanent
[root@grafana02 ~]# firewall-cmd –reload

http://grafana.house.cpb:9090

7°) Installation Grafana

[root@grafana02 ~]# vi /etc/yum.repos.d/grafana.repo

[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt

[root@grafana02 ~]# dnf update
[root@grafana02 ~]# dnf install grafana

Installation de font du Supplémentaire

[root@grafana02 ~]# dnf install fontconfig freetype* urw-fonts

Activer Grafana

[root@grafana02 ~]# systemctl start grafana-server && systemctl enable grafana-server [root@grafana02 ~]# systemctl status grafana-server

Régle de Firewall – grafana

[root@grafana02 ~]# firewall-cmd --zone=public --add-port=3000/tcp --permanent
[root@grafana02 ~]# firewall-cmd --reload

• http://grafana.house.cpb:3000

Passons sur l’installation des « exporter » sur les machines Percona/Mysql.

8°) Installation Mysql-exporter (nodes Mysql)

Créer un user Spécifique aux échanges Promotheus/Cluster Percona

[root@node01-sql ~]# useradd -rs /bin/false prometheus

Télécharger l’exporter Mysql

• https://github.com/prometheus/mysqld_exporter/

[root@node01-sql ~]# cd /tmp
[root@node01-sql tmp]# yum install git
[root@node01-sql tmp]# git clone https://github.com/prometheus/mysqld_exporter.git

Créer un user privilégié pour l’accès aux bases de données

[root@node01-sql tmp]# mysql -u root –p

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

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

MariaDB [mysql]> select user,host from mysql.user;

Installation des outils de compilation

[root@node01-sql tmp]# cd /tmp/mysqld_exporter/
[root@node01-sql mysqld_exporter]# yum groupinstall 'Development Tools'
[root@node01-sql mysqld_exporter]# yum update –y
[root@node01-sql tmp]# cd /tmp;wget https://golang.org/dl/go1.15.3.linux-amd64.tar.gz
[root@node01-sql tmp]# tar -zxvf go1.15.3.linux-amd64.tar.gz -C /usr/local

[root@node01-sql tmp]# echo 'export GOROOT=/usr/local/go' | sudo tee -a /etc/profile
export GOROOT=/usr/local/go
[root@node01-sql tmp]# echo 'export PATH=$PATH:/usr/local/go/bin' | sudo tee -a /etc/profile
export PATH=$PATH:/usr/local/go/bin
[root@node01-sql tmp]# source /etc/profile

[root@node01-sql tmp]# go version
go version go1.15.3 linux/amd64

Compiler et installer exporter Mysql

[root@node01-sql tmp]# cd mysqld_exporter/
[root@node01-sql mysqld_exporter]# make
(ne pas prendre en compte les erreurs sur « Access denied for user 'root'@'localhost' »)
[root@node01-sql mysqld_exporter]# mv mysqld_exporter /usr/local/bin/

[root@node01-sql mysqld_exporter]# cd /etc
[root@node01-sql etc]# vi .exporter.cnf

[client]
user=exporter
password=bettyboop

Création du service exporter sur le client

[root@node01-sql mysqld_exporter]# vi /lib/systemd/system/mysql_exporter.service

[Unit]
Description=MySQL Exporter
User=prometheus
[Service]
Type=simple
Restart=always
ExecStart=/usr/local/bin/mysqld_exporter \
--config.my-cnf /etc/.exporter.cnf \
--collect.auto_increment.columns \
--collect.binlog_size \
--collect.engine_innodb_status \
--collect.global_status \
--collect.global_variables \
--collect.info_schema.clientstats \
--collect.info_schema.innodb_metrics \
--collect.info_schema.innodb_tablespaces \
--collect.info_schema.innodb_cmp \
--collect.info_schema.innodb_cmpmem \
--collect.info_schema.processlist \
--collect.info_schema.processlist.min_time=0 \
--collect.info_schema.query_response_time \
--collect.info_schema.tables \
--collect.info_schema.tables.databases="*" \
--collect.info_schema.tablestats \
--collect.info_schema.userstats \
--collect.perf_schema.eventsstatements \
--collect.perf_schema.eventsstatements.digest_text_limit=120 \
--collect.perf_schema.eventsstatements.limit=250 \
--collect.perf_schema.eventsstatements.timelimit=86400 \
--collect.perf_schema.eventswaits \
--collect.perf_schema.file_events \
--collect.perf_schema.file_instances \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tableiowaits \
--collect.perf_schema.tablelocks \
--web.listen-address=0.0.0.0:9104
[Install]
WantedBy=multi-user.target

[root@node01-sql mysqld_exporter]# systemctl enable mysql_exporter.service
[root@node01-sql mysqld_exporter]# systemctl start mysql_exporter.service
[root@node01-sql mysqld_exporter]# systemctl status mysql_exporter.service

[root@node01-sql mysqld_exporter]# journalctl –e

Régle de Firewall – exporter vers promotheus

[root@grafana02 ~]# firewall-cmd --zone=public --add-port=9104/tcp --permanent
[root@grafana02 ~]# firewall-cmd --reload

9°) Configurer grafana Pomotheus

« Add your first data source »

Ajout d’un dasboard

• https://grafana.com/grafana/dashboards/7374
• https://grafana.com/grafana/dashboards/14617
• https://grafana.com/grafana/dashboards/14619
• https://grafana.com/grafana/dashboards/13106

Import d’un dasboard


« Load »

« Import »

10°) Ajouter les nodes à Grafana/Promotheus

Copier l’exporter sur les autres nodes

[root@node01-sql ~]# scp /usr/local/bin/mysqld_exporter root@node02-sql:/usr/local/bin/mysqld_exporter
[root@node01-sql ~]# scp /usr/local/bin/mysqld_exporter root@node03-sql:/usr/local/bin/mysqld_exporter
[root@node01-sql ~]# scp /usr/local/bin/mysqld_exporter root@node04-sql:/usr/local/bin/mysqld_exporter

Copie de l’utilisateur exporter@localhost

[root@node01-sql ~]# scp /etc/.exporter.cnf root@node02-sql:/etc/.exporter.cnf
[root@node01-sql ~]# scp /etc/.exporter.cnf root@node03-sql:/etc/.exporter.cnf
[root@node01-sql ~]# scp /etc/.exporter.cnf root@node04-sql:/etc/.exporter.cnf

Copier le service exporter sur les autres nodes

[root@node01-sql ~]# scp /lib/systemd/system/mysql_exporter.service root@node02-sql:/lib/systemd/system/mysql_exporter.service
[root@node01-sql ~]# scp /lib/systemd/system/mysql_exporter.service root@node03-sql:/lib/systemd/system/mysql_exporter.service
[root@node01-sql ~]# scp /lib/systemd/system/mysql_exporter.service root@node04-sql:/lib/systemd/system/mysql_exporter.service

Lancer le service exporter sur les nodes MySQL
Node02

[root@node01-sql ~]# systemctl -H root@node02-sql enable mysql_exporter.service
[root@node01-sql ~]# systemctl -H root@node02-sql start mysql_exporter.service
[root@node01-sql ~]# systemctl -H root@node02-sql status mysql_exporter.service

Node03

[root@node01-sql ~]# systemctl -H root@node03-sql enable mysql_exporter.service
[root@node01-sql ~]# systemctl -H root@node03-sql start mysql_exporter.service

Node04

[root@node01-sql ~]# systemctl -H root@node04-sql enable mysql_exporter.service
[root@node01-sql ~]# systemctl -H root@node04-sql start mysql_exporter.service

Ajouter les règles Firewall

[root@node01-sql ~]# ssh -l root node02-sql "firewall-cmd --zone=public --add-port=9104/tcp --permanent && firewall-cmd --reload"
[root@node01-sql ~]# ssh -l root node03-sql "firewall-cmd --zone=public --add-port=9104/tcp --permanent && firewall-cmd --reload"
[root@node01-sql ~]# ssh -l root node04-sql "firewall-cmd --zone=public --add-port=9104/tcp --permanent && firewall-cmd --reload"

Modifier le srape promotheus

[root@grafana02 ~]# vi /etc/prometheus/prometheus.yml

global:
 scrape_interval: 10s

 scrape_configs:
 - job_name: 'Percona'
 scrape_interval: 5s
 static_configs:
 - targets: ['localhost:9090', 'node01-sql:9104']
 - targets: ['localhost:9090', 'node02-sql:9104']
 - targets: ['localhost:9090', 'node03-sql:9104']
 - targets: ['localhost:9090', 'node04-sql:9104']

Relancer le service Promotheus

[root@grafana02 tmp]# systemctl daemon-reload
[root@grafana02 tmp]# systemctl restart prometheus
[root@grafana02 tmp]# systemctl status prometheus

• http://grafana.house.cpb:3000/

Views: 14