分享一个解析percona MySQL slowlog的python正则

直接上脚本把，有需要自取

import re
log="""# User@Host: root[root] @  [127.0.0.1]  Id:    83
# Schema:   Last_errno: 0  Killed: 0
# Query_time: 0.000086  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 57
SET timestamp=1536162668;
SELECT DATABASE();"""
slow_log_patern = '^#\s+User@Host:\s+(?P<user>\w+)\[\w+\]\s+@\s+' \
                  '(?P<proxyHost>(\w|\[|\]|\.|\s)+)\s+Id:\s+(?P<threadID>\d+)\n' \
                  '#\s+Schema:\s(?P<Schema>(\w+|\s))\s+' \
                  'Last_errno:\s(?P<lastErrNo>\d+)\s+Killed:\s(?P<KilledErrNo>\d+)\n' \
                  '#\s+Query_time:\s+(?P<queryTime>\w+\.?\w+)\s+' \
                  'Lock_time:\s+(?P<lockTime>\w+\.?\w+)\s+Rows_sent:\s+(?P<rowsSent>\d+)\s+' \
                  'Rows_examined:\s+(?P<rowsExamined>\d+)\s+Rows_affected:\s+(?P<rowsAffected>\d+)\n' \
                  '#\s+Bytes_sent:\s(?P<bytesSent>\d+)\n' \
                  'SET\s+timestamp=(?P<sqlTimestamp>\d+);\n' \
                  '(?P<sqlText>(.|\n)*)'

slow_log_regex = re.compile(slow_log_patern)
match = slow_log_regex.search(log)
if match:
    sql_dict = match.groupdict()
print sql_dict

输出结果

{'rowsExamined': '0', 'lastErrNo': '0', 'sqlTimestamp': '1536162668', 'queryTime': '0.000086', 'proxyHost': '[127.0.0.1] ', 'user': 'root', 'bytesSent': '57', 'threadID': '83', 'KilledErrNo': '0', 'lockTime': '0.000000', 'Schema': ' ', 'rowsSent': '1', 'sqlText': 'SELECT DATABASE();', 'rowsAffected': '0'}