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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>* 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 <[EMAIL PROTECTED]> 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> >
