mysql执行sql及慢查询监控

【前言】 mysql可以记录用户执行的sql：记录到文件、表格 mysql可以定义执行多少时间以上得sql属于慢查询，也会根据配置，记录相关信息到文件、表格 【背景说明】 公司想监控记录每天执行了哪些sql，哪些sql是慢查询，然后去优化sql 【技术说明】 其实只要搞

 相关mysql视频教程推荐：《mysql教程》 

【前言】

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.log

2、修改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/sh
NDaysAgo=$(date -d '-10 days' "+%F %H:%M:%S")
/usr/local/mysql/bin/mysql  -uXXXX  -p&#39;xxxxxxxx&#39; -D&#39;mysql&#39; -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 =&#39;%s&#39; and event_time 30:
                        #有些sql是u&#39;select\n\t\t\t\t\tcount(m.enquirymainid)&#39;，可以使用print repr(tmpargument)
                        tmpargument=argument_delcom.split(&#39;\n&#39;)[0].strip()
                #如果全是注释，那么就不统计这条目了
                if not tmpargument or tmpargument.strip()==&#39;&#39; or tmpargument.strip()==&#39; &#39;:
                    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+&#39;,&#39;+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            headhtml=&#39;&#39;&#39;=&#39;%s&#39; and start_time <=&#39;%s&#39; order by query_time desc limit 500" %(yestoday,today)
        try:
            dbcon = mysql.connect(host=myip, user=&#39;xxx&#39;, passwd=&#39;xxxxxx&#39;, db=&#39;mysql&#39;, port=3306,charset=&#39;utf8&#39;)
            cur = dbcon.cursor()
            print "step 1,"+myip+&#39;,&#39;+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            cur.execute(sql)
            result = cur.fetchall()
            cur.close()
            dbcon.close()
        except Exception, e:
            print e,&#39;conn mysql error&#39;
        print "step 2,"+myip+&#39;,&#39;+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        if result:
            headhtml=&#39;&#39;&#39;         #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
        }
                                                                                    执行时间                            用户                            查询时长/s                            加锁时长/s                            发送行数目/line                            执行sql                        &#39;&#39;&#39;
            with open(htmlfile,&#39;w&#39;) 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&#39;(\/\*(\s|.)*?\*\/)&#39;).sub("",sql_text)[0:150].replace(u"\x00",&#39;&#39;).strip()
                if not sql or sql.strip()==&#39;&#39; or sql.strip()==&#39; &#39;:
                    continue
                with open(htmlfile,&#39;a&#39;) as htmlfileobj:
                    tmpstring=&#39;&#39;+str(start_time)+&#39;&#39;+user_host+&#39;&#39;+str(query_time)+&#39;&#39;+str(lock_time)+&#39;&#39;+str(rows_sent)+&#39;&#39;+sql+&#39;&#39;
                    htmlfileobj.write(tmpstring)
            with open(htmlfile,&#39;a&#39;) as htmlfileobj:
                tmpline=&#39;&#39;&#39;&#39;&#39;&#39;
                htmlfileobj.write(tmpline)
            with open(htmlfile,&#39;r&#39;) as htmlfileobj:
                mailcontent=htmlfileobj.read()
            print "step 3,"+myip+&#39;,&#39;+datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            sendHtmlMail(mailcontent,myip)
        else:
            print &#39;sql result is None,exit ing&#39;
        print "step 4,"+myip+&#39;,&#39;+datetime.now().strftime("%Y-%m-%d %H:%M:%S")

【小结】

其实本功能技术难度不高，但是需要对mysql日志记录集python有一定的了解。