mysql慢查询分析

FileName: /usr/local/scripts/check.mysql.sh

#!/bin/bash

PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

timeToday=`date +%y%m%d`

contactUser="myalter@vip.126.com"

filename=$1

subject=$2

IP=`ifconfig | grep 'inet addr:10' | awk '{print $2}' | awk -F: '{print $2}'`

/usr/local/scripts/mysql.analysis.slowquery $filename > linshi.txt

echo "日期,时间,用户名,来源IP,数据库,查询耗时,锁表时长,RowsSend,RowsExamined,SQL语句" > alert.csv

/usr/local/scripts/mysql.analysis.slowquery.alert linshi.txt | grep $timeToday | tr ',' '__' | awk '{ print $3","$4","$7","$9","$14","$19","$21","$23","$25","substr($0, index($0,$26))}' >> alert.csv

/bin/sendEmail -f myalter@vip.126.com -t "$contactUser" -s smtp.vip.126.com -u "$subject - $IP - MySQL $timeToday 数据库慢查询大于10秒" -xu myalter@vip.126.com -xp eipharihdvfx -o tls=no -o message-charset=utf8 -m "您好：\n 您收到这封邮件，是因为bigdata-ops认为这些信息会对您的工作有所帮助。如果不想接收，请回复该邮箱，程序会自动终止对您的信息推送。By bigdata-ops-AI" -a alert.csv

FileName: /usr/local/scripts/mysql.analysis.slowquery

#!/bin/env python

#coding:utf-8

# Filemname:

# Description: 分析MySQLDB慢查询日志，帮助业务人员调整优化参数

# Author: bigdata-ops@le.com

import os

import sys

import re

filename=sys.argv[1]

def dingweihang(line):

    flag=0

    result=re.findall('# Time:',line)   

    if len(result)==1:

        flag=1

        return flag

    result=re.findall('# User@Host',line)   

    if len(result)==1:

        flag=2

        return flag

    result=re.findall('# Thread_id',line)   

    if len(result)==1:

        flag=3

        return flag

    result=re.findall('# Query_time',line)  

    if len(result)==1:

        flag=4

        return flag

    return 100



lastflag=0

meikaishi=0

for line in open(filename):

#   print line

    line = line.replace('\n','')

    flag=dingweihang(line)

    if flag == 1 and lastflag == 100:

        print ""

        #print "find 1st"

        #meishikaishi=0

        print line,

        diyihang = line

    elif flag == 2 and lastflag == 1:

        #print "find 2ed"

        print line,

    elif flag == 2 and lastflag == 100:

        #print "find 没有第一行的第二行"

        print ""

        print diyihang,

        print line,

    elif flag == 3 and lastflag == 2:

        #print "find 3nd"

        print line,

    elif flag == 4 and lastflag == 3:

        #print "find 4th"

        print line,

    elif meikaishi==1 and lastflag==100:

        meikaishi=1

    elif flag == 100 and lastflag == 0 :

        #print "find yichang de hang"

        #print line,

        #meikaishi=1

        a=0

    elif flag == 100 and lastflag == 4:

        #print "find 描述的行"

        print line,

    elif flag == 100 and lastflag == 100:

        #print "find 继续的行",

        print line,

    lastflag=flag

FileName: /usr/local/scripts/mysql.analysis.slowquery.alert

#!/bin/env python

#coding:utf-8

# Filemname:

# Description: 分析由运维组写出来的脚本将MySQLDB慢查询日志转换成容易分析的、一行一条case的记录，分析过去一个小时的慢查询记录，讲大于10s的记录发出来。

# Author: bigdata-ops@le.com

import os

import sys

import re

def dingweihang(line):

    flag=0

    result=re.findall('# Time:',line)   

    if len(result)==1:

        flag=1

        return flag

    result=re.findall('# User@Host',line)   

    if len(result)==1:

        flag=2

        return flag

    result=re.findall('# Thread_id',line)   

    if len(result)==1:

        flag=3

        return flag

    result=re.findall('# Query_time',line)  

    if len(result)==1:

        flag=4

        return flag

    return 100

def queryTimeOver(line):

    result=re.findall("Query_time: ([\d|\.]+)",line)

    if len(result)==1:

        return result[0]

    else:

        return 0;



result=[]

for line in open("linshi.txt"):

    if len(line) < 10:

        continue

    queryTime=queryTimeOver(line)

    if float(queryTime) > 10:

        result.append(line)

    # 匹配Query_time: 0.299677

for content in result:

    print content

手动执行

cd /usr/local/scripts && /bin/bash check.mysql.sh /data1/bd_data_market_mysql/logs/bd_data_market.slow 数据集市