<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="point-in-time-recovery-binlog">
      </a>
      9.5.1 Point-in-Time Recovery Using Binary Log
     </h3>
    </div>
   </div>
  </div>
  <p>
   This section explains the general idea of using the binary log
        to perform a point-in-time-recovery. The next section,
   <a class="xref" href="point-in-time-recovery-positions.html" title="9.5.2 Point-in-Time Recovery Using Event Positions">
    Section 9.5.2, “Point-in-Time Recovery Using Event Positions”
   </a>
   , explains the
        operation in details with an example.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Many of the examples in this and the next section use 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 to process binary log output
          produced by
    <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>
    . If your binary log
          contains
    <code class="literal">
     \0
    </code>
    (null) characters, that output
          cannot be parsed by
    <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     <span class="command">
      <strong>
       mysql
      </strong>
     </span>
    </a>
    unless you invoke
          it with the
    <a class="link" href="mysql-command-options.html#option_mysql_binary-mode">
     <code class="option">
      --binary-mode
     </code>
    </a>
    option.
   </p>
  </div>
  <p>
   The source of information for point-in-time recovery is the set
        of binary log files generated subsequent to the full backup
        operation. Therefore, to allow a server to be restored to a
        point-in-time, binary logging must be enabled on it, which is
        the default setting for MySQL 8.4


        (see
   <a class="xref" href="binary-log.html" title="7.4.4 The Binary Log">
    Section 7.4.4, “The Binary Log”
   </a>
   ).
  </p>
  <p>
   To restore data from the binary log, you must know the name and
        location of the current binary log files. By default, the server
        creates binary log files in the data directory, but a path name
        can be specified with the
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --log-bin
    </code>
   </a>
   option to place the
        files in a different location. To see a listing of all binary
        log files, use this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa82463346"><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&gt;</span> <span class="token keyword">SHOW</span> <span class="token datatype">BINARY</span> <span class="token keyword">LOGS</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To determine the name of the current binary log file, issue the
        following statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa79937189"><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&gt;</span> <span class="token keyword">SHOW</span> <span class="token datatype">BINARY</span> LOG <span class="token keyword">STATUS</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   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 converts the events
        in the binary log files from binary format to text so that they
        can be viewed or applied.
   <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>
   has
        options for selecting sections of the binary log based on event
        times or position of events within the log. 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>
  <p>
   Applying events from the binary log causes the data
        modifications they represent to be reexecuted. This enables
        recovery of data changes for a given span of time. To apply
        events from the binary log, process
   <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>
   output using 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-terminal"><div class="docs-select-all right" id="sa5857827"><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> <em class="replaceable">binlog_files</em> | mysql <span class="token property">-u</span> root <span class="token property">-p</span></code></pre>
  </div>
  <p>
   If binary log files have been encrypted,
   <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>
   cannot read them directly as in
        the previous example, but can read them from the server using
        the
   <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_read-from-remote-server">
    <code class="option">
     --read-from-remote-server
    </code>
   </a>
   (
   <code class="literal">
    -R
   </code>
   ) option. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa56632642"><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 property">--read-from-remote-server</span> <span class="token constant">--host</span><span class="token attr-value"><span class="token punctuation">=</span><em class="replaceable">host_name</em></span> <span class="token constant">--port</span><span class="token attr-value"><span class="token punctuation">=</span>3306</span>  <span class="token constant">--user</span><span class="token attr-value"><span class="token punctuation">=</span>root</span> <span class="token property">--password</span> <span class="token constant">--ssl-mode</span><span class="token attr-value"><span class="token punctuation">=</span>required</span>  <em class="replaceable">binlog_files</em> | mysql <span class="token property">-u</span> root <span class="token property">-p</span></code></pre>
  </div>
  <p>
   Here, the option
   <code class="option">
    --ssl-mode=required
   </code>
   has been
        used to ensure that the data from the binary log files is
        protected in transit, because it is sent to
   <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>
   in an unencrypted format.
  </p>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    <code class="literal">
     VERIFY_CA
    </code>
    and
    <code class="literal">
     VERIFY_IDENTITY
    </code>
    are better choices than
    <code class="literal">
     REQUIRED
    </code>
    for the SSL mode, because they
          help prevent man-in-the-middle attacks. To implement one of
          these settings, you must first ensure that the CA certificate
          for the server is reliably available to all the clients that
          use it in your environment, otherwise availability issues will
          result. See
    <a class="xref" href="connection-options.html#encrypted-connection-options" title="Command Options for Encrypted Connections">
     Command Options for Encrypted Connections
    </a>
    .
   </p>
  </div>
  <p>
   Viewing log contents can be useful when you need to determine
        event times or positions to select partial log contents prior to
        executing events. To view events from the log, send
   <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>
   output into a paging program:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa77113729"><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> <em class="replaceable">binlog_files</em> | more</code></pre>
  </div>
  <p>
   Alternatively, save the output in a file and view the file in a
        text editor:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa28287031"><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> <em class="replaceable">binlog_files</em> &gt; tmpfile
<span class="token prompt">$&gt; </span><span class="token command">...</span> <em class="replaceable">edit tmpfile</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   After editing the file, apply the contents as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa71499053"><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">mysql</span> <span class="token property">-u</span> root <span class="token property">-p</span> &lt; tmpfile</code></pre>
  </div>
  <p>
   If you have more than one binary log to apply on the MySQL
        server, use a single connection to apply the contents of all
        binary log files that you want to process. Here is one way to do
        so:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa83379557"><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> binlog<span class="token punctuation">.</span>000001 binlog<span class="token punctuation">.</span>000002 | mysql <span class="token property">-u</span> root <span class="token property">-p</span></code></pre>
  </div>
  <p>
   Another approach is to write the whole log to a single file and
        then process the file:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa3317886"><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> binlog<span class="token punctuation">.</span>000001 &gt;  /tmp/statements<span class="token punctuation">.</span>sql
<span class="token prompt">$&gt; </span><span class="token command">mysqlbinlog</span> binlog<span class="token punctuation">.</span>000002 &gt;&gt; /tmp/statements<span class="token punctuation">.</span>sql
<span class="token prompt">$&gt; </span><span class="token command">mysql</span> <span class="token property">-u</span> root <span class="token property">-p</span> <span class="token property">-e</span> <span class="token atrule">"source /tmp/statements.sql"</span></code></pre>
  </div>
 </div>
 <br/>
</div>
