/mysql_watcher/mysql_logger_deploy_sb

  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