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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 这篇文章是关于如何使用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
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
分布式计算 大数据 Apache
ClickHouse与大数据生态集成:Spark & Flink 实战
【10月更文挑战第26天】在当今这个数据爆炸的时代,能够高效地处理和分析海量数据成为了企业和组织提升竞争力的关键。作为一款高性能的列式数据库系统,ClickHouse 在大数据分析领域展现出了卓越的能力。然而,为了充分利用ClickHouse的优势,将其与现有的大数据处理框架(如Apache Spark和Apache Flink)进行集成变得尤为重要。本文将从我个人的角度出发,探讨如何通过这些技术的结合,实现对大规模数据的实时处理和分析。
134 2
ClickHouse与大数据生态集成:Spark & Flink 实战
|
2月前
|
SQL 关系型数据库 MySQL
案例剖析,MySQL共享锁引发的死锁问题!
案例剖析,MySQL共享锁引发的死锁问题!
|
15天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
|
1月前
|
缓存 监控 关系型数据库
如何根据监控结果调整 MySQL 数据库的参数以提高性能?
【10月更文挑战第28天】根据MySQL数据库的监控结果来调整参数以提高性能,需要综合考虑多个方面的因素
80 1
|
1月前
|
监控 关系型数据库 MySQL
如何监控和诊断 MySQL 数据库的性能问题?
【10月更文挑战第28天】监控和诊断MySQL数据库的性能问题是确保数据库高效稳定运行的关键
149 1
|
2月前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
156 3
|
2月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
2月前
|
Dart Android开发
鸿蒙Flutter实战:03-鸿蒙Flutter开发中集成Webview
本文介绍了在OpenHarmony平台上集成WebView的两种方法:一是使用第三方库`flutter_inappwebview`,通过配置pubspec.lock文件实现;二是编写原生ArkTS代码,自定义PlatformView,涉及创建入口能力、注册视图工厂、处理方法调用及页面构建等步骤。
68 0
|
14天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
94 15
|
8天前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。

推荐镜像

更多