Working on finding a solution to a particular problem we have at the office,we have a web host and at times our web server (Apache) crashes; which causes our web app to be unavailable to users. Usually as a result of the number of users, we noticed that there is a rise in the number of database connections at peak times. Option 1: So we were thinking of getting a server solely to server as the database server(mysql) and another as as our web server on the same host. Q:Is there a better way to solve this problem while using a single server? Q:is there anyway to manage the number of connections to the database server without causing any serious issues for the users? Option 2: We were thinking of getting another server (i.e. one with both apache and mysql running), in the case where the first becomes unavailable, all the traffic switches to the second. The issue here is that mysql only supports 1 way replication (i.e. master-slave). Instance: normally the slave mirrors the database in the master, in a situation where there is a fail over,and a user performs a write action on the slave, when the master comes back up, how can we also replicate all the writes in the slave to the master for the sake of consistency( without which a user might find out that some data he saved previously seems not to be available, apart from primary key conflicts). Q:How can this be implemented, to cater for this need?
Also, would really love any suggestions on solutions (This problem is a new one for us).