Q:

Automatic MySQL Replication Fail-over Strategy

I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the process , i.e if replication is down when no DBA is available then how the automatic fail-over will take place.

A:

Believe it or not, I just answered a question like this 5 days ago: MySQL Replication and High Availability
From that answer I provided, I advise against automatic failover with pure MySQL Replication unless your specifically script it to check for replication lag or totally unavailability of the old master (STONITH)
As an alternative, just setup a DRBD pair and run mysql on the DRBD Primary using HeartBeat to detect the DRBD Primary's unavailability.
Once a real failover happens, there must be manually intervention to setup the failover in the other direction.

