grafana+prometheus+mysqld_exporter监控mysql数据库性能

简介

mysqld_exporter是prometheus官方提供的用于监控mysql运行状态的exporter
本文使用grafana+prometheus+mysqld_exporter对mysql做一个性能监控，对应软件版本如下：
系统版本：Linux version 3.10.0-957.5.1.el7.x86_64
grafana：grafana-9.1.1.linux-amd64
prometheus：prometheus-2.38.0.linux-amd64
mysqld_exporter：mysqld_exporter-0.14.0.linux-amd64

1.grafana+prometheus的安装

见前面的文章：点击查看

2.安装mysqld_exporter

下载地址：下载地址
上传到服务器解压

tar -zxvf mysqld_exporter-0.14.0.linux-amd64.tar.gz

修改文件夹名称

mv mysqld_exporter-0.14.0.linux-amd64 redis_exporter

在正式开始配置前你需要创建一个专门用于监控的mysql账号

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'expoter12345' 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 'expoter12345' WITH MAX_USER_CONNECTIONS 3;

3.修改配置

vim /usr/pthreetools/mysqld_exporter/.my.cnf

[client]
host=127.0.0.1
user=exporter
password=expoter12345

4.编辑mysqld_exporter启动文件

此处启动文件里面的启动参数不能少，为了配合grafana的模板使用

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

[Unit]
Description=mysqld_exporter
Documentation=https://github.com/prometheus/mysqld_exporter
After=network.target

[Service]
Type=simple
#User=prometheus
WorkingDirectory=/usr/pthreetools
ExecStart=/usr/pthreetools/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/pthreetools/mysqld_exporter/.my.cnf --web.listen-address=:9104 \
--collect.engine_innodb_status \
--collect.info_schema.tablestats \
--collect.info_schema.query_response_time \
--collect.info_schema.innodb_tablespaces \
--collect.info_schema.innodb_metrics \
--collect.info_schema.tables \
--collect.info_schema.processlist \
--collect.info_schema.tables.databases=*


Restart=on-failure
RestartSec=10s
SuccessExitStatus=0

#May not be honored if higher than kernel limit (sysctl fs.file-max) or process
#limit (sysctl fs.nr_open). Also may not be honored if lower than systemd limit
#(system.conf) or systemd user limit (user.conf).
LimitNOFILE=300000

[Install]
WantedBy=multi-user.target

5.修改prometheus配置

vim /usr/pthreetools/prometheus/prometheus.yml

新增几个job，名字就叫‘mysqld_exporter’

  - job_name: 'mysqld_exporter'
    static_configs:
    - targets: ['10.90.8.125:9104']

重载一下systemctl使配置生效

systemctl daemon-reload

启动mysqld_exporter

systemctl start mysqld_exporter

查看mysqld_exporter状态

systemctl status mysqld_expoeter

重启prometheus

systemctl restart prometheus

查看prometheus状态

systemctl status prometheus

6.grafana配置监控

(1) 引入数据源

(2)导入模板

这里推荐一款中文版的模板，ID：17320


导入完成后就能看到效果了，下面附上一张我配置完的成果图