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
![終端環境之tmux[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)