Zabbix自动添加Mysql多实例监控

tiyan

　　1、定义每个实例的端口
cat mysql_port
3306
3307
3308
3309
3310
3311
3312
3313

　　2、给所有实例创建一个zabbix用户只允许本地访问，生产环境具体权限各位看官自己看着给吧O(∩_∩)O

GRANT ALL PRIVILEGES ON *.* TO zabbix@'127.0.0.1' IDENTIFIED BY 'zabbix';

　　3、创建自动发现MySQL端口脚本
　　cat /data/PRG/zabbix/scripts/discovery_mysql.sh

#!/bin/bash
# -------------------------------------------------------------------------------
# Script_name: discovery_mysql.sh
# Revision:    1.0
# Date:        2017/03/09
# Author:      kbsonlong
# Email:       kbsonlong@gamil.com
# Website:     www.along.party
# -------------------------------------------------------------------------------
res=`cat /data/PRG/zabbix/scripts/mysql_port|grep -v "^#"`
port=($res)
printf '{\n'
printf '\t"data":[\n'
for key in ${!port[@]}
do
if [[ "${#port[@]}" -gt 1 && "${key}" -ne "$((${#port[@]}-1))" ]];then
printf '\t {\n'
printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"},\n"
else [[ "${key}" -eq "((${#port[@]}-1))" ]]
printf '\t {\n'
printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"}\n"
fi
done
printf '\t ]\n'
printf '}\n'

　　4、创建MySQL检测脚本

# cat /data/PRG/zabbix/scripts/check_Multimysql.sh
#!/bin/bash
# -------------------------------------------------------------------------------
# FileName:    check_Multimysql.sh
# Revision:    1.0
# Date:        2017/03/09
# Author:      kbsonlong
# Email:       kbsonlong@gamil.com
# Website:     www.along.party
# License:     GPL
# -------------------------------------------------------------------------------
# 用户名
MYSQL_USER='zabbix'
# 密码
MYSQL_PWD='zabbix'
# 主机地址/IP
MYSQL_HOST='127.0.0.1'
# 端口
MYSQL_PORT=$2
# 数据连接
MYSQL_CONN="/usr/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"

##help函数
help() {
echo "Usage:$0  [ping|Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin]  port"
}
# 参数是否正确
if [ $# -lt "2" ];then
echo "参数缺失!"
help
exit 2
fi
# 获取数据
case $1 in
ping)
result=`${MYSQL_CONN} ping | grep -c alive`
echo $result
;;
Uptime)
result=`${MYSQL_CONN} status|cut -f2 -d":"|cut -f1 -d"T"`
echo $result
;;
Com_update)
result=`${MYSQL_CONN} extended-status |grep -w "Com_update"|cut -d"|" -f3`
echo $result
;;
Slow_queries)
result=`${MYSQL_CONN} status |cut -f5 -d":"|cut -f1 -d"O"`
echo $result
;;
Com_select)
result=`${MYSQL_CONN} extended-status |grep -w "Com_select"|cut -d"|" -f3`
echo $result
;;
Com_rollback)
result=`${MYSQL_CONN} extended-status |grep -w "Com_rollback"|cut -d"|" -f3`
echo $result
;;
Questions)
result=`${MYSQL_CONN} status|cut -f4 -d":"|cut -f1 -d"S"`
echo $result
;;
Com_insert)
result=`${MYSQL_CONN} extended-status |grep -w "Com_insert"|cut -d"|" -f3`
echo $result
;;
Com_delete)
result=`${MYSQL_CONN} extended-status |grep -w "Com_delete"|cut -d"|" -f3`
echo $result
;;
Com_commit)
result=`${MYSQL_CONN} extended-status |grep -w "Com_commit"|cut -d"|" -f3`
echo $result
;;
Bytes_sent)
result=`${MYSQL_CONN} extended-status |grep -w "Bytes_sent" |cut -d"|" -f3`
echo $result
;;
Bytes_received)
result=`${MYSQL_CONN} extended-status |grep -w "Bytes_received" |cut -d"|" -f3`
echo $result
;;
Com_begin)
result=`${MYSQL_CONN} extended-status |grep -w "Com_begin"|cut -d"|" -f3`
echo $result
;;
*)
help
;;
esac

　　5、测试脚本是否正常

# bash discovery_mysql.sh
{
"data":[
{
"{#MYSQLPORT}":"3306"},
{
"{#MYSQLPORT}":"3307"},
{
"{#MYSQLPORT}":"3308"},
{
"{#MYSQLPORT}":"3309"},
{
"{#MYSQLPORT}":"3310"},
{
"{#MYSQLPORT}":"3311"},
{
"{#MYSQLPORT}":"3312"},
{
"{#MYSQLPORT}":"3313"}
]
}

# bash check_Multimysql.sh
参数缺失!
Usage:check_Multimysql.sh  [ping|Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin]  port
# bash check_Multimysql.sh ping 3306
1
# bash check_Multimysql.sh Uptime 3306
18180446

　　6、zabbix agent配置文件添加自定义脚本，并重启zabbix agent

vim /data/PRG/zabbix/etc/zabbix_agentd.conf
UserParameter=mysql_discovery

,/bin/bash /data/PRG/zabbix/scripts/discovery_mysql.sh
#####$1==command   $2===port
UserParameter=mysql.status

,/data/PRG/zabbix/scripts/check_mysql.sh $1 $2
UserParameter=mysql.ping

,/data/PRG/zabbix/scripts/check_mysql.sh ping $1

　　/etc/init.d/zabbix_agentd restart
　　7、使用zabbix_get检查定义的key是否生效

# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.ping[3307]   
1
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Uptime,3307]
18180813
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Uptime,3306]
18180842
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Uptime,3310]
18180847
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Bytes_sent,3310]
58803854473

　　8、导入模板Template Multi Mysql.xml
　　9、被监控主机关联模板

　　自动发现规则mysql_discovery

　　自动添加的监控项

　　最新监控数据