mysql 碎片率_Mysql查询缓存碎片、缓存命中率及Nagios监控

#!/bin/bash

#function:查询缓存命中率

#time:20121130

#author:system group

while getopts ":w:c:h" optname

do

case "$optname" in

"w")

WARN=$OPTARG

;;

"c")

CIRT=$OPTARG

;;

"h")

echo "Useage: check_mysql_qch.sh -w warn -c cirt"

exit

;;

"?")

echo "Unknown option $OPTARG"

exit

;;

":")

echo "No argument value for option $OPTARG"

exit

;;

*)

# Should not occur

echo "Unknown error while processing options"

exit

;;

esac

done

[ $? -ne 0 ] && echo "error: Unknown option " && exit

[ -z $WARN ] && WARN=60

[ -z $CIRT ] && CIRT=50

export selete=`/usr/local/mysql/bin/mysql -h 127.0.0.1 -uroot -Bse "SHOW GLOBAL STATUS LIKE 'Com_select';" |awk '{print $2}'`

export hits=`/usr/local/mysql/bin/mysql -h 127.0.0.1 -uroot -Bse "SHOW GLOBAL STATUS LIKE 'Qcache_hits';" |awk '{print $2}'`

a=$(($selete+$hits))

#rw_ratio=$(($a/$b))

#echo "rw_ratio=$rw_ratio"

#ratio=$(($rw_ratio*100))

#echo "ratio=$ratio"

if [ $a -ne "0" ];then

percent=`awk 'BEGIN{printf "%.2f%\n",('$hits'/'$a')*100}'`

Qch=`awk 'BEGIN{printf ('$hits'/'$a')*100}'`

fi

C=`echo "$Qch < $CIRT" | bc`

W=`echo "$Qch < $WARN" | bc`

O=`echo "$Qch > $WARN" | bc`

if [ $C == 1 ];then

echo -e "CIRT - Mysql Qcache Hits is $percent,Com_select is $selete,Qcache_hits is $hits|Qcache_hits=${Qch}%;${selete};${hits};0"

exit 2

fi

if [ $W == 1 ];then

echo -e "WARN - Mysql Qcache Hits is $percent,Com_select is $selete,Qcache_hits is $hits|Qcache_hits=${Qch}%;${selete};${hits};0"

exit 1

fi

if [ $O == 1 ];then

echo -e "OK - Mysql Qcache Hits is $percent,Com_select is $selete,Qcache_hits is $hits|Qcache_hits=${Qch}%;${selete};${hits};0"

exit 0

fi