https://severalnines.com/database-blog/how-monitor-mysql-containers-prometheus-deployment-standalone-and-swarm-part-one
1. Docker swarm 구축
#host1에서
$ sudo docker swarm init --advertise-addr [서버1 IP]
#host 2,3에 생성된 토큰으로 node join 예시
$ sudo docker swarm join --token SWMTKN-1-4v0nzrgo3ke6c9eqtwhkpatp2o0rjqpktluxxe1bh9yezm4bqa-1apfoiskt0fvx4400c4f3qcq4 172.30.1.38:2377
2. Docker swarm network 생성 및 galera cluster bootstrap 서버 구성
$ vi my.cnf
---
[mysqld]
default_storage_engine = InnoDB
binlog_format = ROW
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 2
innodb_lock_schedule_algorithm = FCFS # MariaDB >10.1.19 and >10.2.3 only
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_sst_method = mariabackup
---
$ sudo docker network create --driver overlay db_swarm
$ cat ~/my.cnf | sudo docker config create my-cnf -
$ docker service create \
--name galera0 \
--replicas 1 \
--hostname galera0 \
--network db_swarm \
--publish 3306 \
--publish 4444 \
--publish 4567 \
--publish 4568 \
--config src=my-cnf,target=/etc/mysql/mariadb.conf.d/my.cnf \
--env MYSQL_ROOT_PASSWORD=mypassword \
--mount type=volume,src=galera0-datadir,dst=/var/lib/mysql \
mariadb:10.2 \
--wsrep_cluster_address=gcomm:// \
--wsrep_sst_auth="root:mypassword" \
--wsrep_node_address=galera0
3. galera cluster 구성 컨테이너 배포(galera1~3)
$ sudo docker service create \
--name galera1 \
--replicas 1 \
--hostname galera1 \
--network db_swarm \
--publish 3306 \
--publish 4444 \
--publish 4567 \
--publish 4568 \
--config src=my-cnf,target=/etc/mysql/mariadb.conf.d/my.cnf \
--env MYSQL_ROOT_PASSWORD=mypassword \
--mount type=volume,src=galera1-datadir,dst=/var/lib/mysql \
mariadb:10.2 \
--wsrep_cluster_address=gcomm://galera0,galera1,galera2,galera3 \
--wsrep_sst_auth="root:mypassword" \
--wsrep_node_address=galera1
$ sudo docker service create \
--name galera2 \
--replicas 1 \
--hostname galera2 \
--network db_swarm \
--publish 3306 \
--publish 4444 \
--publish 4567 \
--publish 4568 \
--config src=my-cnf,target=/etc/mysql/mariadb.conf.d/my.cnf \
--env MYSQL_ROOT_PASSWORD=mypassword \
--mount type=volume,src=galera2-datadir,dst=/var/lib/mysql \
mariadb:10.2 \
--wsrep_cluster_address=gcomm://galera0,galera1,galera2,galera3 \
--wsrep_sst_auth="root:mypassword" \
--wsrep_node_address=galera2
$ sudo docker service create \
--name galera3 \
--replicas 1 \
--hostname galera3 \
--network db_swarm \
--publish 3306 \
--publish 4444 \
--publish 4567 \
--publish 4568 \
--config src=my-cnf,target=/etc/mysql/mariadb.conf.d/my.cnf \
--env MYSQL_ROOT_PASSWORD=mypassword \
--mount type=volume,src=galera3-datadir,dst=/var/lib/mysql \
mariadb:10.2 \
--wsrep_cluster_address=gcomm://galera0,galera1,galera2,galera3 \
--wsrep_sst_auth="root:mypassword" \
--wsrep_node_address=galera3
4. 배포 확인
$ sudo docker service ls
5. 구성 이후 Bootstrap node 제거
$ sudo docker service rm galera0
6. DB에 mysql_exporter용 계정 정보 삽입
#host1에서
$ docker exec -it [galera1 컨테이너 이름] mysql -uroot -pmypassword
mysql> CREATE USER 'exporter'@'%' IDENTIFIED BY 'exporterpassword' WITH MAX_USER_CONNECTIONS 3;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
#ctrl을 누른채로 p와 q를 눌러 빠져나옴
7. Mysql_exporter1~3 배포
$sudo docker service create \
--name galera1-exporter \
--network db_swarm \
--replicas 1 \
-p 9104 \
-e DATA_SOURCE_NAME="exporter:exporterpassword@(galera1:3306)/" \
prom/mysqld-exporter:latest \
--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
$ sudo docker service create \
--name galera2-exporter \
--network db_swarm \
--replicas 1 \
-p 9104 \
-e DATA_SOURCE_NAME="exporter:exporterpassword@(galera2:3306)/" \
prom/mysqld-exporter:latest \
--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
$ sudo docker service create \
--name galera3-exporter \
--network db_swarm \
--replicas 1 \
-p 9104 \
-e DATA_SOURCE_NAME="exporter:exporterpassword@(galera3:3306)/" \
prom/mysqld-exporter:latest \
--collect.info_schema.processlist \
--collect.info_schema.innodb_metrics \
--collect.info_schema.tablestats \
--collect.info_schema.tables \
--collect.info_schema.userstats \
--collect.engine_innodb_status8. 프로메테우스 타겟용 설정파일(prometheus.yml) 작성 및 컨테이너 배포
$ 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: 'galera'
static_configs:
- targets: ['galera1-exporter:9104','galera2-exporter:9104', 'galera3-exporter:9104']
---
$ cat ~/prometheus.yml | sudo docker config create prometheus-yml -
$ sudo docker service create \
--name prometheus-server \
--publish 9090:9090 \
--network db_swarm \
--replicas 1 \
--config src=prometheus-yml,target=/etc/prometheus/prometheus.yml \
--mount type=volume,src=prometheus-data,dst=/prometheus \
prom/prometheus9. 확인
$ sudo docker service ls
ID NAME MODE REPLICAS IMAGE PORTS
0cryq8h0wlmg galera1 replicated 1/1 mariadb:10.2 *:30004->3306/tcp, *:30005->4444/tcp, *:30006-30007->4567-4568/tcp
wwwn1ir7ebtz galera1-exporter replicated 1/1 prom/mysqld-exporter:latest *:30016->9104/tcp
u3nha71h41qx galera2 replicated 1/1 mariadb:10.2 *:30008->3306/tcp, *:30009->4444/tcp, *:30010-30011->4567-4568/tcp
uqnl5r9m79j4 galera2-exporter replicated 1/1 prom/mysqld-exporter:latest *:30017->9104/tcp
ireqrvbhdfyl galera3 replicated 1/1 mariadb:10.2 *:30012->3306/tcp, *:30013->4444/tcp, *:30014-30015->4567-4568/tcp
37c2r23s482s galera3-exporter replicated 1/1 prom/mysqld-exporter:latest *:30018->9104/tcp
ziftvb8dutdk prometheus-server replicated 1/1 prom/prometheus:latest *:9090->9090/tcp#아무 스웜 호스트 ip:9090으로 접속하면 Prometheus 웹으로 접속가능하며, PromQL 테스트를 진행할 수 있다.
host2로 접속 시도
host1로 접속 시도
클러스터 구성 확인 쿼리
