监控☞mysql慢sql预警

用途

检测数据库中执行缓慢的sql，并记录预警

脚本

#!/bin/bash
# by zyh
# 检查数据库耗时过长的语句
# 所需配置文件:
# 1. userfile.txt 用于判断程序用户的sql是否超时，超时了杀死
  ##用户名      密码    超时时间
# 2. database.conf 需要show full processlist权限
  ##Username=
  ##Password=
  ##Mysqlhostname=
  ##Database=
  ##Port=
# bash start.sh database.conf 放到计划任务里
# -------------------------------------------------------------------------------
#--变量--
basedir=`cd "$(dirname "$0")"; pwd`
currtime=`date "+%Y%m%d_%H%M%S"`
#Username=
#Password=
#Mysqlhostname=
#Database=
#Port=
source $basedir/$1
datadir=$basedir/data/$Database-$1/$currtime
[[ -d $datadir ]] || mkdir -p $datadir

#--main--
mysql -s -r -u$Username -p$Password -h$Mysqlhostname -P$Port -e 'show full processlist' | grep -v 'Sleep' |grep -v 'system' | egrep '(Query|Execute)' | grep -i $'\tselect' > $datadir/$Database.all
awk -F'\t' 'BEGIN{OFS="\t"}ARGIND==1{warntime[$1]=$3;warnpwd[$1]=$2} \
                ARGIND==2{ \
                        for( nametime in warntime ) { \
                                if( $2 == nametime && $6 > warntime[$2] ) { \
                                        print warnpwd[$2],$0;break \
                                } \
                        } \
                }' $basedir/userfile.txt $datadir/$Database.all > $datadir/$Database.warn
cat $datadir/$Database.warn | while read warningpwd id warningname other;do
        echo "# ${warningname}: $id $other"
        echo "mysql -s -r -u$warningname -p$warningpwd -h$Mysqlhostname -P$Port -e 'kill '"$id"'"
done > $datadir/kill.sql
[[ -s $datadir/kill.sql ]] && python ${basedir}/sendmail.py '收件人邮箱地址' "[SQL-TimeOut]-$Database" "($Mysqlhostname)The attachment content is timeout SQL information" '抄送邮箱地址'  "$datadir/kill.sql" || rm -rf $datadir

#!/usr/bin/python
#coding:utf-8
# by zyh
# python sendmail.py '收件人邮箱地址'  '主题' '邮件内容' '抄送邮箱地址' '附件绝对路径'


import smtplib
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import sys

reload(sys)
sys.setdefaultencoding('utf8')

mail_host = 'smtp服务器地址'  # 例如 smtp.gmail.com:587
mail_user = '发件人邮箱'      # 例如 [email protected]
mail_pass = '发件人密码'

def send_mail(*args):
    argsNum=len(args[0])
    if argsNum == 4:
        filename,to_list, subject, content=args[0]
    elif argsNum == 5:
        filename,to_list, subject, content, cc_list=args[0]
    elif argsNum == 6:
        filename,to_list, subject, content, cc_list, subfile=args[0]
    me = mail_user+"<"+mail_user+">"

    #初始化邮件对象 msg
    msg = MIMEMultipart()

    msg['From'] = me
    msg['to'] = to_list
    emailList=to_list
    # 主题
    msg['Subject'] = Header(subject, 'utf-8').encode()
    # 内容
    msg.attach(MIMEText(content, 'plain', 'utf-8'))

    # 抄送
    if 'cc_list' in vars():
        msg['Cc'] = cc_list
        emailstring = to_list+','+cc_list
        emailList=emailstring.split(",")
    # 附件
    if 'subfile' in vars():
        att1 = MIMEText(open(("%s"%subfile),'rb').read(), 'base64', 'utf8')
        att1["Content-Type"] = 'text/plain'
        att1["Content-Disposition"] = 'attachment; filename='+subfile.split('/')[-1] # 这里的filename可以任意写，写什么名字，邮件中显示什么名字
        msg.attach(att1)
    # 发送
    try:
        print "start sendmail"
        s = smtplib.SMTP(mail_host)
        print "connect mail server suesscc"
        s.starttls()
        s.login(mail_user,mail_pass)
        print "login mail server suesscc"
        s.sendmail(me,emailList,msg.as_string())
        s.close()
        return True
    except Exception,e:
        print "%s\t%s"%(to_list,str(e))
        return False

if __name__ == "__main__":
    if len(sys.argv) < 4:
        exit()
    send_mail(sys.argv)