Take the 2-minute tour ×

OS is Linux. I imagine that the process is something like the following:

  • Set up master-master replication between old and new servers.
  • Cut over the db ip on my app server from the source db to the destination db.
  • Confirm that the new db is being used. (show processlist, etc)
  • Break replication on original server.

My question is actually in regard to this last step. What is the most graceful way to perform this step besides just issuing a "service mysql stop"

Also wanted people to look this over to confirm these steps are sane. Thanks!!

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

If you plan on breaking the replication as the last step anyway, is there really any point in setting up Master-Master? It would probably suffice to set up Master->Slave with the Current->New servers. Once you switch the IP address within your App (which I'm assuming will make all writes instantly start using the new server) all writes should go to the new server and then you can issue a stop slave; After all, MySQL Master <=> Master is Really just Master/Slave -> Slave/Master (if that makes sense).

The only benefit of setting up Master - Master is that once you do the IP cut over, all new writes to the new DB would be replicated back to the original.

The one thing you would want to make sure of is that your replication is able to keep up. Are these two DB servers on the same network or are they replicating over a WAN link? Are these busy servers? Is A LOT being replicated at any given moment? One problem that you could run in to is that your replica slave isn't caught up with the Master when you cut over. If the new server starts receiving writes while it's still trying to catch up with what's in the relay logs, you risk key collisions (but this also depends on how your App writes to the database). If you could afford 30 seconds of down time, I'd suggest putting up a "Down for Maintenance" page, verify that the replication is 100% caught up, then switch the DB IP address. Then bring the App back up. Do it at a time of day/night that has your least amount of users.

It probably wouldn't hurt to test this out with some dev machines or VMs before hand.

share|improve this answer
I was planning on doing master-master vs master-slave, as in my mind "master-slave" necessarily meant setting the slave (destination) to read only. However....now that I think of it, I see no reason why I would have to set the slave to read only! Thanks for the idea! (I do understand that master-master is actually master-slave/master-slave.) Good call on the cutover edge case. I will put up that test page and be sure that replication is fully caught up before cutting over the IP to the slave. The two servers are on the same network, so not expecting problems there. Thanks!! –  cat pants Mar 13 '13 at 4:27
add comment

Your Answer


