<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="backup-strategy-example">
      </a>
      9.3 Example Backup and Recovery Strategy
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="backup-policy.html">
       9.3.1 Establishing a Backup Policy
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="recovery-from-backups.html">
       9.3.2 Using Backups for Recovery
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="backup-strategy-summary.html">
       9.3.3 Backup Strategy Summary
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <p>
   This section discusses a procedure for performing backups that
      enables you to recover data after several types of crashes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Operating system crash
     </p>
    </li>
    <li class="listitem">
     <p>
      Power failure
     </p>
    </li>
    <li class="listitem">
     <p>
      File system crash
     </p>
    </li>
    <li class="listitem">
     <p>
      Hardware problem (hard drive, motherboard, and so forth)
     </p>
    </li>
   </ul>
  </div>
  <p>
   The example commands do not include options such as
   <a class="link" href="connection-options.html#option_general_user">
    <code class="option">
     --user
    </code>
   </a>
   and
   <a class="link" href="connection-options.html#option_general_password">
    <code class="option">
     --password
    </code>
   </a>
   for the
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   and
   <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
      programs. You should include such options as necessary to enable
      client programs to connect to the MySQL server.
  </p>
  <p>
   Assume that data is stored in the
   <code class="literal">
    InnoDB
   </code>
   storage engine, which has support for transactions and automatic
      crash recovery. Assume also that the MySQL server is under load at
      the time of the crash. If it were not, no recovery would ever be
      needed.
  </p>
  <p>
   For cases of operating system crashes or power failures, we can
      assume that MySQL's disk data is available after a restart. The
   <code class="literal">
    InnoDB
   </code>
   data files might not contain consistent
      data due to the crash, but
   <code class="literal">
    InnoDB
   </code>
   reads its
      logs and finds in them the list of pending committed and
      noncommitted transactions that have not been flushed to the data
      files.
   <code class="literal">
    InnoDB
   </code>
   automatically rolls back those
      transactions that were not committed, and flushes to its data
      files those that were committed. Information about this recovery
      process is conveyed to the user through the MySQL error log. The
      following is an example log excerpt:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa74325296"><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-simple">InnoDB<span class="token operator">:</span> Database was not shut down normally<span class="token punctuation">.</span>
InnoDB<span class="token operator">:</span> Starting recovery from log files<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
InnoDB<span class="token operator">:</span> Starting log scan based on checkpoint at
InnoDB<span class="token operator">:</span> log sequence number 0 13674004
InnoDB<span class="token operator">:</span> Doing recovery<span class="token operator">:</span> scanned up to log sequence number 0 13739520
InnoDB<span class="token operator">:</span> Doing recovery<span class="token operator">:</span> scanned up to log sequence number 0 13805056
InnoDB<span class="token operator">:</span> Doing recovery<span class="token operator">:</span> scanned up to log sequence number 0 13870592
InnoDB<span class="token operator">:</span> Doing recovery<span class="token operator">:</span> scanned up to log sequence number 0 13936128
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
InnoDB<span class="token operator">:</span> Doing recovery<span class="token operator">:</span> scanned up to log sequence number 0 20555264
InnoDB<span class="token operator">:</span> Doing recovery<span class="token operator">:</span> scanned up to log sequence number 0 20620800
InnoDB<span class="token operator">:</span> Doing recovery<span class="token operator">:</span> scanned up to log sequence number 0 20664692
InnoDB<span class="token operator">:</span> 1 uncommitted transaction<span class="token punctuation">(</span>s<span class="token punctuation">)</span> which must be rolled back
InnoDB<span class="token operator">:</span> Starting rollback of uncommitted transactions
InnoDB<span class="token operator">:</span> Rolling back trx no 16745
InnoDB<span class="token operator">:</span> Rolling back of trx no 16745 completed
InnoDB<span class="token operator">:</span> Rollback of uncommitted transactions completed
InnoDB<span class="token operator">:</span> Starting an apply batch of log records to the database<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
InnoDB<span class="token operator">:</span> Apply batch completed
InnoDB<span class="token operator">:</span> Started
mysqld<span class="token operator">:</span> ready for connections</code></pre>
  </div>
  <p>
   For the cases of file system crashes or hardware problems, we can
      assume that the MySQL disk data is
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   available after a restart. This means that MySQL fails to start
      successfully because some blocks of disk data are no longer
      readable. In this case, it is necessary to reformat the disk,
      install a new one, or otherwise correct the underlying problem.
      Then it is necessary to recover our MySQL data from backups, which
      means that backups must already have been made. To make sure that
      is the case, design and implement a backup policy.
  </p>
 </div>
 <br/>
</div>
