Mysql sql慢查询监控脚本代码实例
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;
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 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")
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持猪先飞。
相关文章
- 本文给大家分享C#连接SQL数据库和查询数据功能的操作技巧,本文通过图文并茂的形式给大家介绍的非常详细,需要的朋友参考下吧...2021-05-17
- 这篇文章主要介绍了Mybatis Plus select 实现只查询部分字段的操作,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-09-01
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 这篇文章主要介绍了PostgreSQL判断字符串是否包含目标字符串的多种方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-02-23
- 这篇文章主要介绍了PostgreSQL TIMESTAMP类型 时间戳操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-26
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
- 这篇文章主要介绍了postgresql 实现多表关联删除操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-02
- 这篇文章主要介绍了mybatis-plus 处理大数据插入太慢的解决,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-12-18
- 这篇文章主要介绍了Postgresl 如何选择正确的关闭模式,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18
MyBatisPlus-QueryWrapper多条件查询及修改方式
这篇文章主要介绍了MyBatisPlus-QueryWrapper多条件查询及修改方式,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2022-06-27深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了Oracle使用like查询时对下划线的处理方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-16
- 这篇文章主要介绍了postgresql数据添加两个字段联合唯一的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 这篇文章主要介绍了解决mybatis-plus 查询耗时慢的问题,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-07-04
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 这篇文章主要介绍了Vscode上使用SQL的方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-01-26
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 这篇文章主要介绍了PostgreSQL 字符串处理与日期处理操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-01
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15