I am testing out some infrastructure options before I start writing a web application. I would like to have two separate MySQL database servers at different physical locations, but if one of them goes down, I would like to be able to fail over to the other with minimal data loss.
From what I can see, MySQL only supports one "master" server and multiple "slave" servers. I'm assuming the reasoning behind this setup is to do your writes on the master, and try to do your "reads" on the slave servers.
Is there a way to set it up so that I have the ability to fail over and write to the slave server if the master server goes down?
Things to keep in mind for my test- I have two VPS from different providers with one external IP each.