五、Prometheus监控实战之mysql数据库监控部署搭建

mahan

当前环境
Prometheus服务器：192.168.77.100
Mysql服务器：192.168.77.104

1、        时间同步
2、        关闭防火墙及selinux


安装及部署
将mysqld_exporter-0.18.0.linux-amd64.tar.gz包上传到mysql服务器的/opt目录，并解压，默认端口是9104
[root@localhost opt]# tar -xvf mysqld_exporter-0.18.0.linux-amd64.tar.gz
[root@localhost opt]# mv mysqld_exporter-0.18.0.linux-amd64 mysqld_exporter
[root@localhost opt]# ls -ltr mysqld_exporter
total 18436
-rwxr-xr-x 1 1001 1002 18859034 Sep 29 16:52 mysqld_exporter
-rw-r--r-- 1 1001 1002       65 Sep 29 16:52 NOTICE
-rw-r--r-- 1 1001 1002    11357 Sep 29 16:52 LICENSE

前往数据库创建监控用户
mysql>
mysql> CREATE USER `exporter`@`localhost` IDENTIFIED BY 'sztech_4U';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT Process, Reload, Replication Client, Select, Super ON *.* TO 'exporter'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

将上面创建的用户信息写入mysqld_exporter目录下的cnf文件
[root@localhost mysqld_exporter]# vim mysql_exporter.cnf
[client]
user=exporter
password=sztech_4U

创建mysqld_exporter服务文件
[root@localhost mysqld_exporter]# cd /etc/systemd/system
[root@localhost system]# vim mysql_exporter.service
[Unit]
Description=mysql_exporter
Documentation=https://github.com/prometheus/mysqld_exporter
After=network.target
[Service]
Type=simple
User=root
ExecStart=/opt/mysqld_exporter/mysqld_exporter  --config.my-cnf=/opt/mysqld_exporter/mysql_exporter.cnf
Restart=on-failure
[Install]
WantedBy=multi-user.target

[root@localhost system]# systemctl daemon-reload
[root@localhost system]# systemctl enable --now mysql_exporter.service

[root@localhost system]# systemctl status  mysql_exporter.service
● mysql_exporter.service - mysql_exporter
     Loaded: loaded (/etc/systemd/system/mysql_exporter.service; enabled; vendor preset: disabled)
     Active: active (running) since Thu 2025-11-13 09:22:26 CST; 1s ago
       Docs: https://github.com/prometheus/mysqld_exporter
   Main PID: 21731 (mysqld_exporter)
      Tasks: 5 (limit: 47382)
     Memory: 4.4M
     CGroup: /system.slice/mysql_exporter.service
             └─ 21731 /opt/mysqld_exporter/mysqld_exporter --config.my-cnf=/opt/mysqld_exporter/mysql_exporter.cnf

Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.129+08:00 level=INFO source=mysqld_exporter.go:256 msg="Starting mysqld_exporter" version="(version=0.18.0, branch=HEAD, revision=4df239645fa28c4>
Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.130+08:00 level=INFO source=mysqld_exporter.go:257 msg="Build context" build_context="(go=go1.25.1, platform=linux/amd64, user=root@5f7e05caa051,>
Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.130+08:00 level=INFO source=mysqld_exporter.go:269 msg="Scraper enabled" scraper=info_schema.innodb_cmp
Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.130+08:00 level=INFO source=mysqld_exporter.go:269 msg="Scraper enabled" scraper=info_schema.innodb_cmpmem
Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.130+08:00 level=INFO source=mysqld_exporter.go:269 msg="Scraper enabled" scraper=info_schema.query_response_time
Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.130+08:00 level=INFO source=mysqld_exporter.go:269 msg="Scraper enabled" scraper=global_status
Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.130+08:00 level=INFO source=mysqld_exporter.go:269 msg="Scraper enabled" scraper=global_variables
Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.130+08:00 level=INFO source=mysqld_exporter.go:269 msg="Scraper enabled" scraper=slave_status
Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.134+08:00 level=INFO source=tls_config.go:346 msg="Listening on" address=[::]:9104
Nov 13 09:22:26 localhost.localdomain mysqld_exporter[21731]: time=2025-11-13T09:22:26.134+08:00 level=INFO source=tls_config.go:349 msg="TLS is disabled." http2=false address=[::]:9104

访问http://192.168.77.104:9104/metrics检查服务是否成功

                               
登录/注册后可看大图

返回prometheus服务器上添加被监控mysql数据库ip
[root@prometheus ~]# cd /opt/prometheus/
[root@prometheus prometheus]# vim prometheus.yml
# my global config
global:
  scrape_interval: 15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
  # scrape_timeout is set to the global default (10s).

# Alertmanager configuration
alerting:
  alertmanagers:
    - static_configs:
        - targets:
          # - alertmanager:9093

# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
  # - "first_rules.yml"
  # - "second_rules.yml"

# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
  - job_name: "linux"
    static_configs:
      - targets: ["192.168.77.100:9100","192.168.77.102:9100"]

  - job_name: "windows"
    static_configs:
      - targets: ["192.168.77.101:9182"]

  - job_name: "oracle"
    static_configs:
      - targets: ["192.168.77.100:9161"]

  - job_name: "mysql"
    static_configs:
      - targets: ["192.168.77.104:9104"]


重启prometheus服务
[root@prometheus prometheus]# systemctl restart prometheus.service

验证

                               
登录/注册后可看大图