MySQL replication - create two slaves but later make one of them master

Solution 1:

Your scheme really lacks comprehensiveness, so I'll just describe the way it has to be.

Say you have two DBs in DC A (master and slave, say they are named M and N) and you need to switch to DC B (master and slave too, Mng and Nng, where ng stands for new-gen). Then you just set up Mng to be slave for M, and Nng to be slave for Mng (and don't forget to say log_slave_updates on Mng, just in case). Optionally you also set read_only on on both Mng and Nng, just in case noone comes with master account and corrupts your new replica set.

When the moment comes, you do the following:

• stop the processing on DC A, say you put custom error pages "Sorry, we are migrating between DCs right now".
• stop M so no new data comes in it.
• stop slave; on Mng
• set global read_only=off; on Mng
• reset slave all; on Mng
• change connection descriptor in whatever is using DB master.
• remove the custom error page dummies, and voila, you're back online.

yeah, Nng just keeps on replicating.