<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysql-cluster-replication-backups">
      </a>
      25.7.9 NDB Cluster Backups With NDB Cluster Replication
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="mysql-cluster-replication-auto-sync.html">
       25.7.9.1 NDB Cluster Replication: Automating Synchronization of the Replica to
the Source Binary Log
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="mysql-cluster-replication-pitr.html">
       25.7.9.2 Point-In-Time Recovery Using NDB Cluster Replication
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045085937456">
  </a>
  <a class="indexterm" name="idm46045085935968">
  </a>
  <a class="indexterm" name="idm46045085934512">
  </a>
  <a class="indexterm" name="idm46045085933024">
  </a>
  <p>
   This section discusses making backups and restoring from them
      using NDB Cluster replication. We assume that the replication
      servers have already been configured as covered previously (see
   <a class="xref" href="mysql-cluster-replication-preparation.html" title="25.7.5 Preparing the NDB Cluster for Replication">
    Section 25.7.5, “Preparing the NDB Cluster for Replication”
   </a>
   , and the
      sections immediately following). This having been done, the
      procedure for making a backup and then restoring from it is as
      follows:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      There are two different methods by which the backup may be
          started.
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: disc; ">
       <li class="listitem">
        <p>
         <b>
          Method A.
         </b>
         This method requires that the cluster backup process was
                previously enabled on the source server, prior to
                starting the replication process. This can be done by
                including the following line in a
         <code class="literal">
          [mysql_cluster]
         </code>
         section in the
         <code class="filename">
          my.cnf file
         </code>
         , where
         <em class="replaceable">
          <code>
           management_host
          </code>
         </em>
         is the IP
                address or host name of the
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         management server for
                the source cluster, and
         <em class="replaceable">
          <code>
           port
          </code>
         </em>
         is the management server's port number:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-ini"><div class="docs-select-all right" id="sa4031905"><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-ini"><span class="token constant">ndb-connectstring</span><span class="token attr-value"><span class="token punctuation">=</span><em class="replaceable">management_host</em>[:<em class="replaceable">port</em>]</span></code></pre>
        </div>
        <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
         <div class="admon-title">
          Note
         </div>
         <p>
          The port number needs to be specified only if the
                default port (1186) is not being used. See
          <a class="xref" href="mysql-cluster-install-configuration.html" title="25.3.3 Initial Configuration of NDB Cluster">
           Section 25.3.3, “Initial Configuration of NDB Cluster”
          </a>
          ,
                for more information about ports and port allocation in
                NDB Cluster.
         </p>
        </div>
        <p>
         <a class="indexterm" name="idm46045085918896">
         </a>
         In this case, the backup can be started by executing this
              statement on the replication source:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa97943730"><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-terminal"><span class="token prompt">shell<em class="replaceable">S</em>&gt;</span><span class="token command"> ndb_mgm</span> <span class="token property">-e</span> <span class="token atrule">"START BACKUP"</span></code></pre>
        </div>
       </li>
       <li class="listitem">
        <p>
         <b>
          Method B.
         </b>
         If the
         <code class="filename">
          my.cnf
         </code>
         file does not specify
                where to find the management host, you can start the
                backup process by passing this information to the
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         management client as
                part of the
         <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. This can be done as shown
                here, where
         <em class="replaceable">
          <code>
           management_host
          </code>
         </em>
         and
         <em class="replaceable">
          <code>
           port
          </code>
         </em>
         are the host name
                and port number of the management server:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa12062299"><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-terminal"><span class="token prompt">shell<em class="replaceable">S</em>&gt;</span><span class="token command"> ndb_mgm</span> <em class="replaceable">management_host</em><span class="token punctuation">:</span><em class="replaceable">port</em> <span class="token property">-e</span> <span class="token atrule">"START BACKUP"</span></code></pre>
        </div>
        <p>
         In our scenario as outlined earlier (see
         <a class="xref" href="mysql-cluster-replication-preparation.html" title="25.7.5 Preparing the NDB Cluster for Replication">
          Section 25.7.5, “Preparing the NDB Cluster for Replication”
         </a>
         ),
              this would be executed as follows:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa2125942"><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-terminal"><span class="token prompt">shell<em class="replaceable">S</em>&gt;</span><span class="token command"> ndb_mgm</span> rep-source<span class="token punctuation">:</span>1186 <span class="token property">-e</span> <span class="token atrule">"START BACKUP"</span></code></pre>
        </div>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Copy the cluster backup files to the replica that is being
          brought on line. Each system running an
      <a class="link" href="mysql-cluster-programs-ndbd.html" title="25.5.1 ndbd — The NDB Cluster Data Node Daemon">
       <span class="command">
        <strong>
         ndbd
        </strong>
       </span>
      </a>
      process for the source cluster has
          cluster backup files located on it, and
      <span class="emphasis">
       <em>
        all
       </em>
      </span>
      of these files must be copied to the
          replica to ensure a successful restore. The backup files can
          be copied into any directory on the computer where the
          replica's management host resides, as long as the MySQL
          and NDB binaries have read permissions in that directory. In
          this case, we assume that these files have been copied into
          the directory
      <code class="filename">
       /var/BACKUPS/BACKUP-1
      </code>
      .
     </p>
     <p>
      While it is not necessary that the replica cluster have the
          same number of data nodes as the source, it is highly
          recommended this number be the same. It
      <span class="emphasis">
       <em>
        is
       </em>
      </span>
      necessary that the replication process
          is prevented from starting when the replica server starts. You
          can do this by starting the replica with
      <a class="link" href="replication-options-replica.html#option_mysqld_skip-replica-start">
       <code class="option">
        --skip-replica-start
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Create any databases on the replica cluster that are present
          on the source cluster and that are to be replicated.
     </p>
     <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Important
      </div>
      <p>
       A
       <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
        <code class="literal">
         CREATE DATABASE
        </code>
       </a>
       (or
       <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
        <code class="literal">
         CREATE
            SCHEMA
        </code>
       </a>
       ) statement corresponding to each database
            to be replicated must be executed on each SQL node in the
            replica cluster.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Reset the replica cluster using this statement in the
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      client:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa45676273"><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">RESET</span> <span class="token keyword">REPLICA</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      You can now start the cluster restoration process on the
          replica using the
      <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>
      command for
          each backup file in turn. For the first of these, it is
          necessary to include the
      <code class="option">
       -m
      </code>
      option to restore
          the cluster metadata, as shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa84625050"><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-terminal">shell<em class="replaceable">R</em>&gt; ndb_restore <span class="token property">-c</span> <em class="replaceable">replica_host</em><span class="token punctuation">:</span><em class="replaceable">port</em> <span class="token property">-n</span> <em class="replaceable">node-id</em> \
        <span class="token property">-b</span> <em class="replaceable">backup-id</em> <span class="token property">-m</span> <span class="token property">-r</span> <em class="replaceable">dir</em></code></pre>
     </div>
     <p>
      <em class="replaceable">
       <code>
        dir
       </code>
      </em>
      is the path to the directory
          where the backup files have been placed on the replica. For
          the
      <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>
      commands corresponding to
          the remaining backup files, the
      <code class="option">
       -m
      </code>
      option
          should
      <span class="emphasis">
       <em>
        not
       </em>
      </span>
      be used.
     </p>
     <p>
      For restoring from a source cluster with four data nodes (as
          shown in the figure in
      <a class="xref" href="mysql-cluster-replication.html" title="25.7 NDB Cluster Replication">
       Section 25.7, “NDB Cluster Replication”
      </a>
      ) where the backup
          files have been copied to the directory
      <code class="filename">
       /var/BACKUPS/BACKUP-1
      </code>
      , the proper
          sequence of commands to be executed on the replica might look
          like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa83908445"><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-terminal">shell<em class="replaceable">R</em>&gt; ndb_restore <span class="token property">-c</span> replica-host<span class="token punctuation">:</span>1186 <span class="token property">-n</span> 2 <span class="token property">-b</span> 1 <span class="token property">-m</span> \
        <span class="token property">-r</span> <span class="token punctuation">.</span>/var/BACKUPS/BACKUP-1
shell<em class="replaceable">R</em>&gt; ndb_restore <span class="token property">-c</span> replica-host<span class="token punctuation">:</span>1186 <span class="token property">-n</span> 3 <span class="token property">-b</span> 1 \
        <span class="token property">-r</span> <span class="token punctuation">.</span>/var/BACKUPS/BACKUP-1
shell<em class="replaceable">R</em>&gt; ndb_restore <span class="token property">-c</span> replica-host<span class="token punctuation">:</span>1186 <span class="token property">-n</span> 4 <span class="token property">-b</span> 1 \
        <span class="token property">-r</span> <span class="token punctuation">.</span>/var/BACKUPS/BACKUP-1
shell<em class="replaceable">R</em>&gt; ndb_restore <span class="token property">-c</span> replica-host<span class="token punctuation">:</span>1186 <span class="token property">-n</span> 5 <span class="token property">-b</span> 1 <span class="token property">-e</span> \
        <span class="token property">-r</span> <span class="token punctuation">.</span>/var/BACKUPS/BACKUP-1</code></pre>
     </div>
     <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Important
      </div>
      <p>
       The
       <code class="option">
        -e
       </code>
       (or
       <a class="link" href="mysql-cluster-programs-ndb-restore.html#option_ndb_restore_restore-epoch">
        <code class="option">
         --restore-epoch
        </code>
       </a>
       ) option
            in the final invocation of
       <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>
       in
            this example is required to make sure that the epoch is
            written to the replica's
       <code class="literal">
        mysql.ndb_apply_status
       </code>
       table. Without
            this information, the replica cannot synchronize properly
            with the source. (See
       <a class="xref" href="mysql-cluster-programs-ndb-restore.html" title="25.5.23 ndb_restore — Restore an NDB Cluster Backup">
        Section 25.5.23, “ndb_restore — Restore an NDB Cluster Backup”
       </a>
       .)
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Now you need to obtain the most recent epoch from the
      <code class="literal">
       ndb_apply_status
      </code>
      table on the replica (as
          discussed in
      <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 class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93077241"><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 keyword">FROM</span> mysql<span class="token punctuation">.</span>ndb_apply_status<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Using
      <code class="literal">
       @latest
      </code>
      as the epoch value obtained
          in the previous step, you can obtain the correct starting
          position
      <code class="literal">
       @pos
      </code>
      in the correct binary log
          file
      <code class="literal">
       @file
      </code>
      from the
      <code class="literal">
       mysql.ndb_binlog_index
      </code>
      table on the source.
          The query shown here gets these from the
      <code class="literal">
       Position
      </code>
      and
      <code class="literal">
       File
      </code>
      columns from the last epoch applied before the logical restore
          position:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30028766"><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><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> epoch <span class="token operator">&gt;</span> <span class="token variable">@latest</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>
     <p>
      In the event that there is currently no replication traffic,
          you can get similar information by running
      <a class="link" href="show-binary-log-status.html" title="15.7.7.1 SHOW BINARY LOG STATUS Statement">
       <code class="literal">
        SHOW BINARY LOG STATUS
       </code>
      </a>
      on the
          source and using the value shown in the
      <code class="literal">
       Position
      </code>
      column of the output for the file
          whose name has the suffix with the greatest value for all
          files shown in the
      <code class="literal">
       File
      </code>
      column. In this
          case, you must determine which file this is and supply the
          name in the next step manually or by parsing the output with a
          script.
     </p>
    </li>
    <li class="listitem">
     <p>
      Using the values obtained in the previous step, you can now
          issue the appropriate in the replica's
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      client. Use 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="sa38868556"><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">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_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>
    </li>
    <li class="listitem">
     <p>
      Now that the replica knows from what point in which binary log
          file to start reading data from the source, you can cause the
          replica to begin replicating with this statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa53425857"><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>
   To perform a backup and restore on a second replication channel,
      it is necessary only to repeat these steps, substituting the host
      names and IDs of the secondary source and replica for those of the
      primary source and replica servers where appropriate, and running
      the preceding statements on them.
  </p>
  <p>
   For additional information on performing Cluster backups and
      restoring Cluster from backups, see
   <a class="xref" href="mysql-cluster-backup.html" title="25.6.8 Online Backup of NDB Cluster">
    Section 25.6.8, “Online Backup of NDB Cluster”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
