Prometheus 모니터링 테스트(mysql)

1. 테스트환경

   4Core 32GB RAM, Ubuntu 18.04

 

2. Topology

Docker
mysql57	mysql80
mysql57-exporter	mysql80-exporter
Prometheus

- 사용 포트 

  1) docker-proxy 자동 지정(mysql57 mysql80 mysql57-exporter mysql80-exporter)

  2) Prometheus : 9090

     * mysql의 3306포트는 host와 매핑하지 않음

 

3. 절차

0) docker 설정

 

$ vi /etc/docker/daemon.json

---

{
  "exec-opts": ["native.cgroupdriver=systemd"],
  "log-driver": "json-file",
  "log-opts": {
    "max-size": "100m",
    "max-file": "1"
  },
  "storage-driver": "overlay2",
  "metrics-addr" : "[hostIP]:9323",
  "experimental" : true

}

---

$ sudo systemctl daemon-reload

$ sudo systemctl restart docker

 

1) mysql57 배포

 

$ sudo docker network create db_network

$ docker run -d --name mysql57 --publish 3306 --network db_network --restart unless-stopped \
--env MYSQL_ROOT_PASSWORD=mypassword --volume mysql57-datadir:/var/lib/mysql \
mysql:5.7

 

2) mysql80 배포

 

$ docker run -d --name mysql80 --publish 3306 --network db_network --restart unless-stopped \

--env MYSQL_ROOT_PASSWORD=mypassword --volume mysql80-datadir:/var/lib/mysql \

--default-authentication-plugin=mysql_native_password \

mysql:8

 

3) mysql에 exporter 계정생성

 

$ docker exec -it mysql80 mysql -uroot -pmypassword

mysql> CREATE USER 'exporter'@'%' IDENTIFIED BY 'exporterpassword' WITH MAX_USER_CONNECTIONS 3;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';

 

$ docker exec -it mysql57 mysql -uroot -pmypassword

mysql> CREATE USER 'exporter'@'%' IDENTIFIED BY 'exporterpassword' WITH MAX_USER_CONNECTIONS 3;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';

 

4) Node_exporter 생성

 

$ docker run -d --name mysql80-exporter --publish 9104 --network db_network --restart always \
--env DATA_SOURCE_NAME="exporter:exporterpassword@(mysql80:3306)/" \
--collect.info_schema.processlist --collect.info_schema.innodb_metrics --collect.info_schema.tablestats \
--collect.info_schema.tables --collect.info_schema.userstats --collect.engine_innodb_status \
prom/mysqld-exporter:latest

$ docker run -d --name mysql57-exporter --publish 9104 --network db_network --restart always \
-e DATA_SOURCE_NAME="exporter:exporterpassword@(mysql57:3306)/" \
--collect.info_schema.processlist --collect.info_schema.innodb_metrics --collect.info_schema.tablestats \
--collect.info_schema.tables --collect.info_schema.userstats --collect.engine_innodb_status \
prom/mysqld-exporter:latest

 

5) 프로메테우스 생성

 

$ vim ~/prometheus.yml

---

global:
  scrape_interval:     5s
  scrape_timeout:      3s
  evaluation_interval: 5s

# Our alerting rule files
rule_files:
  - "alert.rules"

# Scrape endpoints
scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']

  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql57-exporter:9104','mysql80-exporter:9104']

  - job_name: 'docker'
    static_configs:
      - targets: ['hostIP:9323']

---

$ docker run -d --name prometheus-server --publish 9090:9090 --network db_network --restart unless-stopped \

--mount type=volume,src=prometheus-data,target=/prometheus --mount type=bind,src=$(pwd)/prometheus.yml,target=/etc/prometheus/prometheus.yml \

prom/prometheus

 

6) 확인

 

hostip:9090 접속 후 status > target에서 연결 확인

graph 탭에서 Execute 왼쪽 둥근 모양 버튼을 누르면 정의된 쿼리를 찾아볼 수 있다.

테이블로 쿼리 조회모습

쿼리 결과를 그래프로 나타낸 모습

참고 :

https://severalnines.com/database-blog/how-monitor-mysql-containers-prometheus-deployment-standalone-and-swarm-part-one