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 16.4, “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 16.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 16.5, “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.
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
master->>distributed-slave-masters->>>>slaves
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.
NOTE:
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.
1: http://dev.mysql.com/doc/refman/5.0/en/replication-howto-masterbaseconfig.html
2: http://dev.mysql.com/doc/refman/5.5/en/replication-howto-slavebaseconfig.html#c12063
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.