RDSのmysql.slow_logテーブルを通常のテキストのフォーマットに変換するやつ

rds-slowlog-to-text.pl

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBI::Const::GetInfoType;
use Config::Pit;

::main();
exit;


sub main {
    my $endpoint = $ARGV[0]
        or die "usage: $0 rds-endpoint";

    $ENV{EDITOR} ||= 'vi';
    my $config = pit_get("rds", require => {
        user     => "master username",
        password => "password of master user",
    });

    my $dbh = DBI->connect(
        "dbi:mysql:mysql:$endpoint",
        $config->{user},
        $config->{password},
        +{ RaiseError => 1, AutoCommit => 0, }
    );

    my $version = $dbh->get_info( $GetInfoType{SQL_DBMS_VER} );

    print <<HEADER;
/usr/sbin/mysqld, Version: $version. started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
HEADER

    my $sth = $dbh->prepare('SELECT * FROM slow_log ORDER BY start_time');
    $sth->execute;

    while (1) {
        my $rows = $sth->fetchall_arrayref( +{}, 100 )  or last;
        print as_text(%$_) for @$rows;
    }

    $dbh->disconnect;
}

sub as_text {
    my %row = @_;

   $row{time} = sprintf '%02d%02d%02d %s', $row{start_time} =~
        m/^\d{2}(\d{2})-(\d{2})-(\d{2})[ ](\d.*)$/smxo;

    for (qw/query_time lock_time/) {
        $row{$_} =~
            s{^(\d{2}):(\d{2}):(\d{2})$}
             {$1 * 36600 + $2 * 60 + $3}esxm;
    }

    $row{sql_text} .= ';' unless $row{sql_text} =~ /;\s*$/;

    my $text = <<EOT;
# Time: $row{time}
# User\@Host: $row{user_host}
# Query_time: $row{query_time}  Lock_time: $row{lock_time} Rows_sent: $row{rows_sent}  Rows_examined: $row{rows_examined}
use $row{db};
$row{sql_text}
EOT

    return $text;
}


=head1 NAME

 rds-slowlog-to-text.pl

=head1 SYNOPSIS

 rds-slowlog-to-text.pl endpoint

=head1 DESCRIPTION

=head1 AUTHOR

 <lamanotrama at gmail.com>

=cut