【MySQL】Prometheus监控MySQL

需要安装node_exporter+mysqld_exporter

1.下载

node_exporter

wget https://github.com/prometheus/node_exporter/releases/download/v1.3.1/node_exporter-1.3.1.linux-amd64.tar.gz

mysqld_exporter

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz

　　

2.创建Prometheus用户和组

sudo groupadd --system prometheus
sudo useradd -s /sbin/nologin --system -g prometheus prometheus

　　

3.创建数据库用户
创建用于监控的用户

create user 'exporter'@'localhost' identified by 'pwd123' with max_user_connections 20;
grant process, replication client, select on *.* to 'exporter'@'localhost';
create user 'exporter'@'127.0.0.1' identified by 'pwd123' with max_user_connections 20;
grant process, replication client, select on *.* to 'exporter'@'127.0.0.1' ;
flush privileges

　　

4.安装node_exporter

sudo tar -zxvf node_exporter-1.3.1.linux-amd64.tar.gz -C /usr/local/
sudo ln -s /usr/local/node_exporter-1.3.1.linux-amd64/ /usr/local/node_exporter
sudo chown -R prometheus:prometheus /usr/local/node_exporter

　　

编辑启动服务

sudo vim /etc/systemd/system/node_exporter.service
[Unit]
Description=Node Exporter
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
ExecStart=/usr/local/node_exporter/node_exporter

[Install]
WantedBy=default.target

　　

配置服务

sudo systemctl daemon-reload
sudo systemctl start node_exporter
sudo systemctl status node_exporter
sudo systemctl enable node_exporter
ss -tunlp|grep node

　　

5.安装mysqld_exporter

sudo tar -zxvf mysqld_exporter-0.14.0.linux-amd64.tar.gz -C /usr/local/
sudo ln -s /usr/local/mysqld_exporter-0.14.0.linux-amd64/ /usr/local/mysqld_exporter
sudo chown -R prometheus:prometheus /usr/local/mysqld_exporter

　　

创建配置文件

sudo vim /etc/.mysqld_exporter.cnf
[client]
host=localhost
user=exporter
password=pwd123

启动的时候，指定这个配置文件即可。

sudo chown root:prometheus /etc/.mysqld_exporter.cnf

　　

创建服务

sudo vim /etc/systemd/system/mysql_exporter.service
[Unit]
Description=Prometheus MySQL Exporter
After=network.target
User=prometheus
Group=prometheus

[Service]
Type=simple
Restart=always
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/etc/.mysqld_exporter.cnf

[Install]
WantedBy=multi-user.target

　　

配置服务

sudo systemctl daemon-reload
sudo systemctl enable mysql_exporter
sudo systemctl start mysql_exporter
ss -tln |grep 9104

　　

6.添加监控目标
需要把mysqld_exporter和安装mysql的主机的node_exporter的监控目标都添加到prometheus server中。

vim /usr/local/prometheus/prometheus.yml
- job_name: 'mysql'
static_configs:
- targets: ['10.10.10.10:9104']
labels:
alias: db1

- job_name: 'mysql-node'
static_configs:
- targets: ['10.10.10.10:9100']
labels:
alias: db1

　　

注意：这里instance需要设置成相同的值，因为grafana中有些绘图会根据instance来绘制。

7.重启Prometheus server

sudo systemctl restart prometheus
或者热加载
curl -X POST xxxx:9090/-/reload

　　

8.添加Grafana模板