If it were so cut-and-dry, I would know which one to choose ;-) Obviously it's not.
I'm leaning towards the mysqlhotcopy because it is way faster, and now I know how to selectively restore. I have a slight concern with the self-professed beta nature of it -- is that unfounded? Tim > -----Original Message----- > From: Dan Buettner [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 29, 2006 6:01 PM > To: Tim Lucia > Cc: mysql@lists.mysql.com > Subject: Re: Recommended backup scripts for mysql databases > > That's a good thought, Tim - file grep could provide a quick means to > access the data from a mysqldump file. Probably a dumb suggestion > because I'm sure you'll do this, but you should try it out ahead of > time, before you need to actually restore something. > > The reason I say this is - if you use some of the more > performance-oriented options with mysqldump, like --opt or > --extended-insert, grepping may not easily find you just the row(s) > you need, since your dump file ends up being a sea of concatenated > insert values with (IIRC) no linebreaks in between. > > I think it's 6 of one, half a dozen of the other. Each method has > slight advantages/disadvantages but both provide a sound means of > taking a backup, so you have to decide if any of the slight advantages > puts one out in front in your particular situation. > > Dan > > > > On 6/29/06, Tim Lucia <[EMAIL PROTECTED]> wrote: > > > > > -----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] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
