-
April 22nd, 2005, 08:08 PM
#1
MySQL and Snort, DB Huge!!
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.
Quitmzilla is a firefox extension that gives you stats on how long you have quit smoking, how much money you\'ve saved, how much you haven\'t smoked and recent milestones. Very helpful for people who quit smoking and used to smoke at their computers... Helps out with the urges.
-
April 22nd, 2005, 08:20 PM
#2
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?
West of House
You are standing in an open field west of a white house, with a boarded front door.
There is a small mailbox here.
-
April 22nd, 2005, 08:41 PM
#3
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.
Quitmzilla is a firefox extension that gives you stats on how long you have quit smoking, how much money you\'ve saved, how much you haven\'t smoked and recent milestones. Very helpful for people who quit smoking and used to smoke at their computers... Helps out with the urges.
-
April 22nd, 2005, 08:48 PM
#4
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.
kr5kernel
(kr5kernel at hotmail dot com)
Linux: Making Penguins Cool Since 1994.
-
April 22nd, 2005, 08:51 PM
#5
Puresecure allows you to purge everything before date x as well....
Don\'t SYN us.... We\'ll SYN you.....
\"A nation that draws too broad a difference between its scholars and its warriors will have its thinking done by cowards, and its fighting done by fools.\" - Thucydides
-
April 23rd, 2005, 03:00 PM
#6
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.
Code:
> 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
Code:
> 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`.
Code:
> mysql -u root <complete.sql
or
Code:
> 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
Code:
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
Code:
> mysql -u user_snort snort
mysql> delete from table_name
where table_name in a generic snort installation is one of
Code:
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
Code:
> 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
If the only tool you have is a hammer, you tend to see every problem as a nail.
(Abraham Maslow, Psychologist, 1908-70)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|