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.

Answer 2

The Relay_Master_Log_File and Exec_Master_Log_Pos in SHOW SLAVE STATUS is used to determine the best slave as new master: the bigger values win.

Without GTID, I think we must first sync other slaves with the best slave we chose. The obvious sync source is the relay logs. On each slave, determine the differences of relay log from the best slave, download those files and replay the SQL statements. Once all slaves catch up, the slaves could CHANGE MASTER TO the best slave. The MASTER_LOG_FILE and MASTER_LOG_POS would be the tail of the last binlog on the best slave.

With GTID, it's very simple: just CHANGE MASTER TO with MASTER_AUTO_POSITION=1.