定期验证MYSQL主从数据一致性在某些时候是一项很值得做的事情,google还提供了MYSQL补丁来自动实现该功能。但某些时候我们能够定期检查到复制的状态也足够了,目前在网上自动检查mysql主从复制状态的方法比较少,所以自已写了一个脚本,该脚本实现功能:
1.通过mysql命令判断mysql从服务器三个主要的复制状态值是否正常,检测到有问题后,根据设定时间间隔值,再重复检测两遍,其中Seconds_Behind_Master值因为不能准确地描述复制延迟,所以在3600秒以下都判断为正常。
2.检测到有问题后记录日志并发送邮件通知状态值内容,问题持续则根据设定时间间隔值再发邮件通知,恢复正常也邮件通知。另外,无论正常与否,每天都发送一次邮件通知,由计划任务和时间相关的变量值决定。
该脚本可以监控主从复制状态,如果要验证主从MYSQL数据一致性,可以研究下google提供的MYSQL补丁。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 |
#/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<dbname@dbmon.domain.com’ mailtoadd=‘user1<user1@domain.com>’ #mailccadd=’user2<user2@domain.com>’ 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 ( < 25 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 <<EOF 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 <<EOF 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 <<EOF 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 <<EOF 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 |
发表回复