Prometheus监控mysql

docker-compose.yml 创建mysql

mkdir/data/mysql -p

cat > /data/mysql/docker-compose.yml << 'EOF'
version: '3.1'
services:
? db:
? ? image: mysql:8.0
? ? restart: always
? ? container_name: mysql
? ? environment:
? ? ? TZ: Asia/Shanghai
? ? ? LANG: en_US.UTF-8
? ? ? MYSQL_ROOT_PASSWORD: 123456
? ? command:
? ? ? --default-authentication-plugin=mysql_native_password
? ? ? --character-set-server=utf8mb4
? ? ? --collation-server=utf8mb4_general_ci
? ? ? --lower_case_table_names=1
? ? ? --performance_schema=1
? ? ? --sql-mode=""
? ? ? --skip-log-bin
? ? volumes:
? ? ? - /data/mysql/data:/var/lib/mysql
? ? ports:
? ? ? - 3306:3306
EOF

docker-compose up -d

监控mysql

创建用户expoerter
docker exec -it mysql mysql -uroot -p123456

```
CREATE USER 'exporter'@'%' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS,REPLICATION CLIENT,SELECT ON *.* TO 'exporter'@'%';
```
docker exec -it mysql mysql -uexporter -ppassword

mysqld_exporter 创建

mkdir /data/mysqld_exporter -p
cd /data/mysqld_exporter
cat >docker-compose.yml << 'EOF'
version: '3.3'
services:
? mysqld-exporter:
? ? image: prom/mysqld-exporter:v0.12.1
? ? container_name: mysqld-exporter
? ? restart: always
? ? command:
? ? ? - '--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'
? ? environment:
? ? ? - DATA_SOURCE_NAME=exporter:password@(10.19.1.220:3306)/
? ? ports:
? ? ? - 9104:9104
EOF

docker-compose up -d

增加prometheus/prometheus.yml

? - job_name: 'mysqld-exporter'
? ? scrape_interval: 30s
? ? static_configs:
? ? - targets: ['10.19.1.220:9104']
? ? ? labels:
? ? ? ? instance: mysqld服务器

curl -X POST http://localhost:9090/-/reload

触发器设置

cd /opt/data/docker-prometheus

cat >> prometheus/rules/mysqld.yml << 'EOF'
groups:
- name: MYSQL
? rules:
? - alert: MysqlDown
? ? expr: mysql_up == 0
? ? for: 30s
? ? labels:
? ? ? severity: critical
? ? annotations:
? ? ? summary: "Mysql Down,实例:{{ $labels.instance }}"
? ? ? description: "Mysql_exporter 连不上数据库了，当前状态为{{ $value }}"
EOF

热加载

docker exec -it prometheus promtool check config /etc/prometheus/prometheus.yml
curl -X POST http://localhost:9090/-/reload

dashboard

https://grafana.com/grafana/dashboards/7362