Scripts to send mysql slow query logs to anemometer for analysis

process_slow_logs.py

#!/usr/bin/env python

# Processes the files in the incoming directory for query analysis in anemometer
# Supply the db user/pass/host

import glob, os, shutil, sys
from subprocess import Popen

db_user = "anemometer"
db_pass = "password"
db_host = "mysqlserver"

if not os.path.exists('incoming/'):
  print "Incoming directory doesn't exist"
  sys.exit(1)

incoming_logs = glob.glob('incoming/*')

for log in incoming_logs:
  # import each log file into anemometer mysql database using pt-query-digest command
  if log.endswith('-slow.log'):
    hostname = os.path.basename(log[:-9]) #get filename only, and trim the -slow.log part to get the hostname
  else:
    if os.path.exists('rejected/'):
      shutil.move(log,'rejected/')
      break # it's not the log file we're looking for
    else:
      print "Log file name incorrect: %s" % log
      sys.exit(1)
  digest = Popen(["pt-query-digest", "--user=%s" % db_user, "--password=%s" % db_pass, "--review", "h=%s,D=slow_query_log,t=global_query_review" % db_host, "--review-history", "h=%s,D=slow_query_log,t=global_query_review_history" % db_host, "--no-report", "--limit=0%", "--filter= $event->{Bytes} = length($event->{arg}) and $event->{hostname}=\"%s\"" % hostname, "%s" % log ] )
  digest.wait() # wait for it to process - can take a while sometimes
  os.remove(log)

send_to_anemometer.sh

#!/bin/bash
# Send slow logs to anemometer for analysis

# Turn off the log
mysql -u root -p -Be "SET GLOBAL slow_query_log = 'OFF';"

# Move the file for transfering
mv /data/mysql/$(hostname)-slow.log{,-transfer}
scp /data/mysql/$(hostname)-slow.log-transfer anemometer@host:incoming/$(hostname)-slow.log
rm -f /data/mysql/$(hostname)-slow.log-transfer

# Turn on the log again
mysql -u root -p -Be "SET GLOBAL slow_query_log = 'ON';"
mysql -u root -p -Be "FLUSH LOGS;"