Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.2Mb
PDF (A4) - 37.2Mb
PDF (RPM) - 36.6Mb
HTML Download (TGZ) - 10.1Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.8Mb
Man Pages (TGZ) - 206.0Kb
Man Pages (Zip) - 312.9Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Switching Masters During Failover

17.3.7 Switching Masters During Failover

When using replication with GTIDs (see Section 17.1.3, “Replication with Global Transaction Identifiers”), you can provide failover between master and slaves in the event of a failure using mysqlfailover, which is provided by the MySQL Utilities; see mysqlfailover — Automatic replication health monitoring and failover, for more information. If you are not using GTIDs and therefore cannot use mysqlfailover, you must set up a master and one or more slaves; then, you need to write an application or script that monitors the master to check whether it is up, and instructs the slaves and applications to change to another master in case of failure. This section discusses some of the issues encountered when setting up failover in this way.

You can tell a slave to change to a new master using the CHANGE MASTER TO statement. The slave does not check whether the databases on the master are compatible with those on the slave; it simply begins reading and executing events from the specified coordinates in the new master's binary log. In a failover situation, all the servers in the group are typically executing the same events from the same binary log file, so changing the source of the events should not affect the structure or integrity of the database, provided that you exercise care in making the change.

Slaves should be run with the --log-bin option, and if not using GTIDs then they should also be run without --log-slave-updates. In this way, the slave is ready to become a master without restarting the slave mysqld. Assume that you have the structure shown in Figure 17.4, “Redundancy Using Replication, Initial Structure”.

Figure 17.4 Redundancy Using Replication, Initial Structure

Redundancy using replication, initial structure

In this diagram, the MySQL Master holds the master database, the MySQL Slave hosts are replication slaves, and the Web Client machines are issuing database reads and writes. Web clients that issue only reads (and would normally be connected to the slaves) are not shown, as they do not need to switch to a new server in the event of failure. For a more detailed example of a read/write scale-out replication structure, see Section 17.3.4, “Using Replication for Scale-Out”.

Each MySQL Slave (Slave 1, Slave 2, and Slave 3) is a slave running with --log-bin and without --log-slave-updates. Because updates received by a slave from the master are not logged in the binary log unless --log-slave-updates is specified, the binary log on each slave is empty initially. If for some reason MySQL Master becomes unavailable, you can pick one of the slaves to become the new master. For example, if you pick Slave 1, all Web Clients should be redirected to Slave 1, which writes the updates to its binary log. Slave 2 and Slave 3 should then replicate from Slave 1.

The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master. If Slave 1 has --log-slave-updates enabled, it writes any updates that it receives from Master in its own binary log. This means that, when Slave 2 changes from Master to Slave 1 as its master, it may receive updates from Slave 1 that it has already received from Master.

Make sure that all slaves have processed any statements in their relay log. On each slave, issue STOP SLAVE IO_THREAD, then check the output of SHOW PROCESSLIST until you see Has read all relay log. When this is true for all slaves, they can be reconfigured to the new setup. On the slave Slave 1 being promoted to become the master, issue STOP SLAVE and RESET MASTER.

On the other slaves Slave 2 and Slave 3, use STOP SLAVE and CHANGE MASTER TO MASTER_HOST='Slave1' (where 'Slave1' represents the real host name of Slave 1). To use CHANGE MASTER TO, add all information about how to connect to Slave 1 from Slave 2 or Slave 3 (user, password, port). When issuing the CHANGE MASTER TO statement in this, there is no need to specify the name of the Slave 1 binary log file or log position to read from, since the first binary log file and position 4, are the defaults. Finally, execute START SLAVE on Slave 2 and Slave 3.

Once the new replication setup is in place, you need to tell each Web Client to direct its statements to Slave 1. From that point on, all updates statements sent by Web Client to Slave 1 are written to the binary log of Slave 1, which then contains every update statement sent to Slave 1 since Master died.

The resulting server structure is shown in Figure 17.5, “Redundancy Using Replication, After Master Failure”.

Figure 17.5 Redundancy Using Replication, After Master Failure

Redundancy using replication, after master failure

When Master becomes available again, you should make it a slave of Slave 1. To do this, issue on Master the same CHANGE MASTER TO statement as that issued on Slave 2 and Slave 3 previously. Master then becomes a slave of S1ave 1 and picks up the Web Client writes that it missed while it was offline.

To make Master a master again, use the preceding procedure as if Slave 1 was unavailable and Master was to be the new master. During this procedure, do not forget to run RESET MASTER on Master before making Slave 1, Slave 2, and Slave 3 slaves of Master. If you fail to do this, the slaves may pick up stale writes from the Web Client applications dating from before the point at which Master became unavailable.

You should be aware that there is no synchronization between slaves, even when they share the same master, and thus some slaves might be considerably ahead of others. This means that in some cases the procedure outlined in the previous example might not work as expected. In practice, however, relay logs on all slaves should be relatively close together.

One way to keep applications informed about the location of the master is to have a dynamic DNS entry for the master. With bind you can use nsupdate to update the DNS dynamically.

User Comments
  Posted by Zachary Buckholz on June 8, 2007
Another option instead of dynamic dns is to use a network VIP. Read-Only, Read-Write or Write-Only.

Each MySQL server master and slave(s) have two IPs. The first IP is the server's base IP. The second is a floating IP that can be changed at will.

If the master dies, just assign the IP from the master to one of the slaves.

If the master comes backup, it should check if the floating IP is in use before assigning it back to itself.
  Posted by Marian Marinov on December 3, 2009
You could consider Linux-HA for handling the migration of the Master. There are a lot of people using this software for doing just that.
  Posted by Gavin Towey on December 24, 2009

If a master dies and you switch writes to go to one of the slave, you should *never* let the failed master start taking writes again. It would have missed all the updates that happened to the slave, and now you have two copies of the database that need to be merged -- that's a nightmare.

Promoting a slave to master isn't a process that can be reversed. When the failed master comes back up, it's no longer useful. It should be rebuilt as a new slave of the new master.
  Posted by Baron Schwartz on April 26, 2010
Dynamic DNS is not a good way to do anything. DNS is the source of many problems. Use virtual IP addresses and treat DNS as static.
  Posted by Rodolfo Campos on August 19, 2010
I'd many troubles configuring a M->S1->S2 configuration. Here's a cheat sheet that I've made (in spanish):

Replicación Maestro->Esclavo1->Esclavo2 en mySQL
  Posted by rajesh karka on February 10, 2011

We are going to configure the same configuration in Linux cluster environment So Is there any specific configuration required?

  Posted by Richard Lynch on November 4, 2011
Add a log-bin with the same value to all master/slave[s] my.cnf in the mysqld section if you EVER think you might maybe need the slave to become a master. I think.

The Master Setup [1] has a sample entry. The Slave Setup [2] does not, though it discusses it in the last paragraph for "data backups and crash recovery on the slave, and also use the slave as part of a more complex replication topology (for example, where the slave acts as a master to other slaves)"

I believe the implications are:
Use log-bin on slave if you want backup/recovery
Don't use log-bin on slave if it will NEVER be a master
(Using log-bin adds disk write overhead)

I guess if your only goal is read-only slaves for blinding fast performance, not having log-bin on the slave will save some performance.

But then down the road, when you want to build a tier of
You need the log-bin anyway on that middle tier of "branches", so they can replicate to the leaves.

How much overhead does log-bin add, really?

Is it worth never being able to promote a slave to master for disaster recovery or for a complex three-tier topology of distributed "slave-master" in a middle tier? Seems to me that unless the log-bin disk write over head is quite significant, the flexibility of having a slave ready to take over as master is far more beneficial.

I am just puzzling this out for myself as I type this. I could be way off base.

If I am correct, suggested Doc Change:
# comment this out for maximum performance
# penalty: this slave cannot be made master later
log-bin=mysql-bin #MUST match Master log-bin setting

I think that suits the more common use cases, but clearly provides the pro/con for max performance users.


  Posted by Junho Whang on March 16, 2014
I believe description in this page only applies if all the slaves are at same point in recovery. If all the slaves are at different point in recovery when master fails, then the procedure described will not work. In this case, all the slaves should be running with log_slave_update enabled. So that the most advanced slave is chosen to be new master, the other slaves can be repointed to the new master with "change master" command, and the new master's log will have transactions which have not yet been applied to other slaves.
In this case, the new master should be reset with "reset slave all" not "reset master" since that will lose all the binary log information.
Sign Up Login You must be logged in to post a comment.