mysql mgr监控_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