Re: Snort-users digest, Vol 1 #939 - 13 msgs

[Bob Hillegas <bobhillegas () pdq net>]

I just read an article in the September 2001 Linux Magazine, "Clenaing out
a Logging Database(MySQL)", by Randal L. Schwartz.

Here is his code example. Can be customized to assist with
keeping log files in hand, (see lines 5 thru 10).

Haven't has the time to play with it on my system to see what changes (if
any) are needed. At least I saved you a download. :-)

Enjoy.

---- snip ----
#!/usr/bin/perl -w
use strict;
$|++;

my $DIR = "/merlyn/web/OldLog";
my $DB = "merlyn_httpd";
my $TABLE = "requests";
my $STAMP = "when";
my $DB_USER = "--guess--";
my $DB_PASS = "--not--";

use DBI;

chdir $DIR or die "Cannot chdir to $DIR: $!";

my $dbh = DBI->connect("dbi:mysql:$DB", $DB_USER, $DB_PASS,
                       { PrintError => 0, RaiseError => 1 });
$dbh->do("SET OPTION SQL_BIG_TABLES = 1");

## first, get start and end for dates
my ($table_name, $before) = do {
  my $lowest_epoch = $dbh->selectrow_array
    ("SELECT UNIX_TIMESTAMP(MIN($STAMP)) FROM $TABLE");

  print "$lowest_epoch = ".localtime($lowest_epoch)."\n";

  my @low = localtime($lowest_epoch);
  my ($start_year, $start_month) = ($low[5] + 1900, $low[4] + 1);
  my ($end_year, $end_month) = ($start_year, $start_month + 1);
  $end_year++, $end_month = 1 if $end_month > 12;
  sprintf("${TABLE}_%04d_%02d", $start_year, $start_month),
    sprintf("%04d-%02d-01", $end_year, $end_month);
};

print "table is $table_name, before is $before\n";

## now get table definition
my $cols = do {
  my $info = $dbh->prepare("DESCRIBE $TABLE");
  $info->execute;
  $info->bind_columns(\@$_{@{$info->{NAME_lc}}}) for \my %col;

  my @coldefs;
  while ($info->fetch) {
    push @coldefs, "$col{field} $col{type}";
  }
  join(",", @coldefs);
};

## transfer live data to temp table
$dbh->do("DROP TABLE IF EXISTS $table_name");
$dbh->do("CREATE TABLE $table_name ($cols)");
eval {
  my $count_insert =
    $dbh->do("INSERT INTO $table_name SELECT * FROM $TABLE WHERE $STAMP < ?",
             undef, $before);
  print "inserted $count_insert rows\n";

  my $count_selected =
    $dbh->selectrow_array("SELECT count(*) FROM $table_name");
  print "transferred $count_selected rows\n";
  die "mismatch!" unless $count_selected == $count_insert;

  my ($min_date, $max_date) =
    $dbh->selectrow_array("SELECT MIN($STAMP), MAX($STAMP) FROM $table_name");
  print "dates range from $min_date to $max_date\n";

  ## use mysqldump to create file
  print "dumping...\n";
  defined(my $kid = fork) or die "Cannot fork: $!";
  if ($kid) {                   # parent
    waitpid($kid,0);
    die "bad exit status: $?" if $?;
  } else {                      # kid
    my $file = "mysql_$table_name.gz";
    die "$file exists, aborting\n" if -e $file;
    open STDOUT, "|gzip -9 >$file" or die "gzip: $!";
    exec "mysqldump", "--opt",
      "--user=$DB_USER", "--password=$DB_PASS", $DB, $table_name;
    die "Cannot exec: $!";
  }

  ## delete original data, and drop table
  print "deleting...\n";
  my $count_delete =
    $dbh->do("DELETE FROM $TABLE WHERE $STAMP < ?", undef, $before);
  print "deleted $count_delete rows\n";

  $dbh->do("DROP TABLE IF EXISTS $table_name");

  print "optimizing...\n";
  $dbh->do("OPTIMIZE TABLE $TABLE");
}; warn "insert block: $@" if $@;

$dbh->do("DROP TABLE IF EXISTS $table_name"); # in case it didn't happen

$dbh->disconnect;
---- snip ----


-- 
-------------------------------------------------
Bob Hillegas
<bobhillegas () pdq net>
281.546.9311


_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
http://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users