I have two webservers and at the moment have DNS - Round Robin setup so that they are somewhat 'failsafe' this was only ever a temporary failover solution and I am now looking at a more permanent and reliable solution. I have been looking into reverse proxy's, but I am wondering how to integrate mysql with this. I guess my question is: What would be the best setup to implement this?
Solution one: Two reverse proxy servers (for reverse proxy failover) serving from the two webservers (for webhost failover) and having mysql setup on the two reverse proxy servers.
Solution two: Two reverse proxy servers (for reverse proxy failover) serving from the two webservers (for webhost failover) and having mysql setup on the two web servers
Another way?
I should mention that the amount of webservers can increase or decrease at any time so having the mysql servers on the reverse proxy's would be better for replication, than trying to juggle replication around multiple servers.
Any help would be greatly appreciated.
Solution three: Separate database servers.
You mentioned that you web load may need to scale at any time - if you database setup doesn't scale too, then you probably can't scale very far.
This way, the database processes don't need to compete with web server processes for CPU time or memory, and will be able to be scaled according to your database scaling needs instead of being tied irrevocably to your application server tier.