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?

http://stackoverflow.com/questions/14116488/master-slave-configuration attributed — Tmarie Petrovich, May 14 '17 at 05:13
Question... and it's a bit of a trick question because you need to understand the question before you understand the answer: are you replicating based on binlog coordinates or global transaction identifiers (GTIDs)? — Michael - sqlbot, May 14 '17 at 10:38
Use Orchestrator or MHA to "determine the fact". Else, re-invent their code. — Rick James, Jun 04 '17 at 19:23
@RickJames I wish to understand "how", not tools to get job done. — kingluo, Jun 06 '17 at 03:18
It's non trivial to "get it right". The biggest risk is "split brain"; search for that. — Rick James, Jun 06 '17 at 03:29

score 2 · Answer 1 · answered Jun 07 '17 at 17:12

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.

I know there are tools (official or not) to do auto fail-over, but I need the theory behind the scene. That is, I need "why" but not "how". — kingluo, Jun 11 '17 at 10:30
The official reference you gave does not explain the method to determine the newest slave. — kingluo, Jun 11 '17 at 10:31

score 0 · Answer 2 · answered Jun 11 '17 at 10:46

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.

mysql failover: how to choose slave as new master?

2 Answers2

Linked