zabbix agent集成percona监控MySQL的插件实战案例

简介: 这篇文章是关于如何使用Percona监控插件集成Zabbix agent来监控MySQL的实战案例。

作者:尹正杰
版权声明:原创作品,谢绝转载!否则将追究法律责任。

一.自行部署MySQL主从同步

  博主推荐阅读:
    https://www.cnblogs.com/yinzhengjie/p/11816066.html

二.安装percona

1>.percona官网文档

  安装教程:
    https://www.percona.com/doc/percona-monitoring-plugins/LATEST/zabbix/index.html#installation-instructions  
    
  下载地址:
    https://www.percona.com/downloads/
  插件地址:
    https://www.percona.com/doc/percona-monitoring-plugins/LATEST/zabbix/index.html

2>.下载percona

[root@node203.yinzhengjie.org.cn ~]# ll
total 0
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm
--2020-02-29 15:15:55--  https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm
Resolving www.percona.com (www.percona.com)... 74.121.199.234
Connecting to www.percona.com (www.percona.com)|74.121.199.234|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 28296 (28K) [application/x-redhat-package-manager]
Saving to: ‘percona-zabbix-templates-1.1.8-1.noarch.rpm’

100%[====================================================================================================================================================================================================================================>] 28,296       104KB/s   in 0.3s   

2020-02-29 15:15:56 (104 KB/s) - ‘percona-zabbix-templates-1.1.8-1.noarch.rpm’ saved [28296/28296]

[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# ll
total 28
-rw-r--r-- 1 root root 28296 Jan 10  2018 percona-zabbix-templates-1.1.8-1.noarch.rpm
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]#

[root@node203.yinzhengjie.org.cn ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm

3>.安装percona

[root@node203.yinzhengjie.org.cn ~]# ll
total 28
-rw-r--r-- 1 root root 28296 Jan 10  2018 percona-zabbix-templates-1.1.8-1.noarch.rpm
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpm 
warning: percona-zabbix-templates-1.1.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:percona-zabbix-templates-1.1.8-1 ################################# [100%]

Scripts are installed to /var/lib/zabbix/percona/scripts
Templates are installed to /var/lib/zabbix/percona/templates
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# rpm -qpl percona-zabbix-templates-1.1.8-1.noarch.rpm 
warning: percona-zabbix-templates-1.1.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
/var/lib/zabbix/percona
/var/lib/zabbix/percona/scripts
/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
/var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
/var/lib/zabbix/percona/templates
/var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf
/var/lib/zabbix/percona/templates/zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.8.xml
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]#

[root@node203.yinzhengjie.org.cn ~]# rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpm

三.将percona的插件集成到zabbix agent端

1>.安装zabbix agent服务

  CentOS运行环境博主推荐阅读:
    https://www.cnblogs.com/yinzhengjie2020/p/12344057.html
  Ubuntu运行环境博主推荐阅读:
    https://www.cnblogs.com/yinzhengjie2020/p/12309682.html

2>.将percona的配置文件拷贝到zabbix agent的子配置文件目录并重启zabbix agent服务使得配置文件生效

[root@node203.yinzhengjie.org.cn ~]# cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# systemctl restart zabbix-agent.service
[root@node203.yinzhengjie.org.cn ~]#

3>.配置MySQL的用户名和密码,我root用户密码为空,因此直接mysql_pass的值直接給空串即可。

[root@node203.yinzhengjie.org.cn ~]# vim /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# cat /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
<?php
$mysql_user = 'root';
$mysql_pass = '';
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]#

4>.安装php环境,否则会运行报错,如下图所示

[root@node203.yinzhengjie.org.cn ~]# yum -y install php
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.tuna.tsinghua.edu.cn
 * extras: mirror.bit.edu.cn
 * updates: mirrors.huaweicloud.com
Resolving Dependencies
--> Running transaction check
---> Package php.x86_64 0:5.4.16-46.1.el7_7 will be installed
--> Processing Dependency: php-cli(x86-64) = 5.4.16-46.1.el7_7 for package: php-5.4.16-46.1.el7_7.x86_64
--> Processing Dependency: httpd-mmn = 20120211x8664 for package: php-5.4.16-46.1.el7_7.x86_64
--> Processing Dependency: httpd for package: php-5.4.16-46.1.el7_7.x86_64
--> Running transaction check
---> Package httpd.x86_64 0:2.4.6-90.el7.centos will be installed
--> Processing Dependency: httpd-tools = 2.4.6-90.el7.centos for package: httpd-2.4.6-90.el7.centos.x86_64
--> Processing Dependency: /etc/mime.types for package: httpd-2.4.6-90.el7.centos.x86_64
--> Processing Dependency: libaprutil-1.so.0()(64bit) for package: httpd-2.4.6-90.el7.centos.x86_64
--> Processing Dependency: libapr-1.so.0()(64bit) for package: httpd-2.4.6-90.el7.centos.x86_64
---> Package php-cli.x86_64 0:5.4.16-46.1.el7_7 will be installed
--> Running transaction check
---> Package apr.x86_64 0:1.4.8-5.el7 will be installed
---> Package apr-util.x86_64 0:1.5.2-6.el7 will be installed
---> Package httpd-tools.x86_64 0:2.4.6-90.el7.centos will be installed
---> Package mailcap.noarch 0:2.1.41-2.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================================================================================================
 Package                                                           Arch                                                         Version                                                                   Repository                                                     Size
==============================================================================================================================================================================================================================================================================
Installing:
 php                                                               x86_64                                                       5.4.16-46.1.el7_7                                                         updates                                                       1.4 M
Installing for dependencies:
 apr                                                               x86_64                                                       1.4.8-5.el7                                                               base                                                          103 k
 apr-util                                                          x86_64                                                       1.5.2-6.el7                                                               base                                                           92 k
 httpd                                                             x86_64                                                       2.4.6-90.el7.centos                                                       base                                                          2.7 M
 httpd-tools                                                       x86_64                                                       2.4.6-90.el7.centos                                                       base                                                           91 k
 mailcap                                                           noarch                                                       2.1.41-2.el7                                                              base                                                           31 k
 php-cli                                                           x86_64                                                       5.4.16-46.1.el7_7                                                         updates                                                       2.7 M

Transaction Summary
==============================================================================================================================================================================================================================================================================
Install  1 Package (+6 Dependent packages)

Total download size: 7.1 M
Installed size: 23 M
Downloading packages:
(1/7): apr-1.4.8-5.el7.x86_64.rpm                                                                                                                                                                                                                      | 103 kB  00:00:00     
(2/7): apr-util-1.5.2-6.el7.x86_64.rpm                                                                                                                                                                                                                 |  92 kB  00:00:00     
(3/7): httpd-tools-2.4.6-90.el7.centos.x86_64.rpm                                                                                                                                                                                                      |  91 kB  00:00:00     
(4/7): php-cli-5.4.16-46.1.el7_7.x86_64.rpm                                                                                                                                                                                                            | 2.7 MB  00:00:00     
(5/7): mailcap-2.1.41-2.el7.noarch.rpm                                                                                                                                                                                                                 |  31 kB  00:00:00     
(6/7): php-5.4.16-46.1.el7_7.x86_64.rpm                                                                                                                                                                                                                | 1.4 MB  00:00:00     
(7/7): httpd-2.4.6-90.el7.centos.x86_64.rpm                                                                                                                                                                                                            | 2.7 MB  00:00:01     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                                                                                         6.4 MB/s | 7.1 MB  00:00:01     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : apr-1.4.8-5.el7.x86_64                                                                                                                                                                                                                                     1/7 
  Installing : apr-util-1.5.2-6.el7.x86_64                                                                                                                                                                                                                                2/7 
  Installing : httpd-tools-2.4.6-90.el7.centos.x86_64                                                                                                                                                                                                                     3/7 
  Installing : mailcap-2.1.41-2.el7.noarch                                                                                                                                                                                                                                4/7 
  Installing : httpd-2.4.6-90.el7.centos.x86_64                                                                                                                                                                                                                           5/7 
  Installing : php-cli-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                           6/7 
  Installing : php-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                               7/7 
  Verifying  : php-cli-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                           1/7 
  Verifying  : mailcap-2.1.41-2.el7.noarch                                                                                                                                                                                                                                2/7 
  Verifying  : httpd-tools-2.4.6-90.el7.centos.x86_64                                                                                                                                                                                                                     3/7 
  Verifying  : apr-util-1.5.2-6.el7.x86_64                                                                                                                                                                                                                                4/7 
  Verifying  : httpd-2.4.6-90.el7.centos.x86_64                                                                                                                                                                                                                           5/7 
  Verifying  : apr-1.4.8-5.el7.x86_64                                                                                                                                                                                                                                     6/7 
  Verifying  : php-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                               7/7 

Installed:
  php.x86_64 0:5.4.16-46.1.el7_7                                                                                                                                                                                                                                              

Dependency Installed:
  apr.x86_64 0:1.4.8-5.el7             apr-util.x86_64 0:1.5.2-6.el7             httpd.x86_64 0:2.4.6-90.el7.centos             httpd-tools.x86_64 0:2.4.6-90.el7.centos             mailcap.noarch 0:2.1.41-2.el7             php-cli.x86_64 0:5.4.16-46.1.el7_7            

Complete!
[root@node203.yinzhengjie.org.cn ~]#

[root@node203.yinzhengjie.org.cn ~]# yum -y install php            #安装php程序的运行环境

[root@node203.yinzhengjie.org.cn ~]# yum -y install php-mysql
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.tuna.tsinghua.edu.cn
 * extras: mirror.bit.edu.cn
 * updates: mirrors.huaweicloud.com
Resolving Dependencies
--> Running transaction check
---> Package php-mysql.x86_64 0:5.4.16-46.1.el7_7 will be installed
--> Processing Dependency: php-pdo(x86-64) = 5.4.16-46.1.el7_7 for package: php-mysql-5.4.16-46.1.el7_7.x86_64
--> Running transaction check
---> Package php-pdo.x86_64 0:5.4.16-46.1.el7_7 will be installed
--> Processing Dependency: php-common(x86-64) = 5.4.16-46.1.el7_7 for package: php-pdo-5.4.16-46.1.el7_7.x86_64
--> Running transaction check
---> Package php-common.x86_64 0:5.4.16-46.1.el7_7 will be installed
--> Processing Dependency: libzip.so.2()(64bit) for package: php-common-5.4.16-46.1.el7_7.x86_64
--> Running transaction check
---> Package libzip.x86_64 0:0.10.1-8.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================================================================================================
 Package                                                          Arch                                                         Version                                                                    Repository                                                     Size
==============================================================================================================================================================================================================================================================================
Installing:
 php-mysql                                                        x86_64                                                       5.4.16-46.1.el7_7                                                          updates                                                       101 k
Installing for dependencies:
 libzip                                                           x86_64                                                       0.10.1-8.el7                                                               base                                                           48 k
 php-common                                                       x86_64                                                       5.4.16-46.1.el7_7                                                          updates                                                       565 k
 php-pdo                                                          x86_64                                                       5.4.16-46.1.el7_7                                                          updates                                                        99 k

Transaction Summary
==============================================================================================================================================================================================================================================================================
Install  1 Package (+3 Dependent packages)

Total download size: 814 k
Installed size: 4.3 M
Downloading packages:
(1/4): libzip-0.10.1-8.el7.x86_64.rpm                                                                                                                                                                                                                  |  48 kB  00:00:00     
(2/4): php-mysql-5.4.16-46.1.el7_7.x86_64.rpm                                                                                                                                                                                                          | 101 kB  00:00:00     
(3/4): php-pdo-5.4.16-46.1.el7_7.x86_64.rpm                                                                                                                                                                                                            |  99 kB  00:00:00     
(4/4): php-common-5.4.16-46.1.el7_7.x86_64.rpm                                                                                                                                                                                                         | 565 kB  00:00:00     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                                                                                         2.9 MB/s | 814 kB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : libzip-0.10.1-8.el7.x86_64                                                                                                                                                                                                                                 1/4 
  Installing : php-common-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                        2/4 
  Installing : php-pdo-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                           3/4 
  Installing : php-mysql-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                         4/4 
  Verifying  : php-common-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                        1/4 
  Verifying  : libzip-0.10.1-8.el7.x86_64                                                                                                                                                                                                                                 2/4 
  Verifying  : php-mysql-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                         3/4 
  Verifying  : php-pdo-5.4.16-46.1.el7_7.x86_64                                                                                                                                                                                                                           4/4 

Installed:
  php-mysql.x86_64 0:5.4.16-46.1.el7_7                                                                                                                                                                                                                                        

Dependency Installed:
  libzip.x86_64 0:0.10.1-8.el7                                                        php-common.x86_64 0:5.4.16-46.1.el7_7                                                        php-pdo.x86_64 0:5.4.16-46.1.el7_7                                                       

Complete!
[root@node203.yinzhengjie.org.cn ~]#

[root@node203.yinzhengjie.org.cn ~]# yum -y install php-mysql        #安装php连接MySQL的驱动

5>.测试数据

[root@node203.yinzhengjie.org.cn ~]# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
6913
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# ll /tmp/
total 4
-rw-r--r-- 1 root root 1233 Feb 29 15:53 localhost-mysql_cacti_stats.txt
drwx------ 3 root root 17 Feb 29 05:38 systemd-private-a58aea3cb3bc4f2088bea3737fa8e295-chronyd.service-HP0GYE
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# 
[root@node203.yinzhengjie.org.cn ~]# rm -f /tmp/localhost-mysql_cacti_stats.txt 
[root@node203.yinzhengjie.org.cn ~]#

6>.在zabbix server端测试数据是否可以正常获得

[root@zabbix203.yinzhengjie.org.cn ~]# /yinzhengjie/softwares/zabbix/bin/zabbix_get -s 172.200.1.203 -p 10050 -k "MySQL.Key-read-requests"

7>.在Zabbix WebUI配置监控MySQL

  博主推荐阅读:
    https://www.cnblogs.com/yinzhengjie2020/p/12381448.html
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
10月前
|
SQL 关系型数据库 MySQL
Mysql数据恢复—Mysql数据库delete删除后数据恢复案例
本地服务器,操作系统为windows server。服务器上部署mysql单实例,innodb引擎,独立表空间。未进行数据库备份,未开启binlog。 人为误操作使用Delete命令删除数据时未添加where子句,导致全表数据被删除。删除后未对该表进行任何操作。需要恢复误删除的数据。 在本案例中的mysql数据库未进行备份,也未开启binlog日志,无法直接还原数据库。
|
消息中间件 关系型数据库 MySQL
基于 Flink CDC YAML 的 MySQL 到 Kafka 流式数据集成
基于 Flink CDC YAML 的 MySQL 到 Kafka 流式数据集成
1548 0
|
Java 关系型数据库 MySQL
SpringBoot 通过集成 Flink CDC 来实时追踪 MySql 数据变动
通过详细的步骤和示例代码,您可以在 SpringBoot 项目中成功集成 Flink CDC,并实时追踪 MySQL 数据库的变动。
3628 45
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
消息中间件 关系型数据库 MySQL
基于 Flink CDC YAML 的 MySQL 到 Kafka 流式数据集成
本教程展示如何使用Flink CDC YAML快速构建从MySQL到Kafka的流式数据集成作业,涵盖整库同步和表结构变更同步。无需编写Java/Scala代码或安装IDE,所有操作在Flink CDC CLI中完成。首先准备Flink Standalone集群和Docker环境(包括MySQL、Kafka和Zookeeper),然后通过配置YAML文件提交任务,实现数据同步。教程还介绍了路由变更、写入多个分区、输出格式设置及上游表名到下游Topic的映射等功能,并提供详细的命令和示例。最后,包含环境清理步骤以确保资源释放。
1167 2
基于 Flink CDC YAML 的 MySQL 到 Kafka 流式数据集成
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
541 22
 MySQL秘籍之索引与查询优化实战指南
|
关系型数据库 MySQL 数据库
|
存储 SQL 关系型数据库
服务器数据恢复—云服务器上mysql数据库数据恢复案例
某ECS网站服务器,linux操作系统+mysql数据库。mysql数据库采用innodb作为默认存储引擎。 在执行数据库版本更新测试时,操作人员误误将在本来应该在测试库执行的sql脚本在生产库上执行,导致生产库上部分表被truncate,还有部分表中少量数据被delete。
388 25
|
关系型数据库 MySQL OLAP
无缝集成 MySQL,解锁秒级 OLAP 分析性能极限,完成任务可领取三合一数据线!
通过 AnalyticDB MySQL 版、DMS、DTS 和 RDS MySQL 版协同工作,解决大规模业务数据统计难题,参与活动完成任务即可领取三合一数据线(限量200个),还有机会抽取蓝牙音箱大奖!

热门文章

最新文章

推荐镜像

更多