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?
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]
