linux shell脚本：监控MySQL主从同步状态，异常即刻邮件提醒

原创

a772304419 2024-05-16 15:56:22 ©著作权

文章标签 linux 运维 mysql MySQL 服务器 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者a772304419的原创作品，请联系作者获取转载授权，否则将追究法律责任

以下是一段基本的shell脚本，用于监控MySQL主从同步状态，并在检测到异常时发送邮件通知。请根据您的实际环境调整相关参数（如MySQL服务器地址、用户、密码、邮件服务器设置等）。

#!/bin/bash

# MySQL主从服务器信息
MASTER_HOST="master.example.com"
MASTER_USER="monitor_user"
MASTER_PASS="secure_password"

SLAVE_HOST="slave.example.com"
SLAVE_USER="monitor_user"
SLAVE_PASS="secure_password"

# 邮件通知设置
EMAIL_TO="admin@example.com"
EMAIL_FROM="mysql_monitor@example.com"
EMAIL_SUBJECT="MySQL Replication Status Alert"
SMTP_SERVER="smtp.example.com"
SMTP_PORT=587
SMTP_USER="smtp_user"
SMTP_PASS="smtp_password"

# 获取主从服务器当前二进制日志位置及偏移量
MASTER_LOG_POS=$(mysql -h"$MASTER_HOST" -u"$MASTER_USER" -p"$MASTER_PASS" -Nse 'SHOW MASTER STATUS' | awk '{print $2}')
MASTER_LOG_FILE=$(mysql -h"$MASTER_HOST" -u"$MASTER_USER" -p"$MASTER_PASS" -Nse 'SHOW MASTER STATUS' | awk '{print $1}')

SLAVE_IO_RUNNING=$(mysql -h"$SLAVE_HOST" -u"$SLAVE_USER" -p"$SLAVE_PASS" -Nse 'SHOW SLAVE STATUS\G' | awk '/Slave_IO_Running:/{print $2}')
SLAVE_SQL_RUNNING=$(mysql -h"$SLAVE_HOST" -u"$SLAVE_USER" -p"$SLAVE_PASS" -Nse 'SHOW SLAVE STATUS\G' | awk '/Slave_SQL_Running:/{print $2}')
SLAVE_MASTER_LOG_FILE=$(mysql -h"$SLAVE_HOST" -u"$SLAVE_USER" -p"$SLAVE_PASS" -Nse 'SHOW SLAVE STATUS\G' | awk '/Master_Log_File:/{print $2}')
SLAVE_READ_POS=$(mysql -h"$SLAVE_HOST" -u"$SLAVE_USER" -p"$SLAVE_PASS" -Nse 'SHOW SLAVE STATUS\G' | awk '/Read_Master_Log_Pos:/{print $2}')

# 检查主从同步状态
if [ "$SLAVE_IO_RUNNING" != "Yes" ] || [ "$SLAVE_SQL_RUNNING" != "Yes" ]; then
    echo "MySQL replication is not running properly. Details:"
    echo "Slave_IO_Running: $SLAVE_IO_RUNNING"
    echo "Slave_SQL_Running: $SLAVE_SQL_RUNNING"
    send_email
    exit 1
fi

if [ "$SLAVE_MASTER_LOG_FILE" != "$MASTER_LOG_FILE" ] || [ "$SLAVE_READ_POS" -ne "$MASTER_LOG_POS" ]; then
    echo "MySQL replication is not in sync. Details:"
    echo "Master Log File: $MASTER_LOG_FILE, Position: $MASTER_LOG_POS"
    echo "Slave Log File: $SLAVE_MASTER_LOG_FILE, Position: $SLAVE_READ_POS"
    send_email
    exit 1
fi

echo "MySQL replication is healthy."
exit 0

# 发送邮件函数
function send_email {
    echo "MySQL replication status alert" | mail \
        -a "From: $EMAIL_FROM" \
        -s "$EMAIL_SUBJECT" \
        -S smtp="smtp://$SMTP_USER:$SMTP_PASS@$SMTP_SERVER:$SMTP_PORT" \
        $EMAIL_TO
}

此脚本执行以下操作：