-
Notifications
You must be signed in to change notification settings - Fork 1
/
queryanalyse.py
173 lines (144 loc) · 6.28 KB
/
queryanalyse.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
# coding=UTF-8
import getopt
import re
import os
import sys
usage = ''' usage: python [script's path] [option]
ALL options need to assign:
-path : 读取日志文件和写入日志文件的路径 必须有
-f : 读取文件的名字 必须有 binlog 日志文件
-of : 写入文件 满足条件分析后的数据 如果没有默认名字 读取文件名.log
-td : 数据库的名称
-tt : 表的名称
Example: python queryanalyse.py -td=activity -tt=activity -path=mysqllog/ -f=mysql-bin.000268 -of=268123.txt
'''
class queryanalyse:
def __init__(self):
#初始化
self.readname = ''
self.outname = ''
self.path = ''
self.ofpath = ''
self.logpath = ''
self.search_td =''
self.search_tb =''
self.total_num = 0
self._get_db()
if self.path.endswith('/') == False:
self.path = self.path + '/'
if self.path == '' or self.readname == '':
print(usage)
sys.exit()
self.fpath = self.path + self.readname
if os.path.exists(self.fpath) == False:
print '*****************************************************\n'
print('ERROR ：文件不存在 ' + self.fpath + '\n')
print '*****************************************************\n'
sys.exit()
self.logpath = self.path + '/tmp.log'
# 初始化输出路径
if self.outname == '':
self.ofpath = self.path + self.readname + '.log'
else:
self.ofpath = self.path + self.outname
def _get_db(self):
#解析用户输入的选项参数值
if len(sys.argv) == 1:
print(usage)
sys.exit(1)
elif sys.argv[1] == '--help':
print(usage)
sys.exit()
elif len(sys.argv) > 2:
for i in sys.argv[1:]:
_argv = i.split('=')
if _argv[0] == '-f':
self.readname = _argv[1]
elif _argv[0] == '-of':
self.outname = _argv[1]
elif _argv[0] == '-path':
self.path = _argv[1]
elif _argv[0] == '-td':
self.search_td = _argv[1]
elif _argv[0] == '-tt':
self.search_tb = _argv[1]
else:
print(usage)
def rowrecord(self):
record_sql = ''
start_print = ''
isSave = False
isEffective = False
out_sql = ''
isEnd = False
with open(self.logpath,'r') as binlog_file ,open( self.ofpath, 'wt') as out_file:
for bline in binlog_file:
if bline.find('Table_map:') != -1:
l = bline.index('server')
n = bline.index('Table_map')
begin_time = bline[:l:].rstrip(' ').replace('#', '20')
begin_time = begin_time[0:4] + '-' + begin_time[4:6] + '-' + begin_time[6:]
db_name = bline[n::].split(' ')[1].replace('`', '').split('.')[0]
tb_name = bline[n::].split(' ')[1].replace('`', '').split('.')[1]
# print 'start time:' + begin_time
isEffective = False
isEnd = False
out_sql = '\nstart time:' + begin_time + '\n'
out_sql = out_sql + 'Database.Table: ' + db_name + '.' + tb_name + '\n'
elif bline.startswith('### INSERT INTO'):
# print bline.replace("\n", "")
isEffective = True
out_sql = out_sql + bline
elif bline.startswith('### UPDATE'):
# print bline.replace("\n", "")
isEffective = True
out_sql = out_sql + bline
elif bline.startswith('### UPDATE'):
# print bline.replace("\n", "")
isEffective = True
out_sql = out_sql + bline
elif bline.startswith('### DELETE FROM'):
# print bline.replace("\n", "")
isEffective = True
out_sql = out_sql + bline
elif bline.find('Xid =') != -1:
isEffective = False
isEnd = True
l = bline.index('server')
end_time = bline[:l:].rstrip(' ').replace('#', '20')
end_time = end_time[0:4] + '-' + end_time[4:6] + '-' + end_time[6:]
# print 'end time:' + end_time
elif bline.startswith('# at') == False and isEffective:
# print bline.replace("\n", "")
out_sql = out_sql + bline
if isEnd and ((self.search_tb == '' and self.search_td == '') or (self.search_td != '' and db_name == self.search_td) or (self.search_tb != '' and tb_name == self.search_tb)):
out_file.write(out_sql)
self.total_num = self.total_num + 1
# print out_sql
out_sql = ''
def mysqlbinlog(self):
os.system("mysqlbinlog -v --base64-output=DECODE-ROWS " + self.fpath + " > " + self.logpath)
def clearfile(self):
os.system("rm -f " + self.logpath)
def totalnum(self):
return bytes(self.total_num)
def outpath(self):
return self.ofpath
def main():
print '\n*******************************************\n'
print '************ 初始化数据 *****************\n'
query = queryanalyse()
print '****** 开始处理binlog日志信息 ***********\n'
query.mysqlbinlog()
print '******** 开始分析并输出文件 **************\n'
query.rowrecord()
print '*********** 清理无用文件 ****************\n'
query.clearfile()
print '*********** 数据分析完成 ****************\n'
print '*******************************************\n'
print '\n******************************************************************************'
print '\n**** 总条数为 ' + query.totalnum()
print '\n**** 输出路径 ' + query.outpath()
print '\n******************************************************************************'
if __name__ == "__main__":
main()