<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="point-in-time-recovery-positions">
      </a>
      9.5.2 Point-in-Time Recovery Using Event Positions
     </h3>
    </div>
   </div>
  </div>
  <p>
   The last section,
   <a class="xref" href="point-in-time-recovery-binlog.html" title="9.5.1 Point-in-Time Recovery Using Binary Log">
    Section 9.5.1, “Point-in-Time Recovery Using Binary Log”
   </a>
   , explains the
        general idea of using the binary log to perform a
        point-in-time-recovery. The section explains the operation in
        details with an example.
  </p>
  <p>
   As an example, suppose that around 20:06:00 on March 11, 2020,
        an SQL statement was executed that deleted a table. You can
        perform a point-in-time recovery to restore the server up to its
        state right before the table deletion. These are some sample
        steps to achieve that:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Restore the last full backup created before the
            point-in-time of interest (call it
      <code class="literal">
       t
       <sub>
        p
       </sub>
      </code>
      , which is
            20:06:00 on March 11, 2020 in our example). When finished,
            note the binary log position up to which you have restored
            the server for later use, and restart the server.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       While the last binary log position recovered is also
              displayed by InnoDB after the restore and server restart,
              that is
       <span class="emphasis">
        <em>
         not
        </em>
       </span>
       a reliable means for
              obtaining the ending log position of your restore, as
              there could be DDL events and non-InnoDB changes that have
              taken place after the time reflected by the displayed
              position. Your backup and restore tool should provide you
              with the last binary log position for your recovery: for
              example, if you are 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>
       for the task, check the stop position of the binary log
              replay; if you are using MySQL Enterprise Backup, the last binary log
              position has been saved in your backup. See
       <a class="ulink" href="/doc/mysql-enterprise-backup/8.4/en/advanced.point.html" target="_top">
        Point-in-Time Recovery
       </a>
       .
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Find the precise binary log event position corresponding to
            the point in time up to which you want to restore your
            database. In our example, given that we know the rough time
            where the table deletion took place
            (
      <code class="literal">
       t
       <sub>
        p
       </sub>
      </code>
      ), we can find
            the log position by checking the log contents around that
            time using the
      <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>
      utility. Use
            the
      <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_start-datetime">
       <code class="option">
        --start-datetime
       </code>
      </a>
      and
      <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_stop-datetime">
       <code class="option">
        --stop-datetime
       </code>
      </a>
      options
            to specify a short time period around
      <code class="literal">
       t
       <sub>
        p
       </sub>
      </code>
      , and then look
            for the event in the output. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa18339269"><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">$&gt; </span><span class="token command">mysqlbinlog</span> <span class="token constant">--start-datetime</span>=<span class="token atrule">"2020-03-11 20:05:00"</span> \
                   <span class="token constant">--stop-datetime</span>=<span class="token atrule">"2020-03-11 20:08:00"</span> <span class="token property">--verbose</span> \
         /var/lib/mysql/bin<span class="token punctuation">.</span>123456 | grep <span class="token property">-C</span> 15 <span class="token atrule">"DROP TABLE"</span>
 
/*!80014 SET @@session<span class="token punctuation">.</span>original_server_version<span class="token attr-value"><span class="token punctuation">=</span>80019*//*!*/;</span>
/*!80014 SET @@session<span class="token punctuation">.</span>immediate_server_version<span class="token attr-value"><span class="token punctuation">=</span>80019*//*!*/;</span>
SET @@SESSION<span class="token punctuation">.</span>GTID_NEXT= <span class="token atrule">'ANONYMOUS'</span>/*!*/<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true"># at 232</span>
<span class="token comment" spellcheck="true">#200311 20:06:20 server id 1  end_log_pos 355 CRC32 0x2fc1e5ea 	Query	thread_id=16	exec_time=0	error_code=0</span>
SET TIMESTAMP<span class="token attr-value"><span class="token punctuation">=</span>1583971580/*!*/;</span>
SET @@session<span class="token punctuation">.</span>pseudo_thread_id<span class="token attr-value"><span class="token punctuation">=</span>16/*!*/;</span>
SET @@session<span class="token punctuation">.</span>foreign_key_checks<span class="token attr-value"><span class="token punctuation">=</span>1,</span> @@session<span class="token punctuation">.</span>sql_auto_is_null<span class="token attr-value"><span class="token punctuation">=</span>0,</span> @@session<span class="token punctuation">.</span>unique_checks<span class="token attr-value"><span class="token punctuation">=</span>1,</span> @@session<span class="token punctuation">.</span>autocommit<span class="token attr-value"><span class="token punctuation">=</span>1/*!*/;</span>
SET @@session<span class="token punctuation">.</span>sql_mode<span class="token attr-value"><span class="token punctuation">=</span>1168113696/*!*/;</span>
SET @@session<span class="token punctuation">.</span>auto_increment_increment<span class="token attr-value"><span class="token punctuation">=</span>1,</span> @@session<span class="token punctuation">.</span>auto_increment_offset<span class="token attr-value"><span class="token punctuation">=</span>1/*!*/;</span>
/*!\C utf8mb4 *//*!*/<span class="token punctuation">;</span>
SET @@session<span class="token punctuation">.</span>character_set_client<span class="token attr-value"><span class="token punctuation">=</span>255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;</span>
SET @@session<span class="token punctuation">.</span>lc_time_names<span class="token attr-value"><span class="token punctuation">=</span>0/*!*/;</span>
SET @@session<span class="token punctuation">.</span>collation_database<span class="token attr-value"><span class="token punctuation">=</span>DEFAULT/*!*/;</span>
/*!80011 SET @@session<span class="token punctuation">.</span>default_collation_for_utf8mb4<span class="token attr-value"><span class="token punctuation">=</span>255*//*!*/;</span>
DROP TABLE `pets`<span class="token punctuation">.</span>`cats` /* generated by server */
/*!*/<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true"># at 355</span>
<span class="token comment" spellcheck="true">#200311 20:07:48 server id 1  end_log_pos 434 CRC32 0x123d65df 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1583971668462467	immediate_commit_timestamp=1583971668462467	transaction_length=473</span>
<span class="token comment" spellcheck="true"># original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)</span>
<span class="token comment" spellcheck="true"># immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)</span>
/*!80001 SET @@session<span class="token punctuation">.</span>original_commit_timestamp<span class="token attr-value"><span class="token punctuation">=</span>1583971668462467*//*!*/;</span>
/*!80014 SET @@session<span class="token punctuation">.</span>original_server_version<span class="token attr-value"><span class="token punctuation">=</span>80019*//*!*/;</span>
/*!80014 SET @@session<span class="token punctuation">.</span>immediate_server_version<span class="token attr-value"><span class="token punctuation">=</span>80019*//*!*/;</span>
SET @@SESSION<span class="token punctuation">.</span>GTID_NEXT= <span class="token atrule">'ANONYMOUS'</span>/*!*/<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true"># at 434</span>
<span class="token comment" spellcheck="true">#200311 20:07:48 server id 1  end_log_pos 828 CRC32 0x57fac9ac 	Query	thread_id=16	exec_time=0	error_code=0	Xid = 217</span>
use `pets`/*!*/<span class="token punctuation">;</span>
SET TIMESTAMP<span class="token attr-value"><span class="token punctuation">=</span>1583971668/*!*/;</span>
/*!80013 SET @@session<span class="token punctuation">.</span>sql_require_primary_key<span class="token attr-value"><span class="token punctuation">=</span>0*//*!*/;</span>
CREATE TABLE dogs</code></pre>
     </div>
     <p>
      From the output of
      <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>
      , the
      <code class="literal">
       DROP TABLE `pets`.`cats`
      </code>
      statement can be
            found in the segment of the binary log between the line
      <code class="literal">
       # at 232
      </code>
      and
      <code class="literal">
       # at 355
      </code>
      ,
            which means the statement takes place
      <span class="emphasis">
       <em>
        after
       </em>
      </span>
      the log position 232, and the log
            is at position 355 after the
      <code class="literal">
       DROP TABLE
      </code>
      statement.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Only use the
       <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_start-datetime">
        <code class="option">
         --start-datetime
        </code>
       </a>
       and
       <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_stop-datetime">
        <code class="option">
         --stop-datetime
        </code>
       </a>
       options to help you find the actual event positions of
              interest. Using the two options to specify the range of
              binary log segment to apply is not recommended: there is a
              higher risk of missing binary log events when using the
              options. Use
       <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_start-position">
        <code class="option">
         --start-position
        </code>
       </a>
       and
       <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_stop-position">
        <code class="option">
         --stop-position
        </code>
       </a>
       instead.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Apply the events in binary log file to the server, starting
            with the log position your found in step 1 (assume it is
            155) and ending at the position you have found in step 2
            that is
      <span class="emphasis">
       <em>
        before
       </em>
      </span>
      your point-in-time of
            interest (which is 232):
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa77577236"><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">$&gt; </span><span class="token command">mysqlbinlog</span> <span class="token constant">--start-position</span><span class="token attr-value"><span class="token punctuation">=</span>155</span> <span class="token constant">--stop-position</span><span class="token attr-value"><span class="token punctuation">=</span>232</span> /var/lib/mysql/bin<span class="token punctuation">.</span>123456 \
         | mysql <span class="token property">-u</span> root <span class="token property">-p</span></code></pre>
     </div>
     <p>
      The command recovers all the transactions from the starting
            position until just before the stop position. Because the
            output of
      <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>
      includes
      <code class="literal">
       SET TIMESTAMP
      </code>
      statements before each SQL
            statement recorded, the recovered data and related MySQL
            logs reflect the original times at which the transactions
            were executed.
     </p>
     <p>
      Your database has now been restored to the point-in-time of
            interest,
      <code class="literal">
       t
       <sub>
        p
       </sub>
      </code>
      ,
            right before the table
      <code class="literal">
       pets.cats
      </code>
      was
            dropped.
     </p>
    </li>
    <li class="listitem">
     <p>
      Beyond the point-in-time recovery that has been finished, if
            you also want to reexecute all the statements
      <span class="emphasis">
       <em>
        after
       </em>
      </span>
      your point-in-time of interest,
            use
      <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>
      again to apply all the
            events after
      <code class="literal">
       t
       <sub>
        p
       </sub>
      </code>
      to
            the server. We noted in step 2 that after the statement we
            wanted to skip, the log is at position 355; we can use it
            for the
      <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_start-position">
       <code class="option">
        --start-position
       </code>
      </a>
      option, so that any statements after the position are
            included:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa4423772"><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">$&gt; </span><span class="token command">mysqlbinlog</span> <span class="token constant">--start-position</span><span class="token attr-value"><span class="token punctuation">=</span>355</span> /var/lib/mysql/bin<span class="token punctuation">.</span>123456 \
         | mysql <span class="token property">-u</span> root <span class="token property">-p</span></code></pre>
     </div>
     <p>
      Your database has been restored the latest statement
            recorded in the binary log file, but with the selected event
            skipped.
     </p>
    </li>
   </ol>
  </div>
 </div>
 <br/>
</div>
