How to detect MySql server failure?

Question

If for any reason MySql server goes down,

1. What are the ways I could still show my page to user? Probably having a backup DB server would help?
2. If I've multiple DB servers and one goes down, I can fetch data from another temporary server? How to detect that main server is down? Who would select alternate server? role of reverse proxy server here? Is Varnish capable of doing this kind of switching via VCL (Varnish Configuration Language)?
3. Approach to maintain copy of main DB on temporary server? Master slave replica method?

Thanks

Answer 1

1. If you have data replicated elsewhere, you could fall back on one of your slave/backup databases. If not, you could (obviously) display an error message.

2. Yes. If you try to connect to the main database and it's down (eg: fails to respond) then you can assume it's down. How you go about doing that will depend on the language you are using to query the database.

3. See the MySQL manual on replication: here

Answer 2

1. If your service depends on database heavily, yes you need to have a backup DB. Otherwise, if it's only supporting some minor functionality, you could apply the principle of "degradable experience" in that case.

2. Why not write a small service/daemon that acts as an interface to your DB, runs on your server machine and polls your servers for activity? This service would then handle all of the DB-related queries independently, select the appropriate server, etc so your www service would only forward requests to the daemon and receive results accordingly.