<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-recovery">
      </a>
      17.18.2 InnoDB Recovery
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045149214064">
  </a>
  <a class="indexterm" name="idm46045149212576">
  </a>
  <p>
   This section describes
   <code class="literal">
    InnoDB
   </code>
   recovery. Topics
      include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-recovery.html#innodb-recovery-point-in-time" title="Point-in-Time Recovery">
       Point-in-Time Recovery
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-recovery.html#innodb-corruption-disk-failure-recovery" title="Recovery from Data Corruption or Disk Failure">
       Recovery from Data Corruption or Disk Failure
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-recovery.html#innodb-crash-recovery" title="InnoDB Crash Recovery">
       InnoDB Crash Recovery
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-recovery.html#innodb-recovery-tablespace-discovery" title="Tablespace Discovery During Crash Recovery">
       Tablespace Discovery During Crash Recovery
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-recovery-point-in-time">
       </a>
       Point-in-Time Recovery
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045149204688">
   </a>
   <a class="indexterm" name="idm46045149203200">
   </a>
   <p>
    To recover an
    <code class="literal">
     InnoDB
    </code>
    database to the present
        from the time at which the physical backup was made, you must
        run MySQL server with binary logging enabled, even before taking
        the backup. To achieve point-in-time recovery after restoring a
        backup, you can apply changes from the binary log that occurred
        after the backup was made. See
    <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>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-corruption-disk-failure-recovery">
       </a>
       Recovery from Data Corruption or Disk Failure
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045149198336">
   </a>
   <a class="indexterm" name="idm46045149196848">
   </a>
   <a class="indexterm" name="idm46045149195360">
   </a>
   <a class="indexterm" name="idm46045149193872">
   </a>
   <p>
    If your database becomes corrupted or disk failure occurs, you
        must perform the recovery using a backup. In the case of
        corruption, first find a backup that is not corrupted. After
        restoring the base backup, do a point-in-time recovery from the
        binary log files 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>
    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>
    to restore the changes that occurred
        after the backup was made.
   </p>
   <p>
    In some cases of database corruption, it is enough to dump,
        drop, and re-create one or a few corrupt tables. You can use the
    <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
     <code class="literal">
      CHECK TABLE
     </code>
    </a>
    statement to check
        whether a table is corrupt, although
    <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
     <code class="literal">
      CHECK
        TABLE
     </code>
    </a>
    naturally cannot detect every possible kind of
        corruption.
   </p>
   <p>
    In some cases, apparent database page corruption is actually due
        to the operating system corrupting its own file cache, and the
        data on disk may be okay. It is best to try restarting the
        computer first. Doing so may eliminate errors that appeared to
        be database page corruption. If MySQL still has trouble starting
        because of
    <code class="literal">
     InnoDB
    </code>
    consistency problems, see
    <a class="xref" href="forcing-innodb-recovery.html" title="17.20.3 Forcing InnoDB Recovery">
     Section 17.20.3, “Forcing InnoDB Recovery”
    </a>
    for steps to start the
        instance in recovery mode, which permits you to dump the data.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-crash-recovery">
       </a>
       InnoDB Crash Recovery
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045149182656">
   </a>
   <a class="indexterm" name="idm46045149181168">
   </a>
   <p>
    To recover from an unexpected MySQL server exit, the only
        requirement is to restart the MySQL server.
    <code class="literal">
     InnoDB
    </code>
    automatically checks the logs and
        performs a roll-forward of the database to the present.
    <code class="literal">
     InnoDB
    </code>
    automatically rolls back uncommitted
        transactions that were present at the time of the crash.
   </p>
   <a class="indexterm" name="idm46045149177520">
   </a>
   <p>
    <code class="literal">
     InnoDB
    </code>
    <a class="link" href="glossary.html#glos_crash_recovery" title="crash recovery">
     crash recovery
    </a>
    consists of several steps:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Tablespace discovery
      </p>
      <p>
       Tablespace discovery is the process that
       <code class="literal">
        InnoDB
       </code>
       uses to identify tablespaces that
            require redo log application. See
       <a class="xref" href="innodb-recovery.html#innodb-recovery-tablespace-discovery" title="Tablespace Discovery During Crash Recovery">
        Tablespace Discovery During Crash Recovery
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="glossary.html#glos_redo_log" title="redo log">
        Redo log
       </a>
       application
      </p>
      <p>
       Redo log application is performed during initialization,
            before accepting any connections. If all changes are flushed
            from the
       <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
        buffer pool
       </a>
       to the
       <a class="link" href="glossary.html#glos_tablespace" title="tablespace">
        tablespaces
       </a>
       (
       <code class="filename">
        ibdata*
       </code>
       and
       <code class="filename">
        *.ibd
       </code>
       files) at the time of the shutdown or crash, redo log
            application is skipped.
       <code class="literal">
        InnoDB
       </code>
       also skips
            redo log application if redo log files are missing at
            startup.
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          The current maximum auto-increment counter value is
                written to the redo log each time the value changes,
                which makes it crash-safe. During recovery,
          <code class="literal">
           InnoDB
          </code>
          scans the redo log to collect
                counter value changes and applies the changes to the
                in-memory table object.
         </p>
         <p>
          For more information about how
          <code class="literal">
           InnoDB
          </code>
          handles auto-increment values, see
          <a class="xref" href="innodb-auto-increment-handling.html" title="17.6.1.6 AUTO_INCREMENT Handling in InnoDB">
           Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”
          </a>
          , and
          <a class="xref" href="innodb-auto-increment-handling.html#innodb-auto-increment-initialization" title="InnoDB AUTO_INCREMENT Counter Initialization">
           InnoDB AUTO_INCREMENT Counter Initialization
          </a>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          When encountering index tree corruption,
          <code class="literal">
           InnoDB
          </code>
          writes a corruption flag to
                the redo log, which makes the corruption flag
                crash-safe.
          <code class="literal">
           InnoDB
          </code>
          also writes
                in-memory corruption flag data to an engine-private
                system table on each checkpoint. During recovery,
          <code class="literal">
           InnoDB
          </code>
          reads corruption flags from
                both locations and merges results before marking
                in-memory table and index objects as corrupt.
         </p>
        </li>
        <li class="listitem">
         <p>
          Removing redo logs to speed up recovery is not
                recommended, even if some data loss is acceptable.
                Removing redo logs should only be considered after a
                clean shutdown, with
          <a class="link" href="innodb-parameters.html#sysvar_innodb_fast_shutdown">
           <code class="literal">
            innodb_fast_shutdown
           </code>
          </a>
          set to
          <code class="literal">
           0
          </code>
          or
          <code class="literal">
           1
          </code>
          .
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="glossary.html#glos_rollback" title="rollback">
        Roll back
       </a>
       of incomplete
       <a class="link" href="glossary.html#glos_transaction" title="transaction">
        transactions
       </a>
      </p>
      <p>
       Incomplete transactions are any transactions that were
            active at the time of unexpected exit or
       <a class="link" href="glossary.html#glos_fast_shutdown" title="fast shutdown">
        fast shutdown
       </a>
       . The
            time it takes to roll back an incomplete transaction can be
            three or four times the amount of time a transaction is
            active before it is interrupted, depending on server load.
      </p>
      <p>
       You cannot cancel transactions that are being rolled back.
            In extreme cases, when rolling back transactions is expected
            to take an exceptionally long time, it may be faster to
            start
       <code class="literal">
        InnoDB
       </code>
       with an
       <a class="link" href="innodb-parameters.html#sysvar_innodb_force_recovery">
        <code class="literal">
         innodb_force_recovery
        </code>
       </a>
       setting of
       <code class="literal">
        3
       </code>
       or greater. See
       <a class="xref" href="forcing-innodb-recovery.html" title="17.20.3 Forcing InnoDB Recovery">
        Section 17.20.3, “Forcing InnoDB Recovery”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="glossary.html#glos_change_buffer" title="change buffer">
        Change buffer
       </a>
       merge
      </p>
      <p>
       Applying changes from the change buffer (part of the
       <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
        system
            tablespace
       </a>
       ) to leaf pages of secondary indexes, as
            the index pages are read to the buffer pool.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="glossary.html#glos_purge" title="purge">
        Purge
       </a>
      </p>
      <p>
       Deleting delete-marked records that are no longer visible to
            active transactions.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The steps that follow redo log application do not depend on the
        redo log (other than for logging the writes) and are performed
        in parallel with normal processing. Of these, only rollback of
        incomplete transactions is special to crash recovery. The insert
        buffer merge and the purge are performed during normal
        processing.
   </p>
   <p>
    After redo log application,
    <code class="literal">
     InnoDB
    </code>
    attempts
        to accept connections as early as possible, to reduce downtime.
        As part of crash recovery,
    <code class="literal">
     InnoDB
    </code>
    rolls back
        transactions that were not committed or in
    <code class="literal">
     XA
        PREPARE
    </code>
    state when the server exited. The rollback is
        performed by a background thread, executed in parallel with
        transactions from new connections. Until the rollback operation
        is completed, new connections may encounter locking conflicts
        with recovered transactions.
   </p>
   <p>
    In most situations, even if the MySQL server was killed
        unexpectedly in the middle of heavy activity, the recovery
        process happens automatically and no action is required of the
        DBA. If a hardware failure or severe system error corrupted
    <code class="literal">
     InnoDB
    </code>
    data, MySQL might refuse to start. In
        this case, see
    <a class="xref" href="forcing-innodb-recovery.html" title="17.20.3 Forcing InnoDB Recovery">
     Section 17.20.3, “Forcing InnoDB Recovery”
    </a>
    .
   </p>
   <p>
    For information about the binary log and
    <code class="literal">
     InnoDB
    </code>
    crash recovery, see
    <a class="xref" href="binary-log.html" title="7.4.4 The Binary Log">
     Section 7.4.4, “The Binary Log”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-recovery-tablespace-discovery">
       </a>
       Tablespace Discovery During Crash Recovery
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045149130528">
   </a>
   <a class="indexterm" name="idm46045149129040">
   </a>
   <p>
    If, during recovery,
    <code class="literal">
     InnoDB
    </code>
    encounters redo
        logs written since the last checkpoint, the redo logs must be
        applied to affected tablespaces. The process that identifies
        affected tablespaces during recovery is referred to as
    <span class="emphasis">
     <em>
      tablespace discovery
     </em>
    </span>
    .
   </p>
   <p>
    Tablespace discovery relies on the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    setting,
        which defines the directories to scan at startup for tablespace
        files. The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    default setting is NULL, but the directories defined by
    <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
     <code class="literal">
      innodb_data_home_dir
     </code>
    </a>
    ,
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    , and
    <a class="link" href="server-system-variables.html#sysvar_datadir">
     <code class="literal">
      datadir
     </code>
    </a>
    are always appended to
        the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    argument
        value when InnoDB builds a list of directories to scan at
        startup. These directories are appended regardless of whether an
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    setting is
        specified explicitly. Tablespace files defined with an absolute
        path or that reside outside of the directories appended to the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    setting
        should be added to the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    setting.
        Recovery is terminated if any tablespace file referenced in a
        redo log has not been discovered previously.
   </p>
  </div>
 </div>
 <br/>
</div>
