MySQL and Snort, DB Huge!!

[phishphreek]

My MySQL DB is huge and BASE is taking a really long time to show me alerts.

I know it is huge because when I installed it, I was working out all the false positives.
There were quite a few...

Is is possible to "archive" that data and start fresh?

I could probably just delete it and start again, but I'd like to keep it for a while.
I just want to drop/backup/archive the data but keep all the schema and fields and what not..

I'm a DB n00b... so please excuse my ignorence.

[RoadClosed]

Copy it over and use any old ODBC compliant front end to look at it and search. You could use access or even Excel if you have an MS platform. phpMyAdmin on Linux. Then delete the data through there This is the reason I don't use SQL to access snort logs, I took Tigers approach with a text file and a stripper to access specific events. It's FAST. But lags in real time. I am changing this approach though .... stay tuned.

Now is a good time to learn some basic SQL so you can issue statements like mass deletes etc. You may need to copy the database every day or every week to keep it clean and efficient. SQL commands can be issued right on the live database like copy or delete. And then you can store up statements and file them in a procedure or stored proc that's sort of like a batch file. Like perhaps:

Copy the file then
DELETE * FROM Table_Whatever (this will delete all rows in a table WITHOUT deleting the table. But I bet there are some procudures already written for snort...

//EDIT didn't or doesn't ACID have some database tools in it?

[phishphreek]

Thanks for the tip(s).

I have been meaning to learn some mysql. I just haven't really had a need until now.
So much to learn, so little time.

[kr5kernel]

Acid does have some drop down menus that will execute sql commands for you. This is what I used, when I had a false postive taking up a bunch of space, select DELETE, the Table Name, and a query string based on a keyword from the false positive entry.

[Tiger Shark]

Puresecure allows you to purge everything before date x as well....

[sec_ware]

Hi

DB's - I love them

backing up

There are two ways how to backup the snort-database:

1. copy all files from mysql/data/snort/ onto a backup-directory.
Basically, each table in the database has one of those frm, MYD and MYI files.
This is, well, when you know what you are doing

2. perform a dump - this may take a while in your case, because those tables
can be very huge. Basically, this dump is a set of SQL-commands to recreate the database.
Note: the database itself is not created. It creates a dump assuming
that you are logged in that particular database.

 > mysqldump -h localhost -u user_snort snort >snort.sql

If snort is the only database on your mySQL system, you also could perform a complete dump

 > mysqldump -h localhost -u root  -A >complete.sql

This dump also creates the databases.

restoring (test!)

The idea of the restoration is to reproduce the original database.
Here, I would recommend to create a new database snort_backup (see below).
I assume that you want to restore the original entries, hence I continue
using the database name `snort`.

  > mysql -u root <complete.sql

or

  > mysql -u user_snort snort <snort.sql

assuming that the database `snort` exists. Otherwise, add the above
command using "-u root" and add at the beginning of snort.sql

CREATE DATABASE `snort`;
USE `snort`;

Passwords can be handed over using the "--password=password_root" option

deleting table entries

for each table, you can run the command

> mysql -u user_snort snort
mysql> delete from table_name

where table_name in a generic snort installation is one of

  data 
  detail 
  encoding 
  event 
  icmphdr 
  iphdr 
  opt 
  reference 
  reference_system 
  schema 
  sensor 
  sig_class 
  sig_reference 
  signature 
  tcphdr 
  udphdr

/edit: You obtain a list of the tables performing an

> grep "CREATE TABLE" snort.sql
resp.
> type snort.sql | find "CREATE TABLE"

Usually, one tries to perform transactions, which can be
committed or allow for a rollback[1]. I won't comment on them
here. Have also a read at the disaster recovery page of mysql[2].

Good luck!

Cheers

[1] http://dev.mysql.com/doc/mysql/en/an...nsactions.html
[2] http://dev.mysql.com/doc/mysql/en/di...revention.html