Mysql服务器运行状况监控

#!/bin/bash
#说明：本脚本用于检测MySQL所在服务器的运行状况，包括内存使用情况，磁盘可用空间以及MYSQL的运行状况以及同步状况
#作者：zhanglei
#时间：2009-06-22
############################################################################
#公共变量
S_Host="192.168.28.58" #从机IP
S_User="backup" #从机用户名
S_Pwd="cdelbbs1q2w" #从机密码

#M_Host="192.168.28.37" #主机IP
M_Host="127.0.0.1"
M_User="backup" #主机用户名
M_Pwd="cdelbbs1q2w" #主机

MAILLIST="sqlercn@gmail.com zhangleijs@cdeledu.com"
MAILCMD="$(which mail)"
MAILMESSAGE="/tmp/mysql.fail.$$"

MsqlCMD="$(which mysql)"
LogFile="/script/ChkMysqlServer.log"
#检查同步状态
Repl(){
echo "show master status \G" | ${MsqlCMD} -u${M_User} -p${M_Pwd} -h${M_Host} | egrep "File|Position" |sed -e's/: /=/g' >>/tmp/$$.sh
echo "show slave status \G" | ${MsqlCMD} -u${S_User} -p${S_Pwd} -h${S_Host} | egrep "Master_Log_File|Read_Master_Log_Pos|Slave_IO_Running|Slave_SQL_Running" | grep -v "Relay_Master_Log_File"|sed -e's/: /=/g'>>/tmp/$$.sh

source /tmp/$$.sh
#echo $File,$Position,$Master_Log_File,$Read_Master_Log_Pos
if [[ "$File" != "$Master_Log_File" ]]||[ $Position -ne $Read_Master_Log_Pos ]
then
echo "">>$MAILMESSAGE
echo "Mysql Replication Warning:" >> $MAILMESSAGE
echo "Master LogFile: "$File" Slave LogFile:"$Master_Log_File >>$MAILMESSAGE
echo "Master Pos: "$Position" Slave Pos:"$Read_Master_Log_Pos >>$MAILMESSAGE
fi

if [[ "$Slave_IO_Running" != "Yes" ]]||[[ "$Slave_SQL_Running" != "Yes" ]]
then
echo "">>$MAILMESSAGE
echo "MySQL Replication Error:" >>$MAILMESSAGE
echo "Slave_IO_Running :"$Slave_IO_Running >> $MAILMESSAGE
echo "Slave_SQL_Running :"$Slave_SQL_Running >> $MAILMESSAGE
echo "Date & Time: $(date) :MySQL Replication Error Slave_IO_Running is $(Slave_IO_Running) Slave_SQL_Running is $(Slave_SQL_Running)" >>$LogFile
fi
rm -rf /tmp/$$.sh
}

#检查MYSQL运行状态,只检查本地Mysql的运行状态
ChkMysql(){
#本函数用于检查本地MySQL运行状态，如果发现MySQL不可用则重启MySQL
Start_Run="/script/bash/start_mysql.sh"
Stop_Run=""
MADMIN="$(which mysqladmin)"
MPID="/usr/local/mysql/var/localhost.pid" #pid file location
#$MADMIN -h ${M_Host} -u ${M_User} -p${M_Pwd} ping 2>/dev/null 1>/dev/null
echo "select now();" | ${MsqlCMD} -h ${M_Host} -u ${M_User} -p${M_Pwd} 2>/dev/null 1>/dev/null
if [ $? -ne 0 ]; then
echo "">>$MAILMESSAGE
echo "Error: MySQL Server is not running/responding request">>$MAILMESSAGE
echo "Hostname: $(hostname)" >>$MAILMESSAGE
echo "Date & Time: $(date)" >>$MAILMESSAGE
#If exists mysqld process then try to stop mysql
o=$(ps cax | grep -c ' mysqld$')
if [ $o -eq 1 ]; then
$MADMIN -h${M_Host} -u${M_User} -p${M_Pwd} shutdown
o=$(ps cax | grep -c ' mysqld$')
if [ $o -eq 1 ]; then
pid=`cat $MPID`
kill -9 $pid
pkill -9 mysql
fi
fi
echo "Date & Time: $(date) :Stop Mysql Server" >>$LogFile
# try to start mysql
$Start_Run>/dev/null
o=$(ps cax | grep -c ' mysqld$')
if [ $o -eq 1 ]; then
sMess="MySQL Server MySQL server successfull restarted"
echo "Date & Time: $(date) :Restore Mysql Server Successfull" >>$LogFile
else
sMess="MySQL server FAILED to restart"
echo "Date & Time: $(date) :Restore Mysql Server Failed" >>$LogFile
fi
echo "Current Status: $sMess" >>$MAILMESSAGE
echo "*** This email generated by $(basename $0) shell script ***" >>$MAILMESSAGE
echo "*** Please don't reply this email, this is just notification email ***" >>$MAILMESSAGE
else # MySQL is running :) and do nothing
:
fi
}

#检查服务器磁盘使用情况
ChkDisk(){
df -lhP | sed -e's/%//g' | grep -v Mounted| grep -v 挂载点 |awk '{if($5>80) print "挂载点:"$6"使用比率超过80％"}' > /tmp/mysql.disk$$
if [ -s /tmp/mysql.disk$$ ]
then
echo "">>$MAILMESSAGE
echo "$(date) Disk Usage:">>$MAILMESSAGE
cat /tmp/mysql.disk$$ >>$MAILMESSAGE
echo "Date & Time: $(date) :Disk Usage Warning" >>$LogFile
cat /tmp/mysql.disk$$ >>$LogFile
rm -rf /tmp/mysql.disk$$
fi
}

#检查服务器内存使用情况
ChkMem(){
cat /proc/meminfo | egrep "MemFree|SwapFree" | sed -e's/kB//g' | awk '{if($2<(1*1024*1024)) print $1"少于1G"}' > /tmp/mysql.mem$$
if [ -s /tmp/mysql.mem$$ ]
then
echo "">>$MAILMESSAGE
echo "$(date) Memory Usage:">>$MAILMESSAGE
cat /tmp/mysql.mem$$ >>$MAILMESSAGE
echo "Date & Time: $(date) :Memory Usage Warning" >>$LogFile
cat /tmp/mysql.mem$$ >>$LogFile
rm -rf /tmp/mysql.mem$$
fi
}

#发送邮件
SendMail(){
if [ -s $MAILMESSAGE ]
then
for tomail in $MAILLIST
do
mail -s "MySQL Server Error" $tomail < $MAILMESSAGE
done
rm -rf $MAILMESSAGE
fi
}