mysql日志处理_py

统计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 -*-

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每日执行记录的脚本

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 -*-

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")

每天将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\""