Copying a MySQL database FAST

Jon Jensen 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.

Jon

-- 
Jon Jensen
End Point Corporation
http://www.endpoint.com/



More information about the PLUG mailing list