Why not keep your server running and just use scripted SQL to migrate the records from one DB to the other?
CREATE DATABASE dbArchYesterday; USE dbArchYesterday; CREATE TABLE table1 like dbActive.table1; CREATE TABLE table2 like dbActive.table2; CREATE TABLE table3 like dbActive.table3; CREATE TABLE table4 like dbActive.table4; INSERT table1(list of columns to load) SELECT list of columns to read FROM dbActive.table1 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; INSERT table2(list of columns to load) SELECT list of columns to read FROM dbActive.table2 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; INSERT table3(list of columns to load) SELECT list of columns to read FROM dbActive.table3 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; INSERT table4(list of columns to load) SELECT list of columns to read FROM dbActive.table4 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; DELETE FROM dbActive.Table1 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; DELETE FROM dbActive.Table2 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; DELETE FROM dbActive.Table3 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; DELETE FROM dbActive.Table4 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; That way you never have to stop your server and you can transform any data you need to change during the move. This process also allows you to build additional summary tables during the move, if you want them. By scripting the entire thing and replacing my (hopefully) obvious placeholders with live names and dates, this should perform rather well with little or no intervention on your part. Another advantage to scripting this process is that you can monitor each stage and abort the deletes at the end if things did not go well. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Jacob, Raymond A Jr" <[EMAIL PROTECTED]> wrote on 07/29/2004 06:26:26 PM: > I am running MySql 3.23.58-1 on a snort database containing IDS alerts. > At 12:00am I would like move the previous day's alerts from four tables to > a backup database named for the previous day. > Has anyone implemented such a backup schedule? And if so can someone > send me a script? > > I had the following ideas on the subject: > 1.a. mysql shutdown. > b. cp -r database /..../2004-07-29 > c. mysqlstart > /* I need a single user mode for the delete to work */ > d. echo "Delete iphdr; Delete tcphdr;Delete acid_event; Delete > event" | mysql -p xxx -u yyyy > e. go to multiuser mode. > > 2. a. Assuming logging turned on > mysqlhotcopy snortdb > ( echo "Delete iphdr; Delete tcphdr;Delete acid_event; > Delete event" ; mysqlbinlog snort.log ) | mysql -p xxx -u yyy > > 3. a. $ mysql -p xxx -u yyy > 1. if a week then purge tables: > $mysql> Delete iphdr; > ....( repeat for the rest of the tables.) > > > 2. mysql -p xxx -u yyy > mysql > Select iphdr.* > from iphdr, event_id > into outfile /.../backup/2004-07-29/iphdr.txt > where timestamp.event_id < 2004-07-29; > mysql> Delete iphdr; > ....( repeat for the rest of the tables.) > > mysql > use backsnort_db > Select iphdr.* > from iphdr, event_id > Load infile /.../backup/2004-07-29/iphdr.txt > ....( repeat for the rest of the tables.) > > mysql > exit > > b. tar cvf backsnort_db > > That is my best shot if anyone has a more elegant solution I would appreciate > hearing about it. > > Thank you, > Raymond
