监控之Prometheus+grafana+mysqld_exporter

mysqld_exporter监控MySQL

一、服务器环境：

ECS CentOS7.6 x86_64位最小化安装
安全组提前放行mysqld_exporter服务端口
tidb04 192.10.0.59    tidb05 192.10.0.246  
服务器本地绑定hosts文件。
环境和上一篇文章环境保持一致
1.
2.
3.
4.
5.

二、创建数据库账户

创建数据库账户获取MySQL参数变量指标

  
root@tidb04 00:48:  [(none)]>  GRANT SELECT, PROCESS, rEPLICATION CLIENT ON *.* TO 'exporter'@'127.0.0.1' identified by 'EDy&exporter1202' WITH MAX_USER_CONNECTIONS 3;flush privileges;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

[root@tidb04 prometheus]# cat  /opt/server/exporter/mysqld_exporter/.my.cnf
[client]
host=127.0.0.1
user=exporter
password=EDy&exporter1202
1.
2.
3.
4.
5.
6.
7.
8.
9.

三、mysqld_exporter启动脚本

  
[root@tidb04 exporter]# cat /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=/opt/server/exporter
ExecStart=/opt/server/exporter/mysqld_exporter/mysqld_exporter --config.my-cnf=/opt/server/exporter/mysqld_exporter/.my.cnf --web.listen-address=:19102 \
--collect.engine_innodb_status \
--collect.info_schema.tablestats \
--collect.info_schema.query_response_time \
--collect.info_schema.tables.databases="*" \
--collect.info_schema.innodb_tablespaces 

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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

服务启动命令:

服务加载
systemctl daemon-reload
systemctl enable mysqld_exporter
启动服务
systemctl restart mysqld_exporter
服务状态查看
systemctl status mysqld_exporter
1.
2.
3.
4.
5.
6.
7.

同上tidb05服务器也重复执行如上命令

四、prometheus配置

在配置文件/data/soft/prometheus/prometheus.yml 最后添加如下参数:

  
[root@tidb04 soft]# tail -3 /data/soft/prometheus/prometheus.yml
  - job_name: 'mysqld_exporter'
    static_configs:
    - targets: ['192.10.0.59:19102','192.10.0.246:19102']
1.
2.
3.
4.