Prometheus监控mysql

概述

mysql-export是一个mysql信息的导出器，可通过查看mysql变量获取mysql的各项信息并输出到 /metrics

支持DB版本

MySQL >= 5.6
MariaDB >= 10.3

项目地址：mysql_export

本地安装

下载程序

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz

安装程序

mkdir /data/monitor/
tar zxf mysqld_exporter-0.14.0.linux-amd64.tar.gz -C  /data/monitor/
cd /data/monitor
mv  mysqld_exporter-0.14.0.linux-amd64  mysqld_exporter
ln -s /data/monitor/mysqld_exporter/mysqld_exporter  /usr/bin/

创建DB监控用户

CREATE USER 'export'@'%' IDENTIFIED BY 'Pwd@123' WITH MAX_USER_CONNECTIONS 10;
GRANT PROCESS,REPLICATION CLIENT,REPLICATION SLAVE,SELECT ON *.* TO 'export';
flush privileges;

编写配置文件

cd  /data/monitor/mysqld_exporter
vim my.cnf

[client]
host=172.17.34.124       #Mysql内网IP
port=3306                #mysql端口
user=export              #mysql用户
password=Pwd@123         #mysql密码

启动模块

nohup mysqld_exporter --config.my-cnf="/data/monitor/mysqld_exporter/my.cnf" --web.listen-address="172.17.34.124:9999" &

启动说明
–config.my-cnf 指定配置文件
–web.listen-address 指定监听地址及端口
–web.telemetry-path 指定访问路径

访问数据

curl 172.17.34.124:9999/metrics

容器部署

docker run

docker run -d --name mysqld_exporter -p 9104:9104 -e DATA_SOURCE_NAME="export:Pwd@123@(172.17.34.124:3306)/" docker.io/prom/mysqld-exporter:latest 

说明
-d 后台运行
–name 容器名称 DATA_SOURCE_NAME=“mysql用户:mysql密码@(mysqlIP:mysqlport)/”

docker-compose

编写YML

vim  docker-compose-mysql-exporter.yml

version: '3.2'
services:
  mysqld-exporter:
    image: prom/mysqld-exporter:latest
    container_name: mysqld-exporter
    restart: unless-stopped
    ports:
    - 9104:9104
    environment:
      DATA_SOURCE_NAME: "export:Pwd@123@(172.17.34.124:3306)/"

启动容器

docker-compose -f docker-compose-mysql-exporter.yml up -d

接入prometheus

编辑prometheus配置文件（三种方式）

vim prometheus.yml

#单节点模式
  - job_name: 'mysql_one'
    scrape_interval: 5s
    static_configs:
    - targets: ["172.17.34.124:9104"]
#多节点模式
  - job_name: 'mysql_any'
    scrape_interval: 5s
    static_configs:
    - targets:
      - "172.17.34.124:9104"
      -  "ip2:port"
#配置文件模式
  - job_name: 'mysql_file'
    scrape_interval: 5s
    metrics_path: '/metrics'
    file_sd_configs:
    - files:
      - 'modules/mysql.yml'
      refresh_interval: 5s

配置文件模式需创建额外配置文件

vim modules/mysql.yml

- labels:
    server: "mysql"
    project: "test"
    lable3: "value"
  targets:
  - 172.17.34.124:9104
  - IP2:port

重载prometheus配置

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

查看接入节点状态（浏览器访问）

http://IP:9090/targets

搜索指标验证是否接入成功