<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysql-cluster-replication-multi-source">
      </a>
      25.7.10 NDB Cluster Replication: Bidirectional and Circular Replication
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045085751840">
  </a>
  <a class="indexterm" name="idm46045085750416">
  </a>
  <a class="indexterm" name="idm46045085748928">
  </a>
  <a class="indexterm" name="idm46045085747424">
  </a>
  <p>
   It is possible to use NDB Cluster for bidirectional replication
      between two clusters, as well as for circular replication between
      any number of clusters.
  </p>
  <p>
   <b>
    Circular replication example.
   </b>
   In the next few paragraphs we consider the example of a
        replication setup involving three NDB Clusters numbered 1, 2,
        and 3, in which Cluster 1 acts as the replication source for
        Cluster 2, Cluster 2 acts as the source for Cluster 3, and
        Cluster 3 acts as the source for Cluster 1. Each cluster has two
        SQL nodes, with SQL nodes A and B belonging to Cluster 1, SQL
        nodes C and D belonging to Cluster 2, and SQL nodes E and F
        belonging to Cluster 3.
  </p>
  <p>
   Circular replication using these clusters is supported as long as
      the following conditions are met:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The SQL nodes on all sources and replicas are the same.
     </p>
    </li>
    <li class="listitem">
     <p>
      All SQL nodes acting as sources and replicas are started with
          the system variable
      <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
       <code class="literal">
        log_replica_updates
       </code>
      </a>
      enabled.
     </p>
    </li>
   </ul>
  </div>
  <p>
   This type of circular replication setup is shown in the following
      diagram:
  </p>
  <div class="figure">
   <a name="fig-mysql-cluster-replication-circular-all-sources-as-replicas">
   </a>
   <p class="title">
    <b>
     Figure 25.14 NDB Cluster Circular Replication with All Sources As Replicas
    </b>
   </p>
   <div class="figure-contents">
    <div class="mediaobject">
     <img alt="Some content is described in the surrounding text. The diagram shows three clusters, each with two nodes. Arrows connecting SQL nodes in different clusters illustrate that all sources are also replicas." src="images/cluster-circular-replication-1.png" style="width: 100%; max-width: 557px;"/>
    </div>
   </div>
  </div>
  <br class="figure-break"/>
  <p>
   In this scenario, SQL node A in Cluster 1 replicates to SQL node C
      in Cluster 2; SQL node C replicates to SQL node E in Cluster 3;
      SQL node E replicates to SQL node A. In other words, the
      replication line (indicated by the curved arrows in the diagram)
      directly connects all SQL nodes used as replication sources and
      replicas.
  </p>
  <p>
   It is also possible to set up circular replication in such a way
      that not all source SQL nodes are also replicas, as shown here:
  </p>
  <div class="figure">
   <a name="fig-mysql-cluster-replication-circular-not-all-sources-as-replicas">
   </a>
   <p class="title">
    <b>
     Figure 25.15 NDB Cluster Circular Replication Where Not All Sources Are Replicas
    </b>
   </p>
   <div class="figure-contents">
    <div class="mediaobject">
     <img alt="Some content is described in the surrounding text. The diagram shows three clusters, each with two nodes. Arrows connecting SQL nodes in different clusters illustrate that not all sources are replicas." src="images/cluster-circular-replication-2.png" style="width: 100%; max-width: 542px;"/>
    </div>
   </div>
  </div>
  <br class="figure-break"/>
  <p>
   In this case, different SQL nodes in each cluster are used as
      replication sources and replicas. You must
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   start any of the SQL nodes with the
      system variable
   <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
    <code class="literal">
     log_replica_updates
    </code>
   </a>
   enabled. This
      type of circular replication scheme for NDB Cluster, in which the
      line of replication (again indicated by the curved arrows in the
      diagram) is discontinuous, should be possible, but it should be
      noted that it has not yet been thoroughly tested and must
      therefore still be considered experimental.
  </p>
  <p>
   <b>
    Using NDB-native backup and restore to initialize a replica cluster.
   </b>
   <a class="indexterm" name="idm46045085720464">
   </a>
   When setting up circular replication, it is possible to
        initialize the replica cluster by using the management client
   <a class="link" href="mysql-cluster-backup-using-management-client.html" title="25.6.8.2 Using The NDB Cluster Management Client to Create a Backup">
    <code class="literal">
     START BACKUP
    </code>
   </a>
   command on one
        NDB Cluster to create a backup and then applying this backup on
        another NDB Cluster using
   <a class="link" href="mysql-cluster-programs-ndb-restore.html" title="25.5.23 ndb_restore — Restore an NDB Cluster Backup">
    <span class="command">
     <strong>
      ndb_restore
     </strong>
    </span>
   </a>
   . This
        does not automatically create binary logs on the second NDB
        Cluster's SQL node acting as the replica; in order to cause
        the binary logs to be created, you must issue a
   <a class="link" href="show-tables.html" title="15.7.7.39 SHOW TABLES Statement">
    <code class="literal">
     SHOW TABLES
    </code>
   </a>
   statement on that SQL
        node; this should be done prior to running
   <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
    <code class="literal">
     START REPLICA
    </code>
   </a>
   . This is a known
        issue.
  </p>
  <p>
   <b>
    Multi-source failover example.
   </b>
   In this section, we discuss failover in a multi-source NDB
        Cluster replication setup with three NDB Clusters having server
        IDs 1, 2, and 3. In this scenario, Cluster 1 replicates to
        Clusters 2 and 3; Cluster 2 also replicates to Cluster 3. This
        relationship is shown here:
  </p>
  <div class="figure">
   <a name="fig-mysql-cluster-replication-3-sources">
   </a>
   <p class="title">
    <b>
     Figure 25.16 NDB Cluster Multi-Source Replication With 3 Sources
    </b>
   </p>
   <div class="figure-contents">
    <div class="mediaobject">
     <img alt="Multi-source NDB Cluster replication setup with three NDB Clusters having server IDs 1, 2, and 3; Cluster 1 replicates to Clusters 2 and 3; Cluster 2 also replicates to Cluster 3." src="images/cluster-replication-multi-source.png" style="width: 100%; max-width: 366px;"/>
    </div>
   </div>
  </div>
  <br class="figure-break"/>
  <p>
   In other words, data replicates from Cluster 1 to Cluster 3
      through 2 different routes: directly, and by way of Cluster 2.
  </p>
  <p>
   Not all MySQL servers taking part in multi-source replication must
      act as both source and replica, and a given NDB Cluster might use
      different SQL nodes for different replication channels. Such a
      case is shown here:
  </p>
  <div class="figure">
   <a name="fig-mysql-cluster-replication-mysql-servers">
   </a>
   <p class="title">
    <b>
     Figure 25.17 NDB Cluster Multi-Source Replication, With MySQL Servers
    </b>
   </p>
   <div class="figure-contents">
    <div class="mediaobject">
     <img alt="Concepts are described in the surrounding text. Shows three nodes: SQL node A in Cluster 1 replicates to SQL node F in Cluster 3; SQL node B in Cluster 1 replicates to SQL node C in Cluster 2; SQL node E in Cluster 3 replicates to SQL node G in Cluster 3. SQL nodes A and B in cluster 1 have --log-replica-updates=0; SQL nodes C in Cluster 2, and SQL nodes F and G in Cluster 3 have --log-replica-updates=1; and SQL nodes D and E in Cluster 2 have --log-replica-updates=0." src="images/cluster-replication-multi-source-mysqlds.png" style="width: 100%; max-width: 692px;"/>
    </div>
   </div>
  </div>
  <br class="figure-break"/>
  <p>
   MySQL servers acting as replicas must be run with the system
      variable
   <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
    <code class="literal">
     log_replica_updates
    </code>
   </a>
   enabled. Which
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   processes require this
      option is also shown in the preceding diagram.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Using the
    <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
     <code class="literal">
      log_replica_updates
     </code>
    </a>
    system variable has no effect on servers not being run as
        replicas.
   </p>
  </div>
  <p>
   The need for failover arises when one of the replicating clusters
      goes down. In this example, we consider the case where Cluster 1
      is lost to service, and so Cluster 3 loses 2 sources of updates
      from Cluster 1. Because replication between NDB Clusters is
      asynchronous, there is no guarantee that Cluster 3's updates
      originating directly from Cluster 1 are more recent than those
      received through Cluster 2. You can handle this by ensuring that
      Cluster 3 catches up to Cluster 2 with regard to updates from
      Cluster 1. In terms of MySQL servers, this means that you need to
      replicate any outstanding updates from MySQL server C to server F.
  </p>
  <p>
   On server C, perform the following queries:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95778812"><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">mysqlC&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">=</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token prompt">mysqlC&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><span class="token keyword">File</span><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>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> orig_epoch <span class="token operator">&gt;=</span> <span class="token variable">@latest</span>
<span class="token prompt">     -&gt;</span>     <span class="token operator">AND</span> orig_server_id <span class="token operator">=</span> <span class="token number">1</span>
<span class="token prompt">     -&gt;</span>     <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> epoch <span class="token keyword">ASC</span> <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    You can improve the performance of this query, and thus likely
        speed up failover times significantly, by adding the appropriate
        index to the
    <code class="literal">
     ndb_binlog_index
    </code>
    table. See
    <a class="xref" href="mysql-cluster-replication-schema.html" title="25.7.4 NDB Cluster Replication Schema and Tables">
     Section 25.7.4, “NDB Cluster Replication Schema and Tables”
    </a>
    , for more
        information.
   </p>
  </div>
  <p>
   Copy over the values for
   <em class="replaceable">
    <code>
     @file
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     @pos
    </code>
   </em>
   manually from server C to server F
      (or have your application perform the equivalent). Then, on server
      F, execute the following
   <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:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa23378357"><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">mysqlF<span class="token operator">&gt;</span> <span class="token keyword">CHANGE</span> <span class="token keyword">REPLICATION</span> <span class="token keyword">SOURCE</span> <span class="token keyword">TO</span>
<span class="token prompt">     -&gt;</span>     <span class="token keyword">SOURCE_HOST</span> <span class="token operator">=</span> <span class="token string">'serverC'</span>
<span class="token prompt">     -&gt;</span>     <span class="token keyword">SOURCE_LOG_FILE</span><span class="token operator">=</span><span class="token string">'@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>
   Once this has been done, you can issue a
   <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
    <code class="literal">
     START REPLICA
    </code>
   </a>
   statement on MySQL
      server F; this causes any missing updates originating from server
      B to be replicated to server F.
  </p>
  <p>
   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 also supports an
   <a class="link" href="change-replication-source-to.html#crs-opt-ignore_server_ids">
    <code class="literal">
     IGNORE_SERVER_IDS
    </code>
   </a>
   option which
      takes a comma-separated list of server IDs and causes events
      originating from the corresponding servers to be ignored. See the
      documentation for this statement for more information, as well as
   <a class="xref" href="show-replica-status.html" title="15.7.7.35 SHOW REPLICA STATUS Statement">
    Section 15.7.7.35, “SHOW REPLICA STATUS Statement”
   </a>
   . For information about how
      this option interacts with the
   <a class="link" href="mysql-cluster-options-variables.html#sysvar_ndb_log_apply_status">
    <code class="literal">
     ndb_log_apply_status
    </code>
   </a>
   variable,
      see
   <a class="xref" href="mysql-cluster-replication-failover.html" title="25.7.8 Implementing Failover with NDB Cluster Replication">
    Section 25.7.8, “Implementing Failover with NDB Cluster Replication”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
