mysql主从复制监控脚本示例(1)

1、通过检查 show slave status\G后的状态对当前的主从状态进行判断

#!/bin/bash

### STEP01: config the information of conncetion!

v_datetime=`date +"%Y-%m-%d %H:%M:%S"`

v_username='mysql'
#获取本机ip,网卡需要自己配置
v_ip=`/sbin/ifconfig bond1|grep 'inet addr'|awk '{print $2}'|cut -d: -f2`

v_hostname=`hostname`
#邮件发送主机
v_sendmail=sdtest02@sd.chinamobile.com
#邮件接收者
v_receivemail='zhaoying@richinfo.cn'
#邮件发送程序路径
MAIL_BIN=/home/crond/bsmtp
#更改会话级别字符集
export LC_ALL=en_US.UTF-8
#mysql主程序路径
MYSQL=/data/mysql/product/bin/mysql
#临时文件存放地址
path=/home/mysql/scripts/chk_slave
#主机（master）ip地址及连接信息
host_master=172.16.115.52
port_master=3307
user_master=chk_slave_user
password_master=***
#本机（slave）ip地址及连接信息
port=3307
user=root
password='Itty5678%^&*'

### STEP02: check the replication status and return the flag value!!

tmpmysql1=$($MYSQL -u$user -p$password -P$port -e "show slave status\G" | \
                awk 'BEGIN {
                                tel = 0
                        }
                        /Slave_IO_Running: Yes/  { tel++ }
                        /Slave_SQL_Running: Yes/ { tel++ }
                        END {
                                if ( tel == 2 ) {
                                        print "+Yes" 
                                } else {
                                        print "-No"
                                }
                        }'
    )

### STEP03: paint and mail the status when the slave is error!!!
    
#define display function
OutMasterLogbinPosition()
{
    echo  "Master log_bin Position:$tmp2mysql1"
}

OutSlaveRMLAndSBM()
{
    echo  "Slave Read_Master_Log_Pos:$tmp2mysql2"
    echo  "Seconds_Behind_Master:$tmp2mysql3"
}


OutErrorInfo()
{
    echo "Last_Errno:$tmp2mysql4"
    echo "Last_Error:$tmp2mysql5"
#   echo -e "\n" 
}

#get mysql master server logbin position  
GetMasterServerLogbinPosition()
{

    tmp2mysql1=$($MYSQL -u$user_master  -p$password_master -h $host_master -P$port_master -e"show master status\G" | \
    awk '/Position/ {print $2}')
    OutMasterLogbinPosition    
}

#get replication slave status when slave is error
GetSlaveServerStatusInfo()
{    
    tmp2mysql2=$(echo 'show slave status\G' |$MYSQL -u$user -p$password -P$port -e "show slave status\G" | \
        awk '/Read_Master_Log_Pos/ {print $2}')
    tmp2mysql3=$(echo 'show slave status\G' |$MYSQL -u$user -p$password -P$port -e "show slave status\G" | \
        awk '/Seconds_Behind_Master/ {print $2}')
    OutSlaveRMLAndSBM    
    
}
#get slave error information when slave is error
GetSlaveServerErrorStatusInfo()
{   
    tmp2mysql4=$(echo 'show slave status\G' |$MYSQL -u$user -p$password -P$port -e "show slave status\G" | \
        awk '/Last_Errno/ {print $2}')

    tmp2mysql5=$(echo 'show slave status\G' |$MYSQL -u$user -p$password -P$port -e "show slave status\G" | \
        awk '/Last_Error/ {print $2}')
    OutErrorInfo    
}
# output
if [ $tmpmysql1 = '+Yes' ] 
then
    GetMasterServerLogbinPosition
else
    echo '##################################################' > $path/error.log
    echo "USER:  $v_username" >> $path/error.log
    echo "HOSTNAME:  $v_hostname" >> $path/error.log
    echo "IP:  $v_ip" >> $path/error.log
    echo '##################################################' >> $path/error.log
    echo "139_Master/Slave Rep is Error" >> $path/error.log
    GetSlaveServerErrorStatusInfo >> $path/error.log
    GetMasterServerLogbinPosition >> $path/error.log
    GetSlaveServerStatusInfo >> $path/error.log
    $MAIL_BIN -f ${v_sendmail} -h smtp.sd.chinamobile.com -s "${v_hostname} 3307 replication WARNING!!! - ${v_datetime}" ${v_receivemail} < $path/error.log
fi
##### END OF SCRIPT #####