开心学习
当前位置：数据库 > > 正文

mysql数据库延时监控（Mysql sql慢查询监控脚本代码实例）

时间：2022-01-26 01:33:04类别：数据库

mysql数据库延时监控

Mysql sql慢查询监控脚本代码实例

1、修改my.cnf

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
    		•
  • #整体的效果，全局开启表和日志文件都写，但是对于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的表

  • ?
  • 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';
    		•

    • 建立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之中

    #做定时任务每天执行一次 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每日执行记录的脚本

  • ?
  • 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
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
    		•
  • # -*- coding: utf-8 -*-
  • __author__ = 'river'
  • import MySQLdb as mysql
  • import re
  • from datetime import datetime, timedelta
  • import smtplib
  • from email.mime.text import MIMEText
  • def 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 <='%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'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='''
  •       
  •       '''
  •       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")
    		•

    • 以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持开心学习网。

    原文链接：https://www.cnblogs.com/-wenli/p/13782341.html

    标签：
    mysql 慢查询 SQL 监控 脚本
    上一篇下一篇

    猜您喜欢

    热门推荐

    © 2021 开心学习