qYemT7KY

1#!/usr/bin/perl
2use strict;
3use warnings;
4use DBI;
5use DBI::Const::GetInfoType;
6use Config::Pit;
7
8::main();
9exit;
10
11
12sub main {
13    my $endpoint = $ARGV[0]
14        or die "usage: $0 rds-endpoint";
15
16    $ENV{EDITOR} ||= 'vi';
17    my $config = pit_get("rds", require => {
18        user     => "master username",
19        password => "password of master user",
20    });
21
22    my $dbh = DBI->connect(
23        "dbi:mysql:mysql:$endpoint",
24        $config->{user},
25        $config->{password},
26        +{ RaiseError => 1, AutoCommit => 0, }
27    );
28
29    my $version = $dbh->get_info( $GetInfoType{SQL_DBMS_VER} );
30
31    print <<HEADER;
32/usr/sbin/mysqld, Version: $version. started with:
33Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
34Time                 Id Command    Argument
35HEADER
36
37    my $sth = $dbh->prepare('SELECT * FROM slow_log ORDER BY start_time');
38    $sth->execute;
39
40    while (1) {
41        my $rows = $sth->fetchall_arrayref( +{}, 100 )  or last;
42        print as_text(%$_) for @$rows;
43    }
44
45    $dbh->disconnect;
46}
47
48sub as_text {
49    my %row = @_;
50
51   $row{time} = sprintf '%02d%02d%02d %s', $row{start_time} =~
52        m/^\d{2}(\d{2})-(\d{2})-(\d{2})[ ](\d.*)$/smxo;
53
54    for (qw/query_time lock_time/) {
55        $row{$_} =~
56            s{^(\d{2}):(\d{2}):(\d{2})$}
57             {$1 * 36600 + $2 * 60 + $3}esxm;
58    }
59
60    $row{sql_text} .= ';' unless $row{sql_text} =~ /;\s*$/;
61
62    my $text = <<EOT;
63# Time: $row{time}
64# User\@Host: $row{user_host}
65# Query_time: $row{query_time}  Lock_time: $row{lock_time} Rows_sent: $row{rows_sent}  Rows_examined: $row{rows_examined}
66use $row{db};
67$row{sql_text}
68EOT
69
70    return $text;
71}
72
73
74=head1 NAME
75
76 rds-slowlog-to-text.pl
77
78=head1 SYNOPSIS
79
80 rds-slowlog-to-text.pl endpoint
81
82=head1 DESCRIPTION
83
84=head1 AUTHOR
85
86 <lamanotrama at gmail.com>
87
88=cut