Prometheus之监控mysql

一 下载并安装mysqld_exporter

1.1 下载安装包并安装

root@mysql-01:~# wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.13.0/mysqld_exporter-0.13.0.linux-amd64.tar.gz
root@mysql-01:~# tar xf mysqld_exporter-0.13.0.linux-amd64.tar.gz -C /usr/local/
root@mysql-01:~# ln -sv /usr/local/mysqld_exporter-0.13.0.linux-amd64/ /usr/local/mysqld_exporter
'/usr/local/mysqld_exporter' -> '/usr/local/mysqld_exporter-0.13.0.linux-amd64/'

1.2  准备my.cnf

root@mysql-01:~# cat /usr/local/mysqld_exporter/my.cnf 
[client]
host=127.0.0.1
user=exporter
password=exporter

1.3 准备mysqld_exporter.service文件

root@mysql-01:~# cat /lib/systemd/system/mysqld_exporter.service
[Unit]
Descripton=mysqls_exporter server
Documentation=https://prometheus.io/docs/introduction/overview/
After=network.target

[Service]
Restart=on-failure
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/my.cnf

[Install]
WantedBy=multi-user.target

1.4 设置开机启动

root@mysql-01:~# systemctl enable mysqld_exporter.service 
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld_exporter.service → /lib/systemd/system/mysqld_exporter.service.
root@mysql-01:~# systemctl start mysqld_exporter.service
root@mysql-01:~# systemctl status mysqld_exporter.service 
● mysqld_exporter.service
     Loaded: loaded (/lib/systemd/system/mysqld_exporter.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-11-19 15:24:28 CST; 3s ago
       Docs: https://prometheus.io/docs/introduction/overview/
   Main PID: 11039 (mysqld_exporter)
      Tasks: 4 (limit: 2245)
     Memory: 1.9M
     CGroup: /system.slice/mysqld_exporter.service
             └─11039 /usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/my.cnf

二 创建mysql账户

mysql> CREATE USER 'exporter'@'127.0.0.1' IDENTIFIED BY 'exporter' WITH MAX_USER_CONNECTIONS 3;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

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

三 配置Prometheus

3.1 修改prometheus.yml 文件

  - job_name: "mysqld_exporter"
    static_configs:
      - targets: ['192.168.174.200:9104']

3.2 重启Prometheus服务

root@prometheus-01:~# systemctl restart prometheus.service

四 验证Prometheus web界面

五 grafana监控可视化

5.1 导入模板

模板ID：14057

5.2 验证界面数据