【前言】mysql可以记录用户执行的sql：记录到文件、表格mysql可以定义执行多少时间以上得sql属于慢查询，也会根据配置，记录相关信息到文件、表格【背景说明】公
【前言】
mysql可以记录用户执行的sql：记录到文件、表格
mysql可以定义执行多少时间以上得sql属于慢查询，也会根据配置，记录相关信息到文件、表格
【背景说明】
公司想监控记录每天执行了哪些sql，哪些sql是慢查询，然后去优化sql
【技术说明】
其实只要搞清楚了mysql怎样记录执行sql的
怎样记录慢查询的即可
接下来就是写代码去梳理成报告，我这里使用的是python
【最终效果如下】
【技术细节】
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.log2、修改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之中
#做定时任务每天执行一次mysqllogtable.sh#!/bin/shndaysago=$(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 5、python脚本写统计每天sql操作和每天的mysql的慢查询(脚本中有部分是可以抽象的方法，请自己酌情处理)5.1统计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 = '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 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='''nbsp;html> 用户 执行sql 执行次数
''' 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='%s ' %(rowspan,hostkey.encode('utf-8')) htmlfileobj.write(tmpline) countn=0 for runsql,count in listtmp: if countn==0: tmpline='%s %s
' %(runsql.encode('utf-8'),count) else: tmpline='%s %s
' %(runsql.encode('utf-8'),count) countn+=1 htmlfileobj.write(tmpline) tmpline='''
''' 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)