Skip to content

Instantly share code, notes, and snippets.

@httpdss
Created April 29, 2011 14:34
Show Gist options
  • Save httpdss/948386 to your computer and use it in GitHub Desktop.
Save httpdss/948386 to your computer and use it in GitHub Desktop.
Mysql general log parser
#!/usr/bin/perl
use strict;
use Data::Dumper;
use Getopt::Long;
# author: Gavin Towey, 2008 gtowey@gmail.com
# todo, add "follow thread" capability
# so we can give a process name or thread id & see
# all activity in sequence for each thread
my %OPTIONS;
if (
!GetOptions( \%OPTIONS,
"help",
"type|t=s",
"pattern|p=s",
"preserve-newlines|n",
"separator|s=s" )
)
{
$OPTIONS{'help'}++;
}
if (!defined($OPTIONS{'type'})) {
$OPTIONS{'type'} = 'query';
} else {
$OPTIONS{'type'} = lc ($OPTIONS{'type'});
}
my $file = $ARGV[0];
if ( !$file ) {
print "missing log file name\n";
$OPTIONS{'help'}++;
}
if ( $OPTIONS{'help'} ) {
usage();
exit;
}
main();
exit;
my @LINEBUFFER;
sub get_next_query {
my ($FH) = shift;
my ( $query_found, $error, $in_block ) = ( 0, 0, 0 );
if ($#LINEBUFFER ==0 ) { $in_block = 1; }
while ( !$query_found && !$error ) {
$LINEBUFFER[ $#LINEBUFFER + 1 ] = <$FH>;
if ( !$LINEBUFFER[$#LINEBUFFER] ) {
return -1;
}
if ( !$in_block
&& $LINEBUFFER[$#LINEBUFFER] =~ /^(\d{6} [\d:]{8})?\s+(\d+)\s(\w+)(\s(.*))?/ )
{ # we have the beginning of a line
if ( $#LINEBUFFER == 0 ) { # begin block capture
$in_block = 1;
}
}
elsif ($in_block) {
if ( $LINEBUFFER[$#LINEBUFFER] =~ /^(\d{6} [\d:]{8})?\s+(\d+)\s(\w+)(\s(.*))?/ ) {
if ( $#LINEBUFFER > 0 ) { #end block
# return everything up to this statement
$query_found = '';
for ( my $i = 0 ; $i < $#LINEBUFFER ; $i++ ) {
$query_found .= $LINEBUFFER[$i];
}
$LINEBUFFER[0] = $LINEBUFFER[$#LINEBUFFER];
$#LINEBUFFER = 0;
}
} else {
}
}
else {
shift @LINEBUFFER;
}
}
return $query_found;
}
sub main {
open( FILE, $file );
my $done = 0;
while ( !$done ) {
my $query = get_next_query( \*FILE );
if ( $query eq -1 ) {
$done = 1;
}
else {
chomp($query);
$query =~ /^(\d{6} [\d:]{8})?\s+(\d+)\s(\w+)(\s+(.*))?/s;
my ($type, $query ) = (lc($3), $5);
if (!$OPTIONS{'preserve-newlines'}) { $query =~ s/[\r\n]/ /g; }
if ( $type eq $OPTIONS{'type'}) {
if (defined($OPTIONS{'pattern'})) {
if ( $query =~ /$OPTIONS{'pattern'}/ ) {
print $query . $OPTIONS{'separator'} . "\n";
}
} else {
print $query . $OPTIONS{'separator'}. "\n";
}
}
}
}
close FILE;
}
sub usage {
print <<EOF;
NAME
$0 - dump statement from mysql general log format
USAGE
$0 <options> [log file]
SYNOPISIS
For the most part, the general log is pretty straighforward,
except when SQL statements contain newline characters.
This script takes care of finding those boundaries and
extracting whole statements.
Most often some filter is passed to the program in order
to return only certain types of statements.
OPTIONS
--help
Display this screen
--type=s
-t
One of Query or Connect, default is Query
--pattern=s
-p
Regular expression to match statements against.
Usually something like ^SELECT
--preserve-newlines
-n
Keep original newlines in multiline queries default
is to make all queries single line.
-separator=s
-s
Add the separator after every query
EOF
exit;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment