mysql 判断邮箱_自动判断mysql主从复制状态并邮件通知

定期验证MYSQL主从数据一致性在某些时候是一项很值得做的事情，google还提供了MYSQL补丁来自动实现该功能。但某些时候我们能够定期检查到复制的状态也足够了，目前在网上自动检查mysql主从复制状态的方法比较少，所以自已写了一个脚本,该脚本实现功能：

1.通过mysql命令判断mysql从服务器三个主要的复制状态值是否正常，检测到有问题后，根据设定时间间隔值，再重复检测两遍，其中Seconds_Behind_Master值因为不能准确地描述复制延迟，所以在3600秒以下都判断为正常。

2.检测到有问题后记录日志并发送邮件通知状态值内容，问题持续则根据设定时间间隔值再发邮件通知，恢复正常也邮件通知。另外，无论正常与否，每天都发送一次邮件通知，由计划任务和时间相关的变量值决定。

该脚本可以监控主从复制状态，如果要验证主从MYSQL数据一致性，可以研究下google提供的MYSQL补丁。#/bin/bash

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

# author zhaoyanan

# date 2013/01/25   create

# update 2013/01/28  Adding duplicate detection

#

# Execution:

# touch /root/sh/mysql_slave_status.sh; chmod 700 /root/sh/mysql_slave_status.sh

# vi /etc/crontab

# 5,15,25,35,45,55 * * * * root /root/sh/mysql_slave_status.sh >> /root/sh/mysql_slave_status.log 2>&1

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

######### set variables ############

tmpdir=/tmp

#mysqlhost="127.0.0.1"

#mysqlport="3306"

mysqlsocket="/opt/mysql/var/mysql.sock"

mysqlbinpath="/opt/mysql/bin"

mysqluser="root"

mysqlpw="pass"

servername="dbname"

mailfromadd='dbname

mailtoadd='user1'

#mailccadd='user2'

repeat_alarm_time=12           # Repeat alarm time interval ( About *10 minute. value of 12, about two hours)

failure_interval=10            # Interval after a problem is detected, the unit: seconds ( 

reporting_time=0855            # Must be notified of the time, even if normal.

current_time=$(date +%H%M)     # Current time

export LANG=C

export LC_ALL=C

export PATH="$mysqlbinpath":/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

###### do ####################################

echo ""

echo ""

echo "`date` Start monitoring."

#### function ####

function mailto() {

# mail

/usr/sbin/sendmail -t <

From: $mailfromadd

To: $mailtoadd

Cc: $mailccadd

Subject: $servername slave status ok!

----------------------------------

$servername slave status:

$slaveiostatus

$slavesqlstatus

$slavebehind

----------------------------------

EOF

}

function mailto2() {

# mail

/usr/sbin/sendmail -t <

From: $mailfromadd

To: $mailtoadd

Cc: $mailccadd

Subject: $servername slave status problems!

----------------------------------

$servername slave status:

$slaveiostatus

$lastioerror

$slavesqlstatus

$lastsqlerror

$slavebehind

----------------------------------

EOF

}

function mailto3() {

# mail

/usr/sbin/sendmail -t <

From: $mailfromadd

To: $mailtoadd

Cc: $mailccadd

Subject: $servername slave status problems! (too many times)

----------------------------------

$servername slave status:

$slaveiostatus

$lastioerror

$slavesqlstatus

$lastsqlerror

$slavebehind

----------------------------------

EOF

}

function mailto4() {

# mail

/usr/sbin/sendmail -t <

From: $mailfromadd

To: $mailtoadd

Cc: $mailccadd

Subject: $servername slave status return to normal from the question.

----------------------------------

$servername slave status:

$slaveiostatus

$slavesqlstatus

$slavebehind

----------------------------------

EOF

}

function showstatus() {

mysql -S $mysqlsocket -u"$mysqluser" -p"$mysqlpw" -e "show slave status\G" > "$tmpdir"/"$servername"_status.txt

slaveiostatus=`cat "$tmpdir"/"$servername"_status.txt | grep "Slave_IO_Running" | sed 's/^[ \t]*//g'`

lastioerror=`cat "$tmpdir"/"$servername"_status.txt | grep "Last_IO_Error" | sed 's/^[ \t]*//g'`

slavesqlstatus=`cat "$tmpdir"/"$servername"_status.txt | grep "Slave_SQL_Running" | sed 's/^[ \t]*//g'`

lastsqlerror=`cat "$tmpdir"/"$servername"_status.txt | grep "Last_SQL_Error" | sed 's/^[ \t]*//g'`

slavebehind=`cat "$tmpdir"/"$servername"_status.txt | grep "Seconds_Behind_Master" | sed 's/^[ \t]*//g'`

slaveiovalue=`cat "$tmpdir"/"$servername"_status.txt | grep "Slave_IO_Running" | sed 's/^[ \t]*//g' | awk -F ': ' '{print $2}'`

slavesqlvalue=`cat "$tmpdir"/"$servername"_status.txt | grep "Slave_SQL_Running" | sed 's/^[ \t]*//g' | awk -F ': ' '{print $2}'`

slavebehindvalue=`echo "$slavebehind" | awk -F ': ' '{print $2}' | grep '[0-9]'`

}

### check ####

test -f "$tmpdir"/"$servername"_m_value || echo "0" > "$tmpdir"/"$servername"_m_value

test -f "$tmpdir"/"$servername"_n_value || echo "0" > "$tmpdir"/"$servername"_n_value

m=`cat "$tmpdir"/"$servername"_m_value`

n=`cat "$tmpdir"/"$servername"_n_value`

if   showstatus;sync;sleep 1

[ "$slaveiovalue" == "Yes" ] && [ "$slavesqlvalue" == "Yes" ] && [ "$slavebehindvalue" -le 3600 ];then

echo "`date` First detected, $servername slave status ok!"

m=0; echo $m > "$tmpdir"/"$servername"_m_value

elif sleep $failure_interval

showstatus;sync;sleep 1

[ "$slaveiovalue" == "Yes" ] && [ "$slavesqlvalue" == "Yes" ] && [ "$slavebehindvalue" -le 3600 ];then

echo "`date` Second detection, $servername slave status ok!"

m=0; echo $m > "$tmpdir"/"$servername"_m_value

elif sleep $failure_interval

showstatus;sync;sleep 1

[ "$slaveiovalue" == "Yes" ] && [ "$slavesqlvalue" == "Yes" ] && [ "$slavebehindvalue" -le 3600 ];then

echo "`date` Third detection, $servername slave status ok!"

m=0; echo $m > "$tmpdir"/"$servername"_m_value

else

echo "`date` After three detection, $servername slave problems!"

m=$(($m+1)); echo $m > "$tmpdir"/"$servername"_m_value

fi

#### log and mail ####

if   [ "$reporting_time" -eq "$current_time" ] && [ "$m" -eq 0 ] && [ "$n" -eq 0 ]; then

echo "mailto."

mailto

elif [ "$m" -eq 1 ] && [ "$n" -eq 0 ]; then

echo "`date` $servername slave status problems! mailto2."

echo "$servername slave status: "

echo "$slaveiostatus"

echo "$lastioerror"

echo "$slavesqlstatus"

echo "$lastsqlerror"

echo "$slavebehind"

mailto2

n=1; echo $n > "$tmpdir"/"$servername"_n_value

elif [ "$m" -eq "$repeat_alarm_time" ] && [ "$n" -eq 1 ]; then

mailto3

echo "`date` $servername slave status problems! too many times, mailto3."

m=0; echo $m > "$tmpdir"/"$servername"_m_value

elif [ "$m" -eq 0 ] && [ "$n" -eq 1 ]; then

mailto4

echo "$servername slave status return to normal from the question. mailto4."

n=0; echo $n > "$tmpdir"/"$servername"_n_value

elif [ "$m" -ne 0 ] && [ "$n" -eq 1 ]; then

echo "`date` $servername slave status problems! too many times, nomailto"

fi