Prometheus+Grafana+mysqld_exporter 监控 MySQL

创建用户

仅限本机登录

CREATE USER 'exporter'@'localhost' IDENTIFIED BY '123456' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

限制指定IP登录

CREATE USER 'exporter'@'192.168.0.100' IDENTIFIED BY '123456' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'192.168.0.100';

限制指定网段登录

CREATE USER 'exporter'@'192.168.0.%' IDENTIFIED BY '123456' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'192.168.0.%';

任意主机都可登录

CREATE USER 'exporter'@'%' IDENTIFIED BY '123456' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';

下载 mysqld_exporter

https://github.com/prometheus/mysqld_exporter/releases

新建配置文件

命名为my.cnf。

[client]
host=192.168.0.100
port=3306
user=exporter
password=123456

后台启动

nohup /root/exporter/mysqld_exporter-0.13.0/mysqld_exporter --config.my-cnf="my.cnf" > mysqld_exporter.out 2>&1 &

设置开机启动

编辑

vim /etc/systemd/system/mysqld_exporter.service

内容：ExecStart需要修改为mysqld_exporter二进制文件的绝对路径

[Unit]
Description=mysqld_exporter
Documentation=https://github.com/prometheus/mysqld_exporter
After=network.target

[Service]
Type=simple
User=root
ExecStart= /root/exporter/mysqld_exporter-0.13.0/mysqld_exporter
Restart=on-failure

[Install]
WantedBy=multi-user.target

设为开机启动

sudo systemctl enable mysqld_exporter.service

启动mysqld_exporter

sudo systemctl start mysqld_exporter.service

mysqld_exporter 默认端口

http://localhost:9104

修改 Prometheus 配置

scrape_configs下新增job_name为mysql（名称随意）

  - job_name: 'mysql'
    static_configs:
      - targets: ['192.168.0.100:9104']

完整的scrape_configs如下

scrape_configs:
  - job_name: "prometheus"
    static_configs:
      - targets: ["localhost:9090"]
  - job_name: 'mysql'
    static_configs:
      - targets: ['192.168.0.100:9104']

重启 Prometheus

重启后可查看mysqld_exporter是否上线。

http://localhost:9090/targets

Grafana Dashboard

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

开源地址

https://github.com/prometheus/mysqld_exporter