I am using a system with two machines running mysql in master/master replication.

I would like to move the systems to new machines who are also doing master/master replication. Is it possible to do that without removing one node from the old cluster and replacing it with a new node and creating a new replication from the first new node to the second?

Let's say we have two nodes node1 and node2 that are active and doing master/master, how do I "magically" move the cluster to new nodes called new1 and new2 without too much downtime.

  • just a thought : wh, not stop, dump and import the db in a clean setup? – djdomi Jan 22 '20 at 17:58
  • That would create a downtime of a few hours, which I would like to avoid. If the dump takes an hour and the clean import takes a few hours, the site would either be down or the data received after the dump would be lost, which is both not desirable – Alex Lehmann Jan 23 '20 at 12:53

You have M1 <=> M2; you want to end up with new hardware M3 <=> M4.

  1. (brief disruption) Move all clients (writers and readers) to M2.
  2. Take down M1 long enough to clone it to make M3 in this topology: M1 <=> M2 -> M3. (M3 is just a Slave to M2.)
  3. Build M4 from M3: M1 <=> M2 -> M3 -> M4. (M3 is not in use yet, so there is no disruption.)
  4. (brief disruption) Move all clients from M1 and M2 so that they point to M3. In the process of this, make sure that all replication has made it at least to M3.
  5. Jetison M1 and M2. Now you have only M3 -> M4.
  6. (brief disruption) Set up dual-Master (M3 <=> M4); rearrange the clients as you wish.

Note that the three disruptions are each equivalent to failing out of a dead Master.

A general recommendation when using Dual-Master: Write to only one of the two servers; use the other as backup plus read scaling.

Better yet, switch to a Galera Cluster (or InnoDB Cluster) so that you gain automatic failover, etc. This will allow future topology changes to be even less painful. Such migrations would be similar to what I just described.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.