<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysql-cluster-replication-failover">
      </a>
      25.7.8 Implementing Failover with NDB Cluster Replication
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045086002880">
  </a>
  <a class="indexterm" name="idm46045086001392">
  </a>
  <p>
   In the event that the primary Cluster replication process fails,
      it is possible to switch over to the secondary replication
      channel. The following procedure describes the steps required to
      accomplish this.
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045085997984">
      </a>
      <a class="indexterm" name="idm46045085996880">
      </a>
      Obtain the time of the most recent global checkpoint (GCP).
          That is, you need to determine the most recent epoch from the
      <code class="literal">
       ndb_apply_status
      </code>
      table on the replica
          cluster, which can be found using the following query:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76617850"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">mysql<em class="replaceable">R'</em><span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@latest</span><span class="token operator">:=</span><span class="token function">MAX</span><span class="token punctuation">(</span>epoch<span class="token punctuation">)</span>
<span class="token prompt">      -&gt;</span>        <span class="token keyword">FROM</span> mysql<span class="token punctuation">.</span>ndb_apply_status<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      In a circular replication topology, with a source and a
          replica running on each host, when you are using
      <a class="link" href="mysql-cluster-options-variables.html#sysvar_ndb_log_apply_status">
       <code class="literal">
        ndb_log_apply_status=1
       </code>
      </a>
      , NDB
          Cluster epochs are written in the replicas' binary logs.
          This means that the
      <code class="literal">
       ndb_apply_status
      </code>
      table
          contains information for the replica on this host as well as
          for any other host which acts as a replica of the replication
          source server running on this host.
     </p>
     <p>
      In this case, you need to determine the latest epoch on this
          replica to the exclusion of any epochs from any other replicas
          in this replica's binary log that were not listed in the
      <code class="literal">
       IGNORE_SERVER_IDS
      </code>
      options of 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 used to set up this replica. The reason for
          excluding such epochs is that rows in the
      <code class="literal">
       mysql.ndb_apply_status
      </code>
      table whose server
          IDs have a match in the
      <code class="literal">
       IGNORE_SERVER_IDS
      </code>
      list from 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 used to prepare this replicas's
          source are also considered to be from local servers, in
          addition to those having the replica's own server ID. You
          can retrieve this list as
      <code class="literal">
       Replicate_Ignore_Server_Ids
      </code>
      from the output
          of
      <a class="link" href="show-replica-status.html" title="15.7.7.35 SHOW REPLICA STATUS Statement">
       <code class="literal">
        SHOW REPLICA STATUS
       </code>
      </a>
      . We
          assume that you have obtained this list and are substituting
          it for
      <em class="replaceable">
       <code>
        ignore_server_ids
       </code>
      </em>
      in the
          query shown here, which like the previous version of the
          query, selects the greatest epoch into a variable named
      <code class="literal">
       @latest
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87911278"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">mysql<em class="replaceable">R'</em><span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@latest</span><span class="token operator">:=</span><span class="token function">MAX</span><span class="token punctuation">(</span>epoch<span class="token punctuation">)</span>
<span class="token prompt">      -&gt;</span>        <span class="token keyword">FROM</span> mysql<span class="token punctuation">.</span>ndb_apply_status
<span class="token prompt">      -&gt;</span>        <span class="token keyword">WHERE</span> server_id <span class="token operator">NOT</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">ignore_server_ids</em></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      In some cases, it may be simpler or more efficient (or both)
          to use a list of the server IDs to be included and
      <code class="literal">
       server_id IN
       <em class="replaceable">
        <code>
         server_id_list
        </code>
       </em>
      </code>
      in the
      <code class="literal">
       WHERE
      </code>
      condition of the preceding query.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045085973120">
      </a>
      Using the information obtained from the query shown in Step 1,
          obtain the corresponding records from the
      <code class="literal">
       ndb_binlog_index
      </code>
      table on the source
          cluster.
     </p>
     <p>
      You can use the following query to obtain the needed records
          from the
      <code class="literal">
       ndb_binlog_index
      </code>
      table on the
          source:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97054576"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql<em class="replaceable">S'</em>&gt;</span> <span class="token keyword">SELECT</span>
<span class="token prompt">    -&gt;</span>     <span class="token variable">@file</span><span class="token operator">:=</span><span class="token function">SUBSTRING_INDEX</span><span class="token punctuation">(</span>next_file<span class="token punctuation">,</span> <span class="token string">'/'</span><span class="token punctuation">,</span> <span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token variable">@pos</span><span class="token operator">:=</span>next_position
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span> mysql<span class="token punctuation">.</span>ndb_binlog_index
<span class="token prompt">    -&gt;</span> <span class="token keyword">WHERE</span> epoch <span class="token operator">=</span> <span class="token variable">@latest</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      These are the records saved on the source since the failure of
          the primary replication channel. We have employed a user
          variable
      <code class="literal">
       @latest
      </code>
      here to represent the
          value obtained in Step 1. Of course, it is not possible for
          one
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      instance to access user
          variables set on another server instance directly. These
          values must be
      <span class="quote">
       “
       <span class="quote">
        plugged in
       </span>
       ”
      </span>
      to the second query
          manually or by an application.
     </p>
     <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Important
      </div>
      <p>
       You must ensure that the replica
       <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
        <span class="command">
         <strong>
          mysqld
         </strong>
        </span>
       </a>
       is started with
       <a class="link" href="replication-options-replica.html#sysvar_replica_skip_errors">
        <code class="option">
         --replica-skip-errors=ddl_exist_errors
        </code>
       </a>
       before executing
       <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
        <code class="literal">
         START
            REPLICA
        </code>
       </a>
       . Otherwise, replication may stop with
            duplicate DDL errors.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Now it is possible to synchronize the secondary channel by
          running the following query on the secondary replica server:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29860787"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">mysql<em class="replaceable">R<span class="token string">'</span></em><span class="token string">&gt; CHANGE REPLICATION SOURCE TO
      -&gt;     SOURCE_LOG_FILE='</span><span class="token variable">@file</span>'<span class="token punctuation">,</span>
<span class="token prompt">      -&gt;</span>     <span class="token keyword">SOURCE_LOG_POS</span><span class="token operator">=</span><span class="token variable">@pos</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Again we have employed user variables (in this case
      <code class="literal">
       @file
      </code>
      and
      <code class="literal">
       @pos
      </code>
      ) to
          represent the values obtained in Step 2 and applied in Step 3;
          in practice these values must be inserted manually or using an
          application that can access both of the servers involved.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       <code class="literal">
        @file
       </code>
       is a string value such as
       <code class="literal">
        '/var/log/mysql/replication-source-bin.00001'
       </code>
       ,
            and so must be quoted when used in SQL or application code.
            However, the value represented by
       <code class="literal">
        @pos
       </code>
       must
       <span class="emphasis">
        <em>
         not
        </em>
       </span>
       be quoted. Although MySQL
            normally attempts to convert strings to numbers, this case
            is an exception.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      You can now initiate replication on the secondary channel by
          issuing the appropriate command on the secondary replica
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa35445594"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">mysql<em class="replaceable">R'</em><span class="token operator">&gt;</span> <span class="token keyword">START</span> <span class="token keyword">REPLICA</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
  <p>
   Once the secondary replication channel is active, you can
      investigate the failure of the primary and effect repairs. The
      precise actions required to do this depend upon the reasons for
      which the primary channel failed.
  </p>
  <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Warning
   </div>
   <p>
    The secondary replication channel is to be started only if and
        when the primary replication channel has failed. Running
        multiple replication channels simultaneously can result in
        unwanted duplicate records being created on the replicas.
   </p>
  </div>
  <p>
   If the failure is limited to a single server, it should in theory
      be possible to replicate from
   <em class="replaceable">
    <code>
     S
    </code>
   </em>
   to
   <em class="replaceable">
    <code>
     R'
    </code>
   </em>
   , or from
   <em class="replaceable">
    <code>
     S'
    </code>
   </em>
   to
   <em class="replaceable">
    <code>
     R
    </code>
   </em>
   .
  </p>
 </div>
 <br/>
</div>
