How to migrate huge MySQL databases one by one to new server?

Question

We have several servers with huge databases (on average 100-200GB per DB up to ~500GB for the biggest ones). All of them have a slave that is used for backups.

Now I need to migrate those with minimal or no downtime and preferably one DB at a time. First thing that came to my mind is doing a backup including master-data and setup replication. With the size of these databases dumping and reimporting takes quite a while, so I was looking into using innobackupex. That works and I can filter for one DB. However I still get all directories for all databases (even though they're empty) and an ibdata1 file of close to 2GB.

My main concern is, that this will only work for the first database. I can't do that for a second one and add that to a running slave as this would conflict with already existing data. Am I missing something or is it only possible to setup a slave for ALL databases of an existing MySQL server at once?

The servers are a mix of MySQL 5.1 and 5.5 and the new hosts should start with 5.7. Does the multi-source replication feature help with my approach?

Answer 1

One DATABASE at a time -- no. One Server at a time -- yes.

You are asking for trouble if you skip major releases.

Since you already have a Slave on each one, do something like this for one Master-Slave "system":

• Starting with A -> B (the Master and Slave you have now)
• Create machine C with the next version.
• STOP SLAVE on B.
• Take a dump of B; load this on C.
• Connect C as a Slave to B (A -> B -> C)
• Only now START SLAVE on B.
• Shake down. (Numerous things can go wrong in an upgrade, including syntax errors.)
• Set up D (A -> B -> C -> D) (This step could be moved up or down.)
• Disable writes to A.
• Wait for replication on C to catch up. (Hopefully this is only seconds.)
• Point clients to C.
• Abandon A and B.
• Proceed with E, etc, as needed.

As I noted, there is only one brief downtime. On the other hand, Slave B is "down" for an extended period.

Answer 2

I think I found a solution which is close to what I'm trying to achieve: https://dba.stackexchange.com/a/76198/107793.

The only thing I have to look into is, how to avoid the possible downtime with locking the tables. Since I'm on InnoDB exclusively (apart from the MySQL system DBs) that should be possible.