Mysql Slave Server(Replication) Status Check Script

Below script is returning the Mysql Slave server Status.

If Replication  is running then it will not show any message but if replication is stop then it will send the email notification for status.

—————————

#!/usr/bin/env bash

repeat_alert_interval=15 # minutes

lock_file=/tmp/slave_alert.lck

active=yes

 

## Check if alert is already sent ## 

 

function check_alert_lock () {

    if [ -f $lock_file ] ; then

        current_file=`find $lock_file -cmin -$repeat_alert_interval`

        if [ -n "$current_file" ] ; then

            # echo “Current lock file found”

            return 1

        else

            # echo “Expired lock file found”

            return 2 

        fi

    else

    return 0

    fi

}

 

## Find the location of the mysql.sock file ##

 

function check_for_socket () {

        if [ -z $socket ] ; then

                if [ -S /var/lib/mysql/mysql.sock ] ; then

                        socket=/var/lib/mysql/mysql.sock

                elif [ -S /tmp/mysql.sock ] ; then

                        socket=/tmp/mysql.sock

                else

                        ps_socket=`netstat -ln | egrep “mysql(d)?\.sock” | awk ‘{ print $9 }’`

                        if [ "$ps_socket" ] ; then

                        socket=$ps_socket

                        fi

                fi

        fi

        if [ -S "$socket" ] ; then

                echo UP > /dev/null

        else

                echo “No valid socket file “$socket” found!”

                echo “mysqld is not running or it is installed in a custom location”

                echo “Please set the $socket variable at the top of this script.”

                exit 1

        fi

}

 

 

check_for_socket

 

Slave_IO_Running=`mysql -u username -p’password’ -h Slavehostip -Bse “show slave status\G” | grep Slave_IO_Running | awk ‘{ print $2 }’`

Slave_SQL_Running=`mysql -u username -p’password’ -h Slavehostip -Bse “show slave status\G” | grep Slave_SQL_Running | awk ‘{ print $2 }’`

Last_error=`mysql -u username -p’password’ -h Slavehostip -Bse “show slave status\G” | grep Last_error | awk -F \: ‘{ print $2 }’`

 

 

if [ -z $Slave_IO_Running -o -z $Slave_SQL_Running ] ; then

        echo “Replication is not configured or you do not have the required access to MySQL” | mail -s “Mysql Replication Status ” username@domain.com

        exit

fi

 

if [ $Slave_IO_Running == 'Yes' ] && [ $Slave_SQL_Running == 'Yes' ] ; then 

    if [ -f $lock_file ] ; then

        rm $lock_file

        echo “Replication slave is running”

        echo “Removed Alert Lock”

    fi

    exit 0

elif [ $Slave_SQL_Running == 'No' ] ; then

    if [ $active == 'yes' ] ; then

        check_alert_lock

        if [ $? = 1 ] ; then

            ## Current Lock ##

            echo “up” > /dev/null

        else

            ## Stale/No Lock ##

             touch $lock_file

            echo “SQL thread not running on server `hostname -s`!” | mail -s “This is subject” mail_ID@somedomain.com

            echo “Last Error:” $Last_error

        fi

    fi

    exit 1

elif [ $Slave_IO_Running == 'No' ] ; then

        if [ $active == 'yes' ] ; then

                check_alert_lock

                if [ $? = 1 ] ; then

                        ## Current Lock ##

            echo “up” > /dev/null

                else

                        ## Stale/No Lock ##

                        touch $lock_file

                        echo “LOG IO thread not running on server `hostname -s`!”

                        echo “Last Error:” $Last_error

                fi

    fi

    exit 1

else 

        if [ $active == 'yes' ] ; then

                check_alert_lock

                if [ $? = 1 ] ; then

                        ## Current Lock ##

            echo “up” > /dev/null

                else

                        ## Stale/No Lock ##

                        touch $lock_file

            echo “Unexpected Error!”

            echo “Check Your permissions!”

                fi

        fi

    exit 2

fi