October 28, 2008

Show live mysql queries

Filed under: mysql, perl — Tags: , , , — lancevermilion @ 1:10 pm

Original code found at “”. I have stripped much of it and inserted a log of code from “” to make it a little nicer. I also added a lot of stuff via variables now.

Reference “” for more info on reading the pcap info.

The code will return something like this. Keep in mind the question mark has a value that is passed in with the command. I do not know how to strip out the binary around it so I choose to exclude it so I can at least get the SQL commands executed. For me this told me that the application was doing a nice SQL statement to start with then doing a not so friendly one after. I was able to go back to the developer and ask him to chase this down.

SELECT * FROM sys_log  WHERE  field1 = ?  AND  timestamp >= ? AND timestamp <= ? ORDER BY timestamp DESC LIMIT 20000
INSERT INTO log_entry (timestamp,log_type,source,description) VALUES (?, ?, ?, ?)
SELECT * FROM sys_log  WHERE  field1 = ? ORDER BY timestamp DESC LIMIT 20000
INSERT INTO log_entry (timestamp,log_type,source,description) VALUES (?, ?, ?, ?)

You will need libpcap-devel, libpcap, and perl modules (Net::Pcap, Net::PcapUtils, NetPacket::Ethernet, NetPacket::IP, NetPacket::TCP, Socket). Make sure you run “perl -c” on the script.

#!/usr/bin/perl -w
use strict;
use Net::Pcap;
use Net::PcapUtils;
use NetPacket::Ethernet qw(:strip);
use NetPacket::IP qw(:strip);
use NetPacket::Ethernet;
use NetPacket::TCP;
use Socket;
use constant MYSQL_PORT => 3306;

#   Script Version
my $ver = 0.11;

#   Remove the numbers for the command type codes you
#   do not want to see.
#   Beware binary data is not filtered just yet so you
#   might end up screwing up ascii display to your terminal
#   if the right set of binary characters pass through your screen.
#use constant COM_QUERY_TYPES => qw( 2 3 5 6 11 16 22 23 24 25 );
use constant COM_QUERY_TYPES => qw( 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 );

#   If you do not want to filter lines that only contain binary data
#   then change the value to 0;
my $filterbinary = 1;

#   MySQL Command codes
my %cmdcode = (
  0 => 'SLEEP',
  1 => 'QUIT',
  2 => 'INIT_DB',
  3 => 'QUERY',
  4 => 'FIELD_LIST',
  5 => 'CREATE_DB',
  6 => 'DROP_DB',
  7 => 'REFRESH',
  8 => 'SHUTDOWN',
  9 => 'STATISTICS',
  10 => 'PROCESS_INFO',
  11 => 'CONNECT',
  12 => 'PROCESS_KILL',
  13 => 'DEBUG',
  14 => 'PING',
  15 => 'TIME',
  17 => 'CHANGE_USER',
  18 => 'BINLOG_DUMP',
  19 => 'TABLE_DUMP',
  20 => 'CONNECT_OUT',
  22 => 'PREPARE',
  23 => 'EXECUTE',
  24 => 'LONG_DATA',
  25 => 'CLOSE_STMT',
  26 => 'RESET_STMT',
  27 => 'SET_OPTION',

my $err;

#   Use network device passed in program arguments or if no
#   argument is passed, determine an appropriate network
#   device for packet sniffing using the
#   Net::Pcap::lookupdev method

my $dev = $ARGV[0];
unless (defined $dev) {
    $dev = Net::Pcap::lookupdev(\$err);
    if (defined $err) {
        die 'Unable to determine network device for monitoring - ', $err;

#   Specify the MTU you want to use on teh interface
my $mtu = 1500;

#   Source/Destination filter
my $filter_string = "(dst";

#   Do not print these SQL query commands when displaying SQL queries;
#   Change $do_not_print to = ''; if you do not want to filter the printing
#   of the stuff listed below
my $do_not_print = "^SET NAMES|^SET character|^SHOW VARIABLE|^SHOW COLLATION|^SET autocommit|^SET sql_mode";

#   Number of packets to capture
#   Zero or a negative number means capture packets indefinitely
#   (or until an error occurs if the $to_ms argument of
#   the open_live method is set to 0)
#   100 = 100 packets to capture
my $count = -1;

#   Optimize the capture filter
#   0 = false (will not optimize)
#   1 = true  (will optimize)
my $optimize = 0;

#                             #
#                             #

#   Look up network address information about network
#   device using Net::Pcap::lookupnet - This also acts as a
#   check on bogus network device arguments that may be
#   passed to the program as an argument.
#   Note: The address and netmask are returned as integers
#         so you will need to convert them

my ($address, $netmask);
if (Net::Pcap::lookupnet($dev, \$address, \$netmask, \$err)) {
    die 'Unable to look up device information for ', $dev, ' - ', $err;
#   Convert the integer returned for the addresses to decimal ip addr format
my $Address = inet_ntoa(pack "N", $address);
my $Netmask = inet_ntoa(pack "N", $netmask);

#   Create packet capture object on device

my $object;
$object = Net::Pcap::open_live($dev, $mtu, 0, 0, \$err);
unless (defined $object) {
    die 'Unable to create packet capture on device ', $dev, ' - ', $err;

#   Compile and set packet filter for packet capture
#   object - For the capture of TCP packets with the SYN
#   header flag set directed at the external interface of
#   the local host, the packet filter of '(dst IP) && (tcp
#   [13] & 2 != 0)' is used where IP is the IP address of
#   the external interface of the machine.  For
#   illustrative purposes, the IP address of is
#   used in this example.

my $filter_compiled;
) && die 'Unable to compile packet capture filter';
Net::Pcap::setfilter($object, $filter_compiled) &&
    die 'Unable to set packet capture filter';

my $mysqlport = MYSQL_PORT;

# Print the header before the output is displayed
print "+------------------------------------------------+\n";
print "|   MySQL Live SQL command monitor (Ver. $ver)   |\n";
print "+------------------------------------------------+\n";
print "| Filter params:
|   Interface: $dev
|   IP Address: $Address
|   NetMask: $Netmask
|   MTU: $mtu
|   Src/Dst Filter: $filter_string
|   Read # of Packets: $count
|   SQL Command Types: @COM_QUERY_TYPES
|   MySQL Port: $mysqlport
print "+------------------------------------------------+\n";

#   Set callback function and initiate packet capture loop
Net::Pcap::loop($object, $count, \&process_packets, '' ) ||
    die 'Unable to perform packet capture';


sub process_packets {
  my ($user_data, $header, $packet) = @_;

  ## Strip the ethernet and IP headers
  my $tcp_obj = NetPacket::TCP->decode(ip_strip(eth_strip($packet)));

  ## Strip ethernet encapsulation of captured packet
  my $tcp_obj1 = NetPacket::Ethernet::strip($packet);

  ## Decode contents of TCP/IP packet contained within
  ## captured ethernet packet
  my $ip = NetPacket::IP->decode($tcp_obj1);
  my $tcp = NetPacket::TCP->decode($ip->{'data'});

  ## If dest_port (mysql port), grab the payload and parse it
  if ($tcp_obj->{dest_port} == MYSQL_PORT)
     my $data = $tcp_obj->{data};
     return unless $data;

     my $len = unpack('C3',$data);
     return unless $len > 0;

     my $type = unpack('C',substr($data,4));
     foreach (@COM_QUERY_TYPES)
       if ($type == $_)
          my $Data = substr($data,5);
          if ( $do_not_print ne '' )
            printf ( "SQL_CMD from %s to %s: \n  %s: %s\n\n", "$ip->{'src_ip'}", "$ip->{'dest_ip'}", "$cmdcode{$type}", "$Data" ) if $Data !~ /$do_not_print/;
            printf ( "SQL_CMD from %s to %s: \n  %s: %s\n\n", "$ip->{'src_ip'}", "$ip->{'dest_ip'}", "$cmdcode{$type}", "$Data" );

#   POD info for perldoc

=head1 NAME MySQL query sniffer

=head1 VERSION

 0.11 Lance V added support for all command types.
      Added filtering
      Added exit after n packets
      Added Compiled packet filter
      Added many features from

=head1 USAGE [interface]

interface is optional, defaulting to the interface returned by

Note: PCAP will require root/sudo access.

=head1 DESCRIPTION is a query sniffer for mysql.  It sniffs the network with
pcap, extracts queries from mysql packets, and prints them on standard


see: L
COM_QUERY packets look like this:

    0f 00 00 00
    "show databases"

The first three bytes are length, the fourth is the packet number for
this transaction.  I'm ignoring the packet number and only looking at
the length, to make sure it's nonzero before continuing.

The fifth byte is the command type.  QUERY is 03.  (A complete list
can be found in mysql header files).

The rest (in the case of QUERY packets) is the query string.

=head1 AUTHOR

 Lance Vermilion 
 Ian Kilgore 


Copyright 2007 iContact, all rights reserved.

This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.

=head1 TODO

1. Fix the binary + ascii output generated with the EXECUTE query. it should only show ascii ouput and filter the binary output.

2. Find out why the CLOSE_STMT does not show any statment for closing.

3. Inspect the errors that are returned convert them to human readable.

=head1 SEE ALSO



October 20, 2008

Tomcat 5.5 – JMX managmenet

Filed under: java, jmx, snmp, tomcat — Tags: , , , — lancevermilion @ 9:40 am

I have had the need to monitor Java webapps. There are two ways of doing this. One is via SNMP into the JVM and the other is via JMX. Once you make the configuration changes you will need to restart tomcat. Once tomcat is restarted you will want to make sure those ports were opened and are currently being listened on by java. Run “sudo /usr/sbin/lsof | grep :” then look for your port numbers you chose to bind SNMP/JMX to. If they are not listed then they did not bind and you are going to want to simplify your JAVA_OPTS down to just the bare basics + the port commands. Keep in mind applying these configuration to JAVA_OPTS means it will try to do these are start/stop of the JVM which will result in an error in your logs saying something like “the port is already bound”

Add this to your “/etc/sysconfig/tomcat5”. Comment out any JAVA_OPTS (if they are redundant to what is below) you have uncommented and move them to this one if you have extra values. Exclude the all after the snmp line if you only want SNMP monitoring.


JAVA_OPTS="$JAVA_OPTS -Dcatalina.ext.dirs=$CATALINA_HOME/shared/lib:$CATALINA_HOME/common/lib -Djava.awt.headless=true$CATALINA_HOME/conf/jmxremote.password$CATALINA_HOME/conf/jmxremote.access -Djava.rmi.server.hostname=adminsvr"


JAVA_OPTS="$JAVA_OPTS -Dcatalina.ext.dirs=$CATALINA_HOME/shared/lib:$CATALINA_HOME/common/lib -Djava.awt.headless=true -Djava.rmi.server.hostname=adminsvr"

# Specify the RMI server name which is used when RMI connect comes inbound. This is sent to the client connecting to the RMI server.


# Disable use of authentication (options: true/false). True requires passwd file and access file.

# Specifies the location of the password file for JMX management access. (chmod 600, chown user/group )$CATALINA_HOME/conf/jmxremote.password

# Specifies the location of the access file for JMX management access. (chmod 600, chown user/group )$CATALINA_HOME/conf/jmxremote.access

# Disable SSL support (I have not played with getting it setup yet) (options: true/false)

# Specify the JMX management port

# Enable local jmx management from the localhost

# Suppose to enable headless connection where X is required…aka forward jconsole from the server to your X server.


# Used to enable SNMP access to the JVM interface. If you use this you will need to enable the snmp.acl as well to set the community/access control

# /usr/java/jdk1.6.0_03/jre/lib/management/snmp.acl (you should already have a smp.acl.template installed as a part of your JDK) (chmod 600, chown user/group ). IF YOU HAVE A DIFFERENT JDK AND/OR LOCATION KEEP THAT IN MIND!!!

acl = {
      communities = public
      access = read-only
      managers = localhost,

# $CATALINA_HOME/conf/jmxremote.access (chmod 600, chown user/group ).

opennms     readonly
monitorRole readonly
controlRole readwrite

# $CATALINA_HOME/conf/jmxremote.password (chmod 600, chown user/group ).

opennms     OpenNMS
monitorRole tomcat
controlRole tomcat

# Need to add a proxy line so snmp proxy will hit the JMX snmp port when we query the server for the SNMP info. Get system and JMX info in one query.
proxy -v 2c -c cotssnmp localhost:9004 .

# Trouble shooting
A good set of tools to have are same JMX server/client java tools that you can run outside your tomcat JVM containers to make sure your system is accessable.

JMX Java Tutorial for MXBeans

Not sure what connect string to put in jconsole/etc. This is the default.

The default container name is jmxrmi
The JMX Service URL is:

October 15, 2008

Cingular/AT&T APN and authentication

Filed under: Cellular — Tags: , , — lancevermilion @ 9:21 am

Password: CINGULAR1
APN: wap.cingular


Password: CINGULAR1
APN: isp.cingular

October 14, 2008

MySQL add an index to a table.

Filed under: Uncategorized — lancevermilion @ 11:06 am

This is how to add a index to a table. Adding this single index for syslog data added about a index (idx) file of about 12% of the size of the sys_log table data size.
alter table sys_log add index (timestamp);

Referenced from:
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I use for the purpose so I can easily find it later, plus it is quite handy in a way it presents information:
mysql> SELECT concat(table_schema,'.',table_name),concat(round(table_rows/1000000,2),'M') rows,concat(round(data_length/(1024*1024*1024),2),'G') DATA,concat(round(index_length/(1024*1024*1024),2),'G') idx,concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT 10;
| concat(table_schema,'.',table_name) | rows | DATA | idx | total_size | idxfrac |
| art87.link_out87 | 37.25M | 14.83G | 14.17G | 29.00G | 0.96 |
| art87.article87 | 12.67M | 15.83G | 4.79G | 20.62G | 0.30 |
| art116.article116 | 10.49M | 12.52G | 3.65G | 16.18G | 0.29 |
| art84.article84 | 10.10M | 10.11G | 3.59G | 13.70G | 0.35 |
| art104.link_out104 | 23.66M | 6.63G | 6.55G | 13.18G | 0.99 |
| art118.article118 | 7.06M | 10.49G | 2.68G | 13.17G | 0.26 |
| art106.article106 | 9.86M | 10.19G | 2.76G | 12.95G | 0.27 |
| art85.article85 | 6.20M | 9.82G | 2.51G | 12.33G | 0.26 |
| art91.article91 | 8.66M | 9.17G | 2.66G | 11.83G | 0.29 |
| art94.article94 | 5.21M | 10.10G | 1.69G | 11.79G | 0.17 |
10 rows IN SET (2 min 29.19 sec)

Other good mysql links from

October 13, 2008

MySQL, InnoDB rename/copy data from one table to another

Filed under: mysql — Tags: , — lancevermilion @ 4:32 pm

Create a new table with exactly the same table structure as sys_log.
CREATE TABLE `sys_tmp` (
`timestamp` bigint(20) unsigned NOT NULL,
`field1` varchar(255) NOT NULL,
`field2` varchar(255) default NULL,
`field3` varchar(255) default NULL,
`field4` varchar(255) default NULL,
`field5` varchar(255) default NULL,
`field6` varchar(255) default NULL,
`field7` varchar(255) default NULL,
`field8` varchar(255) default NULL,
`time_sent` varchar(255) default NULL

Rename the original “sys_log” table to a new name “syslog_backup_table” and rename your temp syslog table (sys_tmp) to the orignal name syslog table name “sys_log”;
rename table sys_log TO syslog_backup_table, sys_tmp to sys_log;

insert data into sys_log from “syslog_backup_table” where the timestamp field is greater than x time.
insert into sys_log select * from syslog_backup_table where timestamp > 1220227200000;

Blog at