配置Prometheus mysqld_exporter 和Grafana监控MySQL

╰+攻爆jí腚メ 2023-10-06 12:02 38阅读 0赞

在受监控MySQL节点上，我们将为mysqld\_exporter创建一个系统用户。

\[root@prometheus ~\]\# useradd -m -s /bin/false mysqld\_exporter

接下来，前往Prometheus的下载页面，然后下载mysqld\_exporter tarball或使用下面的wget命令从命令行下载下来，

\[root@prometheus ~\]\# wget https://github.com/prometheus/mysqld\_exporter/releases/download/v0.12.1/mysqld\_exporter-0.12.1.linux-amd64.tar.gz

下载mysqld\_exporter文件后，继续进行提取，如下所示

\[root@prometheus ~\]\# tar -zxpvf mysqld\_exporter-0.12.1.linux-amd64.tar.gz

您可以使用tree命令检查提取的文件夹的内容，如下所示

\[root@prometheus ~\]\# tree mysqld\_exporter-0.12.1.linux-amd64

![20200627184230664.png][]

接下来，将名为mysqld\_exporter的二进制文件复制到/ usr / local / bin路径

\[root@prometheus ~\]\# cp mysqld\_exporter-0.12.1.linux-amd64/mysqld\_exporter /usr/local/bin

接下来，设置已复制的mysqld\_exporter文件的文件许可权，如下所示

\[root@prometheus ~\]\# chown mysqld\_exporter:mysqld\_exporter /usr/local/bin/mysqld\_exporter

创建 Prometheus mysqld\_exporter MySQL数据库用户

CREATE USER 'mysqld\_exporter'@'localhost' IDENTIFIED BY 'StrongPassword' WITH MAX\_USER\_CONNECTIONS 2;

GRANT PROCESS, REPLICATION CLIENT, SELECT ON \*.\* TO 'mysqld\_exporter'@'localhost';

FLUSH PRIVILEGES;

EXIT

配置mysqld\_exporter身份验证文件

vi /etc/.mysqld\_exporter.cnf

\[client\]

user=mysqld\_exporter

password=StrongPassword

设置.mysqld\_exporter.cnf文件的文件许可权

chown mysqld\_exporter:mysqld\_exporter /etc/.mysqld\_exporter.cnf

接下来，我们需要配置mysqld\_exporter以作为服务运行。因此，继续进行操作并创建一个systemd服务文件，如下所示

\[root@prometheus ~\]\# vi /etc/systemd/system/mysqld\_exporter.service

然后粘贴如下所示的配置并保存文件

\[Unit\]

Description= Prometheus MySQL Exporter

Wants=network-online.target

After=network-online.target

\[Service\]

User=mysqld\_exporter

Group=mysqld\_exporter

Type=simple

Restart=always

ExecStart=/usr/local/bin/mysqld\_exporter \\

\--config.my-cnf /etc/.mysqld\_exporter.cnf \\

\--collect.auto\_increment.columns \\

\--collect.binlog\_size \\

\--collect.engine\_innodb\_status \\

\--collect.engine\_tokudb\_status \\

\--collect.global\_status \\

\--web.listen-address=0.0.0.0:9104

\[Install\]

WantedBy=multi-user.target

为了使更改生效，请使用以下命令重新加载systemd Manager：

\[root@prometheus ~\]\# systemctl daemon-reload

接下来，启动并启用mysqld\_exporter服务

\[root@prometheus ~\]\# systemctl start mysqld\_exporter

\[root@prometheus ~\]\# systemctl enable mysqld\_exporter

为了确保该服务正在运行，请执行：

\[root@prometheus ~\]\# systemctl status mysqld\_exporter

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70][]

为了确保该服务正在运行，请使用netstat实用程序检查它是否正在按默认预期在端口9100上侦听。

\[root@prometheus ~\]\# netstat -pnltu | grep 9104

tcp6 0 0 :::9104 :::\* LISTEN 9112/mysqld\_exporte

\[root@prometheus ~\]\#

完善！Mysqld\_exporter服务正在按预期运行。

接下来，如图所示在防火墙中打开端口9104

\[root@prometheus ~\]\# firewall-cmd --add-port=9104/tcp --permanent

success

\[root@prometheus ~\]\# firewall-cmd --reload

success

\[root@prometheus ~\]\#

同样，对其他MySQL也重复上述步骤。

最后，您需要将mysqld\_exporter目标添加到prometheus.yml文件。附加以下几行以为Prometheus服务器定义mysqld\_exporter

\[root@prometheus ~\]\# vi /etc/prometheus/prometheus.yml

\---------

\- job\_name: 'mysqld\_exporter'

static\_configs:

\- targets: \['localhost:9104'\]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 1][]

重新启动Prometheus服务

\[root@prometheus ~\]\# systemctl restart prometheus

再次转到浏览器，单击“ 状态 ”标签，然后单击“ 目标 ”

确保 在Prometheus服务器的浏览器上观察到名为mysqld\_exporter的新端点

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 2][]

为确保您从配置的节点接收指标。只需使用curl命令，如下所示：

\# curl http://node-ip:9104/metrics

例如，要显示来自Prometheus服务器的指标，请运行：

\[root@prometheus ~\]\# curl http://localhost:9104/metrics

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 3][]

也可以通过打开浏览器并浏览URL来实现

http://192.168.174.155:9100/metrics

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 4][]

您还可以选择以图形方式绘制所需的指标。只需转到Prometheus服务器的主页，然后单击标记为“ 在光标处插入度量 ”的下拉菜单。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 5][]

选择要绘制图形的指标，

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 6][]

单击“ 执行 ”按钮，然后单击下面的“ 图形 ”选项卡以显示图形

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 7][]

要创建用于可视化指标的仪表板，请点击左侧栏中的加号，然后点击“ 导入 ”

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 8][]

您将需要从Grafana的存储库中填写仪表板的URL。[https://grafana.com/grafana/dashboards/7362][https_grafana.com_grafana_dashboards_7362]

将链接粘贴到“ URL”部分

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 9][]

粘贴链接后，请等待几秒钟，然后将生成以下数据。将Prometheus数据源名称更改为“ Prometheus ”，然后单击“ 导入 ”

Grafana将开始从Prometheus服务器获取指标，然后在色彩鲜艳且直观的仪表板中进行可视化。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 10][]

到此结束了我们的教程。

[20200627184230664.png]: https://img-blog.csdnimg.cn/20200627184230664.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200627184230670.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200627184230683.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200627184230694.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200627184230702.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200627184230710.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200627184230718.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200627184230733.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200627184230748.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200627184230774.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[https_grafana.com_grafana_dashboards_7362]: https://grafana.com/grafana/dashboards/7362
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200627184230780.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200627184230797.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70