Python-过滤Mysql慢日志_mysql

# coding: utf-8 import datetime from optparse import OptionParser  MAX_QUERY_TIME_SECONDS = 10 START_DATETIME = datetime.datetime.min END_DATETIME = datetime.datetime.max SLOW_LOG_PATH = ""   class EventInfo(object):     def __init__(self):         self.exec_time = None         self.login_user = ""         self.query_time_seconds = 0         self.lock_time_seconds = 0         self.rows_sent = 0         self.rows_examined = 0         self.command_text = ""         self.file_line_num = 0   def get_datetime_by_time_line(time_line):     if time_line.startswith("# Time:"):         time_str = "20" + time_line.replace("# Time:", "").replace("  ", " ").strip()         return datetime.datetime.strptime(time_str, "%Y%m%d %H:%M:%S")     else:         return None   def set_query_info(event_info, query_info_line):     line = query_info_line     if line.startswith("# Query_time:"):         tmp_line = line.replace("#", "").replace(": ", ":").replace("  ", " ").replace("  ", " ")         tmp_items = tmp_line.split(" ")         for tmp_item in tmp_items:             # print(tmp_item)             if tmp_item.strip().startswith("Query_time:"):                 event_info.query_time_seconds = float(tmp_item.replace("Query_time:", ""))             if tmp_item.strip().startswith("Lock_time:"):                 event_info.lock_time_seconds = float(tmp_item.replace("Lock_time:", ""))             if tmp_item.strip().startswith("Rows_sent:"):                 event_info.rows_sent = float(tmp_item.replace("Rows_sent:", ""))             if tmp_item.strip().startswith("Rows_examined:"):                 event_info.rows_examined = float(tmp_item.replace("Rows_examined:", ""))   def set_login_user(event_info, login_user_line):     line = login_user_line     if line.startswith("# User@Host:"):         event_info.login_user = line.replace("# User@Host:", "").strip()   def set_command_text(event_info, command_text_line):     line = command_text_line     event_info.command_text += chr(13) + line   def print_event_info(event_info):     print_info = """ file_line_num:      {7} Time:               {0} User@Host:          {1} Query_time:         {2:.5f} seconds Lock_time:          {3:.5f} seconds Rows_sent:          {4} Rows_examined:      {5} command_text: {6} """.format(event_info.exec_time,            event_info.login_user,            event_info.query_time_seconds,            event_info.lock_time_seconds,            event_info.rows_sent,            event_info.rows_examined,            event_info.command_text,            event_info.file_line_num)     print("*" * 70)     print(print_info)   def get_user_paras():     try:         opt = OptionParser()         opt.add_option('--slow_log',                        dest='slow_log',                        type=str,                        help='the file path of the slow log ')         opt.add_option('--long_query_seconds',                        dest='long_query_seconds',                        type=float,                        help='long query seconds')         opt.add_option('--start_datetime',                        dest="start_datetime",                        type=str,                        help="start datetime, format is %Y-%m-%d %H:%M:%S")         opt.add_option('--end_datetime',                        dest="end_datetime",                        type=str,                        help="end datetime,  format is %Y-%m-%d %H:%M:%S")         (options, args) = opt.parse_args()         error_messages = []         slow_log = options.slow_log         start_datetime = options.start_datetime         long_query_seconds = options.long_query_seconds         end_datetime = options.end_datetime         if not slow_log:             error_messages.append("slow_log must be set;")         else:             global SLOW_LOG_PATH             SLOW_LOG_PATH = slow_log         if not long_query_seconds:             error_messages.append("long_query_seconds must be set;")         else:             global MAX_QUERY_TIME_SECONDS             MAX_QUERY_TIME_SECONDS = long_query_seconds         try:             if start_datetime is not None:                 tmp_start_datetime = datetime.datetime.strptime(start_datetime, "%Y-%m-%d %H:%M:%S")                 global START_DATETIME                 START_DATETIME = tmp_start_datetime         except:             error_messages.append("the format of start_datetime was wrong ;")         try:             if end_datetime is not None:                 tmp_end_datetime = datetime.datetime.strptime(end_datetime, "%Y-%m-%d %H:%M:%S")                 global END_DATETIME                 END_DATETIME = tmp_end_datetime         except:             error_messages.append("the format of end_datetime was wrong ;")          if len(error_messages) > 0:             opt.print_help()             for error_message in error_messages:                 print(error_message)             return False         else:             return True     except Exception as ex:         print("exception :{0}".format(str(ex)))         return False   def read_log():     global MAX_QUERY_TIME_SECONDS     file_path = SLOW_LOG_PATH     file_handler = open(file_path)     line = file_handler.readline()     line_num = 1     find_event_count = 0     current_event_info = EventInfo()     current_time = ""     has_time = False     while line:         if line.startswith("# Time:"):             current_time = get_datetime_by_time_line(line)             if current_time < START_DATETIME:                 line = file_handler.readline()                 line_num += 1                 continue             if current_time > END_DATETIME:                 break              line = file_handler.readline()             line_num += 1             has_time = True             continue         if has_time:             if line.startswith("# User@Host:"):                 if current_event_info.query_time_seconds > MAX_QUERY_TIME_SECONDS:                     print_event_info(event_info=current_event_info)                     find_event_count += 1                 current_event_info = EventInfo()                 current_event_info.file_line_num = line_num                 current_event_info.exec_time = current_time                 set_login_user(event_info=current_event_info,                                login_user_line=line)             elif line.startswith("# Query_time:"):                 set_query_info(event_info=current_event_info,                                query_info_line=line)             else:                 set_command_text(event_info=current_event_info,                                  command_text_line=line)         line = file_handler.readline()         line_num += 1     print("*" * 70)     print("parse completely")     print("find {0} events".format(find_event_count))   def main():     if get_user_paras():         read_log()   if __name__ == '__main__':     main()

C:/Python27/python.exe D:/GGA/Code/slow_query_find/find_slow_quer.py --slow_log="D:/slow.log.2016010900" --long_query_seconds=4 --start_datetime="2016-01-08 15:46:00" --end_datetime="2016-01-08 15:50:00"