Re: Fastest way to move many databases to new server
Posted by:
Rick James
Date: January 22, 2015 05:56PM
There is no free lunch. The main time taken is in disk I/O. I'll get to your question in a minute, but first let me digress...
It is possible to migrate to a new machine in minutes, regardless of the dataset size. However, you had to have planned ahead. Maybe you want to consider these _now_ for the _next_ migration.
Plan A: LVM is a way of creating a snapshot of a disk in, literally, seconds. That virtually eliminates the 'read' half of the disk I/O that you need to do.
Plan B: Master-Slave is a way of having two machines always containing the same data. The Slave can be taken offline, cloned onto the new hardware, put back online, allowed to catch up. Then you "failover" to the new, beefier, Slave, making it the Master. Downtime: minute(s).
Plan C: PXC (or other Galera-based cluster). 3 machines are continually in sync, you add your new machine, it syncs, you remove an old machine. Repeat 2 more times and you have a new cluster of all new hardware. Zero seconds downtime.
Back to your question. But first, are you upgrading MySQL at the same time? That might involve running "mysql_upgrade" at some point in the process. I am not covering that here.
Plan X: Stop writing to your machine. mysqldump -h old_machine | mysql -h new_machine . etc. At least the reads and writes are going in parallel.
Plan Y: Stop mysqld. Copy (using rsync or whatever) the entire mysql tree to the other machine. Start mysqld on new machine.
On WAN, consider compressing across the wire; on LAN, don't bother. (The CPU time to compress is likely to be greater than the network savings on a LAN.) Note that I don't distinguish between mysqldump+mysql and rsync in speed -- because the time is mostly I/O, and both have to read/write about the same number of bytes from/to disk.
I have left out a _lot_ of details. If you want more discussion, pick a "Plan".
Plan W: "Hundreds of databases"? WordPress? Can you "shard"? That is, put 'new' users on the new machine, leaving the old users on the old machine. Perhaps you need to move some 'idle' users to the new machine to free up disk space.
Of course, then you would need a way for a user to know which machine to connect to, or to have some proxy or load balancer with enough smarts to redirect the user to the right machine.
There may be more solutions, but I have typed enough for now.