监控mysql主从一致性shell脚本

#监测主从一致性 
#该脚本只检查io进程和sql进程是否都为yes,sql进程是否出现延迟,当延迟时间大于1分钟，会发邮件报警。并没有检查slave上io读取的主二进制日志文件与位置是否和主的完全一致(因为如果io进程为yes的话,很少会不一致),且position位置变化很快,
#远程连接主库和从库进行比较的功夫,说不定position已经变化了。

port='3306'
user='root'
password1='……'

HostGroup1=('10.192.200.100'  '10.192.200.101')

source='……'
target='……'
MailUser='……'
MailPassword='……'
date=`date +%y%m%d-%H:%M:`
echo $date
for host1 in ${HostGroup1[@]}
    do
	title1=$host1' slave problem alert'
	
	v1=$(/usr/local/mysql/bin/mysql --host=$host1 --port=$port --user=$user --password=$password1 -e "show slave status\G"| awk '/Slave_IO_Running/' | awk -F ":" '{print $2}')
	v2=$(/usr/local/mysql/bin/mysql --host=$host1 --port=$port --user=$user --password=$password1 -e "show slave status\G"| awk '/Slave_SQL_Running/' | awk -F ":" '{print $2}')
	v3=$(/usr/local/mysql/bin/mysql --host=$host1 --port=$port --user=$user --password=$password1 -e "show slave status\G"| awk '/Seconds_Behind_Master/' | awk -F ":" '{print $2}')

        if [ "$v1" =  "" ]
        then
            echo 'the username or password is wrong,or the mysql server is down,so we can not get value'
	    content4='the username or password is wrong,or the mysql server is down,so we can not get value'
	    /usr/local/bin/sendEmail -f $source -t $target  -s smtp.chinaunicom.cn -u $title1 -xu $MailUser -xp $MailPassword -m $content4
	else 
       
		if [ $v1 = 'Yes' ]
		then
		   
		   if [ $v2 = 'Yes' ]
		   then
			#判断sql进程是否出现延迟
			if [ $v3 != 0  ]
			
			then
			    if [ $v3 -ge 60 ]
			    then
				content3=$host1' the status of io process and sql process is yes,but slave delayed '$v3' seconds,more than 1 minutes'
				echo $content3
				/usr/local/bin/sendEmail -f $source -t $target  -s smtp.chinaunicom.cn -u $title1 -xu $MailUser -xp $MailPassword -m $content3
			    else
				echo $host1' the status of io process and sql process is yes,but slave delayed '$v3' seconds,less than 1 minutes'
			    fi
			else
			    echo 'There is no problem'
			fi
		   else
			content2=$host1' Slave_IO_Running status:'$v1',Slave_SQL_Running status:'$v2',please deal with it as soon as possible!'
			echo $content2
			/usr/local/bin/sendEmail -f $source -t $target  -s smtp.chinaunicom.cn -u $title1 -xu $MailUser -xp $MailPassword -m $content2
		  fi
		else
		   content1=$host1' Slave_IO_Running status:'$v1',Slave_SQL_Running status:'$v2',please deal with it as soon as possible!'  
		   echo $content1
		   /usr/local/bin/sendEmail -f $source -t $target  -s smtp.chinaunicom.cn -u $title1 -xu $MailUser -xp $MailPassword -m $content1
		fi
         fi      
    done


for host2 in ${HostGroup2[@]}
    do
	title2=$host2' slave problem alert'

	
	v11=$(/usr/local/mysql/bin/mysql --host=$host2 --port=$port --user=$user --password=$password2 -e "show slave status\G"| awk '/Slave_IO_Running/' | awk -F ":" '{print $2}')
        v22=$(/usr/local/mysql/bin/mysql --host=$host2 --port=$port --user=$user --password=$password2 -e "show slave status\G"| awk '/Slave_SQL_Running/' | awk -F ":" '{print $2}')
        v33=$(/usr/local/mysql/bin/mysql --host=$host2 --port=$port --user=$user --password=$password2 -e "show slave status\G"| awk '/Seconds_Behind_Master/' | awk -F ":" '{print $2}')	
	echo $v11
	if [ "$v11" =  "" ]
	then
	    echo 'the username or password is wrong,or the mysql server is down,so we can not get value'
	    content44='the username or password is wrong,or the mysql server is down,so we can not get value'
	    /usr/local/bin/sendEmail -f $source -t $target  -s smtp.chinaunicom.cn -u $title2 -xu $MailUser -xp $MailPassword -m $content44
        else
		if [ $v11 = 'Yes' ]
		then
		   
		   if [ $v22 = 'Yes' ]
		   then
			#判断sql进程是否出现延迟
			if [ $v33 != 0  ]
			
			then
			    if [ $v33 -ge 60 ]
			    then
				content33=$host2' the status of io process and sql process is yes,but slave delayed '$v33' seconds,more than 1 minutes'
				echo $content33
				/usr/local/bin/sendEmail -f $source -t $target  -s smtp.chinaunicom.cn -u $title2 -xu $MailUser -xp $MailPassword -m $content33
			    else
				echo $host2' the status of io process and sql process is yes,but slave delayed '$v33' seconds,less than 1 minutes'
			    fi
			else
			    echo 'There is no problem'
			fi
		   else
			content22=$host2' Slave_IO_Running status:'$v11',Slave_SQL_Running status:'$v22',please deal with it as soon as possible!'
			echo $content22
			/usr/local/bin/sendEmail -f $source -t $target  -s smtp.chinaunicom.cn -u $title2 -xu $MailUser -xp $MailPassword -m $content22
		  fi
		else
		   content11=$host2' Slave_IO_Running status:'$v11',Slave_SQL_Running status:'$v22',please deal with it as soon as possible!'  
		   echo $content11
		   /usr/local/bin/sendEmail -f $source -t $target  -s smtp.chinaunicom.cn -u $title2 -xu $MailUser -xp $MailPassword -m $content11
		fi
	fi
   done