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.
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/