Copying a MySQL database FAST
toelovell at gmail.com
Tue Aug 5 20:27:59 MDT 2008
Jeff Schroeder wrote:
> Dave asked:
>> 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 .
> Where "source" is the name or IP address of the source server.
> This copies the binary MySQL files directly, saving a lot of the
> overhead of creating SQL dumps and so forth.
> If the servers aren't on a LAN together or for some other reason rsync
> won't work, you could just create a tar archive of the source, copy the
> archive to the destination, and untar it.
> [on source]
> # cd /var/mysql
> # tar zcf dave.tar.gz dave
> Note that both of these methods assume MySQL isn't running while you're
> doing the copy (because if it is, you run the risk of data corruption)
> and also that the MySQL servers are roughly the same version so the
> binary files are compatible.
Here is a way to do almost that exact same thing without stopping and
starting MySQL, although it probably requires redoing some partitioning.
If you put the MySQL data files on an LVM volume, you can use the LVM
snapshots to create a snapshot of the LVM volume. You can then mount the
snapshot to a different location in the filesystem and make a simple
file copy from the snapshot to the backup location. Then you just simply
remove the LVM snapshot. I use this method to backup some Xen Virtual
machines that I have without having to having any service interruption
in them. I can't vouch for LVM being able to maintain the same
performance levels while it is also tracking the snapshot. This tutorial
goes through some of the gory details.
Anyways. There is yet another recommendation. It will probably require
some downtime unless you happen to already have MySQL data on an LVM
volume. It works great for me.
More information about the PLUG