List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:February 19 2008 9:36pm
Subject:Re: Backups@multiple locations
View as plain text  
Hi Pierre -

You're correct, mysqlhotcopy will no longer work when you switch to InnoDB.

One option you could pursue is using mysqldump instead, which will write out
full SQL files needed to restore your databases.  It will write these to a
filesystem.

It is generally slower than mysqlhotcopy to take the backup, and slower to
restore, but it is still possible to get a consistent backup snapshot this
way.  I've been using mysqldump for backups for years.

See the mysqldump man pages or
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
specifically, the '--lock-all-tables' option will be of interest for a
consistent db snapshot

HTH.

(copying the list to close the loop)

Best,
Dan


On Feb 19, 2008 9:23 PM, P. Evans <pevansdba@stripped> wrote:

> Dan,
> we have single linux pc's at each location, so replication isnt feasable.
> The concern is really when we go to innodb, since mysqlhotcopy won't work
> then if I understand the documentation ....
> Pierre
>
> *Dan Buettner <drbuettner@stripped>* wrote:
>
> Are you currently dumping raw SQL?  If so, how?  One table at a time, or
> by obtaining a lock on all tables?
>
> If you're getting a lock on all tables now, I don't think anything would
> change if you switched to a transactional engine like InnoDB and did the
> same thing.  The database is "frozen" for a period of time while the backups
> happen, which may be very quick if you don't have a lot of data.
>
> If you're not getting a lock on all tables now, then it's possible you're
> not getting a consistent snapshot of your data, and switching to InnoDB or
> another transactional engine won't fix that.  I'd recommend aiming for a
> consistent backup.  You know your operation better than I do, though - if
> there's truly *never* anything happening at the time you take your backups,
> then it's no big deal.
>
> The best strategy in many people's opinion when you need a consistent
> snapshot and can't spare the time to have the database "frozen", is to set
> up a replica of your master server, and take your backups from the replica
> (slave).  If you have a large number of servers this may be problematic from
> a cost/maintenance standpoint.
>
> If you can spare the time to have the database frozen, no big deal.
>
> -Dan
>
>
> On Fri, Feb 15, 2008 at 4:50 PM, P. Evans <pevansdba@stripped> wrote:
>
> > Greetings,
> >  I've got a retail operation with mysql 5.0.22 on linux pc's across the
> > country, and i need some input on setting up a backup strategy, preferrably
> > without purchasing a package. We're currently using MyISAM, with the
> > databases  being dumped to a filesystem on a separate drive, in case the
> > main drive goes down. However we will need to implement some kind of
> > transactional engine in the near future, and we'd prefer not to take down
> > the database to take a backup.
> >  Any thoughts ?
> >  Thanks
> >  Pierre
> >
> >
> > ---------------------------------
> > Looking for last minute shopping deals?  Find them fast with Yahoo!
> > Search.
> >
>
>
> ------------------------------
> Never miss a thing. Make Yahoo your
> homepage.<http://us.rd.yahoo.com/evt=51438/*http://www.yahoo.com/r/hs>
>

Thread
Backups@multiple locationsP. Evans15 Feb
  • Re: Backups@multiple locationsDan Buettner15 Feb
  • Re: Backups@multiple locationsMartin Gainty15 Feb
Re: Backups@multiple locationsDan Buettner19 Feb