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
Make sure to test extensively, though.
On Tue, Jan 25, 2011 at 5:37 AM, Robinson, Eric <eric.robinson@stripped>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
> 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
> 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
> 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
> 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@stripped. 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=1
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel