分析Mysql慢日志脚本及快速定位慢Sql
#!/usr/bin/python#coding=utf-8#字符编码
import re#导入正则匹配模块
import sys#导入sys模块
import time #导入时间模块
import MySQLdb #导入连接mysql模块
canshu=len(sys.argv) #参数个数
def help():
#帮助函数
print "分析当天慢日志执行命令python %s today today" %sys.argv
print "分析以前慢日志执行命令python %s before 日志名字" %sys.argv
def create_table():
# 打开数据库连接
db=MySQLdb.connect("192.168.1.10","mysql_slow","mysql_slow","log_fenxi")
# 使用cursor()方法获取操作游标
cursor=db.cursor()
# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS `mysql_slow_log`;")
# 创建数据表SQL语句
sql="""CREATE TABLE `mysql_slow_log` (
`id` int(11)unsigned NOT NULL AUTO_INCREMENT,
`IP` varchar(15) NOT NULL,
`Query_time` float(11,6) NOT NULL,
`Lock_time` char(11) NOT NULL,
`Rows_sent` int(11) NOT NULL,
`Rows_examined` int(11) NOT NULL,
`sql_time` datetime NOT NULL,
`slow_sql` text NOT NULL,
PRIMARY KEY (`id`),
KEY `Query_time` (`Query_time`),
KEY `Rows_examined` (`Rows_examined`),
KEY `sql_time` (`sql_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
cursor.execute(sql)
# 关闭数据库连接
db.close()
def insert_table():
log_file=open(log_name) #读取慢日志
# 打开数据库连接
db=MySQLdb.connect("192.168.1.10","mysql_slow","mysql_slow","log_fenxi")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
content=''#默认匹配正则文本
for line in log_file.readlines(): #把日志文件一次性读入内存，以列表方式显示
line=line.strip('\n') #删除空白行回车
content =content+line #添加到coneent默认文本
re_mysql = re.findall('@\s+\[(.*?)\]#.*?Query_time: (.*?) Lock_time: (.*?) Rows_sent: (.*?)Rows_examined: (.*?) Rows_affected: (.*?)Rows_read: (.*?)#.*?timestamp=(.*?);(.*?);',content,re.I);#具体匹配慢日志需要的参数,re.I忽略大小写
for record in re_mysql:
IP=record.strip()
Query_time=record.strip()
Lock_time=record.strip()
Rows_sent=record.strip()
Rows_examined=record.strip()
timestamp=int(record)
timeArray=time.localtime(timestamp)
sql_time=time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
slow_sql=record.strip()
set_charset="set names utf8"
sql = """INSERT INTO mysql_slow_log(IP,Query_time,Lock_time,Rows_sent,Rows_examined,sql_time,slow_sql)
VALUES ('"""+IP+"""',"""+Query_time+""",'"""+Lock_time+"""',"""+Rows_sent+""","""+Rows_examined+""",'"""+sql_time+"""',\""""+slow_sql+"""\;\")""";
try:
# 执行sql语句
cursor.execute(set_charset)
cursor.execute(sql)
print sql
# 提交到数据库执行
db.commit()
except:
# Rollback in case there is any error
db.rollback()
log_file.close()
# 关闭数据库连接
db.close()
def main():
global log_name
if canshu!=3:
print "参数数量错误,请检查!"
help()
else:
create_table()
xuanze=sys.argv #第一个参数(慢日志时间)
log_before=sys.argv #慢日志具体时间
if xuanze=='today':
log_name='/data/mysqlp/mysql_slow.log'
insert_table()
elif xuanze=='before':
log_name='/data/logs/mysql_slow/%s' %log_before
insert_table()
else:
print '参数类型选择错误,类型只包含today|before'
help()
main()
页:
[1]