prometheus监控mysql数据库mysqld_exporter

mysqld_exporter是prometheus官方的针对数据库的数据采集器。本次的安装环境为centos 7

(1).部署mysql_exporter

[root@docker-3 src]# wget -c https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
[root@docker-3 src]# tar xf mysqld_exporter-0.14.0.linux-amd64.tar.gz
[root@docker-3 src]# mv mysqld_exporter-0.14.0.linux-amd64.tar.gz /usr/local/mysqld_exporter

• 通过systemd 方式管理

cat /usr/lib/systemd/system/mysqld-exporter.service 
[Unit]
Description=mysqld_exporter
After=network.target 

[Service]
User=prometheus
Group=prometheus
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf /usr/local/mysqld_exporter/mysqld_exporter.cnf --web.listen-address=0.0.0.0:9104 \
--collect.slave_status \
--collect.binlog_size \
--collect.info_schema.processlist \
--collect.info_schema.innodb_metrics \
--collect.engine_innodb_status \
--collect.perf_schema.file_events \
--collect.perf_schema.replication_group_member_stats
Restart=on-failure

[Install]
WantedBy=multi-user.target

(2).增加配置文件(此为示例，支持多mysqld目标抓取)

cd usr/local/mysqld_exporter
cat mysqld_exporter.cnf

[client_tls_true]
host = server2
port = 3306
user = usr
password = pwd
tls=true
[client_tls_preferred]
host = server3
port = 3306
user = usr
password = pwd
tls=preferred
[client_tls_skip_verify]
host = server3
port = 3306
user = usr
password = pwd
tls=skip-verify

(3).数据库授权

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

(4).启动数据收集器

systemctl daemon-reload
systemctl enable mysqld-exporter
systemctl status mysqld-exporter

[root@jiuzhou ~]# systemctl status mysqld_exporter
● mysqld_exporter.service - mysqld_exporter
   Loaded: loaded (/usr/lib/systemd/system/mysqld_exporter.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2023-03-07 17:20:09 CST; 16h ago
     Docs: https://prometheus.io/
 Main PID: 24689 (mysqld_exporter)
    Tasks: 7
   Memory: 15.9M
   CGroup: /system.slice/mysqld_exporter.service
           └─24689 /usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf /usr/local/mysqld_exporte...

Mar 08 10:09:23 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:09:23.321Z caller=exporter.go:174 l...d'"
Mar 08 10:09:53 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:09:53.319Z caller=exporter.go:174 l...d'"
Mar 08 10:10:23 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:10:23.312Z caller=exporter.go:174 l...d'"
Mar 08 10:10:53 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:10:53.315Z caller=exporter.go:174 l...d'"
Mar 08 10:11:23 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:11:23.318Z caller=exporter.go:174 l...d'"
Mar 08 10:11:53 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:11:53.316Z caller=exporter.go:174 l...d'"
Mar 08 10:12:23 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:12:23.350Z caller=exporter.go:174 l...d'"
Mar 08 10:12:53 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:12:53.311Z caller=exporter.go:174 l...d'"
Mar 08 10:13:23 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:13:23.310Z caller=exporter.go:174 l...d'"
Mar 08 10:13:54 jiuzhou mysqld_exporter[24689]: ts=2023-03-08T02:13:54.154Z caller=exporter.go:174 l...d'"
Hint: Some lines were ellipsized, use -l to show in full.

prometheus 添加收集器。

  - job_name: "mysqlserver"
    static_configs:
      - targets: ["192.168.0.222:9104"]

重启一下prometheus.
查看数据是否正常上传：

grafana 添加仪表盘