Copying a MySQL database FAST
jon at endpoint.com
Tue Aug 5 17:57:36 MDT 2008
On Tue, 5 Aug 2008, Jeff Schroeder wrote:
>> I need to copy a MySQL database fast.
> 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 .
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.
Often you can limit downtime to a few seconds that way, since the bulk of
the copying happened while it was still online.
Aside from being the same major version, the MySQL servers in question
need to be the same architecture (i386 vs. x86_64, for example) and some
compile options may affect the binary format too.
End Point Corporation
More information about the PLUG