监控主从库是否同步, shell,crontab

1. 如何判断从库是否运行正常
从库操作
mysql> show slave status/G 查看其输出
mysql> show slave status/G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.71
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000073
          Read_Master_Log_Pos: 19007391
               Relay_Log_File: mysql-relay-bin.000207
                Relay_Log_Pos: 19007536
        Relay_Master_Log_File: mysql-bin.000073
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: gamecenter_notice,im_message_gateway,issuecode
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: gamecenter_notice.%,im_message_gateway.%,issuecode.%
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 19007391
              Relay_Log_Space: 19007734
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
这个输出，最关键处就是"Slave_IO_Running: Yes“和“Slave_SQL_Running: Yes”，这两个值全是"Yes"就表明主从复制正常，否则就是有问题。

其次判断Last_IO_Error/Last_SQL_Error: 是否为空，如果出现error reconnecting to master则代表有错

再加之判断从库读取主库Master_Log_File: mysql-bin.000073是否与主库mysql-bin文件一致
主库操作：
mysql>show master status;
+------------------+----------+------------------------------------------------+------------------+
| File             | Position | Binlog_Do_DB                                   | Binlog_Ignore_DB |
+------------------+----------+------------------------------------------------+------------------+
| mysql-bin.000073 | 29463504 | gamecenter_notice,im_message_gateway,issuecode |                  |
+------------------+----------+------------------------------------------------+------------------+

最后判断，同步的数据库是否完整。Replicate_Do_DB: gamecenter_notice,im_message_gateway,issuecode 与主库show master status;的Binlog_Do_DB一致

2. 实现
> 给予运行监控脚本的机器分配SUPER或者REPLICATION CLIENT权限查看信息
在主从库设置：
 grant REPLICATION CLIENT on *.* to 'user'@'127.0.0.69' IDENTIFIED BY "pass";
在69上操作：
 is_slave=($(/usr/bin/mysql -uuser -ppass -h127.0.0.72 -e "show slave status/G"|grep Running |awk '{print $2}'))
 if [ "${is_slave[0]}" = "Yes" -a "${is_slave[1]}" = "Yes" ]
     then
     echo "OK -slave is running"
else
     echo "Critical -slave is error"
     #send email
     php /data/scripts/mailsaveapp/sendMsg.php
     exit
fi
 #如果返回error代表有错
 is_error=($(/usr/bin/mysql -uuser -ppass -h127.0.0.72 -e "show slave status/G"|grep Error |awk '{print $2}'))
 if [ "${is_error[0]}" = "error" -a "${is_error[1]}" = "error" ]
     then
     echo "OK -slave is running"
else
     echo "Critical -slave is error"
     #send email
     php /data/scripts/mailsaveapp/sendMsg.php
     exit
fi

#取主库的mysql-bin名称
masterBinLog=`/usr/bin/mysql -uuser -ppass -h127.0.0.71 -e "show master status;" |grep -i "mysql-bin" |awk '{print $1}'`
#取从库的mysql-bin名称
slaverBinLog=`/usr/bin/mysql -uuser -ppass -h127.0.0.72 -e "show slave status" |grep -i "Waiting for master to send event" |awk '{print $11}'`

#判断其是否相等
if [ "$slaverBinLog" != "$masterBinLog" ];then
        echo "synchronous error"
        php /data/scripts/mailsaveapp/sendMsg.php
        exit
fi

#取主库的Binlog_Do_DB所指定数据库
masterDB=`/usr/bin/mysql -uuser -ppass -h127.0.0.71 -e "show master status;" |grep -i "mysql-bin" |awk '{print $3}'`
#取从库的Replicate_Do_DB所指定数据库
slaverDB=`/usr/bin/mysql -uuser -ppass -h127.0.0.72 -e "show slave status/G;" |grep Replicate_Do_DB |awk '{print $2}'`

#判断其是否相等
if [ "$slaverDB" != "$masterDB" ];then
        echo "synchronous error"
        php /data/scripts/mailsaveapp/sendMsg.php
        exit
fi

代码：
#!/bin/bash
SENDAPP="/data/scripts/mailsaveapp/sendMsg.php"
EXEC="/usr/bin/mysql"
PHPEXEC="/usr/bin/php"
user="user"
pass="pass"
masterhost="127.0.0.71"
slaverhost="127.0.0.72"

for type in 1 2
do
 echo "type=$type";
 if [ "$type" = "1" ];then
        masterhost="127.0.0.71"
        slaverhost="127.0.0.72"
 elif [ "$type" = "2" ];then
        masterhost="127.0.0.73"
        slaverhost="127.0.0.75"
 else
        echo "$type is not right"
        exit
 fi
 is_slave=($($EXEC -u$user -p$pass -h$slaverhost -e "show slave status/G"|grep Running |awk '{print $2}'))
 if [ "${is_slave[0]}" = "Yes" -a "${is_slave[1]}" = "Yes" ]
     then
     echo $slaverhost" 1: OK -slave is running"
else
     echo $slaverhost"Critical -slave is error"
     #send email
     $PHPEXEC $SENDAPP $type
     exit
fi

 is_error=($($EXEC -u$user -p$pass -h$slaverhost -e "show slave status/G"|grep Error |awk '{print $2}'))
 if [ "${is_error[0]}" = "error" -a "${is_error[1]}" = "error" ]
     then
     echo $slaverhost" Critical -slave is error"
     #send email
     $PHPEXEC $SENDAPP $type
elif [ "${is_error[2]}" = "error" ];then
     echo $slaverhost"Critical -slave is error"
     #send email
     $PHPEXEC $SENDAPP $type
else
     echo $slaverhost" 2: OK -slave is running"
fi

#get master mysql-bin postion
echo "$EXEC -u$user -p$pass -h$masterhost -e /"show master status;/" |grep -i /"mysql-bin/" |awk '{print /$1}'"
masterBinLog=`$EXEC -u$user -p$pass -h$masterhost -e "show master status;" |grep -i "mysql-bin"|awk '{print $1}'`

#get slaver mysql-bin postion
echo "$EXEC -u$user -p$pass -h$slaverhost -e /"show slave status/" |grep -i /"Waiting for master to send event/" |awk '{print /$11}'"
slaverBinLog=`$EXEC -u$user -p$pass -h$slaverhost -e "show slave status" |grep -i "Waiting for master to send event" |awk '{print $11}'`

if [ "$slaverBinLog" != "$masterBinLog" ];then
        echo "synchronous error"
        $PHPEXEC $SENDAPP $type
        exit
fi

echo "$EXEC -u$user -p$pass -h$masterhost -e /"show master status;/" |grep -i /"mysql-bin/" |awk '{print $3}'"
masterDB=`$EXEC -u$user -p$pass -h$masterhost -e "show master status;" |grep -i "mysql-bin" |awk '{print $3}'`
echo "$EXEC -u$user -p$pass -h$slaverhost -e /"show slave status/G;/" |grep Replicate_Do_DB |awk '{print $2}'"
slaverDB=`$EXEC -u$user -p$pass -h$slaverhost -e "show slave status/G;" |grep Replicate_Do_DB |awk '{print $2}'`

if [ "$slaverDB" != "$masterDB" ];then
        echo "synchronous error"
        $PHPEXEC $SENDAPP $type
        exit
fi

done