0

I've run into an unforeseen issue regarding a MySQL Master node: The cloud instance on which it's installed is soon to be retired.

I've not needed to perform a migration like this before, so I'm unsure if one of these options I've come up with is better than the other.

Option 1

I can setup a new server acting as a MySQL Slave and start replicating data to it. I have not yet determined how much data is on the Master, so the replication process may not finish before I need to perform the cutover.

If I go with this option, my plan was to wait for replication to complete and then update the Web Services to point to the MySQL Slave IP address. From reading the MySQL docs, I'm getting the impression I'm unable to do this (start writing to a Slave?).

Option 1 - Create Slave, Replicate, Update Web Services to point to Slave

Option 2

I'm able to create a brand new instance and set this instance as a new MySQL Master. I could then take a Snapshot of the /data volume, and use this snapshot to create a new volume and attach it to the new MySQL Master instance. Once this is done, I can update the Web Services to use the new IP of the SQL Master.

The problem here is that during the time it takes to perform a snapshot, data is still being written to the original Master. If I were to set the new MySQL Master instance as "live," there would be a loss of data and I can't really think of a way to import those deltas back into the new MySQL Master instance (how would I resolve inserts taking place at different times and primary keys?).

Option 2 - Create new instance, snapshot data volume, attach volume.

I would really appreciate any tips, tricks, or advice concerning this issue. Thanks in advance.

dmux
  • 131
  • 4
  • What cloud provider? With AWS when this occurs you just reboot the instance and they move it to a new server. No need to migrate. – Nick Aug 07 '15 at 00:43
  • @Nick, It's with AWS. The problem I wanted to mitigate was the fact that I only had 1 MySQL server and I would need to shut it down. During its downtime, I was going to lose information. – dmux Aug 07 '15 at 14:17
  • Good with AWS you can cause the server switch to occur at any time - you don't have to wait for the date AWS gives you. It should only be a 5 minute reboot. So I'd schedule some planned downtime at say midnight and just do the reboot. If you must have zero downtime, are you just collecting data? Could you create another MySQL server to simply collect new data, reboot the main one, then merge the new server's collected data with the proper DB server? – Nick Aug 08 '15 at 04:32

0 Answers0