使用promtheus监控mysql服务

安装Prometheus使用docker方式部署，因为本机9090端口已经给cockpit用了，所以映射到主机的9091端口

[root@aspfj738vhd034j /]# docker run -itd --name prometheus -p 9091:9090  --restart always prom/prometheus
afd6dc79a03f3e59d09c2e05b7bed14e81e9173b653a054ecb9791b2c1137089

查看容器状态
[root@aspfj738vhd034j /]# docker ps
CONTAINER ID   IMAGE             COMMAND                  CREATED         STATUS         PORTS                                       NAMES
afd6dc79a03f   prom/prometheus   "/bin/prometheus --c…"   3 seconds ago   Up 2 seconds   0.0.0.0:9091->9090/tcp, :::9091->9090/tcp   prometheus

下载mysql的exporter

地址 https://prometheus.io/download/

往下翻可以找到

解压文件

[root@aspfj738vhd034j soft]# wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz

[root@aspfj738vhd034j soft]# tar -zxvf mysqld_exporter-0.15.1.linux-amd64.tar.gz 

编辑变量文件.my.cnf

[root@aspfj738vhd034j mysqld_exporter-0.15.1.linux-amd64]# cat .my.cnf 
[client]
user=root
password=数据库密码

也可以新建一个有权限的用户写在这里

运行测试一下，出现监听端口代表成功了

[root@aspfj738vhd034j mysqld_exporter-0.15.1.linux-amd64]# ./mysqld_exporter 
ts=2024-01-02T05:06:30.256Z caller=mysqld_exporter.go:220 level=info msg="Starting mysqld_exporter" version="(version=0.15.1, branch=HEAD, revision=cc349684494b5038ec5a52233bdca9eb9291e6f2)"
ts=2024-01-02T05:06:30.257Z caller=mysqld_exporter.go:221 level=info msg="Build context" build_context="(go=go1.21.5, platform=linux/amd64, user=root@d89c15b9f5ad, date=20231212-07:55:09, tags=unknown)"
ts=2024-01-02T05:06:30.257Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=global_status
ts=2024-01-02T05:06:30.257Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=global_variables
ts=2024-01-02T05:06:30.257Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=slave_status
ts=2024-01-02T05:06:30.257Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=info_schema.innodb_cmp
ts=2024-01-02T05:06:30.257Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=info_schema.innodb_cmpmem
ts=2024-01-02T05:06:30.257Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=info_schema.query_response_time
ts=2024-01-02T05:06:30.258Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9104
ts=2024-01-02T05:06:30.258Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9104

到prometheus容器里修改yml文件

[root@aspfj738vhd034j mysqld_exporter-0.15.1.linux-amd64]# docker exec -it prometheus /bin/sh

在容器中编辑vi /etc/prometheus/prometheus.yml 

/prometheus $ vi /etc/prometheus/prometheus.yml 

添加如下几行              
  - job_name: 'mysql'                
    static_configs:                                                                             
    - targets: [142.171.235.206:9104] 

保存退出，然后重启prometheus容器

[root@aspfj738vhd034j mysqld_exporter-0.15.1.linux-amd64]# docker restart prometheus 

在prometheus查看status>targets里状态为up就对了

设置mysql_exporter为系统服务

[root@aspfj738vhd034j multi-user.target.wants]# cat /usr/lib/systemd/system/mysqld_exporter.service 

[Unit]
Description=mysqld_exporter

[Service]
Environment=DATA_SOURCE_NAME=mysql用户:mysql密码@(IP地址:3306)
ExecStart=/soft/mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter --config.my-cnf=/soft/mysqld_exporter-0.15.1.linux-amd64/.my.cnf --web.listen-address=:9104
Restart=on-failure

[Install]
WantedBy=multi-user.target

重新载入

systemctl daemon-reload 

启动服务
systemctl start mysqld_exporter

[root@aspfj738vhd034j multi-user.target.wants]# systemctl status mysqld_exporter
● mysqld_exporter.service
   Loaded: loaded (/usr/lib/systemd/system/mysqld_exporter.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-02 00:43:58 EST; 6s ago
 Main PID: 127824 (mysqld_exporter)
    Tasks: 4 (limit: 4962)
   Memory: 2.0M
   CGroup: /system.slice/mysqld_exporter.service
           └─127824 /soft/mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter --config.my-cnf=/soft/mysqld_exporter-0.15.1.linux-amd64/.my.cnf --web.listen-address=:9104

Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.941Z caller=mysqld_exporter.go:220 level=info msg="Starting mysqld_exporter" version="(version=0.15.1>
Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.942Z caller=mysqld_exporter.go:221 level=info msg="Build context" build_context="(go=go1.21.5, platfo>
Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.942Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=global_status
Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.942Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=global_variables
Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.942Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=slave_status
Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.942Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=info_schema.innodb_cmp
Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.943Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=info_schema.innodb_cmpmem
Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.943Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=info_schema.query_respons>
Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.945Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9104
Jan 02 00:43:58 aspfj738vhd034j mysqld_exporter[127824]: ts=2024-01-02T05:43:58.945Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9104