MySQL 执行 SQL 及慢查询监控
前言
MySQL 可以记录用户执行的sql:记录到文件、表格
MySQL 可以自定义执行多少时间以上的sql属于慢查询,也会根据配置,记录相关信息到文件、表格
目前公司想监控记录每天执行了哪些sql,哪些sql是慢查询,然后去优化sql
技术说明
1)搞清楚mysql是怎样记录执行sql的
2)怎样记录慢查询的
3)接下来就是写代码去梳理成报告,这里使用的是python语言
报告效果如下
技术细节
1、修改my.cnf
#整体的效果,全局开启表和日志文件都写,对于general_log, slow_query_log,表和日志文件都记录。
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; # 不能使用innodb的形式 set global general_log = on;
#默认general_log是csv的格式,修改为MyISAM格式查询效率会高很多
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表
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的表
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之中
# 做定时任务每天执行一次 mysql_logtable.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 脚本统计
python脚本统计每天sql操作,以及每天的mysql的慢查询
5.1 统计mysql每日执行记录的脚本
# -*- 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的脚本
# -*- 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 有一定的了解。
参考推荐:
mysql执行sql及慢查询监控 (51CTO)
MySQL 中 distinct 和 group by 性能比较
版权所有: 本文系米扑博客原创、转载、摘录,或修订后发表,最后更新于 2021-01-11 15:28:38
侵权处理: 本个人博客,不盈利,若侵犯了您的作品权,请联系博主删除,莫恶意,索钱财,感谢!