MySQL同步自动监控脚本

syxian

#!/usr/bin/env bash
#Check MySQL Replication Slave Status

#This script produces no output when replication slave is running normally.
#If either IO or SQL threads are not running it will print a message indicating so
#and print the contentsof the "Last_error:"

#I typically set it up on a 2minute cron. It has some logic in place to avoid repeat
#e-mailing from CRON when down status is found. It will wait 15 minutes before producing
#the next message. You can also disable the scripts checking by changing the "active"
#variable to "no". This allows users without direct access to cron to controll the script.
#This script assumes that a ~/.my.cnf will be in place for authentication.

## Check if alert is already sent ## 
#  $repeat_alert_interval, it is used in the function check_alert_lock()
#
repeat_alert_interval=10 # minutes
lock_file=/tmp/slave_alert.lck
active=yes
dbuser=root
dbpassword=123456

## Sometimes I can't get the $MYSQLIP, so I assign the system $PATH here and it works well.
PATH="/usr/java/jdk1.6.0_03/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin"
echo "     "
echo "========================================================================================"
echo $(date +"%y-%m-%d %H:%M:%S")
MYSQLIP=`ifconfig eth0|awk '/inet/{print $2}'|cut -c 6-`
EMAIL_TITLE="Mysql slave server auto_monitor alert"

## send_alert_sms() Description ##
#  control the alart time, via judging the create time of the custom-built file:lock_file or deleting the already exists file;
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
}

## send_alert_sms() Description ##
#  parameter1 '$1' is the send type: "SMS" means send sms, "mail" means send mail.
#  parameter2 '$2' is the send content:"sms_content" or "mail_content"
function send_alert_sms () {
if [ $1 = "SMS" ] ; then
## Send alert sms ##
## there has a job thread that is used to sense whether there has new sms to send.
/usr/local/mysql/bin/mysql -u $dbuser -p$dbpassword -Bse "INSERT INTO sms(Content,Receiver, CreateDttm)VALUES('Alert $2',152XXXXXXXX, NOW());"
else
## Send alert mail ##
## using the phpmailer class to send mail
/usr/local/php/bin/php /var/local/phpmailer/send.php "$EMAIL_TITLE" "$2"
fi
echo "send_alert_sms"
}

## 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."
sms_content="$MYSQLIP:No valid socket file $socket found!"
send_alert_sms "SMS" "$sms_content"
exit 1
fi
}

check_for_socket

Slave_IO_Running=`/usr/local/mysql/bin/mysql -u $dbuser -p$dbpassword -Bse "show slave status\G" | grep Slave_IO_Running | awk '{ print $2 }'`
Slave_SQL_Running=`/usr/local/mysql/bin/mysql -u $dbuser -p$dbpassword -Bse "show slave status\G" | grep Slave_SQL_Running | awk '{ print $2 }'`
Last_error=`/usr/local/mysql/bin/mysql -u $dbuser -p$dbpassword -Bse "show slave status\G" | grep Last_error | awk -F \: '{ print $2 }'`
/usr/local/mysql/bin/mysql -u $dbuser -p$dbpassword -Bse "show slave status\G"

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"
sms_content="In the server $MYSQLIP, replication is not configured or you do not have the required access to MySQL."
send_alert_sms "SMS" "$sms_content"
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 $MYSQLIP!"
echo $Last_error
## here we can know the error that causes the sql thread stop.
sms_content="SQL thread not running on server $MYSQLIP! The sql error:$Last_error."
send_alert_sms "SMS" "$sms_content"
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 $MYSQLIP!"
mail_content="LOG IO thread not running on server $MYSQLIP!"
send_alert_sms "mail" "$mail_content"
## when the slave io thread is dead, it will automatic start after send the alert mail. ##
/usr/local/mysql/bin/mysql -u backupshell -pbackupshell -Bse "start slave io_thread;"
echo "start slave io_thread"
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!"
sms_content="Unexpected Error or Check Your permissions!"
send_alert_sms "SMS" "$sms_content"
fi
fi
exit 2
fi