Prometheus-采集MySQL指标

mysql_exporter 项目地址：https://github.com/prometheus/mysqld_exporter

• 云厂商地址: 1.1.1.1

• 服务器地址: 2.2.2.2

1. 安装 mysql_exporter

mkdir /data

cd /usr/local/src

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

mv mysqld_exporter-0.15.0.linux-amd64 /data/mysqld_exporter

cd /data/mysqld_exporter

cat >> mysql_node.cnf << EOF
[client]
host="1.1.1.1"
port=3306
user=exporter
password=123456
EOF

2. 数据库授权

GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'exporter'@'%' identified by '123456';
GRANT SELECT ON performance_schema.* TO 'exporter'@'%';
flush privileges;

3. 启动

./mysqld_exporter --config.my-cnf=="./mysql_node.cnf" &
ss -tln |grep 9104

• 方式二，使用 systemd 管理

cat >> /usr/lib/systemd/system/mysqld_exporter.service << EOF

[Unit]
Description=mysqld_exporter
After=network.target

[Service]
Type=simple
ExecStart=/data/mysqld_exporter/mysqld_exporter --config.my-cnf=/data/mysqld_exporter/.my.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target

EOF

systemctl daemon-reload
systemctl enable mysqld_exporter
systemctl start mysqld_exporter
systemctl status mysqld_exporter

$ ss -antpl |grep 9104
LISTEN     0      128       [::]:9104                  [::]:*                   users:(("mysqld_exporter",pid=3141,fd=3))

4. 检查是否收集了数据

curl -s localhost:9104/metrics |grep ^mysql_up
mysql_up 1    ----> 1 说明收集监控信息成功， 0 代表不成功

5. Prometheus 配置

root@monitor-1:/data/monitor/prometheus cat prometheus.yml
  #- job_name: 'mysql'
  #  static_configs:
  #    - targets: ['x.x.x.x:9104']
  #      labels:
  #        instance: db1
  ... 略
  # MySQL数据库相关
  - job_name: 'MySQL数据库'
    static_configs:
    file_sd_configs:
    - refresh_interval: 30s
      files:
      - "vhosts/mysql/*.json" 

root@monitor-1:/data/monitor/prometheus# cat vhosts/mysql/saas.json 
[
  {
    "targets": ["2.2.2.2:9104"],
    "labels": {
         "label": "xxxx使用",
         "account": "[e-mail]",
         "cloud": "xxxxx",
         "instance": "1.1.1.1:3306"          # mysql不在服务器，覆盖 instance
    }
  }
]

• Prometheus 命令热加载, 我这里端口: 8090

./promtool  check config  prometheus.yml
curl  -XPOST localhost:8090/-/reload    # 主配置文件热加载，修改子配置会自动加载

6. Grafana 配置

根据喜好自行选择: https://grafana.com/grafana/dashboards