Copying a MySQL database FAST

Stephen Ward sjward at gmail.com
Tue Aug 5 19:47:14 MDT 2008


> If the servers (source and destination) are on the same LAN, you might
> consider using rsync.  For example, let's say your database is
> called "dave" and is in /var/mysql/dave.  Shut down MySQL on both
> servers and on the destination (the one you're copying to) do something
> like this:
>
> # cd /var/mysql
> # rsync -avz --rsh=ssh source:/var/mysql/dave .
>
> Where "source" is the name or IP address of the source server.
>

When I'm using rsync with both the source and destination on the same
LAN, I generally skip the -z option, which is meant for compression.
It's been my experience that the overhead involved actually slows down
a LAN transfer significantly.

Another handy option is -P, which both shows progress and enables
resuming the transfer of a file that only got partially transferred
before being interrupted (through a sudden loss of connectivity, for
example).

> A variation on that theme that reduces downtime a lot is to rsync while
> the source MySQL daemon is still running, then stop the daemon and
> re-run the rsync to catch up the few changes that happened after the
> last rsync and during shutdown.

If you follow Jon's advice (above), you may want to throw in a
--delete flag, which will ensure that any files you got in the first
transfer that no longer exist by the time you make the final transfer,
all get deleted.  Bye bye, cruft (if any).

Long live rsync!

-Stephen



More information about the PLUG mailing list