MySQL监控脚本

#/bin/bash

# * * * * * sh /tmp/check_kpi_local.sh > /dev/null 2>&1

LOGFILE=/tmp/KPI_$(date +%F).log
CURRENT=$(date "+%T %F")
HOST=`/sbin/ifconfig | grep "inet addr" | awk -F: '{print $2}' | awk {'print $1'} | head -1`


getstat_ioutil()
{
   # Get status of  ioutil.
  local _tmpfile=/tmp/stat_$$.tmp
  local IOSTAT
  local _cnttime=4
  local DISKS

  iostat -x -k 1 $_cnttime > $_tmpfile

  local diskcnt=$(echo $(cat $_tmpfile | wc -l),${_cnttime} | awk -F "," '{total=($1-2)/$2+2}{printf "%d",total}')
  sed -i 1,${diskcnt}'d' $_tmpfile

  MAXAVG=0

  for DISKS in $(grep -e "^sd" -e "^cc" -e "^hd" $_tmpfile | awk '{print $1}' | sort -u)
  do
    MAXAVG_TMP=$(grep "${DISKS} "  $_tmpfile | awk '{ if( $NF != "0.00" ) {total+=$NF;cnt+=1;printf "%f\n", total/cnt}}' | tail -1)
    MAXAVG=$(echo $MAXAVG $MAXAVG_TMP | awk '{if ($2 > $1) {print $2} else {print $1} }' )
  done

  _stat=$MAXAVG
  _var=ioutil
  _result=${_result}${_var}"="${_stat}"&"

  echo $(echo $_result | sed -e 's/\&$//' )

  \rm -f $_tmpfile
  return 0

}

getstat_iorwkb()
{
   # Get status of  io rKB/s wKB/s .
  local _tmpfile=/tmp/stat_$$.tmp
  local IOSTAT
  local _cnttime=4
  local DISKS

  if [ $? -ne 0 ] ;  then
         echo "iostat error "
         exit 1
  fi

  iostat -k 1 $_cnttime > $_tmpfile

  local diskcnt=$(echo $(cat $_tmpfile | wc -l),${_cnttime} | awk -F "," '{total=($1-2)/$2+2}{printf "%d",total}')
  sed -i 1,${diskcnt}'d' $_tmpfile

  RKB_SUM=0
  WKB_SUM=0

  for DISKS in $(grep -e "^sd" -e "^cc" -e "^hd" $_tmpfile | awk '{print $1}' | sort -u)
  do
    RKB_TMP=$(grep "${DISKS} "  $_tmpfile | awk '{ if( $3 != "0.00" ) {total+=$3;cnt+=1;printf "%f\n", total/cnt}}' | tail -1)
    if [ "$RKB_TMP" != "" ] ; then
      RKB_SUM=$(echo $RKB_SUM $RKB_TMP | awk '{printf "%f\n" , $1+$2/2}' )
    fi
    WKB_TMP=$(grep "${DISKS} "  $_tmpfile | awk '{ if( $4 != "0.00" ) {total+=$4;cnt+=1;printf "%f\n", total/cnt}}' | tail -1)
    if [ "$WKB_TMP" != "" ] ; then
      WKB_SUM=$(echo $WKB_SUM $WKB_TMP | awk '{printf "%f\n" , $1+$2/2}' )
    fi
  done
 
   _stat=$RKB_SUM
  _var=rkb
  _result=${_result}${_var}"="${_stat}"&"

  _stat=$WKB_SUM
  _var=wkb
  _result=${_result}${_var}"="${_stat}"&"

  echo $(echo $_result | sed -e 's/\&$//' )

  \rm -f $_tmpfile
  return 0

}


CPUIDLE=$(top -bn2   | grep "^Cpu" |tail -1 | awk -F "%" '{print $4}' | awk -F ","  '{print $NF}' )
IOUTIL=$(iostat -x 1 2  | grep "%util" -A 1 | tail -1 | awk '{print $NF}')
LOAD=$(w |  head -1 | awk -F ":" '{print $NF}')

QUESTION1=$(. /root/.bash_profile; mysqladmin -u root -h127.0.0.1  status  |grep Uptime | awk  '{print $6}')
sleep 5
QUESTION2=$(. /root/.bash_profile; mysqladmin -u root -h127.0.0.1  status  |grep Uptime | awk  '{print $6}')
QPS=$(echo $QUESTION2  $QUESTION1 5 | awk '{total=($1-$2)/5};{printf "%d",total}')

IOUTIL=$(getstat_ioutil)
IOUTIL=$(echo $IOUTIL | awk -F '=' '{print $NF}')
IORWKB=$(getstat_iorwkb)
IOREAD_KBPS=$(echo $IORWKB | awk -F '&' '{print $1}' | awk -F '=' '{print $NF }' )
IOWRN_KBPS=$(echo $IORWKB | awk -F '&' '{print $2}' | awk -F '=' '{print $NF }' )
NETFLAG=$(/sbin/ifconfig |head -1 | awk  '{print $1}' )
NETINFO=$( sar -n DEV  1 5  | grep Average | grep $NETFLAG )
NET_RX_BPS=$(echo $NETINFO | awk '{printf "%d", $5/1024}'   )
NET_TX_BPS=$(echo $NETINFO | awk '{printf "%d", $6/1024}'   )


echo "($HOST -- $CURRENT) LOAD = $LOAD" >> $LOGFILE 
echo "($HOST -- $CURRENT) QPS = $QPS" >> $LOGFILE 
echo "($HOST -- $CURRENT) CPUIDLE = $CPUIDLE" >> $LOGFILE 
echo "($HOST -- $CURRENT) IOUTIL = $IOUTIL %" >> $LOGFILE 
echo "($HOST -- $CURRENT) IOREAD_KBPS = $IOREAD_KBPS"  >> $LOGFILE 
echo "($HOST -- $CURRENT) IOWRN_KBPS = $IOWRN_KBPS"  >> $LOGFILE 
echo "($HOST -- $CURRENT) NET_RX_KBPS = $NET_RX_BPS"  >> $LOGFILE 
echo "($HOST -- $CURRENT) NET_TX_KBPS = $NET_TX_BPS"  >> $LOGFILE 

exit 0