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="[e-mail]"

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 [e-mail] -t "$contactUser" -s smtp.vip.126.com -u "$subject - $IP - MySQL $timeToday 数据库慢查询大于10秒" -xu [e-mail] -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: [e-mail]

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: [e-mail]

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 数据集市