How do you move your web servers to another location with no downtime?
The answer: the magic of MySQL Master-Master Replication
So, we are in the process of moving our web servers to another location. As usual, we would prefer to move them without anyone really noticing. What we have done is set up a skeleton of web servers in the new location as well as one database server.
Now the trick is, to move without anyone knowing it AND that everything else continues working. In this example, the original location has servers that query the database if there are any new orders and process them. So it is important to keep all the parts of the system running by continually updating the database in the original location. So in this example, if you point your DNS at the new web servers and don’t update the old database, you will basically lose money.
So, what do you do?
Well firstly, you should set up a secure and stable connection between the two locations so that the databases can communicate with one another. An SSH tunnel, will do the trick. You can set them up on the actual database server on a certain port, and it will seamlessly connect to the other database server as if it’s on your local network.
Secondly, you set up a Master/Master Active/Passive replication. This is a replication set up which is usually used for high-availability. You can also use Active/Active replication for load balancing if you take into consideration that auto-incrementing numbers would conflict on both servers. There is a solution for that as well.
On the day of the move, tell your web servers from the original location to query the database in the new location through the SSH tunnel. This will slow them down, but it should be only for a few moments.
Change the DNS to point to the new web servers and that they are pointing to the new database server.
Now you have your web servers in your new location and all the other servers and services running from the updated database in your old location.
All that’s left is to move the other servers and complete the entire move.
Job well done! You have completed the move with (hopefully) no downtime and no orders were lost. Your boss(es) will definitely be pleased with you.
Three quick comments regarding "Master/Master" replication.
ReplyDelete. You should never assuming your MySQL slave is consistent with your Master. You need to do adequate checking first.
. You must be very careful that your slave environment is up to date, especially during your failover.
. It's a general misconception that active master/master for load balancing spreads the load, therefore improves performance. All writes occur twice, regardless, so your improvements in a low volume system is only latency. In a high volume system, the single threaded nature of replication can actually hurt performance.
Ronald
http://42sql.com