MySQL性能监控

news/2024/5/18 22:22:00 标签: mysql, prometheus

1. 创建监控用户

mysql> CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'Mysqld_exporter@123' WITH MAX_USER_CONNECTIONS 3;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> \q

2. 验证登录

mysql -umysqld_exporter -p

3. 安装mysqld_exporter

下载地址：

官网：Download | Prometheus

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

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar -zxvf mysqld_exporter-0.15.1.linux-amd64.tar.gz
mv mysqld_exporter-0.15.1.linux-amd64 /home/mysqld_exporter

4. 创建mysqld_exporter配置文件

vim /home/mysqld_exporter/my.cnf


[client]
user=mysqld_exporter # 创建的用户名
password=Mysqld_exporter@123 # 密码

5. 创建mysqld_exporter.service

vim /usr/lib/systemd/system/mysqld_exporter.service

[Unit]
Description=mysql Monitoring SystemDocumentation=mysql Monitoring System
[Service]
ExecStart=/home/mysqld_exporter/mysqld_exporter \
--collect.info_schema.processlist \
--collect.info_schema.innodb_tablespaces \
--collect.info_schema.innodb_metrics \
--collect.perf_schema.tableiowaits \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tablelocks \
--collect.engine_innodb_status \
--collect.perf_schema.file_events \
--collect.binlog_size \
--collect.info_schema.clientstats \
--collect.perf_schema.eventswaits \
--config.my-cnf=/home/mysqld_exporter/my.cnf
[Install]
WantedBy=multi-user.target

6. 启动mysqld_exporter

systemctl daemon-reload
systemctl enable mysqld_exporter --now

7. 修改prometheus配置

增加以下配置：

- job_name: mysql 
  params:
    auth_module: [client.servers]
  static_configs:
    - targets:
      - 192.168.33.131:9104
      - 192.168.33.96:9104