mysql failover: how to choose slave as new master?

Question

I'm mysql newbie.

when it comes to fail-over, which slave should be promoted to the new master?

For example, A is master, B and C are slaves, and A does async replication to B and C.

At some point of time, B receives more data from A than C, A crashes.

If we promote C to new master, and changes B's master to C, then what happens to B? It truncates its data to match C?

Obviously, B is the best new master candidate, but my question is, how to determine this fact?

Answer 1

From the MySQL documentation, there two ways to set up a master-slave architecture. The traditional way, using the log files to replicate transactions and the new version (5.6+) using GTIDs (global transaction identifiers).

If you choose to use GTIDs to make the failover handling you will use the mysqlfailover utility. The utility handles fails of master in one of three ways defined by the database administrator:

• auto (default): A search is made in a list of prefered slaves to become master, if none are available another slave is chosen. The chosen slave first becomes the slave to all others slaves and has all the changes from the other slaves copied to it, this way the new master will be the most up to date version possible.
• elect: the same as above, except that if no slaves from the list are available it returns an error and finishes (no failover)
• fail: No failover happens mysqlfailover will just monitor the database and return an error if a fail happens.

The traditional way requires that you implement your own script to database management and is better explained here.