I'd like some feedback about this failover strategy for a couple of MySQL servers I'm speccing up for a cluster, and I want to check if there's something obvious I'm not missing here.
One app server that connects to a mysql master server in day to day operations, and that has a mysql-server set up as a slave to it for master-slave replication purposes.
If the mysql server fails, I want to the web app to try connecting to the master, and then after
n failed attempts, perform the following:
- assume the master will no longer be available
- send a signal to the slave server to stop replication
- send a signal to the slave server to tell it to act as the new mysql master
- begin connecting to the server again, and treat it like the master from now on
Once the app is up again, and serving users, I'd like to be able to spin up a new slave server in the background, once it's ready to serve requests, set up master slave replication once again to provide the same failover support as before.
I'm pretty sure this has been done before, but I can't see any guides on this, so I'm assuming there must be some obvious reason you wouldn't try this, that I haven't thought of yet.
What are the pitfalls of taking this approach for providing automated failover like this with MySQL?
As an aside, I'm aware of master-master replication, but a) I've seen it go horribly wrong, and it b) seems worryingly over-complicated.