PageRenderTime 38ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 0ms

/mysql_watcher/mysql_logger_deploy_sb

https://bitbucket.org/lindenlab/apiary/
Python | 222 lines | 120 code | 41 blank | 61 comment | 19 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. Log all queries hitting a particular mysql database
  28. Dump all queries into streambase
  29. """
  30. try:
  31. import psyco
  32. psyco.full()
  33. except:
  34. pass
  35. import getopt
  36. import os.path
  37. import re
  38. import socket
  39. import sys
  40. import time
  41. import md5
  42. import struct
  43. import time
  44. import errno
  45. from dblibs.dbutil import LLQueryStream, remote_mysql_stream
  46. from socket import *
  47. # Socket Params
  48. SB_HOST = 'query-profiler.lindenlab.com'
  49. SB_PORT = 15998
  50. BUFSIZ = 1024
  51. SB_ADDR = (SB_HOST, SB_PORT)
  52. def open_SB_socket():
  53. tcpSBSocket=socket(AF_INET, SOCK_STREAM)
  54. try:
  55. tcpSBSocket.connect(SB_ADDR)
  56. except error, e:
  57. print "Could not create socket connection to %s ", SB_ADDR
  58. print str(e)
  59. tcpSBSocket.close()
  60. return tcpSBSocket
  61. def close_SB_socket(tcpSBSocket):
  62. tcpSBSocket.close()
  63. def watch_host(query_stream, host):
  64. "Watches query traffic for a particular host. Dumps queries into streambase."
  65. tcpSBSocket = open_SB_socket()
  66. # make output path
  67. log_path = "./%s" % host
  68. os.system("mkdir -p %s" % log_path)
  69. done = 0
  70. query_count = 0
  71. while not done:
  72. (event_type, query) = query_stream.getNextEvent()
  73. event_num = None
  74. event_time = None
  75. if event_type == "QueryStart":
  76. event_num = 1
  77. event_time = query.mStartTime
  78. elif event_type == "QueryResponse":
  79. event_num = 2
  80. event_time = query.mResponseTime
  81. elif event_type == "Quit":
  82. event_num = 3
  83. event_time = "null"
  84. if event_num is not None:
  85. if event_num == 3:
  86. query_clean = "null"
  87. md5query = "null"
  88. else:
  89. query_clean = query.mData['query_clean']
  90. md5query = md5.new(query_clean).hexdigest()
  91. event_hash = "%s:%s" % (query.mData['host'], query.mData['port'])
  92. event_hash = md5.new(event_hash).digest()[:4]
  93. event_hash = "%s" % struct.unpack("I", event_hash)
  94. event_hash = event_hash[:4]
  95. query_hash = "%s:%s:%s" % (query.mData['host'], query.mData['port'], query.mData['query_clean'])
  96. query_hash = md5.new(query_hash).hexdigest()
  97. # query_no_whitespace = query.mData['query'].replace("\n", "")
  98. # query_test = 'dfsdf"sdf'
  99. # query_test_escaped = query_test.replace('"', '\\"')
  100. # print query_test_escaped
  101. # query_escaped = query_no_whitespace.replace('"', '\\"')
  102. # query_escaped = query_escaped.replace("'", "\\'")
  103. # query_escaped_quoted = '"' + query_escaped + '"'
  104. # query_no_whitespace = query_no_whitespace[:10]
  105. # print "unclean query %s" % (query.mData['query'])
  106. # print "query %s" % (query_no_whitespace)
  107. dummy_query = "foo"
  108. 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)
  109. # 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)
  110. # if query_count % 10 == 0:
  111. # print "skipping query %s" % (tuple)
  112. # query_count = query_count+1
  113. # continue
  114. try:
  115. # uncomment for debuggery
  116. datestring = time.strftime ("%D %T").strip()
  117. #print datestring, " : " , tuple
  118. #query_error_file.write(tuple + "\n")
  119. tcpSBSocket.send(tuple + "\n")
  120. query_count = query_count+1
  121. # print "%s : query count = %s, connection table = %s" % (datestring, query_count, len (query_stream.mConnStatus))
  122. # try:
  123. # query_log_file = open("%s/query_log.log" % log_path, "a")
  124. # datestring = time.strftime ("%D %T").strip()
  125. # query_log_file.write("%s: %s \n" % (datestring, tuple))
  126. # except IOError:
  127. # print ("can't write to file %s", query_log_file)
  128. if query_count % 1000000 == 0:
  129. print "%s : query count = %s, connection table = %s" % (datestring, query_count, len (query_stream.mConnStatus))
  130. try:
  131. query_error_file = open("%s/query_error.log" % log_path, "a")
  132. query_error_file.write("%s query count = %s, connection table = %s \n" % (datestring, query_count, len (query_stream.mConnStatus)))
  133. query_error_file.close()
  134. except IOError:
  135. print ("can't write to file %s", query_error_file)
  136. except Exception, error:
  137. count = 0
  138. # retry the same query 5 times
  139. while count < 1:
  140. datestring = time.strftime ("%D %T").strip()
  141. # find out what the error was, log to file
  142. print "%s error #%s printing to socket: %s " % (datestring, count, tuple)
  143. print str(error)
  144. print "%s query count = %s, connection table = %s" % (datestring, query_count, len (query_stream.mConnStatus))
  145. try:
  146. query_error_file = open("%s/query_error.log" % log_path, "a")
  147. query_error_file.write("%s error #%s printing to socket: %s \n" % (datestring, count, tuple))
  148. query_error_file.write("%s \n" % str(error))
  149. query_error_file.write("%s query count = %s, connection table = %s \n" % (datestring, query_count, len (query_stream.mConnStatus)))
  150. query_error_file.close()
  151. except IOError:
  152. print ("can't write to file %s", query_error_file)
  153. # If we don't close and reopen the socket, it will stop accepting
  154. # writes after the first error.
  155. close_SB_socket(tcpSBSocket)
  156. time.sleep(2)
  157. tcpSBSocket = open_SB_socket()
  158. count = count+1
  159. print "Skipping query ... \n"
  160. # return 0
  161. continue
  162. continue
  163. if __name__ == "__main__":
  164. opts, args = getopt.getopt(sys.argv[1:], "", ["host="])
  165. host = None
  166. for o, a in opts:
  167. if o in ("--host"):
  168. host = a
  169. if not host:
  170. print "Specify a host using --host="
  171. sys.exit(1)
  172. # Start up the stream from the target host and create a file
  173. # that we can hand to LLQueryStream
  174. query_stream_file = remote_mysql_stream(host)
  175. query_stream = LLQueryStream(query_stream_file)
  176. watch_host(query_stream, host)