检测mysql主从复制是否正常的shell脚本

最新推荐文章于 2025-01-22 11:17:52 发布

freedom5112

最新推荐文章于 2025-01-22 11:17:52 发布

阅读量1.8k

点赞数

分类专栏： MYSQL

MYSQL 专栏收录该内容

121 篇文章

订阅专栏

本文介绍了一个用于检测MySQL主从复制状态的Shell脚本。该脚本通过比较主服务器与从服务器的状态来判断复制过程是否正常，并在发现问题时发送邮件通知管理员。

http://www.zhaokunyao.com/archives/2371

检测mysql主从复制是否正常的shell脚本

以前写过一文，《检测mysqld是否活着的shell脚本》。
今天看到datacharmer放出来了一个检测主从复制是否正常的shell脚本，转载如下：

#!/bin/bash
 
#主服务器
USERNAME=msandbox
PASSWORD=msandbox
EXPECTED_MASTER_HOST=127.0.0.1
EXPECTED_MASTER_PORT=27371
 
#从服务器
SLAVE_HOST=127.0.0.1
SLAVE_PORT=27372
 
MYSQL="mysql -u $USERNAME -p$PASSWORD "
MASTER="$MYSQL -h $EXPECTED_MASTER_HOST -P $EXPECTED_MASTER_PORT"
SLAVE="$MYSQL -h $SLAVE_HOST -P $SLAVE_PORT"
 
#查看主服务器状态
$MASTER -e 'SHOW MASTER STATUS\G' > mstatus
#查看从服务器状态
$SLAVE -e 'SHOW SLAVE STATUS\G' > sstatus
 
function extract_value {
    FILENAME=$1
    VAR=$2
    grep -w $VAR $FILENAME | awk '{print $2}'
}
 
#主服务器的binlog 和position
Master_Binlog=$(extract_value mstatus File )
Master_Position=$(extract_value mstatus Position )
 
#从服务器上读取到的主服务器信息
Master_Host=$(extract_value sstatus Master_Host)
Master_Port=$(extract_value sstatus Master_Port)
#主从复制的进度
Master_Log_File=$(extract_value sstatus Master_Log_File)
Read_Master_Log_Pos=$(extract_value sstatus Read_Master_Log_Pos)
 
#从服务器的二个进程信息
Slave_IO_Running=$(extract_value sstatus Slave_IO_Running)
Slave_SQL_Running=$(extract_value sstatus Slave_SQL_Running)
 
ERROR_COUNT=0
#...
if [ "$Master_Host" != "$EXPECTED_MASTER_HOST" ]
then
    ERRORS[$ERROR_COUNT]="the slave is not replicating from the host that it is supposed to"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi
#...
if [ "$Master_Port" != "$EXPECTED_MASTER_PORT" ]
then
    ERRORS[$ERROR_COUNT]="the slave is not replicating from the host that it is supposed to"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi
 
if [ "$Master_Binlog" != "$Master_Log_File" ]
then
    ERRORS[$ERROR_COUNT]="master binlog ($Master_Binlog) and Master_Log_File ($Master_Log_File) differ"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi
 
POS_DIFFERENCE=$(echo ${Master_Position}-$Read_Master_Log_Pos|bc)
 
if [ $POS_DIFFERENCE -gt 1000 ]
then
    ERRORS[$ERROR_COUNT]="The slave is lagging behind of $POS_DIFFERENCE"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi
 
if [ "$Slave_IO_Running" == "No" ]
then
    ERRORS[$ERROR_COUNT]="Replication is stopped"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi
 
if [ "$Slave_SQL_Running" == "No" ]
then
    ERRORS[$ERROR_COUNT]="Replication (SQL) is stopped"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi
 
if [ $ERROR_COUNT -gt 0 ]
then
    EMAIL=myname@gmail.com
    SUBJECT="ERRORS in replication"
    BODY=''
    CNT=0
    while [ "$CNT" != "$ERROR_COUNT" ]
    do
        BODY="$BODY ${ERRORS[$CNT]}"
        CNT=$(($CNT+1))
    done
    echo $SUBJECT
    echo $BODY
    echo $BODY | mail -s "$SUBJECT" $EMAIL
else
    echo "Replication OK"
    printf "file: %s at %'d\n" $Master_Log_File  $Read_Master_Log_Pos
fi