While I haven't done it a good strategy for InnoDB backups is to setup a machine that will replicate off the master, but not actually serve, so after it replicates you can safely backup without service interruption, and you can do more frequent backups this way.
On Wed, Aug 12, 2009 at 12:58 PM, Dino K <[email protected]> wrote: > Ah cool, good info, I have been avoiding using code that uses innodb simply > because of the backup and restore factor being more cumbersome than > regularly ole mysql tables but this sounds like a good solution. > > -DK > > > On Wed, Aug 12, 2009 at 9:38 AM, Todd Lyons <[email protected]> wrote: > >> On Mon, Aug 10, 2009 at 12:13 PM, Dino K<[email protected]> >> wrote: >> > >> > The advantage of such an external SAN unit is that you can do >> Snapshotting >> > for added redundancy even though with INNODB (if you are using it) >> you'll >> > still have to manually dump the DB for snapshotting. >> >> We use xtrabackup to copy live running mysql innodb backend servers. >> This is a huge advantage because the databases do not have to be >> locked for most of the time it is copying. Nightly backup is a two >> step process: >> >> 1. run innobackupex-1.5.1 telling it where to put the backup >> a. It copies the ibdata, all *.ibd files (if using >> innodb_file_per_table), and the iblogfile, all without locking the >> database. >> b. It copies the *.MY* and *.frm files, but it does lock the >> database while it does this. >> 2. run innobackupex-1.5.1 --apply-log with the path to the backup >> a. This uses the innodb redo/undo functionality to fix the state of >> the innodb files to something that can be dropped in place to a mysql >> server (shut down, copy, start up) and it will start clean. >> >> On our system, which is about 2000 databases (all >> innodb_file_per_table) and consumes 73GB, it takes 7 hours to copy the >> data to an nfs mounted backup location and 3 hours to "fix" it across >> that nfs mount. >> >> -- >> Regards... Todd >> _______________________________________________ >> LinuxUsers mailing list >> [email protected] >> http://socallinux.org/cgi-bin/mailman/listinfo/linuxusers >> > > > _______________________________________________ > LinuxUsers mailing list > [email protected] > http://socallinux.org/cgi-bin/mailman/listinfo/linuxusers > > -- Peter Manis (678) 269-7979
