<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="replication-solutions-switch">
      </a>
      19.4.8 Switching Sources During Failover
     </h3>
    </div>
   </div>
  </div>
  <p>
   You can tell a replica to change to a new source using the
   <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
    <code class="literal">
     CHANGE REPLICATION SOURCE TO
    </code>
   </a>
   statement. The replica does not check whether the databases on the
      source are compatible with those on the replica; it simply begins
      reading and executing events from the specified coordinates in the
      new source'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.
  </p>
  <p>
   Replicas should be run with binary logging enabled (the
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --log-bin
    </code>
   </a>
   option), which is the
      default. If you are not using GTIDs for replication, then the
      replicas should also be run with
   <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
    <code class="option">
     --log-replica-updates=OFF
    </code>
   </a>
   (logging
      replica updates is the default). In this way, the replica is ready
      to become a source without restarting the replica
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   . Assume that you have the structure
      shown in
   <a class="xref" href="replication-solutions-switch.html#figure_replication-redundancy-before" title="Figure 19.4 Redundancy Using Replication, Initial Structure">
    Figure 19.4, “Redundancy Using Replication, Initial Structure”
   </a>
   .
  </p>
  <div class="figure">
   <a name="figure_replication-redundancy-before">
   </a>
   <p class="title">
    <b>
     Figure 19.4 Redundancy Using Replication, Initial Structure
    </b>
   </p>
   <div class="figure-contents">
    <div class="mediaobject">
     <img alt="Two web clients direct both database reads and database writes to a single MySQL source server. The MySQL source server replicates to Replica 1, Replica 2, and Replica 3." src="images/redundancy-before.png" style="width: 100%; max-width: 374px;"/>
    </div>
   </div>
  </div>
  <br class="figure-break"/>
  <p>
   In this diagram, the
   <code class="literal">
    Source
   </code>
   holds the source
      database, the
   <code class="literal">
    Replica*
   </code>
   hosts are replicas, and
      the
   <code class="literal">
    Web Client
   </code>
   machines are issuing database
      reads and writes. Web clients that issue only reads (and would
      normally be connected to the replicas) 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
   <a class="xref" href="replication-solutions-scaleout.html" title="19.4.5 Using Replication for Scale-Out">
    Section 19.4.5, “Using Replication for Scale-Out”
   </a>
   .
  </p>
  <p>
   Each MySQL replica (
   <code class="literal">
    Replica 1
   </code>
   ,
   <code class="literal">
    Replica
      2
   </code>
   , and
   <code class="literal">
    Replica 3
   </code>
   ) is a replica
      running with binary logging enabled, and with
   <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
    <code class="option">
     --log-replica-updates=OFF
    </code>
   </a>
   . Because
      updates received by a replica from the source are not written to
      the binary log when
   <code class="option">
    --log-replica-updates=OFF
   </code>
   is
      specified, the binary log on each replica is initially empty. If
      for some reason
   <code class="literal">
    Source
   </code>
   becomes unavailable, you
      can pick one of the replicas to become the new source. For
      example, if you pick
   <code class="literal">
    Replica 1
   </code>
   , all
   <code class="literal">
    Web Clients
   </code>
   should be redirected to
   <code class="literal">
    Replica 1
   </code>
   , which writes the updates to its
      binary log.
   <code class="literal">
    Replica 2
   </code>
   and
   <code class="literal">
    Replica
      3
   </code>
   should then replicate from
   <code class="literal">
    Replica
      1
   </code>
   .
  </p>
  <p>
   The reason for running the replica with
   <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
    <code class="option">
     --log-replica-updates=OFF
    </code>
   </a>
   is to
      prevent replicas from receiving updates twice in case you cause
      one of the replicas to become the new source. If
   <code class="literal">
    Replica
      1
   </code>
   has
   <code class="option">
    --log-replica-updates
   </code>
   enabled,
      which is the default, it writes any updates that it receives from
   <code class="literal">
    Source
   </code>
   in its own binary log. This means that,
      when
   <code class="literal">
    Replica 2
   </code>
   changes from
   <code class="literal">
    Source
   </code>
   to
   <code class="literal">
    Replica 1
   </code>
   as its
      source, it may receive updates from
   <code class="literal">
    Replica 1
   </code>
   that it has already received from
   <code class="literal">
    Source
   </code>
   .
  </p>
  <p>
   Make sure that all replicas have processed any statements in their
      relay log. On each replica, issue
   <code class="literal">
    STOP REPLICA
      IO_THREAD
   </code>
   , then check the output of
   <a class="link" href="show-processlist.html" title="15.7.7.31 SHOW PROCESSLIST Statement">
    <code class="literal">
     SHOW PROCESSLIST
    </code>
   </a>
   until you see
   <code class="literal">
    Has read all relay log
   </code>
   . When this is true for
      all replicas, they can be reconfigured to the new setup. On the
      replica
   <code class="literal">
    Replica 1
   </code>
   being promoted to become the
      source, issue
   <a class="link" href="stop-replica.html" title="15.4.2.5 STOP REPLICA Statement">
    <code class="literal">
     STOP REPLICA
    </code>
   </a>
   and
   <a class="link" href="reset-binary-logs-and-gtids.html" title="15.4.1.2 RESET BINARY LOGS AND GTIDS Statement">
    <code class="literal">
     RESET BINARY LOGS AND GTIDS
    </code>
   </a>
   .
  </p>
  <p>
   On the other replicas
   <code class="literal">
    Replica 2
   </code>
   and
   <code class="literal">
    Replica 3
   </code>
   , use
   <a class="link" href="stop-replica.html" title="15.4.2.5 STOP REPLICA Statement">
    <code class="literal">
     STOP
      REPLICA
    </code>
   </a>
   and
   <code class="literal">
    CHANGE REPLICATION SOURCE TO
      SOURCE_HOST='Replica1'
   </code>
   (where
   <code class="literal">
    'Replica1'
   </code>
   represents the real host name of
   <code class="literal">
    Replica 1
   </code>
   ). To use
   <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
    <code class="literal">
     CHANGE
      REPLICATION SOURCE TO
    </code>
   </a>
   , add all information about how to
      connect to
   <code class="literal">
    Replica 1
   </code>
   from
   <code class="literal">
    Replica
      2
   </code>
   or
   <code class="literal">
    Replica 3
   </code>
   (
   <em class="replaceable">
    <code>
     user
    </code>
   </em>
   ,
   <em class="replaceable">
    <code>
     password
    </code>
   </em>
   ,
   <em class="replaceable">
    <code>
     port
    </code>
   </em>
   ). When issuing the statement in
      this scenario, there is no need to specify the name of the
   <code class="literal">
    Replica 1
   </code>
   binary log file or log position to
      read from, since the first binary log file and position 4 are the
      defaults. Finally, execute
   <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
    <code class="literal">
     START
      REPLICA
    </code>
   </a>
   on
   <code class="literal">
    Replica 2
   </code>
   and
   <code class="literal">
    Replica 3
   </code>
   .
  </p>
  <p>
   Once the new replication setup is in place, you need to tell each
   <code class="literal">
    Web Client
   </code>
   to direct its statements to
   <code class="literal">
    Replica 1
   </code>
   . From that point on, all updates sent
      by
   <code class="literal">
    Web Client
   </code>
   to
   <code class="literal">
    Replica 1
   </code>
   are written to the binary log of
   <code class="literal">
    Replica 1
   </code>
   ,
      which then contains every update sent to
   <code class="literal">
    Replica
      1
   </code>
   since
   <code class="literal">
    Source
   </code>
   became unavailable.
  </p>
  <p>
   The resulting server structure is shown in
   <a class="xref" href="replication-solutions-switch.html#figure_replication-redundancy-after" title="Figure 19.5 Redundancy Using Replication, After Source Failure">
    Figure 19.5, “Redundancy Using Replication, After Source Failure”
   </a>
   .
  </p>
  <div class="figure">
   <a name="figure_replication-redundancy-after">
   </a>
   <p class="title">
    <b>
     Figure 19.5 Redundancy Using Replication, After Source Failure
    </b>
   </p>
   <div class="figure-contents">
    <div class="mediaobject">
     <img alt="The MySQL source server has failed, and is no longer connected into the replication topology. The two web clients now direct both database reads and database writes to Replica 1, which is the new source. Replica 1 replicates to Replica 2 and Replica 3." src="images/redundancy-after.png" style="width: 100%; max-width: 429px;"/>
    </div>
   </div>
  </div>
  <br class="figure-break"/>
  <p>
   When
   <code class="literal">
    Source
   </code>
   becomes available again, you should
      make it a replica of
   <code class="literal">
    Replica 1
   </code>
   . To do this,
      issue on
   <code class="literal">
    Source
   </code>
   the same
   <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
    <code class="literal">
     CHANGE REPLICATION SOURCE TO
    </code>
   </a>
   statement as that issued on
   <code class="literal">
    Replica 2
   </code>
   and
   <code class="literal">
    Replica 3
   </code>
   previously.
   <code class="literal">
    Source
   </code>
   then becomes a replica of
   <code class="literal">
    Replica 1
   </code>
   and picks
      up the
   <code class="literal">
    Web Client
   </code>
   writes that it missed while
      it was offline.
  </p>
  <p>
   To make
   <code class="literal">
    Source
   </code>
   a source again, use the
      preceding procedure as if
   <code class="literal">
    Replica 1
   </code>
   were
      unavailable and
   <code class="literal">
    Source
   </code>
   were to be the new
      source. During this procedure, do not forget to run
   <a class="link" href="reset-binary-logs-and-gtids.html" title="15.4.1.2 RESET BINARY LOGS AND GTIDS Statement">
    <code class="literal">
     RESET BINARY LOGS AND GTIDS
    </code>
   </a>
   on
   <code class="literal">
    Source
   </code>
   before making
   <code class="literal">
    Replica
      1
   </code>
   ,
   <code class="literal">
    Replica 2
   </code>
   , and
   <code class="literal">
    Replica
      3
   </code>
   replicas of
   <code class="literal">
    Source
   </code>
   . If you fail to
      do this, the replicas may pick up stale writes from the
   <code class="literal">
    Web Client
   </code>
   applications dating from before the
      point at which
   <code class="literal">
    Source
   </code>
   became unavailable.
  </p>
  <p>
   You should be aware that there is no synchronization between
      replicas, even when they share the same source, and thus some
      replicas 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
      replicas should be relatively close together.
  </p>
  <p>
   One way to keep applications informed about the location of the
      source is to have a dynamic DNS entry for the source host. With
   <code class="literal">
    BIND
   </code>
   , you can use
   <span class="command">
    <strong>
     nsupdate
    </strong>
   </span>
   to update the DNS
      dynamically.
  </p>
 </div>
 <br/>
</div>
