使用Galera Cluster进行构建和使用mysqld_exporter进行监视
关于这篇文章
以类似的方式，使监控结果可以在Grafana上查看。
虽然未提及，但数据库和监控都将使用Docker进行构建。
环境
环境如下：
操作系统：
– Ubuntu 20.04
中间件：
– Docker: 20.10.8
– Docker Compose: 1.29.0
– mysqld_exporter: 0.13.0
容器：
– MariaDB: 10.6.2-focal
– prom/prometheus
– grafana/grafana
数据库设置 (DB设置)
构建Galera集群。
我们首先要搭建三个MariaDB，并进行GaleraCluster的配置。
DB1 could be paraphrased in Chinese as “数据库1” Yī).
root@db00:~# mkdir db01
root@db00:~# cd db01
root@db00:~/db01# mkdir log
root@db00:~/db01# mkdir mysql
root@db00:~/db01# ls
log mysql
root@db00:~/db01# vi docker-compose.yml
以下是DB1的docker-compose.yml：
其他数据库也使用相同的docker-compose.yml，但只有DB1上有一个command。在启动时，首先启动DB1并生成集群，然后启动其他服务器并加入集群。
version: '3.7'
services:
db:
image: mariadb:10.6.2-focal
volumes:
- "./db.cnf:/etc/mysql/conf.d/db.cnf"
- "db-data:/var/lib/mysql"
- "db-log:/var/log/mysql"
environment:
- TZ=Asia/Tokyo
- MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=yes
network_mode: host
#commandはDB1のみ
command: ["--wsrep-new-cluster"]
volumes:
db-data:
driver: local
driver_opts:
type: none
device: ${PWD}/mysql
o: bind
db-log:
driver: local
driver_opts:
type: none
device: ${PWD}/log
o: bind
接下来，在相同的目录下，我们需要编写db.cnf文件，并将以下内容写入其中。
[galera]
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name = "MariaDB Galera Cluster"
#各サーバのIPアドレスを記載
wsrep_cluster_address = gcomm://192.168.153.5,192.168.153.6,192.168.153.7
#自サーバのIPアドレスを記載
wsrep_node_address = 192.168.153.5
#今回はsst_methodはmariabackupを利用する
wsrep_sst_method = "mariabackup"
wsrep_sst_auth = 'mariabackup:mypassword'
wsrep_slave_threads = 4
#node_nameはhost名と同じにする
wsrep_node_name ="db01"
wsrep_sst_donor ="db01,db02,db03"
[mysqld]
default_storage_engine = InnoDB
binlog_format = ROW
log_bin = /var/log/mysql/mysql-bin
innodb_autoinc_lock_mode = 2
innodb_locks_unsafe_for_binlog = 1
到目前为止已经变成了这样。
root@db00:~/db01# ls
db.cnf docker-compose.yml log mysql
DB2 数据库
以相同的方式进行设置。
请注意docker-compose.yml中没有命令（command）, 以及db.cnf中”wsrep_node_address”和”wsrep_node_name”是特定于服务器的。
root@db01:~# mkdir db02
root@db01:~# cd db02
root@db01:~/db02# mkdir log
root@db01:~/db02# mkdir mysql
root@db01:~/db02# vi docker-compose.yml
root@db01:~/db02# vi db.cnf
version: '3.7'
services:
db:
image: mariadb:10.6.2-focal
volumes:
- "./db.cnf:/etc/mysql/conf.d/db.cnf"
- "db-data:/var/lib/mysql"
- "db-log:/var/log/mysql"
environment:
- TZ=Asia/Tokyo
- MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=yes
network_mode: host
volumes:
db-data:
driver: local
driver_opts:
type: none
device: ${PWD}/mysql
o: bind
db-log:
driver: local
driver_opts:
type: none
device: ${PWD}/log
o: bind
[galera]
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name = "MariaDB Galera Cluster"
#各サーバのIPアドレスを記載
wsrep_cluster_address = gcomm://192.168.153.5,192.168.153.6,192.168.153.7
#自サーバのIPアドレスを記載
wsrep_node_address = 192.168.153.6
#今回はsst_methodはmariabackupを利用する
wsrep_sst_method = "mariabackup"
wsrep_sst_auth = 'mariabackup:mypassword'
wsrep_slave_threads = 4
#node_nameはhost名と同じにする
wsrep_node_name ="db02"
wsrep_sst_donor ="db01,db02,db03"
[mysqld]
default_storage_engine = InnoDB
binlog_format = ROW
log_bin = /var/log/mysql/mysql-bin
innodb_autoinc_lock_mode = 2
innodb_locks_unsafe_for_binlog = 1
DB3
DB3
root@db01:~# mkdir db03
root@db01:~# cd db03
root@db01:~/db02# mkdir log
root@db01:~/db02# mkdir mysql
root@db01:~/db02# vi docker-compose.yml
root@db01:~/db02# vi db.cnf
version: '3.7'
services:
db:
image: mariadb:10.6.2-focal
volumes:
- "./db.cnf:/etc/mysql/conf.d/db.cnf"
- "db-data:/var/lib/mysql"
- "db-log:/var/log/mysql"
environment:
- TZ=Asia/Tokyo
- MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=yes
network_mode: host
volumes:
db-data:
driver: local
driver_opts:
type: none
device: ${PWD}/mysql
o: bind
db-log:
driver: local
driver_opts:
type: none
device: ${PWD}/log
o: bind
[galera]
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name = "MariaDB Galera Cluster"
#各サーバのIPアドレスを記載
wsrep_cluster_address = gcomm://192.168.153.5,192.168.153.6,192.168.153.7
#自サーバのIPアドレスを記載
wsrep_node_address = 192.168.153.7
#今回はsst_methodはmariabackupを利用する
wsrep_sst_method = "mariabackup"
wsrep_sst_auth = 'mariabackup:mypassword'
wsrep_slave_threads = 4
#node_nameはhost名と同じにする
wsrep_node_name ="db03"
wsrep_sst_donor ="db01,db02,db03"
[mysqld]
default_storage_engine = InnoDB
binlog_format = ROW
log_bin = /var/log/mysql/mysql-bin
innodb_autoinc_lock_mode = 2
innodb_locks_unsafe_for_binlog = 1
启动集群
开始运行DB1
root@db00:~/db01# docker-compose up -d
Creating volume "db01_db-data" with local driver
Creating volume "db01_db-log" with local driver
Creating db01_db_1 ... done
root@db00:~/db01# docker-compose ps
Name Command State Ports
----------------------------------------------------------
db01_db_1 docker-entrypoint.sh --wsr ... Up
让我们确认一下集群是否启动了。
root@db00:~/db01# docker-compose exec db mysql -u root -e "show status like 'wsrep_cluster_%'"
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_cluster_weight | 1 |
| wsrep_cluster_capabilities | |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | ffc5854e-21a5-11ec-ac7b-9b4df50357e9 |
| wsrep_cluster_status | Primary |
+----------------------------+--------------------------------------+
在启动其他数据库之前，要创建一个用于mariabackup的用户。
如果不先创建用户，当启动数据库并执行SST时会失败。
root@db00:~/db01# docker-compose exec db mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.6.2-MariaDB-1:10.6.2+maria~focal-log mariadb.org binary distribution
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 'mariabackup'@'localhost' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
Query OK, 0 rows affected (0.005 sec)
现在准备工作已完成，可以启动其他DB容器了。
root@db01:~/db02# docker-compose up -d
Creating volume "db02_db-data" with local driver
Creating volume "db02_db-log" with local driver
Creating db02_db_1 ... done
root@db01:~/db02# docker-compose ps
Name Command State Ports
-------------------------------------------------------
db02_db_1 docker-entrypoint.sh mysqld Up
root@db01:~/db02# docker-compose exec db mysql -u root -e "show status like 'wsrep_cluster_%'"
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_cluster_weight | 2 |
| wsrep_cluster_capabilities | |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | 276f65ef-21a6-11ec-ab3a-b716b6fe3644 |
| wsrep_cluster_status | Primary |
+----------------------------+--------------------------------------+
root@db02:~/db03# docker-compose up -d
Creating volume "db03_db-data" with local driver
Creating volume "db03_db-log" with local driver
Creating db03_db_1 ... done
root@db02:~/db03# docker-compose ps
Name Command State Ports
-------------------------------------------------------
db03_db_1 docker-entrypoint.sh mysqld Up
root@db02:~/db03# docker-compose exec db mysql -u root -e "show status like 'wsrep_cluster_%'"
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_cluster_weight | 3 |
| wsrep_cluster_capabilities | |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 276f65ef-21a6-11ec-ab3a-b716b6fe3644 |
| wsrep_cluster_status | Primary |
+----------------------------+--------------------------------------+
我們成功搭建了一個由三個台構成的Galera Cluster。
(Korede de Galera Cluster .)
将 mysqld_exporter 注册为服务
首先下载mysqld_exporter。
root@db00:~# cd /var/tmp/
##ダウンロード
root@db00:/var/tmp# curl -LO https://github.com/prometheus/mysqld_exporter/releases/download/v0.13.0/mysqld_exporter-0.13.0.linux-amd64.tar.gz
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 647 100 647 0 0 2028 0 --:--:-- --:--:-- --:--:-- 2028
100 7072k 100 7072k 0 0 3388k 0 0:00:02 0:00:02 --:--:-- 4025k
root@db00:/var/tmp# ls
mysqld_exporter-0.13.0.linux-amd64.tar.gz
##解凍
root@db00:/var/tmp# tar -zxvf mysqld_exporter-0.13.0.linux-amd64.tar.gz
mysqld_exporter-0.13.0.linux-amd64/
mysqld_exporter-0.13.0.linux-amd64/LICENSE
mysqld_exporter-0.13.0.linux-amd64/NOTICE
mysqld_exporter-0.13.0.linux-amd64/mysqld_exporter
##実行ファイルを適当な場所に配置する
root@db00:/var/tmp# mkdir /var/lib/mysqld_exporter
root@db00:/var/tmp# cp mysqld_exporter-0.13.0.linux-amd64/mysqld_exporter /var/lib/mysqld_exporter/
root@db00:/var/tmp# ls /var/lib/mysqld_exporter
mysqld_exporter
接下来创建服务文件。
root@db00:/var/tmp# vi /etc/systemd/system/mysqld_exporter.service
[Unit]
Description=Mysql Exporter
After=network-online.target
[Service]
EnvironmentFile=-/etc/default/mysqld_exporter
WorkingDirectory=/var/lib/mysqld_exporter
ExecStart=/var/lib/mysqld_exporter/mysqld_exporter $OPTIONS
Restart=always
RestartSec=1
Type=simple
[Install]
WantedBy = multi-user.target
root@db00:/var/tmp# vi /etc/default/mysqld_exporter
DATA_SOURCE_NAME= "exporter:test@(localhost:3306)/"
OPTIONS="--collect.engine_innodb_status --collect.global_status"
在这里，这是所有DB服务器通用的工作。
在启动服务之前，需要创建mysqld_exporter用户。
由于我们正在组建集群，所以只需在某一台上创建即可与所有服务器共享。
root@db00:~/db01# ls
db.cnf docker-compose.yml log mysql
root@db00:~/db01# docker-compose exec db mysql -u root -e "CREATE USER 'exporter'@'127.0.0.1' IDENTIFIED BY 'test' WITH MAX_USER_CONNECTIONS 3;"
root@db00:~/db01# docker-compose exec db mysql -u root -e "GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'127.0.0.1';"
由于事先准备工作已经完成，现在可以启动服务了。
root@db00:~/db01# systemctl daemon-reload
root@db00:~/db01# systemctl start mysqld_exporter
root@db00:~/db01# systemctl status mysqld_exporter
● mysqld_exporter.service - Mysql Exporter
Loaded: loaded (/etc/systemd/system/mysqld_exporter.service; disabled; vendor preset: enabled)
Active: active (running) since Thu 2021-09-30 21:15:29 JST; 9s ago
Main PID: 40329 (mysqld_exporter)
Tasks: 4 (limit: 1072)
Memory: 1.7M
CGroup: /system.slice/mysqld_exporter.service
mq40329 /var/lib/mysqld_exporter/mysqld_exporter --collect.engine_innodb_status --collect.global_status
在其他数据库中也以类似的方式启动服务。
监视 –
的火焰throw敌人自由。
使用Docker来熟悉Prometheus
参考这个链接。
在监视服务器上创建docker-compose.yml文件和配置文件。
root@prometheus:~# mkdir prometheus
root@prometheus:~# cd prometheus/
root@prometheus:~/prometheus# vi docker-compose.yml
root@prometheus:~/prometheus# vi prometheus.yml
version: '3.7'
services:
prometheus:
image: prom/prometheus
volumes:
- ./prometheus.yml:/etc/prometheus/prometheus.yml
ports:
- '9090:9090'
grafana:
image: grafana/grafana
ports:
- "3000:3000"
global:
scrape_interval: 15s # By default, scrape targets every 15 seconds.
# Attach these labels to any time series or alerts when communicating with
# external systems (federation, remote storage, Alertmanager).
external_labels:
monitor: 'codelab-monitor'
# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
# The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
- job_name: 'prometheus'
# Override the global default and scrape targets from this job every 5 seconds.
scrape_interval: 5s
static_configs:
- targets: ['localhost:9090']
- job_name: "db1"
scrape_interval: "40s"
scrape_timeout: "30s"
static_configs:
- targets: ['192.168.153.5:9104']
- job_name: "db2"
scrape_interval: "40s"
scrape_timeout: "30s"
static_configs:
- targets: ['192.168.153.6:9104']
- job_name: "db3"
scrape_interval: "40s"
scrape_timeout: "30s"
static_configs:
- targets: ['192.168.153.7:9104']
关于prometheus.yml，我正在将以下内容添加到官方提供的配置文件中。
- job_name: "db1"
scrape_interval: "40s"
scrape_timeout: "30s"
static_configs:
- targets: ['192.168.153.5:9104']
- job_name: "db2"
scrape_interval: "40s"
scrape_timeout: "30s"
static_configs:
- targets: ['192.168.153.6:9104']
- job_name: "db3"
scrape_interval: "40s"
scrape_timeout: "30s"
static_configs:
- targets: ['192.168.153.7:9104']
启动。
root@prometheus:~/prometheus# docker-compose up -d
oot@prometheus:~/prometheus# docker-compose ps
Name Command State Ports
-----------------------------------------------------------------------------------------------------------
prometheus_grafana_1 /run.sh Up 0.0.0.0:3000->3000/tcp,:::3000->3000/tcp
prometheus_prometheus_1 /bin/prometheus --config.f ... Up 0.0.0.0:9090->9090/tcp,:::9090->9090/tcp
Grafana的设置
只需要在仪表板上设置想要监控的配置即可。
