MySQLのクエリログをDBごとに分割＆整形して出力

MySQLをこういうふうに設定すると

log = /var/log/mysqld/query.log

全てのクエリをログファイルに出力してくれます。

[root@test01 work]# tail -f /var/log/mysqld/mysqld.log
                5777179 Quit
120121 16:28:02 5777180 Connect dbuser@192.168.1.1 on testdb1
                5777181 Connect dbuser@192.168.1.2 on testdb2
                5777181 Query   SELECT COUNT(*) FROM tasttbl1 WHERE create_time >= '2012-01-21 14:28:02' AND create_time <= '2012-01-21 16:26:02' AND delete_flg = 0
                5777180 Quit
                5777181 Quit

開発サーバとかだとよく設定しています。

ただ

• Databaseごとに別ファイルに出力したい
• 見やすく整形したい

と思ったのでちょっとやってみました。
※ここのawkスクリプトを参考にしています。

使ってみる

パイプかファイル指定でログを食わせると

[root@test01 work]# tail -f /var/log/mysqld/query.log | ./mysql_log_split.pl

こんな感じで分割＆整形して出力してくれます。

[root@test01work]# ls /tmp/mysql_*
/tmp/mysql_testdb01.log
/tmp/mysql_testdb02.log

[root@test01 work]# tail -f /tmp/mysql_testdb01.log
/* dbuser@192.168.1.1 on testdb01 */
SELECT id,
       name
    FROM testtbl2
    WHERE id=1

/* dbuser@192.168.1.1 on testdb01 */
SELECT COUNT(*)
    FROM testtbl1
    WHERE create_time >= '2012-01-21 14:05:03'
      AND create_time <= '2012-01-21 16:03:03'
      AND delete_flg = 0

ソースコード

#!/usr/bin/perl
use strict;
use warnings;
use Switch;

my $LOG_DIR = "/tmp";
my %conns;
my %fhs;

# ログローテート対応
$SIG{HUP} = sub {
  for my $key (keys %fhs){
   close($fhs{$key});
  }
  %fhs = ();
};

while(<>){
  chomp;

  if(/(\d+) Connect\s(\w+)@(\S+) on (\w+)/){
    my ($id, $user, $src, $db) = ($1, $2, $3, $4);

    $conns{$id} = [$user, $src, $db];
    unless($fhs{$db}){
      open $fhs{$db}, '>>', "$LOG_DIR/mysql_$db.log";
    }
  }elsif(/(\d+) Query/){
    next unless $conns{$1};

    my ($user, $src, $db) = @{$conns{$1}};

    my $fh = $fhs{$db};
    my @words = split;
    @words = @words[2..$#words];

    print $fh "\n/* $user\@$src on $db */\n";
    for my $word (@words){
      switch($word) {
        case "FROM"   { print $fh "\n    "; }
        case "WHERE"  { print $fh "\n    "; }
        case "GROUP"  { print $fh "\n    "; }
        case "HAVING" { print $fh "\n    "; }
        case "ORDER"  { print $fh "\n    "; }
        case "LIMIT"  { print $fh "\n    "; }
        case "AND"    { print $fh "\n      "; }
      }
      $word =~ s/,/,\n      /;
      print $fh "$word ";
    }
    print $fh "\n";

  }elsif(/(\d+) Quit/){
   delete($conns{$1});
  }
}

探せばもっと簡単な方法ありそうですがつい