Mysql sql慢查询监控脚本代码实例

#整体的效果，全局开启表和日志文件都写，对于general_log只写表，对于slow_query_log，表和日志文件都记录。
general_log=1#开启mysql执行sql的日志 slow_query_log=1#开启mysql慢sql的日志

#设置之后会影响general_log和slow_query_log，
log_output=table,File#日志输出会写表，也会写日志文件，为了便于程序去统计，所以最好写表

#这里没配置general_log_file，那么general_log就只会写表了
#在mysql5.1.29以上，设置以下即可打开mysql将执行的sql记录在文件中
#general_log_file=/log/general.log

#5.1.29以以前为
#log=/var/lib/mysql/sql_row.log
long_query_time=1#设置mysql的慢查询为超过1s的查询 slow_query_log_file=/log/slow.log

CREATE TABLE `slow_log_dba` (
 `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `user_host` mediumtext NOT NULL,
 `query_time` time NOT NULL,
 `lock_time` time NOT NULL,
 `rows_sent` int(11) NOT NULL,
 `rows_examined` int(11) NOT NULL,
 `db` varchar(512) NOT NULL,
 `last_insert_id` int(11) NOT NULL,
 `insert_id` int(11) NOT NULL,
 `server_id` int(10) unsigned NOT NULL,
 `sql_text` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log for dba';

CREATE TABLE `general_log_dba` (
 `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `user_host` mediumtext NOT NULL,
 `thread_id` int(11) NOT NULL,
 `server_id` int(10) unsigned NOT NULL,
 `mand_type` varchar(64) NOT NULL,
 `argument` mediumtext NOT NULL,
 KEY `user_host` (`user_host`(200)),
 KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='general log for dba op';

# -- coding: utf-8 --
__author__ = 'river'
import MySQLdb as mysql
import re
from datetime import datetime, timedelta
import smtplib
from email.mime.text import MIMEText
def sendHtmlMail(mailcontent,myip):
  try:
    yestoday=(datetime.now()-timedelta(days=1)).strftime("%Y-%m-%d")
    sender = 'xxx@xxx.'
    receiver = ['xxx@xxx.']
    subject = myip+' mysql operation report '+yestoday
    smtpserver = 'smtp.exmail.xx.'
    username = 'xxx@xxx.'
    password = 'xxxxx'
    msg = MIMEText(mailcontent,'html','utf-8')#'你好','text','utf-8'
    msg['Subject'] = subject
    msg['From'] = sender
    msg['To'] = 'xxx@xxxxxxxx.'
    smtp = smtplib.SMTP()
    smtp.connect(smtpserver)
    smtp.login(username, password)
    smtp.sendmail(sender, receiver, msg.as_string())
    smtp.quit()
  except Exception, e:
    print e,'send mail error'
if __name__=='__main__':
  result=None
  htmlfile='mysqlLogMon.html'
  myiplist=['192.168.10.10','192.168.10.19']
  yestoday=(datetime.now()-timedelta(days=1)).strftime("%Y-%m-%d 00:00:00")
  today=datetime.now().strftime("%Y-%m-%d 00:00:00")
  for myip in myiplist:
    sql="select user_host,argument from general_log_dba where event_time >='%s' and event_time <='%s'" %(yestoday,today)
    try:
      dbcon = mysql.connect(host=myip, user='xxxxx', passwd='xxxxx', db='mysql', port=3306,charset='utf8')
      cur = dbcon.cursor()
      print "step 1,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
      cur.execute(sql)
      result = cur.fetchall()
      cur.close()
      dbcon.close()
    except Exception, e:
      print e,'conn mysql error'
    user_host_set=set()
    print "step 2,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    allhash={}
    if result:
      for user_host,argument in result:
        argument_del=re.pile(r'(\/\(\s|.)?\\/)').sub("",argument).strip().replace(u"\x00",'').lower()
        if re.pile(r'^aess.').match(argument_del) or re.pile(r'^.@.on.').match(argument_del) or re.pile(r'^grant.').match(argument_del):
          tmpargument=argument_del.strip()
        else:
          tmpargument=argument_del.split(' ')[0].strip()
          if len(tmpargument)>30:
            #有些sql是u'select\n\t\t\t\t\tcount(m.enquirymainid)'，可以使用print repr(tmpargument)
            tmpargument=argument_del.split('\n')[0].strip()
        #如果全是注释，那么就不统计这条目了
        if not tmpargument or tmpargument.strip()=='' or tmpargument.strip()==' ':
          continue
        if allhash.has_key(user_host):
          allhash[user_host][tmpargument]=allhash[user_host].get(tmpargument,0)+1
        else:
          allhash[user_host]={tmpargument:1}
      print "step 3,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
      headhtml='''
      
      '''
      print "step 4,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
      with open(htmlfile,'w') as htmlfileobj:
        htmlfileobj.write(headhtml)
        htmlfileobj.flush()
      print "step 5,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
      with open(htmlfile,'a') as htmlfileobj:
        for hostkey in allhash.keys():
          listtmp=sorted(allhash[hostkey].iteritems(),key=lambda labkey:labkey[1],reverse=True)
          rowspan=len(allhash[hostkey])
          #htmlfileobj.write()
          tmpline='' %(rowspan,hostkey.encode('utf-8'))
          htmlfileobj.write(tmpline)
          countn=0
          for runsql,count in listtmp:
            if countn==0:
              tmpline='' %(runsql.encode('utf-8'),count)
            else:
              tmpline='' %(runsql.encode('utf-8'),count)
            countn+=1
            htmlfileobj.write(tmpline)
        tmpline='''
用户  执行sql  执行次数
%s  %s  %s
%s  %s
'''
        htmlfileobj.write(tmpline)
      with open(htmlfile,'r') as htmlfileobj:
        mailcontent=htmlfileobj.read()
      sendHtmlMail(mailcontent,myip)
    else:
      print 'sql result is None,exit ing'
    print "step 6,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")