> -----Original Message-----
> From: Dan Buettner [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 29, 2006 9:26 AM
> To: Tim Lucia
> Cc: mysql@lists.mysql.com
> Subject: Re: Recommended backup scripts for mysql databases
>
> Hi Tim - those are all important considerations, yes.
>
> In my mind neither mysqldump nor mysqlhotcopy really make selective
> restore of one or a few records truly easy. Unless you edit the SQL
> dump file (impratical in most text editors when it gets beyond several
> MB) you won't be able to restore a record any more easily than from
> mysqlhotcopy.
>
> It's certainly possible to accomplish a selective restore with backups
> from either method, though. I wouldn't say easy but it's not bad.
> Picture this:
>
> - you have database DATABASE, with tables TABLE1 and TABLE2
> - you back them up, either to SQL dump or with hotcopy
> - somebody makes a mistake
> - you need to restore a record for STUDENT1
> - you create database RESTORE_DB
> - you pipe the DATABASE SQL dump file into RESTORE_DB, creating TABLE1
> and TABLE2
> - OR, you place copies of the hot-copied TABLE1.* and TABLE2.* files
> into the RESTORE_DB directory (possibly faster than waiting for an SQL
> dump to load)
> - in the mysql client, perform a SQL operation such as
> INSERT INTO DATABASE.TABLE1
> SELECT * from RESTORE_DB.TABLE1
> WHERE RESTORE_DB.TABLE1.STUDENTNAME = 'STUDENT1'
>
> Make sense?
Yes. Thanks to Dan and Daniel.
I do expect that if you know the student that was accidentally (deleted,
updated, ...) then you can find the appropriate data from the dump via
fgrep, which can handle large files pretty quickly, and without having to
attach the backup copy of the table to the server.
>
> Dan
>
>
> On 6/29/06, Tim Lucia <[EMAIL PROTECTED]> wrote:
> > I am in the process of designing the backup procedures for a soon-to-be
> > production DB. I have gone back and forth on mysqldump and
> mysqlhotcopy.
> > As I see it (for MyISAM tables), the hot copy backs up faster, restores
> > faster, but does not allow for selective restores ("Hello, Support? I
> > accidentally deleted student Tim Lucia... can you get him back").
> Mysqldump
> > is slower to back up, slower to restore, but allows for selective
> restores.
> >
> > Are those the only things to consider? I know that piping mysqldump
> into
> > gzip results in disk space savings of the dump file, whereas
> mysqlhotcopy
> > requires n * 2 free bytes on your system. But at least you know in
> advance
> > exactly how much space it will take...
> >
> > Tim
> >
> >
> > > -----Original Message-----
> > > From: Dan Buettner [mailto:[EMAIL PROTECTED]
> > > Sent: Tuesday, June 27, 2006 8:50 AM
> > > To: Andreas Widerøe Andersen
> > > Cc: mysql@lists.mysql.com
> > > Subject: Re: Recommended backup scripts for mysql databases
> > >
> > > Andreas, if you are only using MyISAM tables, the included
> mysqlhotcopy
> > > script may work for you. We used it at my previous employer with good
> > > results. We would run it to create a snapshot of our data files every
> > > day, then run a network backup utility that backed up the snapshot
> (but
> > > did not access the live data files).
> > >
> > > Other folks have recommended mysqldump, which is also a good solution,
> > > but could potentially take a long time to restore into the database,
> > > depending on your data.
> > >
> > > Dan
> > >
> > >
> > > Andreas Widerøe Andersen wrote:
> > > > Hi,
> > > > I have a few FreeBSD servers running various web/database things and
> I'm
> > > > looking for a good a reliable backup script that I can run through a
> > > > cronjob. I'm currently running the latest version of mysql323, but
> will
> > > > upgrade to version 4.1 soon aswell as upgrade most of the servers to
> the
> > > > latest FreeBSD version.
> > > >
> > > > Any good suggestions to a script that will back up my databases and
> make
> > > > things ready for an easy restore if I need to?
> > > >
> > > > Thanks!
> > > > Andreas
> > > >
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
