监控之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文件。
环境和上一篇文章环境保持一致

二、创建数据库账户

linux建数据库账户获取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

三、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

服务启动命令:

服务加载
systemctl daemon-reload
systemctl enable mysqld_exporter
启动服务
systemctl restart mysqld_exporter
服务状态查看
systemctl status mysqld_exporter

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

四、prometheus配置

在配置文件/dmysql创建表ata/soft/promemysql面试题theus/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']

重服务:systemctl restart prometheuslinux系统

验证mysqld_exporter是否接入到prometheus:

添加的mysqld_exporter的服务器都可以查到

五、导入linux是什么操作系统mysqld_exporter dashboard模版

监控MySQL服务 导入mysql安装配置教程myslinux删除文件命令qld_exporter dashboard模版

登录 grafana官mysql密码忘记了怎么办网查找node_expor系统运维工作内容ter dashboard的模板 导入到自建的grafana系统:

dashboard的模板查找地址: h​​ttps://grafamysql密码忘记了怎么办na.com/graflinux是什么操作系统ana/dashboards​​


                                            监控之Prometheus+grafana+mysqld_exporter

找到官房提供的MySQL_exporter的dash系统运维工程师board的模板id: 14057 导入到grafana控制台

​​https://grafana.com/oss/mysql安装prometheus/exporters/mysql-exp系统运维工程师面试问题及答案orter/?tab=dashboards​​

下面是具体的导入过程:


                                            监控之Prometheus+grafana+mysqld_exporter


                                            监控之Prometheus+grafana+mysqld_exporter

完成对数据库的监控:


                                            监控之Prometheus+grafana+mysqld_exporter