mysql_processlist.pl

#!/usr/bin/perl

########################################################################
# Copyright (C) 2015  yoku0825
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################

use strict;
use warnings;
use DBI;
use Fcntl;
use IO::Handle;
use Getopt::Long qw/:config bundling gnu_compat no_ignore_case posix_default/;
use Time::Piece;
use Data::Dumper;

my $interval= 10;                # if parameter is not given.
my $outfile = "processlist.txt"; # if parameter is not given.
my $port    = 3306;
my $host    = "localhost";
GetOptions("u|user=s"     => \my $user,
           "P|port=i"     => \$port,
           "p|password=s" => \my $password,
           "S|socket=s"   => \my $socket,
           "i|interval=i" => \$interval,
           "h|host=s"     => \$host,
           "outfile=s"    => \$outfile,
           "usage|h|help" => \my $usage) or die;
usage() if $usage;

sysopen(my $fh, $outfile, O_WRONLY|O_APPEND|O_CREAT|O_SYNC) or die;
$fh->autoflush;

my $dsn= "dbi:mysql:";
$dsn  .= ":$host"                if ($host);
$dsn  .= ";mysql_socket=$socket" if ($socket);
$dsn  .= ";port=$port"           if ($port);

my $conn= DBI->connect($dsn, $user, $password) or die;
my $sql = "SHOW FULL PROCESSLIST";

while ()
{
  my $tp= Time::Piece::localtime();
  my $rs= $conn->selectall_arrayref($sql, {Slice => {}});

  printf($fh "%s\t%s\t%d\t%s\n", $tp->strftime("%Y/%m/%d %H:%M:%S"),
                                 $host,
                                 $port,
                                 Dumper($rs));

  sleep($interval);
}


exit 0;


sub usage
{
  print << "EOF";
$0 is simple logger of MySQL's "SHOW FULL PROCESSLIST"

options are:
  --user=user           user which uses for login to MySQL.
  --host=host           hostname or IP address of MySQL Server.
  --socket=socket       socket-file path for using connection.
  --password=password   password which uses for login to MySQL.
                        This value effects only under --host=localhost.
  --port=port           port number of MySQL Server.
  --interval, -i=num    Sleeping seconds between each "SHOW FULL PROCESSLIST"
  --usage, -h, --help   print this message.
EOF

  exit 0;
}

processlist.txt

2015/03/02 15:08:23     localhost       3306    $VAR1 = [
          {
            'Time' => '0',
            'Id' => '1374',
            'db' => undef,
            'User' => 'root',
            'State' => 'starting',
            'Command' => 'Query',
            'Info' => 'SHOW FULL PROCESSLIST',
            'Host' => 'localhost'
          }
        ];

2015/03/02 15:08:24     localhost       3306    $VAR1 = [
          {
            'Time' => '0',
            'Id' => '1374',
            'db' => undef,
            'User' => 'root',
            'State' => 'starting',
            'Command' => 'Query',
            'Info' => 'SHOW FULL PROCESSLIST',
            'Host' => 'localhost'
          }
        ];