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.loglong_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’import MySQLdb as mysqlimport refrom datetime import datetime, timedeltaimport smtplibfrom email.mime.text import MIMETextdef sendHtmlMail(mailcontent,myip): try: yestoday=(datetime.now()-timedelta(days=1)).strftime('%Y-%m-%d') sender = ’[email protected]’ receiver = [’[email protected]’] subject = myip+’ mysql operation report ’+yestoday smtpserver = ’smtp.exmail.xx.com’ username = ’[email protected]’ password = ’xxxxx’ msg = MIMEText(mailcontent,’html’,’utf-8’)#’你好’,’text’,’utf-8’ msg[’Subject’] = subject msg[’From’] = sender msg[’To’] = ’[email protected]’ 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’selectntttttcount(m.enquirymainid)’，可以使用print repr(tmpargument) tmpargument=argument_delcom.split(’n’)[0].strip()#如果全是注釋，那么就不統計這條目了if not tmpargument or tmpargument.strip()==’’ or tmpargument.strip()==’ ’: continueif allhash.has_key(user_host): allhash[user_host][tmpargument]=allhash[user_host].get(tmpargument,0)+1else: 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 in allhash.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=0 for runsql,count in listtmp: if countn==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) 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')

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持好吧啦網。