Re: MySQL Log rotate

[David Gadbois <gadbois () computer org>]

adrian.hobbs () au pwcglobal com wrote:
> I am using MySQL to handle all the logging for Snort.
>
> How so I rotate the logs in MySQL? Is the best way to just delete the rows
> in the event table? What if I want to archive the information?

I figure old events are not worth keeping around.  I have attached a
Perl script I use to delete events over 30 days old.  It works with the
version 103 schema.  Since MySQL does not have nested queries or foreign
key constraints, it is pretty crufty.  Lose the "acid_ag_alert" lines if
you are not using Acid.

--David Gadbois

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=snort:host=localhost", "cleaner", "cleaner password") 
    or die "Can't connect: $DBI::errstr\n";

my @deletes = (
               $dbh->prepare("DELETE FROM data    WHERE sid = ? AND cid = ?"),
               $dbh->prepare("DELETE FROM icmphdr WHERE sid = ? AND cid = ?"),
               $dbh->prepare("DELETE FROM udphdr  WHERE sid = ? AND cid = ?"),
               $dbh->prepare("DELETE FROM tcphdr  WHERE sid = ? AND cid = ?"),
               $dbh->prepare("DELETE FROM iphdr   WHERE sid = ? AND cid = ?"),
               $dbh->prepare("DELETE FROM opt     WHERE sid = ? AND cid = ?"),
               $dbh->prepare("DELETE FROM acid_ag_alert WHERE ag_sid = ? AND ag_cid = ?"),
               $dbh->prepare("DELETE FROM event   WHERE sid = ? AND cid = ?"));

my $sth = $dbh->prepare("SELECT sid,cid FROM event WHERE timestamp < ( NOW() - INTERVAL 30 DAY ) ");
my ($sid, $cid);
$sth->execute();
$sth->bind_columns(undef, \$sid, \$cid);
my $count = 0;
while (my $ref = $sth->fetch) {
    $count++;
    foreach my $delete (@deletes) {
        $delete->execute($sid, $cid);
    }
}

if ($count) {
    $dbh->do("OPTIMIZE TABLE data");
    $dbh->do("OPTIMIZE TABLE icmphdr");
    $dbh->do("OPTIMIZE TABLE udphdr");
    $dbh->do("OPTIMIZE TABLE tcphdr");
    $dbh->do("OPTIMIZE TABLE iphdr");
    $dbh->do("OPTIMIZE TABLE opt");
    $dbh->do("OPTIMIZE TABLE acid_ag_alert");
    $dbh->do("OPTIMIZE TABLE event");
}

$dbh->disconnect or warn "Disconnect failed: $DBI::errstr\n";