Shawn:
Thank you for your reply. I did not know you could reference a Database in a select
clause, I thought you
could only reference tables.
I do have a followup question. I assume once I have created the dbArchYesterday that
mysqld has the database in memory.Is there way to:
1. write the database to disk,
2. unload the database from memory, if it is in memory,
in order to file utilities such as tar and gzip to archive and compress the
database into to a tgz file?
Thank you,
raymond
PS: Is there a document that describes how mysql loads and unloads tables and
databases in memory?
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, July 30, 2004 9:03
To: Jacob, Raymond A Jr
Cc: [EMAIL PROTECTED]
Subject: Re: How do you archive db daily?
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
