Copying a MySQL database FAST

Mike Lovell 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.
>
> HTH,
> Jeff
>   
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. 
http://www.howtoforge.com/linux_lvm_snapshots
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.

Mike



More information about the PLUG mailing list