/mysql_watcher/mysql_logger_deploy_sb
Python | 222 lines | 120 code | 41 blank | 61 comment | 9 complexity | 405cc0e8cb646d404adc487b1cfe9165 MD5 | raw file
1#!/usr/bin/env python 2# 3# $LicenseInfo:firstyear=2010&license=mit$ 4# 5# Copyright (c) 2010, Linden Research, Inc. 6# 7# Permission is hereby granted, free of charge, to any person obtaining a copy 8# of this software and associated documentation files (the "Software"), to deal 9# in the Software without restriction, including without limitation the rights 10# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 11# copies of the Software, and to permit persons to whom the Software is 12# furnished to do so, subject to the following conditions: 13# 14# The above copyright notice and this permission notice shall be included in 15# all copies or substantial portions of the Software. 16# 17# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 18# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 19# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 20# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 21# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 22# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN 23# THE SOFTWARE. 24# $/LicenseInfo$ 25# 26 27""" 28Log all queries hitting a particular mysql database 29Dump all queries into streambase 30""" 31 32try: 33 import psyco 34 psyco.full() 35except: 36 pass 37 38import getopt 39import os.path 40import re 41import socket 42import sys 43import time 44import md5 45import struct 46import time 47import errno 48 49from dblibs.dbutil import LLQueryStream, remote_mysql_stream 50from socket import * 51 52# Socket Params 53SB_HOST = 'query-profiler.lindenlab.com' 54SB_PORT = 15998 55BUFSIZ = 1024 56SB_ADDR = (SB_HOST, SB_PORT) 57 58def open_SB_socket(): 59 60 tcpSBSocket=socket(AF_INET, SOCK_STREAM) 61 try: 62 tcpSBSocket.connect(SB_ADDR) 63 except error, e: 64 print "Could not create socket connection to %s ", SB_ADDR 65 print str(e) 66 tcpSBSocket.close() 67 68 return tcpSBSocket 69 70 71def close_SB_socket(tcpSBSocket): 72 73 tcpSBSocket.close() 74 75 76def watch_host(query_stream, host): 77 "Watches query traffic for a particular host. Dumps queries into streambase." 78 79 tcpSBSocket = open_SB_socket() 80 81 # make output path 82 log_path = "./%s" % host 83 os.system("mkdir -p %s" % log_path) 84 85 done = 0 86 query_count = 0 87 88 while not done: 89 (event_type, query) = query_stream.getNextEvent() 90 event_num = None 91 event_time = None 92 93 if event_type == "QueryStart": 94 event_num = 1 95 event_time = query.mStartTime 96 elif event_type == "QueryResponse": 97 event_num = 2 98 event_time = query.mResponseTime 99 elif event_type == "Quit": 100 event_num = 3 101 event_time = "null" 102 103 if event_num is not None: 104 105 if event_num == 3: 106 query_clean = "null" 107 md5query = "null" 108 else: 109 query_clean = query.mData['query_clean'] 110 md5query = md5.new(query_clean).hexdigest() 111 112 event_hash = "%s:%s" % (query.mData['host'], query.mData['port']) 113 event_hash = md5.new(event_hash).digest()[:4] 114 event_hash = "%s" % struct.unpack("I", event_hash) 115 event_hash = event_hash[:4] 116 117 query_hash = "%s:%s:%s" % (query.mData['host'], query.mData['port'], query.mData['query_clean']) 118 query_hash = md5.new(query_hash).hexdigest() 119 120# query_no_whitespace = query.mData['query'].replace("\n", "") 121 122# query_test = 'dfsdf"sdf' 123# query_test_escaped = query_test.replace('"', '\\"') 124# print query_test_escaped 125# query_escaped = query_no_whitespace.replace('"', '\\"') 126# query_escaped = query_escaped.replace("'", "\\'") 127# query_escaped_quoted = '"' + query_escaped + '"' 128 129# query_no_whitespace = query_no_whitespace[:10] 130 131# print "unclean query %s" % (query.mData['query']) 132# print "query %s" % (query_no_whitespace) 133 134 dummy_query = "foo" 135 136 tuple = "%s^%s^%s^%s^%s^%s^%s^%s^%s^%s^%s^%s" % (host, event_num, event_hash, event_time, query.mData['host'], query.mData['port'], query.mData['host_clean'], query.mData['host_full'], query_clean, md5query, query_hash, dummy_query) 137# tuple = "%s^%s^%s^%s^%s^%s^%s^%s^%s^%s^%s^%s" % (host, event_num, event_hash, event_time, query.mData['host'], query.mData['port'], query.mData['host_clean'], "IVAN-RULES.lindenlab.com", query_clean, md5query, query_hash, dummy_query) 138 139# if query_count % 10 == 0: 140# print "skipping query %s" % (tuple) 141# query_count = query_count+1 142# continue 143 144 try: 145 # uncomment for debuggery 146 datestring = time.strftime ("%D %T").strip() 147 #print datestring, " : " , tuple 148 #query_error_file.write(tuple + "\n") 149 tcpSBSocket.send(tuple + "\n") 150 query_count = query_count+1 151# print "%s : query count = %s, connection table = %s" % (datestring, query_count, len (query_stream.mConnStatus)) 152 153# try: 154# query_log_file = open("%s/query_log.log" % log_path, "a") 155# datestring = time.strftime ("%D %T").strip() 156# query_log_file.write("%s: %s \n" % (datestring, tuple)) 157# except IOError: 158# print ("can't write to file %s", query_log_file) 159 if query_count % 1000000 == 0: 160 print "%s : query count = %s, connection table = %s" % (datestring, query_count, len (query_stream.mConnStatus)) 161 try: 162 query_error_file = open("%s/query_error.log" % log_path, "a") 163 query_error_file.write("%s query count = %s, connection table = %s \n" % (datestring, query_count, len (query_stream.mConnStatus))) 164 query_error_file.close() 165 except IOError: 166 print ("can't write to file %s", query_error_file) 167 168 except Exception, error: 169 count = 0 170 # retry the same query 5 times 171 while count < 1: 172 datestring = time.strftime ("%D %T").strip() 173 # find out what the error was, log to file 174 print "%s error #%s printing to socket: %s " % (datestring, count, tuple) 175 print str(error) 176 print "%s query count = %s, connection table = %s" % (datestring, query_count, len (query_stream.mConnStatus)) 177 178 try: 179 query_error_file = open("%s/query_error.log" % log_path, "a") 180 query_error_file.write("%s error #%s printing to socket: %s \n" % (datestring, count, tuple)) 181 query_error_file.write("%s \n" % str(error)) 182 query_error_file.write("%s query count = %s, connection table = %s \n" % (datestring, query_count, len (query_stream.mConnStatus))) 183 query_error_file.close() 184 except IOError: 185 print ("can't write to file %s", query_error_file) 186 # If we don't close and reopen the socket, it will stop accepting 187 # writes after the first error. 188 close_SB_socket(tcpSBSocket) 189 time.sleep(2) 190 tcpSBSocket = open_SB_socket() 191 count = count+1 192 print "Skipping query ... \n" 193# return 0 194 continue 195 196 continue 197 198 199 200 201 202 203 204if __name__ == "__main__": 205 opts, args = getopt.getopt(sys.argv[1:], "", ["host="]) 206 207 host = None 208 for o, a in opts: 209 if o in ("--host"): 210 host = a 211 if not host: 212 print "Specify a host using --host=" 213 sys.exit(1) 214 215 # Start up the stream from the target host and create a file 216 # that we can hand to LLQueryStream 217 query_stream_file = remote_mysql_stream(host) 218 query_stream = LLQueryStream(query_stream_file) 219 220 watch_host(query_stream, host) 221 222