mysql replication環境檢查指令碼

author：skate
time:2013/05/13

 

mysql replication環境檢查指令碼

 

此指令碼部署在slave端，可以通過簡訊或郵件監控slave的mysqld健康，replication環境健康，及複寫延遲。

 

指令碼：mysql_rep_moni.sh

#!/bin/bash
### author: skate
### time: 2013-05-14
### function: This script detects the mysqld health, replicaton health, replication latency

#variables
pwd=pwd
dir=/mysql/script/
ipaddr=1.1.1.1

###Determine whether there is connection
mysql_status=`netstat -nl | awk 'NR>2{if ($4 ~ /.*:3306/) {print "Yes";exit 0}}'`
  if [ "$mysql_status" == "Yes" ] ; then
        slave_status=`mysql -uroot -p$pwd -e "show slave status\G" | grep "Running" | awk '{if ($2 != "Yes") {print "No";exit 1}}'`
        slave_delay=`mysql -uroot -p$pwd -e "show slave status\G" | grep "Seconds_Behind_Master" | awk '{if ($2 >=0) {print $2 ;exit
 0}}'`
       echo "delay"$slave_delay
       echo "status"$slave_status
###replication is not working
         if [ "$slave_status" == "No" ];then
             echo "slave is not working!"
             sh "$dir"notify.sh "phone" "$ipaddr""_warn_zxg_rep" "$ipaddr""_mysql_replication_is_not_working" "dba"
             sh "$dir"notify.sh "email" "$ipaddr""_error_zxg_rep" "mysql_replication_is_not_working" "dba"
###replication is working,but replication have delay
        elif [ "$slave_status" != "No" ] && [ $slave_delay -ge 10 ] ; then
             echo "slave is working,but replication have delay!"
             sh "$dir"notify.sh "phone" "$ipaddr""_warn_zxg_rep" "$ipaddr""_mysql_replication_is_delay:$slave_delay" "dba"
             sh "$dir"notify.sh "email" "$ipaddr""_warn_zxg_rep" "mysql_replication_is_delay:$slave_delay" "dba"
        else
             echo "slave is working."
        fi
  else
       echo "mysqld is down!"
       sh "$dir"notify.sh "email" "$ipaddr""_error_zxg_mysqld" "$ipaddr""_mysqld_is_down" "dba" 

       sh "$dir"notify.sh "phone" "$ipaddr""_error_zxg_mysqld" "mysqld_is_down" "dba"
  fi

建立定時crontab
*/5     *       *       *       *       su - mysql -c "sh /mysql/script/mysql_rep_moni.sh> /dev/null 2>&1"

說明：
上面的警示API(notify.sh)可以根據自己的使用方式修改

 

------end------