MySQL查询与索引
  • [MySQL查询与索引]mysql 索引类型以及创建
  • [MySQL查询与索引]Mysql索引全介绍
  • [MySQL查询与索引]MySQL索引类型
  • [MySQL查询与索引]mysql创建索引
  • [MySQL查询与索引]MYSQL主键基础介绍
  • [MySQL查询与索引]mysql执行sql及慢查询监控
  • [MySQL查询与索引]mysql LOAD语句批量录入数据
  • [MySQL查询与索引]MYSQL中获取得最后一条记录的语句
  • [MySQL查询与索引]MySQL 字符串和扩展正则表达式模式匹配
  • 直达腾讯云服务器
    腾讯云服务器,数据库,短信等热销产品限量秒杀;新购爆款3折起;
    腾讯云数据库MySQL入门机型仅12元/月
    腾讯云云服务器新购特惠,最低2折起,1核1G3年仅794.73元,即0.73元/日。
    腾讯云国际顶级CA机构SSL证书8.8折,云解析买一年最高送半年!
    腾讯云为数百万企业和开发者提供安全、稳定的云服务器、云数据库、CDN等云服务
    腾讯云服务器安全可靠高性能,多种配置供您选择
    腾讯云数据库性能卓越稳定可靠,为您解决数据库运维难题
    阿里云服务器入口
    180天免费用腾讯云服务器马上领取
    您当前位置:狗鱼MySQL教程 >> MySQL查询与索引 >> mysql执行sql及慢查询监控
    mysql执行sql及慢查询监控
    更新日期: 2018年05月25日 来源: 本站原创 作者: 佚名 阅读:
    上一篇MYSQL主键基础介绍 下一篇:mysql LOAD语句批量录入数据

     

    【前言】

    mysql可以记录用户执行的sql:记录到文件、表格

    mysql可以定义执行多少时间以上得sql属于慢查询,也会根据配置,记录相关信息到文件、表格

    【背景说明】

    公司想监控记录每天执行了哪些sql,哪些sql是慢查询,然后去优化sql

    【技术说明】

    其实只要搞清楚了mysql怎样记录执行sql的

    怎样记录慢查询的即可

    接下来就是写代码去梳理成报告,我这里使用的是python

     

    【技术细节】

    1、修改my.cnf

    #整体的效果,全局开启表和日志文件都写,对于general_log,slow_query_log,表和日志文件都记录。

    1
    2
    general_log=1#开启mysql执行sql的日志
    slow_query_log=1#开启mysql慢sql的日志

    #设置之后会影响general_log和slow_query_log,

    1
    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

    1
    2
    long_query_time=1#设置mysql的慢查询为超过1s的查询
    slow_query_log_file=/log/slow.log

    2、修改mysql的日志表(在mysql库中)的格式

    #默认general_log是csv的格式,修改为MyISAM格式查询效率会高很多

    1
    2
    3
    set global general_log = off;
    alter table general_log engine = MyISAM;#不能使用innodb的形式
    set global general_log = on;

    #默认general_log是csv的格式,修改为MyISAM格式查询效率会高很多

    1
    2
    3
    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的表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    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的表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    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之中
     

    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
    #做定时任务每天执行一次
    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 ='%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='''='%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='''         #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                        '''
                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=''+str(start_time)+''+user_host+''+str(query_time)+''+str(lock_time)+''+str(rows_sent)+''+sql+''
                        htmlfileobj.write(tmpstring)
                with open(htmlfile,'a') as htmlfileobj:
                    tmpline=''''''
                    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日志记录集python有一定的了解。

     

    感觉本站内容不错,读后有收获?

    我要小额赞助,鼓励提供更好的内容教程

    上一篇MYSQL主键基础介绍 下一篇:mysql LOAD语句批量录入数据
    Copyright 2013-2017 Powered by 狗鱼MySQL教程,All Rights Reserved.
    广州相如计算机科技有限有限公司 版权所有 粤ICP备08130661号-4
    售前咨询:020-38667011 手机:13711588918