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

1、修改my.cnf

sql" id="highlighter_114855">

									#整体的效果，全局开启表和日志文件都写，但是对于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

2、修改mysql的日志表（在mysql库中）的格式

#默认general_log是csv的格式，修改为MyISAM格式查询效率会高很多

set global general_log = off;

alter table general_log engine = MyISAM;

set global general_log = on;

#默认slow_query_log是csv的格式，修改为MyISAM格式查询效率会高很多

set global slow_query_log = off;等于0效果一样

alter table slow_log engine = MyISAM;

set global slow_query_log = on;等于1效果一样

3、因为mysql的日志表：general_log和slow_query_log不允许修改，所以需要新建出一个便于删除修改的表（这个日志表太大，需要定期清理n天前得数据）

建立slow_log_dba的表

									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';

建立general_log_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,

									 `command_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';

4、因为程序最终使用的general_log_dba和slow_log_dba的表，所以需要定时的将general_log和slow_query_log的数据拷贝到general_log_dba和slow_log_dba之中

因为报告是每天生成一次，所以这个动作只要每天操作一次即可

#脚本是保存10天得数据，每天将general_log和slow_query_log的数据拷贝到general_log_dba和slow_log_dba之中

#做定时任务每天执行一次 mysqllogtable.sh

#!/bin/sh

NDaysAgo=$(date -d '-10 days' "+%F %H:%M:%S")

/usr/local/mysql/bin/mysql -uXXXX -p'xxxxxxxx' -D'mysql' -e "insert general_log_dba select * from general_log;

truncate general_log;

delete from general_log_dba where event_time < \"$NDaysAgo\";

insert slow_log_dba select * from slow_log;

truncate slow_log;

delete from slow_log_dba where start_time < \"$NDaysAgo\""

5、python脚本写统计每天sql操作和每天的mysql的慢查询(脚本中有部分是可以抽象的方法，请自己酌情处理)

统计mysql每日执行记录的脚本

100

101

									# -*- 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.com'

									    receiver = ['xxx@xxx.com']

									    subject = myip+' mysql operation report '+yestoday

									    smtpserver = 'smtp.exmail.xx.com'

									    username = 'xxx@xxx.com'

									    password = 'xxxxx'

									    msg = MIMEText(mailcontent,'html','utf-8')#'你好','text','utf-8'

									    msg['Subject'] = subject

									    msg['From'] = sender

									    msg['To'] = 'xxx@xxxxxxxx.com'

									    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_delcom=re.compile(r'(\/\*(\s|.)*?\*\/)').sub("",argument).strip().replace(u"\x00",'').lower()

									        if re.compile(r'^access.*').match(argument_delcom) or re.compile(r'^.*@.*on.*').match(argument_delcom) or re.compile(r'^grant.*').match(argument_delcom):

									          tmpargument=argument_delcom.strip()

									        else:

									          tmpargument=argument_delcom.split(' ')[0].strip()

									          if len(tmpargument)>30:

									            #有些sql是u'select\n\t\t\t\t\tcount(m.enquirymainid)'，可以使用print repr(tmpargument)

									            tmpargument=argument_delcom.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")