Zabbix-agent5.0_CentOS6.8监控主机与Mysql

CentOS6.8安装Zabbix-agent5.0监控主机性能与Mysql数据库

环境介绍：

 CentOS 6.8

 Zabbix-agent 5.0

 Zabbix-server：192.168.92.111

 Zabbix-agent：192.168.92.14

二、Zabbix-agent安装

1.设置Selinux为disabled

[root@localhost ~]# getenforce Enforcing [root@localhost ~]# setenforce 0 [root@localhost ~]# getenforce Permissive [root@localhost ~]# vim /etc/sysconfig/selinux

SELINUX=disabled

 

2.设置Iptables防火墙规则

[root@localhost ~]# iptables -L

Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED ACCEPT icmp -- anywhere anywhere ACCEPT all -- anywhere anywhere ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:ssh REJECT all -- anywhere anywhere reject-with icmp-host-prohibited Chain FORWARD (policy ACCEPT) target prot opt source destination REJECT all -- anywhere anywhere reject-with icmp-host-prohibited Chain OUTPUT (policy ACCEPT) target prot opt source destination [root@localhost ~]# iptables -I INPUT -p tcp --dport 10050 -j ACCEPT ----放行zabbix端口10050 [root@localhost ~]# iptables -L

Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT tcp -- anywhere anywhere tcp dpt:zabbix-agent ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED ACCEPT icmp -- anywhere anywhere ACCEPT all -- anywhere anywhere ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:ssh REJECT all -- anywhere anywhere reject-with icmp-host-prohibited Chain FORWARD (policy ACCEPT) target prot opt source destination REJECT all -- anywhere anywhere reject-with icmp-host-prohibited Chain OUTPUT (policy ACCEPT) target prot opt source destination

 

3.安装Zabbix Yum源仓库

rpm -Uvh https://repo.zabbix.com/zabbix/5.0/rhel/6/x86_64/zabbix-release-5.0-1.el6.noarch.rpm

Retrieving https://repo.zabbix.com/zabbix/5.0/rhel/6/x86_64/zabbix-release-5.0-1.el6.noarch.rpm warning: /var/tmp/rpm-tmp.8Lm2y3: Header V4 RSA/SHA512 Signature, key ID a14fe591: NOKEY Preparing... ########################################### [100%] 1:zabbix-release ########################################### [100%] yum clean all

已加载插件：fastestmirror Cleaning repos: base extras updates zabbix zabbix-non-supported 清理一切 Cleaning up list of fastest mirrors

 

4.安装Zabbix-agent

yum -y install zabbix-agent

已加载插件：fastestmirror 设置安装进程 Loading mirror speeds from cached hostfile * base: mirrors.bfsu.edu.cn * extras: mirrors.bfsu.edu.cn * updates: mirrors.bfsu.edu.cn 解决依赖关系 --> 执行事务检查 ---> Package zabbix-agent.x86_64 0:5.0.0-1.el6 will be 安装 --> 完成依赖关系计算 依赖关系解决 =============================================================================================================================== 软件包 架构 版本 仓库 大小 =============================================================================================================================== 正在安装: zabbix-agent x86_64 5.0.0-1.el6 zabbix 435 k 事务概要 =============================================================================================================================== Install 1 Package(s) 总下载量：435 k Installed size: 1.7 M 下载软件包： http://repo.zabbix.com/zabbix/5.0/rhel/6/x86_64/^H^H^H^Hzabbix-agent-5.0.0-1.el6.x86_64.rpm 18% [======= zabbix-agent-5.0.0-1.el6.x86_64.rpm | 435 kB 00:08 warning: rpmts_HdrFromFdno: Header V4 RSA/SHA512 Signature, key ID a14fe591: NOKEY Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-ZABBIX-A14FE591 Importing GPG key 0xA14FE591: Userid : Zabbix LLC <packager@zabbix.com> Package: zabbix-release-5.0-1.el6.noarch (installed) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-ZABBIX-A14FE591 运行 rpm_check_debug 执行事务测试 事务测试成功 执行事务 Warning: RPMDB altered outside of yum. 正在安装 : zabbix-agent-5.0.0-1.el6.x86_64 1/1 Verifying : zabbix-agent-5.0.0-1.el6.x86_64 1/1 已安装: zabbix-agent.x86_64 0:5.0.0-1.el6

 

注意：

 如果下载的zabbix-agent 5.0安装包使用rpm安装时提示“错误：依赖检测失败：libpcre.so.0()(64bit)”，则可以使用zabbix 5.0 Yum源来安装zabbix-agent 5.0软件。

5.修改zabbix-agent配置文件：Hostname需要和web配置的主机名称一致，若不一致则报错：cannot send list of active checks to "192.168.92.14": host [mysql-92.14] not found，但不影响zabbix-agent采集信息

vim /etc/zabbix/zabbix_agentd.conf

Server=127.0.0.1,192.168.92.111 ServerActive=192.168.92.111 Hostname=192.168.92.14

service zabbix-agent start service zabbix-agent enable

用法: /etc/init.d/zabbix-agent {start|stop|status|restart|try-restart|force-reload} [root@localhost ~]# ss -antulp | grep :10050 tcp LISTEN 0 128 :::10050 :::* users:(("zabbix_agentd",2963,5),("zabbix_agentd",2965,5),("zabbix_agentd",2966,5),("zabbix_agentd",2967,5),("zabbix_agentd",2968,5),("zabbix_agentd",2969,5)) tcp LISTEN 0 128 *:10050 *:* users:(("zabbix_agentd",2963,4),("zabbix_agentd",2965,4),("zabbix_agentd",2966,4),("zabbix_agentd",2967,4),("zabbix_agentd",2968,4),("zabbix_agentd",2969,4))

 

三、Zabbix-server端Web界面配置

–主机系统性能监控

1.添加Zabbix-agent主机

 

2.添加主机信息

 

3.选择主机性能监控模板

 

4.查看被监控主机状态

 现在可以看到在Zabbix-server端web页面上centos6主机状态显示绿色，说明被监控主机已经添加成功。

 

5.查看被监控主机系统性能监控信息

 

6.以图形形式查看性能监控信息

 

–Mysql数据库监控

Zabbix-agent端配置

1.基础环境配置

yum install tree net-tool net-snmp vim ntpdate wget #安装部分基础软件包 cd /etc/yum.repos.d/ #修改yum源 更改为阿里云 mv CentOS-Base.repo CentOS-Base.repo.bak #备份文件 wget -O CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo #下载阿里云镜像仓库 wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo #下载epel仓库 yum clean all #清理缓存 yum makecache #重新生成缓存

 

2.安装Mysql数据库

Mysql5.7下载

wget https://repo.mysql.com//mysql57-community-release-el6-9.noarch.rpm //下载Mysql5.7 Yum源 ls mysql57-community-release-el6-9.noarch.rpm rpm -ivh mysql57-community-release-el6-9.noarch.rpm //安装Mysql5.7 Yum源 yum -y install mysql-server //安装Mysql 已安装: mysql-community-libs.x86_64 0:5.7.30-1.el6 mysql-community-libs-compat.x86_64 0:5.7.30-1.el6 mysql-community-server.x86_64 0:5.7.30-1.el6 作为依赖被安装: mysql-community-client.x86_64 0:5.7.30-1.el6 mysql-community-common.x86_64 0:5.7.30-1.el6 numactl.x86_64 0:2.0.9-2.el6 作为依赖被升级: postfix.x86_64 2:2.6.6-8.el6

 

#启动Mysqld服务 service mysqld start ss -antulp | grep :3306 grep 'root@localhost:' /var/log/mysqld.log mysql -hlocalhost -uroot -p'haiwhAT<g1O+' alter user root@"localhost" identified by "123qqq...A"; mysql -uroot -p123qqq...A

 

3.授权Zabbix用户，用于发送数据

mysql -uroot -p123qqq...A grant all on *.* to zabbix@"%" identified by "123qqq...A"; flush privileges;

 

4.测试使用Zabbix用户登录数据库

mysql -uzabbix -p123qqq...A show databases; select user();

 

5.查看mysql的sock文件路径

[root@localhost ~]# find / -name mysql.sock /var/lib/mysql/mysql.sock

 

6.查找并拷贝Zabbix监控Mysql模板文件

find / -name user*_mysql.conf

 

/usr/share/doc/zabbix-agent-5.0.0/userparameter_mysql.conf cp /usr/share/doc/zabbix-agent-5.0.0/userparameter_mysql.conf /etc/zabbix/zabbix_agentd.d/ id zabbix chown -R zabbix:zabbix /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf //设置模板文件归属 ll /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

 

7.查看Zabbix自带的监控模板文件

 由配置文件可以知道，在Linux系统中需要在zabbix-agent home目录中创建.my.cnf(默认情况下为/var/lib/zabbix)，并且文件必须以下有三个字符串：

• [client]
• user=zbx_monitor
• password=<password>

 

#监控脚本添加,指定ip地址连接方式

vi /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

#template_db_mysql.conf created by Zabbix for "Template DB MySQL" and Zabbix 4.2 #For OS Linux: You need create .my.cnf in zabbix-agent home directory (/var/lib/zabbix by default) #For OS Windows: You need add PATH to mysql and mysqladmin and create my.cnf in %WINDIR%my.cnf,C:my.cnf,BASEDIRmy.cnf https://dev.mysql.com/doc/refman/5.7/en/option-files.html #The file must have three strings: #[client] #user=zbx_monitor #password=<password>

 

#添加模版 -uzabbix -p123qqq...A等价于HOME=/etc/zabbix可以省略

UserParameter=mysql.ping[*], HOME=/etc/zabbix /usr/local/mysql/bin/mysqladmin -h192.168.92.14 -uzabbix -p123qqq...A ping

UserParameter=mysql.get_status_variables[*], HOME=/etc/zabbix /usr/bin/mysql -h192.168.92.14 -sNX -e "show global status"

UserParameter=mysql.version[*], HOME=/etc/zabbix /usr/local/mysql/bin/mysqladmin -h192.168.92.14 -s version

UserParameter=mysql.uptime,HOME=/etc/zabbix /usr/local/mysql/bin/mysqladmin -h192.168.92.14 -uzabbix -p123qqq...A status | cut -f2 -d ":" | cut -f1 -d "T" | tr -d " "

UserParameter=mysql.db.discovery[*], HOME=/etc/zabbix /usr/bin/mysql -h192.168.92.14 -sN -e "show databases"

UserParameter=mysql.dbsize[*], HOME=/etc/zabbix /usr/bin/mysql -h192.168.92.14 -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"

UserParameter=mysql.replication.discovery[*], HOME=/etc/zabbix /usr/bin/mysql -h192.168.92.14 -sNX -e "show slave status"

UserParameter=mysql.slave_status[*], HOME=/etc/zabbix /usr/bin/mysql -h192.168.92.14 -sNX -e "show slave status"

# 连接数

UserParameter=mysql.max_connections,HOME=/etc/zabbix echo "show variables where Variable_name='max_connections';" | /usr/bin/mysql -h192.168.92.14 -N | awk '{print $2}'

UserParameter=mysql.current_connections,HOME=/etc/zabbix echo "show global status where Variable_name='Threads_connected';" | /usr/bin/mysql -h192.168.92.14 -N | awk '{print $2}'

# 缓冲池

UserParameter=mysql.buffer_pool_size,HOME=/etc/zabbix echo "show variables where Variable_name='innodb_buffer_pool_size';" | /usr/bin/mysql -h192.168.92.14 -N |awk '{printf "%.2f",$2/1024/1024/1024}'

UserParameter=mysql.buffer_pool_usage_percent,HOME=/etc/zabbix echo "show global status where Variable_name='Innodb_buffer_pool_pages_free' or Variable_name='Innodb_buffer_pool_pages_total';" | /usr/bin/mysql -h192.168.92.14 -N | awk '{a[NR]=$2}END{printf "%.1f",100-((a[1]/a[2])*100)}'

# 增删改查

UserParameter=mysql.status[*],HOME=/etc/zabbix echo "show global status where Variable_name='$1';" | /usr/bin/mysql -h192.168.92.14 -N | awk '{print $$2}'

#UserParameter=mysql.status[*],/etc/zabbix/zabbix_agentd.d/chk.sh $1

 

#默认值

#UserParameter=mysql.ping[*],HOME=/etc/zabbix mysqladmin -h"$1" -P"$2" ping

#UserParameter=mysql.get_status_variables[*],HOME=/etc/zabbix mysql -h"$1" -P"$2" -sNX -e "show global status"

#UserParameter=mysql.version[*],HOME=/etc/zabbix mysqladmin -s -h"$1" -P"$2" version

#UserParameter=mysql.db.discovery[*],HOME=/etc/zabbix mysql -h"$1" -P"$2" -sN -e "show databases"

#UserParameter=mysql.dbsize[*],HOME=/etc/zabbix mysql -h"$1" -P"$2" -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"

#UserParameter=mysql.replication.discovery[*],HOME=/etc/zabbix mysql -h"$1" -P"$2" -sNX -e "show slave status"

#UserParameter=mysql.slave_status[*],HOME=/etc/zabbix mysql -h"$1" -P"$2" -sNX -e "show slave status"

 

8.在/var/lib/zabbix目录下创建.my.cnf配置文件，实际用/etc/zabbix

mkdir /etc//zabbix

vi /etc//zabbix

[mysql] host=localhost user=zabbix //数据库中授权的zabbix用户 password=123qqq...A //授权用户密码 socket=/var/lib/mysql/mysql.sock [mysqladmin] host=localhost user=zabbix password=123qqq...A socket=/vsr/lib/mysql/mysql.sock

 

##################################### #重启Zabbix-agent服务

service zabbix-agent restart

#Zabbix服务端查看zabbix-get进程，若正常则可以监视mysql

zabbix_get -s192.168.92.14 -p 10050 -k mysql.ping

---------------------------------------------------------------------------------------------------

#写个脚本给配置文件用,2>/dev/null输出结果时不输出应答信息

#Usage:./chk.sh(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)

#示例查看Connections

sh chk.sh Connections

vi /etc/zabbix/zabbix_agentd.d/chk.sh

 

#!/bin/bash

#Filename:chk.sh

MYSQL_SOCK="/var/lib/mysql/mysql.sock"

MYSQL_USER='zabbix'

MYSQL_PASSWORD='123qqq...A'

MYSQL_HOST='192.168.92.14'

MYSQL_PORT='3306'

MYSQL_Connect="/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT -S$MYSQL_SOCK"

 

if [ $# -ne 1 ];then

echo "please input one arguement"

fi

 

case $1 in

 

Uptime) #查询当前MySQL本次启动后的运行统计时间

result=`HOME=/etc/zabbix ${MYSQL_Connect} status 2>/dev/null | cut -d ":" -f 2 | cut -d " " -f 2`

echo $result

;;

 

Slow_queries) #查看当前慢查询语句的个数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Slow_queries" | cut -d "|" -f 3`

echo $result

;;

 

Com_rollback) #执行回滚的个数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_rollback" | cut -d "|" -f 3`

echo $result

;;

 

Questions)

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Questions" | cut -d "|" -f 3`

echo $result

;;

 

Com_commit)

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_commit" | cut -d "|" -f 3`

echo $result

;;

 

Bytes_sent) #发送的字节数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Bytes_sent" | cut -d "|" -f 3`

echo $result

;;

 

Bytes_received) #接受的字节数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Bytes_received" | cut -d "|" -f 3`

echo $result

;;

 

Com_begin)

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_begin" | cut -d "|" -f 3`

echo $result

;;

 

Open_tables) #查看当前打开的表数量

result=`HOME=/etc/zabbix ${MYSQL_Connect} status 2>/dev/null | cut -d ":" -f 5 | cut -d " " -f 2`

echo $result

;;

 

Threads_connected) #查看当前打开的连接数量

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Threads_connected" | cut -d "|" -f 3`

echo $result

;;

 

Threads_cached) #查看线程缓存内的线程数量

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Threads_cached" | cut -d "|" -f 3`

echo $result

;;

 

Threads_created) #查看创建用来处理连接的线程数。如果Threads_created较大，可能要增加thread_cache_size值。

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Threads_created" | cut -d "|" -f 3`

echo $result

;;

 

Threads_running) #查看激活的(非睡眠状态)线程数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Threads_running" | cut -d "|" -f 3`

echo $result

;;

 

Slow_launch_threads) #查看创建时间超过slow_launch_time秒的线程数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Slow_launch_threads" | cut -d "|" -f 3`

echo $result

;;

 

Com_select) #查看select语句的执行数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_select" |cut -d "|" -f 3`

echo $result

;;

 

Com_insert) #查看insert语句的执行数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_insert" |cut -d "|" -f 3`

echo $result

;;

 

Com_update) #查看update语句的执行数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_update" | cut -d "|" -f 3`

echo $result

;;

 

Com_delete) #查看delete语句的执行数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_delete" | cut -d "|" -f 3`

echo $result

;;

 

Connections) #查看试图连接到MySQL(不管是否连接成功)的连接数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Connections" | cut -d "|" -f 3`

echo $result

;;

 

Table_locks_immediate) #查看立即获得的表的锁的次数

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Table_locks_immediate" | cut -d "|" -f 3`

echo $result

;;

 

Table_locks_waited) #查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制

result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Table_locks_waited" | cut -d "|" -f 3`

echo $result

;;

 

*)

echo "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)"

;;

esac

 

---------------------------------

chmod +x /etc/zabbix/zabbix_agentd.d/chk.sh

#测试脚本

./etc/zabbix/zabbix_agentd.d/chk.sh Connections

#或者 sh执行方式不需要执行权限可以执行

sh /etc/zabbix/zabbix_agentd.d/chk.sh Connections

#脚本在配置文件引入

UserParameter=mysql.status[*],HOME=/etc/zabbix /etc/zabbix/zabbix_agentd.d/chk.sh $1

#测试chk.sh 是否有用

#zabbix_get -slocalhost -p 10050 -k "mysql.threads_connected"

zabbix_get -s192.168.92.14 -p 10050 -k mysql.ping

zabbix_get -slocalhost -p 10050 -k "mysql.status[Com_update]"

#zabbix_get调用自定义脚本Connections部分报错：ERROR 1045 (28000): Access denied for user 'root'@'mysql-92.14' (using password: NO)

#zabbix_get中向chk.sh中传入参数加方括号[]：[Connections]

zabbix_get -slocalhost -p 10050 -k "mysql.status[Connections]"

解决：chk.sh需要添加HOME=/etc/zabbix：result=`HOME=/etc/zabbix ${MYSQL_Connect} extended-status

 

#新版本zabbix对应的key的形式。

mysql.connections.rate

mysql.threads_connected

result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w “Threads_connected” | cut -d “|” -f 3`

echo $result

 

#单独配置一个参数mysql.threads_connected

UserParameter=mysql.threads_connected,HOME=/etc/zabbix /usr/local/mysql/bin/mysqladmin -hlocalhost -uzabbix -p'123qqq...A' extended-status 2>/dev/null | grep -w "Connections" | cut -d "|" -f 3

 

#zabbix 日志

tail -f /var/log/zabbix/zabbix_server.log

tail -f /var/log/zabbix/zabbix_agentd.log

 

Zabbix-server Web端配置

1.选择Mysql监控模板

 

 

2.查看Mysql监控数据

 要查看单个监控项的数据图形，直接点击监控项末尾的“图形”即可。

 

3.查看所有监控项图形信息