python3 mysql报警日志_Python-mysql日志分析

#!/usr/bin/python

# -*- coding:utf-8 -*-

import re

import sys

import time

import MySQLdb

def create_table():

db=MySQLdb.connect(host="localhost", user="root", passwd="mysql", db="slow_log")

cursor=db.cursor()

cursor.execute("DROP TABLE IF EXISTS `mysql_slow_log`;")

sql="""CREATE TABLE `mysql_slow_log` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`IP_HOST` varchar(150) 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` LongText 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(‘mysql-slow.log‘)

db=MySQLdb.connect("localhost","root","mysql","slow_log")

cursor=db.cursor()

content=‘‘

for line in log_file.readlines():

line=line.strip(‘\n‘)

content=content+line

re_mysql = re.findall(‘#.*?User@Host: (.*?)#.*?Query_time: (.*?) Lock_time: (.*?) Rows_sent: (.*?) Rows_examined: (.*?)SET.*?timestamp=(.*?);(.*?);‘, content, re.I);

for record in re_mysql:

IP_HOST=record[0].strip()

Query_time=record[1].strip()

Lock_time=record[2].strip()

Rows_sent=record[3].strip()

Rows_examined=record[4].strip()

timestamp=int(record[5])

timeArray=time.localtime(timestamp)

sql_time=time.strftime("%Y-%m-%d %H:%M:%S", timeArray)

slow_sql=record[6].strip()

set_charset="set names utf8"

sql = """INSERT INTO mysql_slow_log(IP_HOST,Query_time,Lock_time,Rows_sent,Rows_examined,sql_time,slow_sql)

VALUES (‘"""+IP_HOST+"""‘,"""+Query_time+""",‘"""+Lock_time+"""‘,"""+Rows_sent+""","""+Rows_examined+""",‘"""+sql_time+"""‘,\""""+slow_sql+"""\;\")""";

try:

cursor.execute(set_charset)

cursor.execute(sql)

print sql

db.commit()

except:

db.rollback()

log_file.close()

db.close()

def main():

create_table()

insert_table()

main()