自建mysql监控方案

开始

• 下载并安装构建

$ curl -s https://api.github.com/repos/prometheus/mysqld_exporter/releases/latest   | grep browser_download_url | grep linux-amd64 |  cut -d '"' -f 4 | wget -qi -
$ tar xvf *******

• 验证

$ ./mysqld_exporter --version
mysqld_exporter, version 0.13.0 (branch: HEAD, revision: ad2847c7fa67b9debafccd5a08bacb12fc9031f1)
  build user:       root@e2043849cb1f
  build date:       20210531-07:30:16
  go version:       go1.16.4
  platform:         linux/amd64

• 增加mysql配置

$ groupadd --system prometheus
$ useradd -s /sbin/nologin --system -g prometheus prometheus
$ cat /etc/.mysqld_exporter.cnf
[client]
user=exporter
password=*******
$ chown root:prometheus /etc/.mysqld_exporter.cnf

• 修改mysql关于prometheus权限

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

• 增加启动脚本(内容自己调整)

$ cat /etc/systemd/system/mysql_exporter.service
[Unit]
Description=Prometheus MySQL Exporter
After=network.target
User=prometheus
Group=prometheus

[Service]
Type=simple
Restart=always
ExecStart=/www/monitor/mysqld_exporter/mysqld_exporter-0.13.0.linux-amd64/mysqld_exporter \
--config.my-cnf /etc/.mysqld_exporter.cnf \
--collect.global_status \
--collect.info_schema.innodb_metrics \
--collect.auto_increment.columns \
--collect.info_schema.processlist \
--collect.binlog_size \
--collect.info_schema.tablestats \
--collect.global_variables \
--collect.info_schema.query_response_time \
--collect.info_schema.userstats \
--collect.info_schema.tables \
--collect.perf_schema.tablelocks \
--collect.perf_schema.file_events \
--collect.perf_schema.eventswaits \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tableiowaits \
--collect.slave_status \
--web.listen-address=0.0.0.0:9104

[Install]
WantedBy=multi-user.target

• 启动

$ systemctl enable mysql_exporter
$ systemctl start mysql_exporter
$ systemctl status mysql_exporter

• 测试

  $ curl localhost:9104/metrics

  配置prometheus job

---

- job_name: 'my_mysql_exporter'
  scrape_interval: 60s
  scrape_timeout: 60s
  metrics_path: '/metrics'
  scheme: http
  static_configs:
  - targets:
    - '172.16.5.89:9104'
    labels:
      instance: 'my_mysql'

配置grafana

导入grafana之后，效果如下：