<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="mysql-cluster-replication-pitr">
      </a>
      25.7.9.2 Point-In-Time Recovery Using NDB Cluster Replication
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045085811312">
  </a>
  <a class="indexterm" name="idm46045085809824">
  </a>
  <a class="indexterm" name="idm46045085808368">
  </a>
  <p>
   <span class="firstterm">
    Point-in-time
   </span>
   recovery—that is, recovery of data changes made since a
        given point in time—is performed after restoring a full
        backup that returns the server to its state when the backup was
        made. Performing point-in-time recovery of NDB Cluster tables
        with NDB Cluster and NDB Cluster Replication can be accomplished
        using a native
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   data backup
        (taken by issuing
   <a class="link" href="mysql-cluster-mgm-client-commands.html#ndbclient-create-nodegroup">
    <code class="literal">
     CREATE
        BACKUP
    </code>
   </a>
   in the
   <a class="link" href="mysql-cluster-programs-ndb-mgm.html" title="25.5.5 ndb_mgm — The NDB Cluster Management Client">
    <span class="command">
     <strong>
      ndb_mgm
     </strong>
    </span>
   </a>
   client) and
        restoring the
   <code class="literal">
    ndb_binlog_index
   </code>
   table (from a
        dump made using
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   ).
  </p>
  <p>
   To perform point-in-time recovery of NDB Cluster, it is
        necessary to follow the steps shown here:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Back up all
      <code class="literal">
       NDB
      </code>
      databases in the cluster,
            using 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 in the
      <a class="link" href="mysql-cluster-programs-ndb-mgm.html" title="25.5.5 ndb_mgm — The NDB Cluster Management Client">
       <span class="command">
        <strong>
         ndb_mgm
        </strong>
       </span>
      </a>
      client (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>
    </li>
    <li class="listitem">
     <p>
      At some later point, prior to restoring the cluster, make a
            backup of the
      <code class="literal">
       mysql.ndb_binlog_index
      </code>
      table. It is probably simplest to use
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      for this task. Also back up the
            binary log files at this time.
     </p>
     <p>
      This backup should be updated regularly—perhaps even
            hourly—depending on your needs.
     </p>
    </li>
    <li class="listitem">
     <p>
      (
      <span class="emphasis">
       <em>
        Catastrophic failure or error occurs
       </em>
      </span>
      .)
     </p>
    </li>
    <li class="listitem">
     <p>
      Locate the last known good backup.
     </p>
    </li>
    <li class="listitem">
     <p>
      Clear the data node file systems (using
      <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>
      <a class="link" href="mysql-cluster-programs-ndbd.html#option_ndbd_initial">
       <code class="option">
        --initial
       </code>
      </a>
      or
      <a class="link" href="mysql-cluster-programs-ndbmtd.html" title="25.5.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded)">
       <span class="command">
        <strong>
         ndbmtd
        </strong>
       </span>
      </a>
      <a class="link" href="mysql-cluster-programs-ndbd.html#option_ndbd_initial">
       <code class="option">
        --initial
       </code>
      </a>
      ).
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Disk Data tablespace and log files are also removed by
       <a class="link" href="mysql-cluster-programs-ndbd.html#option_ndbd_initial">
        <code class="option">
         --initial
        </code>
       </a>
       .
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Use
      <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
       <code class="literal">
        DROP TABLE
       </code>
      </a>
      or
      <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
       <code class="literal">
        TRUNCATE TABLE
       </code>
      </a>
      with the
      <code class="literal">
       mysql.ndb_binlog_index
      </code>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Execute
      <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>
      , restoring all data.
            You must include the
      <a class="link" href="mysql-cluster-programs-ndb-restore.html#option_ndb_restore_restore-epoch">
       <code class="option">
        --restore-epoch
       </code>
      </a>
      option
            when you run
      <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>
      , so that the
      <code class="literal">
       ndb_apply_status
      </code>
      table is populated
            correctly. (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>
      , for
            more information.)
     </p>
    </li>
    <li class="listitem">
     <p>
      Restore the
      <code class="literal">
       ndb_binlog_index
      </code>
      table from
            the output of
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      and restore the
            binary log files from backup, if necessary.
     </p>
    </li>
    <li class="listitem">
     <p>
      Find the epoch applied most recently—that is, the
            maximum
      <code class="literal">
       epoch
      </code>
      column value in the
      <code class="literal">
       ndb_apply_status
      </code>
      table—as the user
            variable
      <code class="literal">
       @LATEST_EPOCH
      </code>
      (emphasized):
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71976648"><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 keyword">SELECT</span> <span class="token variable"><em>@LATEST_EPOCH</em></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>
      Find the latest binary log file
            (
      <code class="literal">
       @FIRST_FILE
      </code>
      ) and position
            (
      <code class="literal">
       Position
      </code>
      column value) within this file
            that correspond to
      <code class="literal">
       @LATEST_EPOCH
      </code>
      in the
      <code class="literal">
       ndb_binlog_index
      </code>
      table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17346255"><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 keyword">SELECT</span> Position<span class="token punctuation">,</span> <span class="token variable"><em>@FIRST_FILE</em></span><span class="token operator">:=</span><span class="token keyword">File</span>
    <span class="token keyword">FROM</span> mysql<span class="token punctuation">.</span>ndb_binlog_index
    <span class="token keyword">WHERE</span> epoch <span class="token operator">&gt;</span> <span class="token variable"><em>@LATEST_EPOCH</em></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>
    </li>
    <li class="listitem">
     <p>
      Using
      <a class="link" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
       <span class="command">
        <strong>
         mysqlbinlog
        </strong>
       </span>
      </a>
      , replay the binary log
            events from the given file and position up to the point of
            the failure. (See
      <a class="xref" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
       Section 6.6.9, “mysqlbinlog — Utility for Processing Binary Log Files”
      </a>
      .)
     </p>
    </li>
   </ol>
  </div>
  <p>
   See also
   <a class="xref" href="point-in-time-recovery.html" title="9.5 Point-in-Time (Incremental) Recovery">
    Section 9.5, “Point-in-Time (Incremental) Recovery”
   </a>
   , for more
        information about the binary log, replication, and incremental
        recovery.
  </p>
 </div>
 <br/>
</div>
