Keeping a 2 week running backup of MySQL snortdb

["JC" <monroe () peoplego com>]

-----BEGIN PGP SIGNED MESSAGE-----

I'm going to be working on a automated version of this process but in
order 
to to do the 2-x week backup process you must complete the following

1) select cid from event where timestamp >= "200x-xx-xx" limit 1;
# since it starts counting at midnight if you do just "2002-xx-xx" it
will 
try to match "2002-xx-xx 00:00:00" and you quite possible will not
have 
records at that time.

2) select cid from event where timestamp >= "2002-xx-xx" and
timestamp < 
"2002-xx-xx" order by timestamp desc limit 1; 
#this is to find the last cid in that date range and again since it
starts 
counting at midnight if you order descending it will give you the
last cid 
before the next day

3) create table data_copy select * from data where cid >=
<beginning_cid> and 
cid <= <ending_cid>;
#you would then need to do the same operations for tables 
event,icmphdr,iphdr,opt,tcphdr,udphdr 

4) backup table
data_copy,event_copy,icmphdr_copy,iphdr_copy,opt_copy,tcphdr_copy,udph
dr_copy to
'/data/directory/that/the/mysql/user/that/you/start/mysql/under/can/wr
ite/to';

5) create database old_snort1;
6) restore table
data_copy,event_copy,icmphdr_copy,iphdr_copy,opt_copy,tcphdr_copy,udph
dr_copy from '/the/data/directory/that/you/wrote/to'; 

7) create table data select * from data_copy 
# then do this on all of the copy tables
#of course you could do rename them from perl or something else

8) then you can use your custom query engine to query from the 2 week
old 
running copy
9) you could then use myisampack to pack each of these MYD files
since you 
aren't going to be writing to these tables any more this will give
you about 
a 50% space savings.

10) you then might want to make sure that the data has copied
correctly through a select that writes to a file that would query
both the running backup and the live database then diff those file(s)

11) drop table
data_copy,event_copy,icmphdr_copy,iphdr_copy,opt_copy,tcphdr_copy,udph
dr_copy; 

12) delete from data,event,icmphdr,iphdr,opt,tcphdr,udphdr where cid
> = <beginning_cid> and cid < <ending_cid> 

If someone can show me how to do this in 1 line using mysqldump I'll
be a monkey's uncle

Thanks, 
JC

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>

iQEVAwUBPM9XjZPmnNGPaJbxAQE/SAf/a4mjeBCwp0nCWNsy893JdcM8oPmYOdFN
R4HS3s6BTaYFYcUQXiB4PZktw0ZSxiSIpjQ9JiqmlIfR7q1V6TR1w3717/RVhUdA
qIkNt8EguTlw2hT2GeCS0wHeq1tOscrJaEfszXvld0SmcGgMPBMZ/lYpx+eXKH4u
ZDqeZ01BzbH7iIN9eSP2k40E+LrjN3RFDEhL/ggnFfergQcFhyVd6iikDrlgHmhV
qONDQG1U0Wsy0/x8n7Tp78gN2K+TEdhBoKUB0PTHK15bXhXzrQdf+eAX4zWfQlLG
rZJXy4vP7euIG8jCN94QnBj0IlT4GITMS82y067Euq6QCb+OkDEIGA==
=Stf1
-----END PGP SIGNATURE-----