Zabbix实战-简易教程--DB类--Mysql多实例

作者：@skyflask
转载本文请注明出处：https://www.cnblogs.com/skyflask/articles/7623638.html

目录
一、获取LLD监控类（多实例端口）
二、测试和自定义key
三、制作模板
四、主机上套
五、效果图
六、参考文献和文件索引

一、获取LLD监控类（多实例端口）

通过脚本获取mysql多实例端口：

#!/bin/bash
#Fucation: mysql low-level discovery
#Script_name: monitor-mysql.sh
mysql() {
    port1=(`sudo /bin/netstat -tpln | awk -F "[ : ]+" 'BEGIN {IGNORECASE=1; } /mysql/ && /0.0.0.0/ {print $5}'`)
    port2=(`sudo /bin/netstat -tpln | awk -F "[ : ]+" 'BEGIN {IGNORECASE=1; } /mysql/ {print $4}'`)
    if [ ! -n "${port1}"  ];then
         port=$port2
    else
         port=$port1
    fi
    max_index=$[${#port[@]}-1]
    printf '{\n'
    printf '\t"data":['
    for key in `seq -s' ' 0 $max_index`
    do
        printf '\n\t\t{'
        printf "\"{#MYSQLPORT}\":\"${port[${key}]}\"}"
        if [ $key -ne $max_index ];then
            printf ","
        fi
    done
    printf '\n\t]\n'
    printf '}\n'
}
$1

运行结果如下：

脚本已经上传至github：https://github.com/loveqx/zabbix-doc/tree/master/zabbix-scripts/zabbix-template-mysql

二、测试和自定义key

我们从第一步中获取多实例端口后，就可以通过端口获取具体的item了。

1、获取mysql状态（mysql性能数据，通过mysqldump）

#!/bin/bash
#Fucation: mysql low-level discovery
#Script_name:monitor-multi-mysql.sh
#judge one or multi
 
one=`ps xua|grep mysqld|grep -w "port=3306"|wc -l`
if [ $one -lt 1 ];then
 
    socket=`ps xua|grep -w "socket"|grep -v "mysqld_safe"|awk 'BEGIN {FS="--socket="} {print $2}'|awk '{print $1}'|grep sock|head -n 1`
    mysqladmin -r ext -uzabbix -psoft+5688 -P 3306 -S $socket 2>/dev/null|grep -w "$2"|awk '{print $4}'
 
else
 
    socket=`ps xua|grep -w "socket"|grep -v "mysqld_safe"|grep -w "port=$1"|awk 'BEGIN {FS="--socket="} {print $2}'|awk '{print $1}'`
    mysqladmin -r ext -uzabbix -psoft+5688 -P $1 -S $socket 2>/dev/null|grep -w "$2"|awk '{print $4}'
fi

部分取值结果：

2、获取mysql主从复制状态

#!/bin/bash
#Fucation: monitor mysql slave status
#Script_name:monitor-slave-status.sh
one=`ps xua|grep mysqld|grep -w "port=3306"|wc -l`
 
if [ $one -lt 1  ];then
 
    socket=`ps xua|grep -w "socket"|grep -v "mysqld_safe"|awk 'BEGIN {FS="--socket="} {print $2}'|awk '{print $1}'|grep  sock|head -n 1`
     mysql -uzabbix -psoft+5688  -P 3306 -S $socket -e "show slave status\G" 2>/dev/null|grep -w "$2"|awk '{print $2}'
else
    socket=`ps xua|grep -w "socket"|grep -v "mysqld_safe"|grep -w "port=$1"|awk 'BEGIN {FS="--socket="} {print $2}'|awk '{print $1}'`
    #socket=`ps xua|grep -w "socket"|grep $1|awk 'BEGIN {FS="--socket="} {print $2}'`
    mysql -uzabbix -psoft+5688  -P $1 -S $socket -e "show slave status\G" 2>/dev/null|grep -w "$2"|awk '{print $2}'
fi

部分运行结果如下：

3、mysql存活状态

#!/bin/bash
#Fucation: monitor mysql slave status
#Script_name:monitor-slave-status.sh
one=`ps xua|grep mysqld|grep -w "port=3306"|wc -l`
 
if [ $one -lt 1   ];then
 
    socket=`ps xua|grep -w "socket"|grep -v "mysqld_safe"|awk 'BEGIN {FS="--socket="} {print $2}'|awk '{print $1}'|grep sock|head -n 1`
     mysqladmin -h localhost -P 3306 -S $socket -uzabbix -p'soft+5688' ping 2>/dev/null| grep -c alive
 
else
    socket=`ps xua|grep -w "socket"|grep -v "mysqld_safe"|grep -w "port=$1"|awk 'BEGIN {FS="--socket="} {print $2}'|awk '{print $1}'`
    mysqladmin -h localhost -P $1 -S $socket -uzabbix -p'soft+5688' ping 2>/dev/null| grep -c alive
fi

运行结果如下：

4、自定义key

#discovery mysql
UserParameter=mysql_port_discovery[*], /bin/bash /etc/zabbix/externalscripts/mysql_port_discovery.sh $1
UserParameter=mysql_status[*], /bin/bash /etc/zabbix/externalscripts/monitor-multi-mysql.sh $1 $2
UserParameter=mysql_slave_status[*], /bin/bash /etc/zabbix/externalscripts/monitor-slave-status.sh $1 $2
UserParameter=mysql_ping[*], /bin/bash  /etc/zabbix/externalscripts/monitor-mysql-alive.sh $1