MGR监控报警
2019-09-27 16:14 williamzheng 阅读(807) 评论(0) 编辑 收藏 举报一、报警思路
- 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
WilliamZheng©版权所有 转载请注明出处! 觉得对您有帮助请点个赞哟~ 运维架构师群:833329925