gistfile1.rb

#! /usr/bin/env ruby

$: << '/ops/lib/ruby'

require 'pcaplet'
require 'rubygems'
require 'mysql'

MYSQL_USER = 'ffs_readonly'
MYSQL_PASS = 'dar~quil6'
MYSQL_HOST = '10.21.222.15'
MYSQL_DB = 'ffs_production'

MYSQL_COM_QUERY = 0x03

db = Mysql.connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB)

mysqlsniff = Pcaplet.new('-s 65535 -i eth0')
mysqlsniff.add_filter Pcap::Filter.new('tcp port 3306', mysqlsniff.capture)

query = ''
query_length = 0

mysqlsniff.each_packet do |pkt|
  next unless pkt.tcp_data
  if query then
    if query.length == query_length and query[0,6] == 'SELECT' then
      $stderr.puts "#{query}"
      start = Time.now
      res = db.query(query)
      finish = Time.now
      $stderr.printf "#{res.num_rows} rows in %.2fs\n\n", finish - start

      query = nil
      query_length = 0
    else
      query << pkt.tcp_data
    end
  end

  if pkt.tcp_data[3] == 0 and pkt.tcp_data[4] == MYSQL_COM_QUERY then
    query_length = (pkt.tcp_data[0,3] + "\0").unpack('V')[0] - 1
    next if query_length < 1
    query = pkt.tcp_data[5..-1]
  end
end