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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
MySQL 5.6 introduces Global Transaction Identifiers to replication, and complements these with new failover and switchover utilities which can automate fault recovery
You can learn more about replication in MySQL 5.6 here: http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html
You can also watch a tutorial demonstrating how these utilities work to provide self-healing clusters: http://dev.mysql.com/tech-resources/articles/mysql-replication-utilities.html
There is no direct or simple answer to your question.
MySQL replication provides one thing: replication. It's a mechanism that maintains replicas of your live data set, usually in very-near-real-time, on more than one server.
Replication is used for a number of different purposes, including (but not limited to) fault/failure tolerance for your application in case of a master server outage; however, what MySQL provides is the synchronization of the data.
Failover can only be be provided by resources and systems external to MySQL, and these vary widely.
From the documentation: