Automatic MySQL Replication Fail-over Strategy

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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:

How you implement redundancy is entirely dependent on your application and circumstances. High-availability solutions (with automatic failover) require active monitoring and either custom scripts or third party tools to provide the failover support from the original MySQL server to the slave.

-- http://dev.mysql.com/doc/refman/5.5/en/faqs-replication.html#qandaitem-B-13-1-9

There is currently no official solution for providing failover between master and slaves in the event of a failure. With the currently available features, you would have to set up a master and a slave (or several slaves), and to write a script that monitors the master to check whether it is up. Then instruct your applications and the slaves to change master in case of failure.

-- http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html