check_mysql_status

#!/bin/bash 

###############################################

# Used for eagleye to monitor all instance's status; 

# instance list read from 'INSFILE'

# if 'INSFILE' not exists . Get all instance list from /etc/my*.cnf

#

#

# Created : yunfei.heyf @ 2011-04-26 22:00

# Modified:

#

######################################

#### Variables define 

[ -z /root/.bash_profile ] && . /root/.bash_profile

export PATH="/usr/ali/bin:/usr/ali/sbin:/usr/local/bin:/usr/local/sbin:/home/admin/bin:/home/admin/sbin:/usr/ali/java/bin:/usr/kerberos/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/opt/dell/srvadmin/bin"

my_host=127.0.0.1

my_user=eagleye

my_pass="902D9BEC4D5C1633EF"

my_portlist=""

my_bin=/usr/bin/mysql

my_admin=/usr/bin/mysqladmin

INSFILE=/usr/local/dbaas/config/instance.conf

if [ "X${my_pass}" != "X" ] ; then 

        my_pass="-p${my_pass}"

fi

#### Functions 

usage()

{

cat <<EOF

Usage: $0 [OPTIONS]

        slave_io_running 

        slave_sql_running

        slave_lag           

        thread_connected

        aborted_connections

        alive

        help                    Show this messages

EOF

exit 1

}

get_portlist()

{

local portlist=""

local insfile=$1

if [ -n ${insfile} -a -f ${insfile} ] ; then

        #### Read port list from "insfile"

        for port in $(grep ^3 ${INSFILE} | awk -F '=' '{print $1}')

        do

                portlist="${portlist} "${port}

        done

else 

        #### Read post list from /etc/myX.cnf

        for port in $(/bin/ls /etc/my*.cnf 2>/dev/null | awk -F "." '{print $1}' | awk -F 'my' '{print $NF}')

        do

                portlist="${portlist} "${port}

        done

fi

portlist=$(echo $portlist | sed -e 's/^ *//' -e 's/ *$//' )

if [ 'x${portlist}' = 'x' ] ; then

        if [ -f /etc/my.cnf ]; then 

                portlist='3306'

        fi

fi

echo $portlist 

return 0

}

chk_alive()

{

        local portlist=$1

        local errlist=''

        if [ -z "$portlist" ] ; then

                echo "ok-No instance."

                return 1 

        fi

        for my_port in $portlist 

        do

                res=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show processlist;" 2>/dev/null | wc -l )

                if [ $res -eq 0 ]; then

                        ## No slave configed or Instance not running. Pass 

                        errlist=${errlist},${my_port}

                fi

        done

        errlist=$(echo $errlist | sed 's/^,//')

        if [ -z "$errlist" ] ;then

                echo "ok-All instance alive." 

        else

                echo "critical-instance Not alive:$errlist"

        fi

        return 0

}

chk_thread_connected()

{

        local portlist=$1

        local errlist=''

        local warnpara=8000

        if [ -z "$portlist" ] ; then

                echo "ok-No instance."

                return 1 

        fi

        for my_port in $portlist 

        do

                res=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show processlist;" 2>/dev/null | wc -l )

                if [ -z "$res" ]; then

                        ## No slave configed or Instance not running. Pass 

                        :

                elif [ $res -gt $warnpara ]; then 

                        errlist=${errlist},${my_port}=${res}

                fi

        done

        errlist=$(echo $errlist | sed 's/^,//')

        if [ -z "$errlist" ] ;then

                echo "ok-All instance's thread count <= $warnpara." 

        else

                echo "critical-threads connect to ins:$errlist"

        fi

        return 0

}

chk_slavelag()

{

        local portlist=$1

        local errlist=''

        local warnpara=3600

        if [ -z "$portlist" ] ; then

                echo "ok-No instance."

                return 1 

        fi

        for my_port in $portlist 

        do

                res=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show slave status \G" 2>/dev/null | grep Seconds_Behind_Master | awk -F ':' '{print $NF}' | tr -d ' ')

                if [ -z "$res" ]; then

                        ## No slave configed or Instance not running. Pass 

                        :

                elif [ "$res" = "NULL" ]; then

                        ## May be sqlthread is not running.

                        errlist=${errlist},${my_port}=${res}

                elif [ $res -gt $warnpara ]; then

                        sleep 2

                ##########we will check the slave lag again  ########

                ##########add by xuancan@2013-01-28##################

                res=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show slave status \G" 2>/dev/null | grep Seconds_Behind_Master | awk -F ':' '{print $NF}' | tr -d ' ')

                if [ $res -gt $warnpara ]; then         

                        errlist=${errlist},${my_port}=${res}

                fi

                fi

        done

        errlist=$(echo $errlist | sed 's/^,//')

        if [ -z "$errlist" ] ;then

                echo "ok-All instance's slavelag <= $warnpara." 

        else

                echo "critical-slavelag on ins:$errlist"

        fi

        return 0

}

chk_slavesql()

{

        local portlist=$1

        local errlist=''

        if [ -z "$portlist" ] ; then

                echo "ok-No instance."

                return 1 

        fi

        for my_port in $portlist 

        do

                res=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show slave status \G" 2>/dev/null | grep Slave_SQL_Running | awk -F ':' '{print $NF}' | tr -d ' ')

                if [ -z "$res" -o "$res" = "Yes" ]; then

                        ## No slave configed or thread is running . status is OK

                        :

                else 

                        errlist=$errlist,$my_port

                fi

        done

        errlist=$(echo $errlist | sed 's/^,//')

        if [ -z "$errlist" ] ;then

                echo "ok-All instance's slavesql running." 

        else

                echo "critical-slavesql not run on ins:$errlist"

        fi

        return 0

}

chk_mysql_aborted_conn()

{

        local portlist=$1

        local errlist=''

        local warnpara=10

        if [ -z "$portlist" ] ; then

                echo "ok-No instance."

                return 1

        fi

        for my_port in $portlist

        do

                abort1=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show global status like 'Aborted_connects'" 2>/dev/null | awk -F ' ' '{print $2}' )

            sleep 1

                abort2=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show global status like 'Aborted_connects'" 2>/dev/null | awk -F ' ' '{print $2}'  )  

                abort=$(($abort2-$abort1))

############if the abort>10,then we check again#############             

            if [ $abort -gt 10 ]; then

                 abort3=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show global status like 'Aborted_connects'" 2>/dev/null | awk -F ' ' '{print $2}' )

            sleep 1

                 abort4=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show global status like 'Aborted_connects'" 2>/dev/null | awk -F ' ' '{print $2}'  )     

                abort=$(($abort4-$abort3))

                if [ $abort -gt 10 ]; then

                        errlist=${errlist},${my_port}=${abort}

                fi

            fi

############end check################################################

        done

        errlist=$(echo $errlist | sed 's/^,//')

        if [ -z "$errlist" ] ;then

                echo "ok-All instance's aborted connections<= 10."

        else

                echo "critical-aborted connections:$errlist"

        fi

        return 0

}

chk_slaveio()

{

        local portlist=$1

        local errlist=''

        if [ -z "$portlist" ] ; then

                echo "ok-No instance."

                return 1 

        fi

        for my_port in $portlist 

        do

                res=$(${my_bin} -u${my_user} -h${my_host} ${my_pass} -P${my_port} -s -e "show slave status \G" 2>/dev/null | grep Slave_IO_Running | awk -F ':' '{print $NF}' | tr -d ' ')

                if [ -z "$res" -o "$res" = "Yes" ]; then

                        ## No slave configed or thread is running . status is OK

                        :

                else 

                        errlist=$errlist,$my_port

                fi

        done

        errlist=$(echo $errlist | sed 's/^,//')

        if [ -z "$errlist" ] ;then

                echo "ok-All instance's slaveio running." 

        else

                echo "critical-slaveio not run on ins:$errlist"

        fi

        return 0

}

#### Main()

my_portlist=$(get_portlist $INSFILE)

case "$1" in

        slave_io_running)

                chk_slaveio "$my_portlist"

        ;;

        slave_sql_running)

                chk_slavesql "$my_portlist"

        ;;

        slave_lag)

                chk_slavelag "$my_portlist"

        ;;

        thread_connected)

                chk_thread_connected "$my_portlist"

        ;;

        aborted_connections)

                chk_mysql_aborted_conn "$my_portlist"

        ;;

        alive)

                chk_alive "$my_portlist"

        ;;

        *)

                usage

esac

exit 0