Zabbix 监控 NySQL

wsjz_01

Zabbix监控MySQL
　　首先，大家如果看到有什么不懂的地方，欢迎吐槽！！！  

我会在当天或者第二天及时回复，并且改进~~
　　Zabbix 3.0中已经有Mysql监控模板了，所以我们只要引用ZabbixServer自带的模版即可。
　　一、在Zabbix_Agentd中添加Include目录，这里面存放Zabbix的Item监控项-Key.

vim /usr/local/zabbix/etc/zabbix_agentd.conf


Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/

　　二、创建监控Mysql的Key

该Key的目录为上面Include指定的路径

• check_mysql_performance_key
  

监控Mysql性能
vim /usr/local/zabbix/etc/zabbix_agentd.conf.d/check_mysql_performance_key
UserParameter=mysql.version,/usr/local/mysql/bin/mysql -V
UserParameter=mysql.ping,/usr/local/mysql/bin/mysqladmin -uzabbix -pzabbix -S /usr/local/mysql/data/mysql.sock ping 2> /dev/null | grep -c alive
UserParameter=mysql.status

,/usr/local/zabbix/shell/check_mysql_performance.sh $1 $2

• check_mysql_slave_key
  

监控Mysql主从是否同步
vim /usr/local/zabbix/etc/zabbix_agentd.conf.d/check_mysql_slave_key
UserParameter=mysql.slave

,/usr/local/zabbix/shell/check_mysql_slave.sh $1 $2
　　三、创建监控Mysql的脚本

其实在上面Key步骤中已经知名了Mysql监控脚本名称及路径，脚本记得加权限哦~~


脚本内容贴在文章尾部，这里内容繁多，所以不再赘述，下面是脚本路径
/usr/local/zabbix/shell/check_mysql_performance.sh
/usr/local/zabbix/shell/check_mysql_slave.sh
　　四、启动Zabbix_Agentd


/usr/local/zabbix/sbin/zabbix_agentd
　　五、Zabbix_Agentd脚本及监控item已经设置好，下面我们在Zabbix_Web界面为需要监控的主机设置模板

点击-Configuration-Hosts


　　选择需要监控的主机，我们来链接Mysql的监控模板

在Templates页面中选择Select，然后添加Templates App MySQL模板




接着点击主页中的Screen，创建一个Mysql的Screen
创建一个Mysql_Monitoring的screen，然后添加两个图像
Mysql_bandwidth
Mysql_operations
　　看到这个图片，OK，你的Mysql监控就此完成啦~~



　　











　　下面是Mysql性能监控脚本


#!/bin/sh
#Create by zijin 2014.09.28
##Need "grant select on mysql.* to zabbix@localhost identified by 'zabbix_agent'"
MYSQL_SOCK="/tmp/mysql.sock"
MYSQL_PWD="zabbix"
MYSQL_ADMIN="/usr/local/mysql/bin/mysqladmin"
ARGS=1
if [ $# -ne "$ARGS" ];then
echo "Please input one arguement:"
fi
case $1 in
Uptime)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK status 2> /dev/null |cut -f2 -d":"|cut -f1 -d"T"`
echo $result
;;
Com_update)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status 2> /dev/null |grep -w "Com_update"|cut -d"|" -f3`
echo $result
;;
Slow_queries)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK status 2> /dev/null |cut -f5 -d":"|cut -f1 -d"O"`
echo $result
;;
Com_select)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status 2> /dev/null |grep -w "Com_select"|cut -d"|" -f3`
echo $result
;;
Com_rollback)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status 2> /dev/null |grep -w "Com_rollback"|cut -d"|" -f3`
echo $result
;;
Questions)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK status 2> /dev/null |cut -f4 -d":"|cut -f1 -d"S"`
echo $result
;;
Com_insert)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status 2> /dev/null |grep -w "Com_insert"|cut -d"|" -f3`
echo $result
;;
Com_delete)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status 2> /dev/null |grep -w "Com_delete"|cut -d"|" -f3`
echo $result
;;
Com_commit)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status 2> /dev/null |grep -w "Com_commit"|cut -d"|" -f3`
echo $result
;;
Bytes_sent)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status 2> /dev/null |grep -w "Bytes_sent" |cut -d"|" -f3`
echo $result
;;
Bytes_received)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status 2> /dev/null |grep -w "Bytes_received" |cut -d"|" -f3`
echo $result
;;
Com_begin)
result=`${MYSQL_ADMIN} -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status 2> /dev/null |grep -w "Com_begin"|cut -d"|" -f3`
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
　　Mysql_Slave监控脚本


#!/bin/bash
MYSQL_SOCK="/usr/local/mysql/data/mysql.sock"
MYSQL_PWD="zabbix"
MYSQL="/usr/local/mysql/bin/mysql"
MYSQL_ADMIN="/usr/local/mysql/bin/mysqladmin"
ARGS=1
if [ $# -ne "$ARGS" ];then
echo "Please input one arguement:"
fi
case $1 in
Slave_Running)
slave_is=($($MYSQL -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK -e "show slave status\G"|egrep "\bSlave_.*_Running\b"|awk '{print $2}'))
if [ "${slave_is[0]}" = "Yes" -a "${slave_is[1]}" = "Yes" ];then
result="1"
else
result="0"
fi
echo $result
;;
Slave_Seconds)
result=$($MYSQL -uzabbix -p${MYSQL_PWD} -S $MYSQL_SOCK -e "show slave status\G"|egrep "\bSeconds_Behind_Master\b"|awk '{print $2}')
echo $result
;;
*)
echo "Usage:$0(Slave_Running|Slave_Seconds)"
;;
esac