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