mysql数据库延时监控(Mysql sql慢查询监控脚本代码实例)
mysql数据库延时监控
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" ) |
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持开心学习网。
原文链接:https://www.cnblogs.com/-wenli/p/13782341.html
- navicat连接mysql1045解决方法(Navicat 连接服务器端中的docker数据库的方法)
- mysql里修改密码命令(MySQL修改账号密码方法大全小结)
- mysql千万数据如何优化(MySQL千万级数据的表如何优化)
- mysql的索引及其介绍总结(浅析MysQL B-Tree 索引)
- docker运行redis并操作(Docker安装MySQL和Redis的方法步骤)
- navicat创建MySql定时任务的方法详解(navicat创建MySql定时任务的方法详解)
- mysql中的null和空值的区别(解决mysql使用not in 包含null值的问题)
- mysql数据表实例教程(mysql数据库入门第一步之创建表)
- mysql集群是用什么搭建的(MySQL5.7 集群配置的步骤)
- mysql8使用技巧(一篇学会MySQL 8.0 ROLE管理)
- mysql 时间戳获取(MySQL时间盲注的五种延时方法实现)
- mysql索引面试总结(Mysql数据库索引面试题程序员基础技能)
- MySQL执行事务的语法与流程详解(MySQL执行事务的语法与流程详解)
- 查看mysql支持的字符集(不可忽视的MySQL字符集)
- mysql 主从配置详解(MySQL 8.0.15配置MGR单主多从的方法)
- mysql模糊查询语句(mysql中like % %模糊查询的实现)
- 今天要穿什么颜色(今天要穿什么颜色的衣服最吉利)
- 一道高中题-求杯子的高度(一道高中题-求杯子的高度)
- 网坛停摆三巨头亏损惨重,费德勒跌幅88 纳达少赚2400万(网坛停摆三巨头亏损惨重)
- Beyond 版本《无人深空》主线任务攻略 阿特拉斯之道(版本无人深空主线任务攻略)
- 全球科技界最有钱大佬TOP 15 你知道几位(全球科技界最有钱大佬TOP)
- 2主力后腰缺阵 泰山队奇兵有望获重用,赛季0出场,迎来中超首秀(泰山队奇兵有望获重用)
热门推荐
- php实现session入库实例(PHP使用Session实现上传进度功能详解)
- pythonselenium自动化教程(python使用selenium实现批量文件下载)
- 能自动点赞的小程序(python实现QQ空间自动点赞功能)
- mybatissql解析(mybatis动态sql实现逻辑代码详解)
- pyqt5怎么在pycharm中安装(pycharm+PyQt5+python最新开发环境配置踩坑)
- docker安装镜像失败(阿里云镜像安装docker报错的问题及解决方案)
- 程序员职业该如何规划
- SqlServer 表单查询问题及解决方法(SqlServer 表单查询问题及解决方法)
- mysql乐观锁与mvcc(MySQL中的乐观锁,悲观锁和MVCC全面解析)
- django数据库查询条件(djang常用查询SQL语句的使用代码)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9