Amazon EC2 will change the way MySQL is used: it suddenly opens a whole slew of new possibilities. What’s really exciting is that it can also simplify the management of MySQL which enables powerful automation as provided in the RightScale Manager for MySQL. The factors that enable this are: * it takes <10 minutes to fire up a fresh MySQL instance, and there’s a virtually limitless supply * there is virtually no cost to keeping old database instances around while setting up fresh ones * there is virtually no cost to firing up temporary database instances for special tasks
Let’s take an example: you have 1 master and 2 slaves hanging off it. Your master fails. You determine that slave 1 is at a more advanced position than slave 2, so you promote it to master. How do you proceed to get back to 1 master + 2 slaves? Without some risky magic you can’t roll slave 2 forward to the same position as slave 1 and start replicating from slave 1, so slave 2 is more or less useless. On EC2 you can discard the master and slave 2 and fire up two new slaves that you set-up fresh to replicate from the new master. In addition you can keep slave 2 around for a few hours until everything is stable again just in case a problem develops with slave 1 or you discover that you need to roll back a few transactions because something caused a problem.
This is very different from a physical set-up where you would have promoted slave 1 to master, “fixed-up” the original master machine and made it a slave of the new master, and where you would have wiped the data from slave 2 to set it up fresh. Both of these actions sound simple, but in real life they often end up being very stressful. Ideally you don’t want to touch the old master so you can do in-depth troubleshooting so you find out what went wrong and can fix the problem. But you also badly need the old master machine to be back in the cluster making for a difficult choice. Wiping data from slave 2 is also not an easy decision: what if there is a problem with slave 1, or what if you really need to run some of your read-only applications in degraded mode off the now-frozen slave 2 until you have enough machines in the cluster to take the full load? Again, on EC2 all this becomes easier: you fire up fresh instances and simply keep the old machines around until you’re confident that the new ones are ready to take over and you’ve gotten all the information you wanted off the old ones.