How to copy large MySQL databases with minimal downtime
From Ajswiki
Copying large MySQL databases can lead to extended downtime for your application. Large tables must be locked in order to dump or load them, and that can really be a pain. Here's an easier way, assuming you're using Linux. If you're under some other OS, your mileage may vary.
The first pass
First, you need to move over the bulk of the data. This is easier than you might think. Simply use rsync. Rsync has the wonderful feature of copying only the data that changes, so if you use it in two passes, you'll get exactly what you need, but with the least downtime.
If you can afford to bring down the remote database, then do so, and then run:
rsync -azvPe ssh /var/lib/mysql/yourdb remotehost:/var/lib/mysql/
If you cannot afford to bring down the remote database, then copy its datafiles to a new location (don't bother to lock the db first, you don't need it to be a usable copy) and then run the above command, but with the target directory set to where ever you put the copy of the database. If you can afford the storage, copy the database to the same partition so that, in the final step, you won't have to copy the files into place (when you "mv" across partition, mv actually copies the files and then deletes the originals).
The second pass
Now that the data has been moved over, you need to finalize the copy by locking both databases.
mysql -u root targetdb mysql> FLUSH TABLES WITH READ LOCK
Now, in another window (while that mysql session is still running) run the same rsync command again. This time it will only rsync over the blocks that changed when you locked the database (and any changes in between). Most of the data will remain the same.
Once the rsync is done, you can execute "UNLOCK TABLES" in the mysql session on the source host.
If you brought down the remote database, now you can bring it back up. If you did not bring it down, you will need to bring it down, move (not copy) the new data files into place, and then bring it back up.
Other options
If you use a Network Appliance filer, then this gets much easier. Simply lock your database, create a "snapshot" (which takes only a few seconds) and then unlock the database. Now sync over the snapshot. Snapshots on a filer are atomic, so you can even take the snapshot without locking, but that runs the same risk that you would run in a system crash. It's always possible that you'll lose data, but not likely.
You can also load data into another tablename by editing the dumps created by mysqldump. Then load the dump and lock the database while you rename the table into place.
