mysql执行sql及慢查询监控

432ewd

【前言】
mysql可以记录用户执行的sql：记录到文件、表格
mysql可以定义执行多少时间以上得sql属于慢查询，也会根据配置，记录相关信息到文件、表格
【背景说明】
公司想监控记录每天执行了哪些sql，哪些sql是慢查询，然后去优化sql
【技术说明】
其实只要搞清楚了mysql怎样记录执行sql的
怎样记录慢查询的即可
接下来就是写代码去梳理成报告，我这里使用的是python
【最终效果如下】




【技术细节】
1、修改my.cnf
#整体的效果，全局开启表和日志文件都写，但是对于general_log只写表，对于slow_query_log，表和日志文件都记录。

1 2	general_log=1#开启mysql执行sql的日志 slow_query_log=1#开启mysql慢sql的日志

#设置之后会影响general_log和slow_query_log，

1	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

1 2	long_query_time=1#设置mysql的慢查询为超过1s的查询 slow_query_log_file=/log/slow.log

2、修改mysql的日志表（在mysql库中）的格式
#默认general_log是csv的格式，修改为MyISAM格式查询效率会高很多

1 2 3

set global general_log = off; alter table general_log engine = MyISAM;#不能使用innodb的形式 set global general_log = on;

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

1 2 3

set global slow_query_log = off;等于0效果一样 alter table slow_log engine = MyISAM;#不能使用innodb的形式 set global slow_query_log = on;等于1效果一样

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

1 2 3 4 5 6 7 8 9 10 11 12 13

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

3.2建立general_log_dba的表

1 2 3 4 5 6 7 8 9 10

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之中

1 2 3 4 5

#做定时任务每天执行一次 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的慢查询(脚本中有部分是可以抽象的方法，请自己酌情处理)
5.1统计mysql每日执行记录的脚本

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118

# -*- 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='''<!DOCTYPE html><html class=" MacOS"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><style type="text/css">          #customers {             FONT-FAMILY: "Trebuchet MS", Arial, Helvetica, sans-serif; WIDTH: 100%; BORDER-COLLAPSE: collapse         }          #customers TD {             BORDER-TOP: #98bf21 1px solid; BORDER-RIGHT: #98bf21 1px solid; BORDER-BOTTOM: #98bf21 1px solid; PADDING-BOTTOM: 2px; PADDING-TOP: 3px; PADDING-LEFT: 7px; BORDER-LEFT: #98bf21 1px solid; PADDING-RIGHT: 7px         }          #customers TH {             BORDER-TOP: #98bf21 1px solid; BORDER-RIGHT: #98bf21 1px solid; BORDER-BOTTOM: #98bf21 1px solid; PADDING-BOTTOM: 2px; PADDING-TOP: 3px; PADDING-LEFT: 7px; BORDER-LEFT: #98bf21 1px solid; PADDING-RIGHT: 7px         }          #customers THEAD {             FONT-SIZE: 1.0em; COLOR: #fff; PADDING-BOTTOM: 4px; TEXT-ALIGN: left; PADDING-TOP: 5px; BACKGROUND-COLOR: #a7c942         }          #customers TR.alt TD {             COLOR: #000; BACKGROUND-COLOR: #eaf2d3         }         </style>             </head><body>             <table id="customers" align="center" style="width:85%;">                         <thead><tr align="left">                             <td>用户</td>                             <td>执行sql</td>                             <td>执行次数</td>                         </tr></thead><tbody>'''             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='<tr><td rowspan=%s align="left">%s</td>' %(rowspan,hostkey.encode('utf-8'))                     htmlfileobj.write(tmpline)                     countn=0                     for runsql,count in listtmp:                         if countn==0:                             tmpline='<td align="left">%s</td><td>%s</td></tr>' %(runsql.encode('utf-8'),count)                         else:                             tmpline='<tr><td align="left">%s</td><td>%s</td></tr>' %(runsql.encode('utf-8'),count)                         countn+=1                         htmlfileobj.write(tmpline)                 tmpline='''</tbody></table></html>'''                 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")

5.2统计mysql每日慢sql的脚本

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94

# -*- 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='mysqlSlowMon.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 start_time,user_host,query_time,lock_time,rows_sent,sql_text from slow_log_dba where start_time >='%s' and start_time <='%s' order by query_time desc limit 500" %(yestoday,today)         try:             dbcon = mysql.connect(host=myip, user='xxx', passwd='xxxxxx', 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'         print "step 2,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")         if result:             headhtml='''<!DOCTYPE html><html class=" MacOS"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><style type="text/css">          #customers {             FONT-FAMILY: "Trebuchet MS", Arial, Helvetica, sans-serif; WIDTH: 100%; BORDER-COLLAPSE: collapse         }          #customers TD {             BORDER-TOP: #98bf21 1px solid; BORDER-RIGHT: #98bf21 1px solid; BORDER-BOTTOM: #98bf21 1px solid; PADDING-BOTTOM: 2px; PADDING-TOP: 3px; PADDING-LEFT: 7px; BORDER-LEFT: #98bf21 1px solid; PADDING-RIGHT: 7px         }          #customers TH {             BORDER-TOP: #98bf21 1px solid; BORDER-RIGHT: #98bf21 1px solid; BORDER-BOTTOM: #98bf21 1px solid; PADDING-BOTTOM: 2px; PADDING-TOP: 3px; PADDING-LEFT: 7px; BORDER-LEFT: #98bf21 1px solid; PADDING-RIGHT: 7px         }          #customers THEAD {             FONT-SIZE: 1.0em; COLOR: #fff; PADDING-BOTTOM: 4px; TEXT-ALIGN: left; PADDING-TOP: 5px; BACKGROUND-COLOR: #a7c942         }          #customers TR.alt TD {             COLOR: #000; BACKGROUND-COLOR: #eaf2d3         }         </style>             </head><body>             <table id="customers" align="center" style="width:90%;">                         <thead><tr align="left">                             <td>执行时间</td>                             <td>用户</td>                             <td>查询时长/s</td>                             <td>加锁时长/s</td>                             <td>发送行数目/line</td>                             <td>执行sql</td>                         </tr></thead><tbody>'''             with open(htmlfile,'w') as htmlfileobj:                 htmlfileobj.write(headhtml)                 htmlfileobj.flush()             for start_time,user_host,query_time,lock_time,rows_sent,sql_text in result:                 sql=re.compile(r'(\/\*(\s|.)*?\*\/)').sub("",sql_text)[0:150].replace(u"\x00",'').strip()                 if not sql or sql.strip()=='' or sql.strip()==' ':                     continue                 with open(htmlfile,'a') as htmlfileobj:                     tmpstring='<tr align="left"><td>'+str(start_time)+'</td><td>'+user_host+'</td><td>'+str(query_time)+'</td><td>'+str(lock_time)+'</td><td>'+str(rows_sent)+'</td><td>'+sql+'</td></tr>'                     htmlfileobj.write(tmpstring)             with open(htmlfile,'a') as htmlfileobj:                 tmpline='''</tbody></table></html>'''                 htmlfileobj.write(tmpline)             with open(htmlfile,'r') as htmlfileobj:                 mailcontent=htmlfileobj.read()             print "step 3,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")             sendHtmlMail(mailcontent,myip)         else:             print 'sql result is None,exit ing'         print "step 4,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")

【小结】
其实本功能技术难度不高，但是需要对mysql日志记录集python有一定的了解。