MGR监控报警

一、报警思路

m.conf文件记录配置信息，只需要修改这个文件的内容即可(需要将mysql_stat.sh里面的信息写到这里，进行中)
mysql_stat.sh文件作为MGR状态监测脚本，加入定时任务每分钟执行
send_mail.py文件作为报警发送脚本，一旦MGR状态不正常则mysql_stat.sh会调用send_mail.py脚本发送邮件
由于MGR状态非ONLINE时，无法检测其他MGR节点是否正常，所以需在运行MGR的每台机器上都配置如下脚本

二、脚本配置

需要自行修改如下两项配置：

m.conf：MySQL相关

send_mail.py：修改报警邮件相关

三、脚本路径

[root@oratest51 data]# tree /data/mysql_monitor/
/data/mysql_monitor/
├── log
│   └── mysql_stat.log
├── m.conf
├── mysql_stat.sh
└── send_mail.py

三、脚本内容

3.1m.conf配置文件

[root@oratest52 mysql_monitor]# cat m.conf 
#收件地址
mailaddress:xxx@xxx.com

#mysql相关信息
mysql_user:root
mysql_pwd:123456
ip:172.16.9.52

#mysql进程记录日志
mysql_stat:/data/mysql_monitor/log/mysql_stat.log

##同步状态及延时检查配置##
#slave同步状态
#sync_status:/data/mysql_monitor/log/sync_stat.log
#slave同步错误记录日志
#sync_err:/data/mysql_monitor/log/sync_err.log

###增量及全量备份配置###
#mysql  binlog目录
bindir:/data/mysql/data/3306
#mysql binlog index文件
binfile:/data/mysql/data/3306/mysql-bin.index
#每日增量备份存储路径
bakdir:/data/mysql_monitor/bakup/daily
#增量备份操作记录日志
logfile:/data/mysql_monitor/log/dailybak.log
#全量备份存储路径
full_bakdir:/data/mysql_monitor/bakup/weekly
#全量备份存储路径
full_baklog:/data/mysql_monitor/log/weeklybak.log

3.2MGR状态检查

以下两个脚本二选一

#!/bin/bash

MAIL_ADDR=`cat /data/mysql_monitor/m.conf |grep mailaddress |cut -d ":" -f2`
USER=`cat /data/mysql_monitor/m.conf |grep mysql_user |cut -d ":" -f2`
PASSWORD=`cat /data/mysql_monitor/m.conf |grep mysql_pwd |cut -d ":" -f2`
MYSQL_STAT_LOG=`cat /data/mysql_monitor/m.conf |grep mysql_stat |cut -d ":" -f2`

IP=`cat /data/mysql_monitor/m.conf |grep ip |cut -d ":" -f2`
MYSQL_PORT=`netstat -na|grep "LISTEN"|grep -w "3306"|awk -F[:" "]+ '{print $4}'`
DATE=$(date "+%Y-%m-%d %H:%M.%S")

STATUS=$(mysql -u$USER -p$PASSWORD --connect_timeout=5 -e "SELECT * FROM performance_schema.replication_group_members;" 2>&1 |sed -n '/group_replication_applier/p' |grep -w "ONLINE")
MGR=`echo $STATUS |grep ONLINE |awk '{print $5}' |head -n 1`
if [ "$MGR" = "ONLINE" ]
then
    echo "MySQL MGR is ONLINE" > $MYSQL_STAT_LOG
else
    echo "$DATE Server: $IP MySQL MGR status is not ONLINE,Please check MGR status!" > $MYSQL_STAT_LOG
    python /data/mysql_monitor/mail.py 'mysql' $MAIL_ADDR  "$IP Mysql Warn"  < $MYSQL_STAT_LOG
fi

#!/bin/bash

MAIL_ADDR=`cat /data/mysql_monitor/m.conf |grep mailaddress |cut -d ":" -f2`
USER=`cat /data/mysql_monitor/m.conf |grep mysql_user |cut -d ":" -f2`
PASSWORD=`cat /data/mysql_monitor/m.conf |grep mysql_pwd |cut -d ":" -f2`
MYSQL_STAT_LOG=`cat /data/mysql_monitor/m.conf |grep mysql_stat |cut -d ":" -f2`

IP=`cat /data/mysql_monitor/m.conf |grep ip |cut -d ":" -f2`
MYSQL_PORT=`netstat -na|grep "LISTEN"|grep -w "3310"|awk -F[:" "]+ '{print $4}'`

DATE=$(date "+%Y-%m-%d %H:%M.%S")
mysql -u$USER -p$PASSWORD --connect_timeout=5 -e "SELECT * FROM performance_schema.replication_group_members;" 2>&1 |sed -n '/group_replication_applier/p' |grep -w ONLINE > /dev/null
if [ $? -ne 0 ]
then
    echo "$DATE Server: $IP MySQL MGR status is not ONLINE,Please check MGR status!" > $MYSQL_STAT_LOG
    python /data/mysql_monitor/send_mail.py 'mysql' $MAIL_ADDR  "$IP Mysql Warn"  < $MYSQL_STAT_LOG
else
    echo "MySQL MGR is ONLINE" > $MYSQL_STAT_LOG
fi

3.3send_mail.py发送邮件

[root@oratest52 mysql_monitor]# cat send_mail.py 
#!/usr/bin/python
#-*- coding: UTF-8 -*-
import sys,os,string,time,datetime,re
from sys import stdout
import poplib
import smtplib
from email.header import decode_header
from email.mime.text import MIMEText
import email

def send_mail(sender, receiver,strsubject,strcontent):
        _user = "13912345678@139.com"
        _pwd  = "xxx"
        sent =smtplib.SMTP_SSL('smtp.139.com',465)
        sent.login(_user, _pwd)
        to = receiver.split(";")
        content=MIMEText(strcontent,'html',_charset='UTF-8')
        content['Subject']=strsubject
        content['From']=sender
        content['To']=','.join(to)
        sent.sendmail('13912345678@139.com',to,content.as_string())
        sent.close()

## main ##
if __name__=='__main__':
## get the path in the config file
        if len(sys.argv) != 4:
                print "sender,receiver,subject"
                sys.exit(1)
        sender=sys.argv[1]
        receiver=sys.argv[2]
        subject=sys.argv[3]
        content =sys.stdin.read()
        send_mail(sender, receiver, subject, content);

四、定时任务

将脚本加入定时任务执行

* * * * * /bin/sh /data/mysql_monitor/mysql_stat1.sh > /dev/null