Mysql sql慢查询监控脚本代码实例
1、修改my.cnf
#整体的效果，全局开启表和日志文件都写，但是对于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格式查询效率会高很多
setglobalgeneral_log=off;
altertablegeneral_logengine=MyISAM;
setglobalgeneral_log=on;
#默认slow_query_log是csv的格式，修改为MyISAM格式查询效率会高很多
setglobalslow_query_log=off;等于0效果一样
altertableslow_logengine=MyISAM;
setglobalslow_query_log=on;等于1效果一样
3、因为mysql的日志表：general_log和slow_query_log不允许修改，所以需要新建出一个便于删除修改的表（这个日志表太大，需要定期清理n天前得数据）
建立slow_log_dba的表
CREATETABLE`slow_log_dba`( `start_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP, `user_host`mediumtextNOTNULL, `query_time`timeNOTNULL, `lock_time`timeNOTNULL, `rows_sent`int(11)NOTNULL, `rows_examined`int(11)NOTNULL, `db`varchar(512)NOTNULL, `last_insert_id`int(11)NOTNULL, `insert_id`int(11)NOTNULL, `server_id`int(10)unsignedNOTNULL, `sql_text`mediumtextNOTNULL )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='Slowlogfordba';
建立general_log_dba的表
CREATETABLE`general_log_dba`( `event_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP, `user_host`mediumtextNOTNULL, `thread_id`int(11)NOTNULL, `server_id`int(10)unsignedNOTNULL, `command_type`varchar(64)NOTNULL, `argument`mediumtextNOTNULL, KEY`user_host`(`user_host`(200)), KEY`event_time`(`event_time`) )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='generallogfordbaop';
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'-10days'"+%F%H:%M:%S")
/usr/local/mysql/bin/mysql-uXXXX-p'xxxxxxxx'-D'mysql'-e"insertgeneral_log_dbaselect*fromgeneral_log;
truncategeneral_log;
deletefromgeneral_log_dbawhereevent_time<\"$NDaysAgo\";
insertslow_log_dbaselect*fromslow_log;
truncateslow_log;
deletefromslow_log_dbawherestart_time<\"$NDaysAgo\""
5、python脚本写统计每天sql操作和每天的mysql的慢查询(脚本中有部分是可以抽象的方法，请自己酌情处理)
统计mysql每日执行记录的脚本
#-*-coding:utf-8-*-
__author__='river'
importMySQLdbasmysql
importre
fromdatetimeimportdatetime,timedelta
importsmtplib
fromemail.mime.textimportMIMEText
defsendHtmlMail(mailcontent,myip):
try:
yestoday=(datetime.now()-timedelta(days=1)).strftime("%Y-%m-%d")
sender='xxx@xxx.com'
receiver=['xxx@xxx.com']
subject=myip+'mysqloperationreport'+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()
exceptException,e:
printe,'sendmailerror'
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-%d00:00:00")
today=datetime.now().strftime("%Y-%m-%d00:00:00")
formyipinmyiplist:
sql="selectuser_host,argumentfromgeneral_log_dbawhereevent_time>='%s'andevent_time<='%s'"%(yestoday,today)
try:
dbcon=mysql.connect(host=myip,user='xxxxx',passwd='xxxxx',db='mysql',port=3306,charset='utf8')
cur=dbcon.cursor()
print"step1,"+myip+','+datetime.now().strftime("%Y-%m-%d%H:%M:%S")
cur.execute(sql)
result=cur.fetchall()
cur.close()
dbcon.close()
exceptException,e:
printe,'connmysqlerror'
user_host_set=set()
print"step2,"+myip+','+datetime.now().strftime("%Y-%m-%d%H:%M:%S")
allhash={}
ifresult:
foruser_host,argumentinresult:
argument_delcom=re.compile(r'(\/\*(\s|.)*?\*\/)').sub("",argument).strip().replace(u"\x00",'').lower()
ifre.compile(r'^access.*').match(argument_delcom)orre.compile(r'^.*@.*on.*').match(argument_delcom)orre.compile(r'^grant.*').match(argument_delcom):
tmpargument=argument_delcom.strip()
else:
tmpargument=argument_delcom.split('')[0].strip()
iflen(tmpargument)>30:
#有些sql是u'select\n\t\t\t\t\tcount(m.enquirymainid)'，可以使用printrepr(tmpargument)
tmpargument=argument_delcom.split('\n')[0].strip()
#如果全是注释，那么就不统计这条目了
ifnottmpargumentortmpargument.strip()==''ortmpargument.strip()=='':
continue
ifallhash.has_key(user_host):
allhash[user_host][tmpargument]=allhash[user_host].get(tmpargument,0)+1
else:
allhash[user_host]={tmpargument:1}
print"step3,"+myip+','+datetime.now().strftime("%Y-%m-%d%H:%M:%S")
headhtml='''
'''
print"step4,"+myip+','+datetime.now().strftime("%Y-%m-%d%H:%M:%S")
withopen(htmlfile,'w')ashtmlfileobj:
htmlfileobj.write(headhtml)
htmlfileobj.flush()
print"step5,"+myip+','+datetime.now().strftime("%Y-%m-%d%H:%M:%S")
withopen(htmlfile,'a')ashtmlfileobj:
forhostkeyinallhash.keys():
listtmp=sorted(allhash[hostkey].iteritems(),key=lambdalabkey:labkey[1],reverse=True)
rowspan=len(allhash[hostkey])
#htmlfileobj.write()
tmpline=''%(rowspan,hostkey.encode('utf-8'))
htmlfileobj.write(tmpline)
countn=0
forrunsql,countinlisttmp:
ifcountn==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)
withopen(htmlfile,'r')ashtmlfileobj:
mailcontent=htmlfileobj.read()
sendHtmlMail(mailcontent,myip)
else:
print'sqlresultisNone,exiting'
print"step6,"+myip+','+datetime.now().strftime("%Y-%m-%d%H:%M:%S")
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。