Prometheus监控MySQL服务（六）

一、监控使用mysqld_exporter插件

在Prometheus官网下载：https://prometheus.io/download/

github的地址：https://github.com/prometheus/mysqld_exporter

支持的MySQL和MariaDB版本：5.5及以上，需要注意的MySQL/MariaDB<5.6并不支持所有的收集方法

# wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gz
# tar xf mysqld_exporter-0.12.1.linux-amd64.tar.gz -C /usr/local/
# ln -s /usr/local/mysqld_exporter-0.12.1.linux-amd64/ /usr/local/mysqld_exporter
# cd /usr/local/mysqld_exporter
# 查看需要监控的指标
# ./mysqld_exporter --help

二、在MySQL中创建帐号

# mysql -uroot -p
mysql> CREATE USER 'exporter'@'192.168.5.237' IDENTIFIED BY 'exporter';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'192.168.5.237';Query OK, 0 rows affected (0.03 sec)

mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'192.168.5.237';
Query OK, 0 rows affected (0.00 sec)

三、创建客户端连接mysql的默认帐号

# vim /usr/local/mysqld_exporter/.my.cnf
[client]
host=192.168.5.237
user=exporter
password=exporter

四、启动mysqld_exporter服务

# /usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf='/usr/local/mysqld_exporter/.my.cnf' &
INFO[0000] Starting mysqld_exporter (version=0.12.1, branch=HEAD, revision=48667bf7c3b438b5e93b259f3d17b70a7c9aff96)  source="mysqld_exporter.go:257"
INFO[0000] Build context (go=go1.12.7, user=root@0b3e56a7bc0a, date=20190729-12:35:58)  source="mysqld_exporter.go:258"
INFO[0000] Enabled scrapers:                             source="mysqld_exporter.go:269"
INFO[0000]  --collect.global_status                      source="mysqld_exporter.go:273"
INFO[0000]  --collect.global_variables                   source="mysqld_exporter.go:273"
INFO[0000]  --collect.slave_status                       source="mysqld_exporter.go:273"
INFO[0000]  --collect.info_schema.innodb_cmp             source="mysqld_exporter.go:273"
INFO[0000]  --collect.info_schema.innodb_cmpmem          source="mysqld_exporter.go:273"
INFO[0000]  --collect.info_schema.query_response_time    source="mysqld_exporter.go:273"
INFO[0000] Listening on :9104                            source="mysqld_exporter.go:283"

可以通过9104端口查看暴露的监控数据

 五、修改Pormetheus配置文件

# vim /usr/local/prometheus/prometheus.yml                                            
# my global config
global:
  scrape_interval:     15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.

alerting:
  alertmanagers:
  - static_configs:
    - targets:
      # - alertmanager:9093

rule_files:
  # - "first_rules.yml"
  # - "second_rules.yml"

scrape_configs:
  - job_name: 'prometheus'

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
    - targets: ['192.168.5.237:9090']

  - job_name: 'node'
    file_sd_configs:
    - refresh_interval: 30s
      files: 
      - "/usr/local/prometheus/sd_config/node*.yml"

  - job_name: 'docker'
    static_configs:
    - targets: ['192.168.5.85:8080']

# 增加如下信息
  - job_name: 'mysql'
    static_configs:
    - targets: ['192.168.5.237:9104']
      labels:
        app: zabbix-server-mysql

# 检查配置文件
# /usr/local/prometheus/promtool check config /usr/local/prometheus/prometheus.yml 
Checking /usr/local/prometheus/prometheus.yml
  SUCCESS: 0 rule files found

# 重启服务
# systemctl restart prometheus.service 

Targets中能查看到mysql的信息

 六、配置Grafana图表

导入7362图表，并选择prometheus数据源

 能查看到监控数据

 七、将mysqld_exporter配置为服务

# vim /usr/lib/systemd/system/mysqld_exporter.service
[Unit]
Description=node_exporter
Documentation=https://prometheus.io/
After=network.target

[Service]
Type=simple
User=root
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target

# systemctl daemon-reload
# systemctl start mysqld_exporter.service 
# systemctl enable mysqld_exporter.service    

八、多数据库配置使用

参照github上提供的方法，使用docker启动多个容器对不同的实例进行采集

docker run -d \
  -p 9104:9104 \
  --network my-mysql-network  \
  -e DATA_SOURCE_NAME="user:password@(hostname:3306)/" \
  prom/mysqld-exporter

可以写成docker-compose的方法

# cat docker-compose.yml 
version: '3.7'
services:
  mysql-master:
    restart: always
    image: prom/mysqld-exporter
    privileged: true
    environment:
      - 'DATA_SOURCE_NAME=root:password@(10.1.1.6:3306)/'
    ports:
      - 9103:9104

  mysql-slave:
    restart: always
    image: prom/mysqld-exporter
    privileged: true
    environment:
      - 'DATA_SOURCE_NAME=root:password@(10.1.1.7:3306)/'
    ports:
      - 9104:9104
# docker-compose up -d

启动之后，然后再配置prometheus

  - job_name: 'mysql'
    metrics_path: /metrics
    static_configs:
      - targets: ['10.1.1.6:9103']
        labels:
          instance: MySQL-master

      - targets: ['10.1.1.7:9104']
        labels:
          instance: MySQL-slave

重新加载prometheus配置文件