Topic: Primary and secondary DB server

I would like to run my Rails application with a primary and secondary database server using MySQL5.

1. I need a backup
Damn it, a stupid user deleted important data and want to restore it immediately. But how should the backup process work? Once per hour?

2. Secondary database server stands in for primary database server
Primary database server crashes, but we have a secondary database server and the Rails app is still running.

Has anyone some experiences with these issues?

Re: Primary and secondary DB server

Depends on how you want to manage the servers, For example:

"warm" where the 2nd server is slightly behind the 1st server - thereby if the 1st server goes down then you loose a small amount of customer data - but can be back up and running within hours.

"cold" where the 2nd server is (re)started upon a crash of the 1st server - startup time is a lot longer and will depend on all kinds of environmental factors.

As you can imagine there are varying degrees of "warm" and "cold", there is also the factor of how time critical your application is, e.g. financial servers require real time, shopping carts can get away with "one step behind".

Finally you'll want to consider the costs, in terms of money, PR, competitors, etc

From a technical view, have you thought about writing the same piece of information to both servers? Or put an extra layer of customer interaction at the application layer. There is also the point that you could protect sensative data at the d/b layer by ensuring it cannot be deleted.

Hope this helps,