zabbix监控mysql,监控磁盘IO,监控cpu各单核情况,监控网络数据包

友情链接

Zabbix拓扑图绘制，快速定位故障主机

Mycat版部署mysql读写分离

一/mysql监控

1/设置权限

被监控mysql设置权限
ALTER USER `zabbix`@`localhost` IDENTIFIED BY 'zabbix127';
CREATE USER `zabbix`@`localhost` IDENTIFIED BY 'zabbix127';
GRANT Create User ON *.* TO `zabbix`@`localhost`;

2/编写脚本

# vim /etc/zabbix/script/mysql_status.sh    #编辑脚本
#!/bin/bash 
#主机
HOST="127.0.0.1"
#用户
USER="zabbix"
#密码
PASSWORD="zabbix127"
#端口
PORT="3306"
#MySQL连接
CONNECTION="mysqladmin -h ${HOST} -u ${USER} -P ${PORT} -p${PASSWORD}"

if [ $# -ne "1" ];then
    echo "arg error!"
fi

case $1 in
    Uptime)
        result=`${CONNECTION} status 2>/dev/null |awk '{print $2}'`
        echo $result
        ;;
    Questions)
        result=`${CONNECTION} status 2>/dev/null |awk '{print $6}'`
        echo $result
        ;;
    Com_update)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_update" |awk '{print $4}'`
        echo $result
        ;;
    Slow_queries)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Slow_queries" |awk '{print $4}'`
        echo $result
        ;;
    Com_select)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_select" |awk '{print $4}'`
        echo $result
        ;;
    Com_rollback)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_rollback" |awk '{print $4}'`
        echo $result
        ;;
    Com_insert)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_insert" |awk '{print $4}'`
        echo $result
        ;;
    Com_delete)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_delete" |awk '{print $4}'`
        echo $result
        ;;
    Com_commit)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_commit" |awk '{print $4}'`
        echo $result
        ;;
    Bytes_sent)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Bytes_sent" |awk '{print $4}'`
        echo $result
        ;;
    Bytes_received)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Bytes_received" |awk '{print $4}'`
        echo $result
        ;;
    Com_begin)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_begin" |awk '{print $4}'`
        echo $result
        ;;*)
        echo "Usage:$0(Uptime|Questions|Com_update|Slow_queries|Com_select|Com_rollback|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)"
        ;;
esac

3/修改zabbix-agent配置

# vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
#获取MySQL性能指标，这个是上一步编辑好的脚本
UserParameter=mysql.status[*],/etc/zabbix/zabbix_agentd.d/mysql_status.sh $1
#获取MySQL运行状态
UserParameter=mysql.ping,mysqladmin -u root -h 192.168.5.8 -proot ping 2>/dev/null | grep -c alive
注：可能zabbix-agent有默认mysql需要注释掉

重启zabbix-agent

4/zabbix-service 测试

[root@localhost ~]# zabbix_get -s 192.168.5.8 -k mysql.status[Uptime]
5239195
[root@localhost ~]# zabbix_get -s 192.168.5.8 -k mysql.ping
1

5/web界面配置

使用自带的 Template DB mysql 模板

二/磁盘IO监控

1/iostat命令安装

iostat属于sysstat软件包。可以直接安装。

[root@localhost ~]# yum -y install sysstat

显示磁盘设备负载

[root@HK_192_168_5_5 zabbix_agentd.d]# iostat
Linux 5.9.12-1.el7.elrepo.x86_64 (HK_192_168_5_5)      2023年05月12日  _x86_64_        (4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.04    0.00    0.07    0.02    0.00   99.87

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               0.40         0.08         3.77     507332   24547864
scd0              0.00         0.00         0.00          1          0
dm-0              0.47         0.08         3.76     493811   24524653
dm-1              0.00         0.00         0.00       2072          0

2/参数详解：

rrqm/s:   每秒进行 merge 的读操作数目。即 delta(rmerge)/s
wrqm/s:  每秒进行 merge 的写操作数目。即 delta(wmerge)/s
rps:           每秒完成的读 I/O 设备次数。即 delta(rio)/s
wps:         每秒完成的写 I/O 设备次数。即 delta(wio)/s
rsec/s:    每秒读扇区数。即 delta(rsect)/s
wsec/s:  每秒写扇区数。即 delta(wsect)/s
rkBps:      每秒读K字节数。是 rsect/s 的一半，因为每扇区大小为512字节。(需要计算)
wkBps:    每秒写K字节数。是 wsect/s 的一半。(需要计算)
avgrq-sz: 平均每次设备I/O操作的数据大小 (扇区)。delta(rsect+wsect)/delta(rio+wio)
avgqu-sz: 平均I/O队列长度。即 delta(aveq)/s/1000 (因为aveq的单位为毫秒)。
await:    平均每次设备I/O操作的等待时间 (毫秒)。即 delta(ruse+wuse)/delta(rio+wio)
svctm:   平均每次设备I/O操作的服务时间 (毫秒)。即 delta(use)/delta(rio+wio)
%util:      一秒中有百分之多少的时间用于 I/O 操作，或者说一秒中有多少时间 I/O 队列是非空的。即 delta(use)/s/1000 (因为use的单位为毫秒)

3/zabbix-agent配置

[root@HK_192_168_5_5 zabbix]# cat zabbix_agentd.conf
PidFile=/var/run/zabbix/zabbix_agentd.pid
LogFile=/var/log/zabbix/zabbix_agentd.log
LogFileSize=0
Server=192.168.5.10,192.168.5.50
ServerActive=192.168.5.10,192.168.5.50
Hostname=XGLINUX_192.168.5.5
Include=/etc/zabbix/zabbix_agentd.d/*.conf
UnsafeUserParameters=1
AllowRoot=1

4/自动发现磁盘脚本

[root@HK_192_168_5_5 zabbix_agentd.d]# cat disk_discovery.sh 
#!/bin/bash
diskarray=(`cat /proc/diskstats |grep -E "bsd[a-z]b|bxvd[a-z]b|bvd[a-z]b"|awk '{print $3}'|sort|uniq  2>/dev/null`)
length=${#diskarray[@]}
printf "{
"
printf  '	'""data":["
for ((i=0;i<$length;i++))
do
        printf '
		{'
        printf ""{#DISK_NAME}":"${diskarray[$i]}"}"
        if [ $i -lt $[$length-1] ];then
                printf ','
        fi
done
printf  "
	]
"
printf "}
"
[root@HK_192_168_5_5 zabbix_agentd.d]#

5/获取磁盘IO数据

[root@HK_192_168_5_5 zabbix_agentd.d]# cat disk_status.sh 
#/bin/sh
Device=$1
DISK=$2
case $DISK in
         rrqm)
            iostat -dxkt 1 2|grep "b$Deviceb"|tail -1|awk '{print $2}'
            ;;
         wrqm)
            iostat -dxkt 1 2|grep "b$Deviceb"|tail -1|awk '{print $3}'
            ;;
          rps)
            iostat -dxkt 1 2|grep "b$Deviceb"|tail -1|awk '{print $4}'
            ;;
          wps)
            iostat -dxkt 1 2|grep "b$Deviceb" |tail -1|awk '{print $5}'
            ;;
        rKBps)
            iostat -dxkt 1 2|grep "b$Deviceb" |tail -1|awk '{print $6}'
            ;;
        wKBps)
            iostat -dxkt 1 2|grep "b$Deviceb" |tail -1|awk '{print $7}'
            ;;
        avgrq-sz)
            iostat -dxkt 1 2|grep "b$Deviceb" |tail -1|awk '{print $8}'
            ;;
        avgqu-sz)
            iostat -dxkt 1 2|grep "b$Deviceb" |tail -1|awk '{print $9}'
            ;;
        await)
            iostat -dxkt 1 2|grep "b$Deviceb" |tail -1|awk '{print $10}'
            ;;
        svctm)
            iostat -dxkt 1 2|grep "b$Deviceb" |tail -1|awk '{print $11}'
            ;;
        rsec)
            iostat -dxkt |grep -F "sda" |tail -1|awk '{print $4}'
            ;;
        wsec)
            iostat -dxkt|grep -F "sda" |tail -1|awk '{print $5}'
            ;;
         util)
            iostat -dxkt |grep "b$Deviceb" |tail -1|awk '{print $12}'
            ;;
esac
[root@HK_192_168_5_5 zabbix_agentd.d]#

6/配置zabbix执行

[root@HK_192_168_5_5 zabbix_agentd.d]#chmod +x *.sh
[root@HK_192_168_5_5 zabbix_agentd.d]# ll disk*
-rwxr-xr-x 1 root root  420 11月  9 2022 disk_discovery.sh
-rwxr-xr-x 1 root root 1194 11月  9 2022 disk_status.sh

[root@HK_192_168_5_5 zabbix_agentd.d]#vi userparameter_mysql.conf
UserParameter=disk.discovery[*],/etc/zabbix/zabbix_agentd.d/disk_discovery.sh
UserParameter=disk.status[*],/etc/zabbix/zabbix_agentd.d/disk_status.sh $1 $2

[root@HK_192_168_5_5 zabbix_agentd.d]#service zabbix-agent restart

7/zabbix-service 测试

[root@localhost ~]# zabbix_get -s 192.168.5.5 -k disk.status[sda,wsec]
1.23

8/磁盘IO模板

链接：https://pan.baidu.com/s/15HLPmEpqVzmKS2rrncoGEw

提取码：99dr

三/监控cpu各单核情况

1/编写脚本

[root@HK_192_168_5_5 zabbix_agentd.d]# cat mpstat_cpu.sh
#/bin/sh
DISK=$1
case $DISK in
 cpu_all)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '4,4p'
    ;;
 cpu_0)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '5,5p'
    ;;
 cpu_1)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '6,6p'
    ;;
 cpu_2)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '7,7p'
    ;;
 cpu_3)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '8,8p'
    ;;
 cpu_4)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '9,9p'
    ;;
 cpu_5)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '10,10p'
    ;;
 cpu_6)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '11,11p'
    ;;
 cpu_7)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '12,12p'
    ;;
 cpu_8)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '13,13p'
    ;;
 cpu_9)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '14,14p'
    ;;
 cpu_10)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '15,15p'
    ;;
 cpu_11)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '16,16p'
    ;;
 cpu_12)
    mpstat -P ALL 1 2|awk '{print $12}'|grep -v "3.10"|sed -n '17,17p'
    ;;
 cpu_13)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '18,18p'
    ;;
 cpu_14)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '19,19p'
    ;;
 cpu_15)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '20,20p'
    ;;
 cpu_16)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '21,21p'
    ;;
 cpu_17)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '22,22p'
    ;;
 cpu_18)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '23,23p'
    ;;
 cpu_19)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '24,24p'
    ;;
 cpu_20)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '25,25p'
    ;;
 cpu_21)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '26,26p'
    ;;
 cpu_22)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '27,27p'
    ;;
 cpu_23)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '28,28p'
    ;;
 cpu_24)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '29,29p'
    ;;
 cpu_25)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '30,30p'
    ;;
 cpu_26)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '31,31p'
    ;;
 cpu_27)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '32,32p'
    ;;
 cpu_28)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '33,33p'
    ;;
 cpu_29)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '34,34p'
    ;;
 cpu_30)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '35,35p'
    ;;
 cpu_31)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '36,36p'
    ;;
 cpu_32)
    mpstat -P ALL  1 2|awk '{print $12}'|grep -v "3.10"|sed -n '37,37p'
    ;;
esac

2/配置agentd

[root@HK_192_168_5_5 zabbix_agentd.d]#chmod +x *.sh
[root@HK_192_168_5_5 zabbix_agentd.d]# ll mp*
-rwxr-xr-x 1 root root  420 11月  9 2022 mpstat_cpu.sh

[root@HK_192_168_5_5 zabbix_agentd.d]#vi userparameter_mysql.conf
UserParameter=cpu.mpstat[*],/etc/zabbix/zabbix_agentd.d/mpstat_cpu.sh $1

[root@HK_192_168_5_5 zabbix_agentd.d]#service zabbix-agent restart

3/监控图

四/监控网络数据包

1/编写脚本

[root@HK_192_168_5_5 zabbix_agentd.d]# cat network_sar.sh
#!/bin/bash
Sar=(`sar -n DEV 1 1 |grep -E "bem[1-9]|bens[1-999]"|awk '{print $2}'|sort|uniq  2>/dev/null`)
length=${#Sar[@]}
printf "{
"
printf  '	'""data":["
for ((i=0;i<$length;i++))
do
        printf '
		{'
        printf ""{#SAR_NAME}":"${Sar[$i]}"}"
        if [ $i -lt $[$length-1] ];then
                printf ','
        fi
done
printf  "
	]
"
printf "}
"

[root@HK_192_168_5_5 zabbix_agentd.d]# cat network_sar_status.sh 
#/bin/sh
NET=$1
SAR=$2
case $SAR in
         rxpck)
            sar -n DEV 1 1 |grep -E '平均时间'|grep -E "$NET"|tail -l|awk '{print $3}'
            ;;
         txpck)
            sar -n DEV 1 1 |grep -E '平均时间'|grep -E "$NET"|tail -l|awk '{print $4}'
            ;;
          rxkB)
            sar -n DEV 1 1 |grep -E '平均时间'|grep -E "$NET"|tail -l|awk '{print $5}'
            ;;
          txkB)
            sar -n DEV 1 1 |grep -E '平均时间'|grep -E "$NET"|tail -l|awk '{print $6}'
            ;;
        rxcmp)
            sar -n DEV 1 1 |grep -E '平均时间'|grep -E "$NET"|tail -l|awk '{print $7}'
            ;;
        txcmp)
            sar -n DEV 1 1 |grep -E '平均时间'|grep -E "$NET"|tail -l|awk '{print $8}'
            ;;
        rxmcst)
            sar -n DEV 1 1 |grep -E '平均时间'|grep -E "$NET"|tail -l|awk '{print $9}'
            ;;
esac

2/配置agent

[root@HK_192_168_5_5 zabbix_agentd.d]#chmod +x *.sh
[root@HK_192_168_5_5 zabbix_agentd.d]# ll net*
-rwxr-xr-x 1 root root  420 11月  9 2022 network_sar_status.sh
-rwxr-xr-x 1 root root  420 11月  9 2022 network_sar.sh

[root@HK_192_168_5_5 zabbix_agentd.d]#vi userparameter_mysql.conf
UserParameter=netswork.sar[*],/etc/zabbix/zabbix_agentd.d/network_sar.sh
UserParameter=netswork.sar.status[*],/etc/zabbix/zabbix_agentd.d/network_sar_status.sh $1 $2

[root@HK_192_168_5_5 zabbix_agentd.d]#service zabbix-agent restart