I suspect the same trick might work with InnoDB (with pretty much the same caveats), but you'd be best off setting innodb-file-per-table - I'm sure you've already seen that the large datafiles are a hindrance to smooth rsyncing :-)
Make sure to test extensively, though. On Tue, Jan 25, 2011 at 5:37 AM, Robinson, Eric <eric.robin...@psmnv.com>wrote: > Is there a way to safely backup an InnoDB database using rsync? > > Right now we have a very efficient and reliable way to backup 240+ > separate instances of MySQL with MyISAM tables. The databases range in > size from .5GB to 16GB. During this time, users can still access the > system, so our customers can work 24x7. In the process, we also refresh > 240+ slave instances with a perfect byte-for-byte replica of the master > databases. > > The whole thing takes about 30 minutes. > > Here's how we do it. > > Late at night when the number of users on the system is low, we do the > following for each of the 240+ instances of MySQL... > > 1. Shut down the slave and remove all replication-related log files. > > 2. Perform an rsync of the master's data directory to the slave. Users > may be making changes to tables during this rsync. > > 3. Issue a FLUSH TABLES WITH READ LOCK on the master followed by a RESET > MASTER. > > 4. Perform a second rsync of the data directory from the master to the > slave to copy any user changes that happened during step 2. This usually > completes in a few seconds, often less than 1. If any users were trying > to insert records at this exact moment, their application may appear to > pause very briefly. > > 5. Start the slave. > > When I'm all done, I have 240+ slave servers in perfect sync with their > masters, each having a 100% identical binary copy of its master's > database. Since these copies are truly identical, they can be used for a > second layer of backup to other media. > > Like I said, the whole process takes about 30 minutes because the rsync > algorithm only copies the byte-level changes. > > IS THERE ANY WAY TO SET UP SOMETHING THIS EASY AND EFFICIENT USING > INNODB? > > I've been reading about InnoDB hot copy and other approaches, but none > of them seem to work as well as the approach I have worked out with > MyISAM. Unfortunately, my software wants to force us to switch to > InnoDB, so I'm really stuck. If we have to switch to InnoDB and we > cannot come up with a method for doing fast, rsync-style backups, it > will probably mean huge, costly, and unnecessary changes to our > infrastructure. > > Any help will be GREATLY appreciated. > > -- > Eric Robinson > > > Disclaimer - January 24, 2011 > This email and any files transmitted with it are confidential and intended > solely for mysql@lists.mysql.com. If you are not the named addressee you > should not disseminate, distribute, copy or alter this email. Any views or > opinions presented in this email are solely those of the author and might > not represent those of Physicians' Managed Care or Physician Select > Management. Warning: Although Physicians' Managed Care or Physician Select > Management has taken reasonable precautions to ensure no viruses are present > in this email, the company cannot accept responsibility for any loss or > damage arising from the use of this email or attachments. > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
