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格式查询效率会高很多
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每日执行记录的脚本
#-*- coding: utf-8 -*-
__author__ = 'river'
importMySQLdb as mysqlimportrefrom datetime importdatetime, timedeltaimportsmtplibfrom email.mime.text importMIMETextdefsendHtmlMail(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()exceptException, 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 inmyiplist:
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()exceptException, e:print e,'conn mysql error'user_host_set=set()print "step 2,"+myip+','+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
allhash={}ifresult:for user_host,argument inresult:
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
ifallhash.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 inallhash.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=0for runsql,count inlisttmp:if countn==0:
tmpline='' %(runsql.encode('utf-8'),count)else:
tmpline='' %(runsql.encode('utf-8'),count)
countn+=1htmlfileobj.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")
转载:http://m.gxlcms.com/mysql-308670.html