将有效慢日志转存到数据库v2

import re
import sys
import getopt
import MySQLdb
from subprocess import call
import os

host='10.76.45.7'
port=3306
user='test'
password='test'
dbName='test'

def loadSlowLogtoDb(mysqlConn,keyValueList):
    insertSql="insert into slowLog( \
    Rows_examined,Rows_sent,Thread_id,arg,cmd,db,fingerprint,host,ip,timestamp,user) \
    values(%s,%s,%s,%s,%s,%s,%s,%s,%s,from_unixtime(%s),%s ) " % \
    (keyValueList['Rows_examined'],keyValueList['Rows_sent'],keyValueList['Thread_id'],keyValueList['arg'],keyValueList['cmd'],keyValueList['db'],keyValueList['fingerprint'],keyValueList['host'],keyValueList['ip'],keyValueList['timestamp'],keyValueList['user'])
    #print insertSql
    try:
        cursor = mysqlConn.cursor()
        cursor.execute(insertSql)
        mysqlConn.commit()
    except MySQLdb.Error, e:
                print "Error %s: %s" % (e.args[0], e.args[1])
                pass

def getConnection():
    try:
        conn=connection=MySQLdb.connect(host=host, port=port, user=user, passwd=password, db=dbName,connect_timeout=10)
        return conn
    except MySQLdb.Error, e:
            print "Error %s: %s" % (e.args[0], e.args[1])
        pass
    return None

def main(argv):

    try:
        opts, args =getopt.getopt(argv,"hs:",["slowFileName="])
    except getopt.GetoptError:
        print 'processFile.py -s <slowFileName>'
        sys.exit(1)
    for opt, arg in opts:
        if opt == '-s':
            slowFileName= arg
            print "slowFileName is %s" %(slowFileName)

    slowFileNameEventDetail="%s.EventDetail" %(slowFileName)
    generateEventDetailCmd="/usr/local/bin/pt-query-digest --filter '$event->{Rows_examined} >=1000 && print Dumper $event' --noreport %s > %s " %(slowFileName , slowFileNameEventDetail)
    print generateEventDetailCmd
    os.system(generateEventDetailCmd)
    slowFile = open(slowFileNameEventDetail)
    keyValueList={}
    conn=getConnection()
    for line in slowFile:
        if None != re.match("^\$VAR1 = {$",line):
            print "begin"
            keyValueList={}
        elif None != re.match("};",line):
            print "end"
            loadSlowLogtoDb(conn,keyValueList)
        else:
            #print line
            lineStrip=line.strip()
            lineLen=len(lineStrip)
            if lineStrip.endswith(','):
                lineWitoutLastComma=lineStrip[0:-1]
            else:
                lineWitoutLastComma=lineStrip
            #print lineStrip
            keyValuePair=re.split(' => ',lineWitoutLastComma)
            if len(keyValuePair)==1:
                keyValueList["arg"]="undefined"
                continue
            #print keyValuePair[0]
            #print keyValuePair[1]
            keyValueList[keyValuePair[0]]=keyValuePair[1]

if __name__ == "__main__":
   main(sys.argv[1:])

 

posted @ 2017-09-18 15:38  友哥  阅读(372)  评论(0编辑  收藏  举报