Actually, no, it isn't in memory (except for that breif period of time before the write cache is committed to disk). You are actually creating a new, disk-based database and populating it with data.
Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Jacob, Raymond A Jr" <[EMAIL PROTECTED]> wrote on 07/30/2004 07:40:20 PM: > 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 wouldappreciate > > hearing about it. > > > > Thank you, > > Raymond > >
