<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="rebuilding-tables">
      </a>
      3.14 Rebuilding or Repairing Tables or Indexes
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045325860784">
  </a>
  <a class="indexterm" name="idm46045325859328">
  </a>
  <a class="indexterm" name="idm46045325857840">
  </a>
  <p>
   This section describes how to rebuild or repair tables or indexes,
      which may be necessitated by:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Changes to how MySQL handles data types or character sets. For
          example, an error in a collation might have been corrected,
          necessitating a table rebuild to update the indexes for
          character columns that use the collation.
     </p>
    </li>
    <li class="listitem">
     <p>
      Required table repairs or upgrades reported by
      <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
       <code class="literal">
        CHECK TABLE
       </code>
      </a>
      or
      <a class="link" href="mysqlcheck.html" title="6.5.3 mysqlcheck — A Table Maintenance Program">
       <span class="command">
        <strong>
         mysqlcheck
        </strong>
       </span>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Methods for rebuilding a table include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="rebuilding-tables.html#rebuilding-tables-dump-reload" title="Dump and Reload Method">
       Dump and Reload Method
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="rebuilding-tables.html#rebuilding-tables-alter-table" title="ALTER TABLE Method">
       ALTER TABLE Method
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="rebuilding-tables.html#rebuilding-tables-repair-table" title="REPAIR TABLE Method">
       REPAIR TABLE Method
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="rebuilding-tables-dump-reload">
       </a>
       Dump and Reload Method
      </h3>
     </div>
    </div>
   </div>
   <p>
    If you are rebuilding tables because a different version of
        MySQL cannot handle them after a binary (in-place) upgrade or
        downgrade, you must use the dump-and-reload method. Dump the
        tables
    <span class="emphasis">
     <em>
      before
     </em>
    </span>
    upgrading or downgrading
        using your original version of MySQL. Then reload the tables
    <span class="emphasis">
     <em>
      after
     </em>
    </span>
    upgrading or downgrading.
   </p>
   <p>
    If you use the dump-and-reload method of rebuilding tables only
        for the purpose of rebuilding indexes, you can perform the dump
        either before or after upgrading or downgrading. Reloading still
        must be done afterward.
   </p>
   <p>
    If you need to rebuild an
    <code class="literal">
     InnoDB
    </code>
    table
        because a
    <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
     <code class="literal">
      CHECK TABLE
     </code>
    </a>
    operation
        indicates that a table upgrade is required, use
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    to create a dump file 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 reload the file. If the
    <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
     <code class="literal">
      CHECK TABLE
     </code>
    </a>
    operation indicates
        that there is a corruption or causes
    <code class="literal">
     InnoDB
    </code>
    to fail, refer to
    <a class="xref" href="forcing-innodb-recovery.html" title="17.20.3 Forcing InnoDB Recovery">
     Section 17.20.3, “Forcing InnoDB Recovery”
    </a>
    for
        information about using the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_force_recovery">
     <code class="literal">
      innodb_force_recovery
     </code>
    </a>
    option to
        restart
    <code class="literal">
     InnoDB
    </code>
    . To understand the type of
        problem that
    <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
     <code class="literal">
      CHECK TABLE
     </code>
    </a>
    may be
        encountering, refer to the
    <code class="literal">
     InnoDB
    </code>
    notes in
    <a class="xref" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
     Section 15.7.3.2, “CHECK TABLE Statement”
    </a>
    .
   </p>
   <p>
    To rebuild a table by dumping and reloading it, use
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    to create a dump file 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 reload the file:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa79568684"><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">mysqldump <em class="replaceable">db_name</em> t1 &gt; dump<span class="token punctuation">.</span>sql
mysql <em class="replaceable">db_name</em> &lt; dump<span class="token punctuation">.</span>sql</code></pre>
   </div>
   <p>
    To rebuild all the tables in a single database, specify the
        database name without any following table name:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa69480297"><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">mysqldump <em class="replaceable">db_name</em> &gt; dump<span class="token punctuation">.</span>sql
mysql <em class="replaceable">db_name</em> &lt; dump<span class="token punctuation">.</span>sql</code></pre>
   </div>
   <p>
    To rebuild all tables in all databases, use the
    <a class="link" href="mysqldump.html#option_mysqldump_all-databases">
     <code class="option">
      --all-databases
     </code>
    </a>
    option:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa19300286"><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">mysqldump <span class="token property">--all-databases</span> &gt; dump<span class="token punctuation">.</span>sql
mysql &lt; dump<span class="token punctuation">.</span>sql</code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="rebuilding-tables-alter-table">
       </a>
       ALTER TABLE Method
      </h3>
     </div>
    </div>
   </div>
   <p>
    To rebuild a table with
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER
        TABLE
     </code>
    </a>
    , use a
    <span class="quote">
     “
     <span class="quote">
      null
     </span>
     ”
    </span>
    alteration; that is,
        an
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    statement that
    <span class="quote">
     “
     <span class="quote">
      changes
     </span>
     ”
    </span>
    the table to use the storage engine that
        it already has. For example, if
    <code class="literal">
     t1
    </code>
    is an
    <code class="literal">
     InnoDB
    </code>
    table, use this statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa62473108"><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">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If you are not sure which storage engine to specify in the
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    statement, use
    <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
     <code class="literal">
      SHOW CREATE TABLE
     </code>
    </a>
    to display the
        table definition.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="rebuilding-tables-repair-table">
       </a>
       REPAIR TABLE Method
      </h3>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
     <code class="literal">
      REPAIR TABLE
     </code>
    </a>
    method is only
        applicable to
    <code class="literal">
     MyISAM
    </code>
    ,
    <code class="literal">
     ARCHIVE
    </code>
    , and
    <code class="literal">
     CSV
    </code>
    tables.
   </p>
   <p>
    You can use
    <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
     <code class="literal">
      REPAIR TABLE
     </code>
    </a>
    if the
        table checking operation indicates that there is a corruption or
        that an upgrade is required. For example, to repair a
    <code class="literal">
     MyISAM
    </code>
    table, use this statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa53678265"><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">REPAIR</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    <a class="link" href="mysqlcheck.html" title="6.5.3 mysqlcheck — A Table Maintenance Program">
     <span class="command">
      <strong>
       mysqlcheck --repair
      </strong>
     </span>
    </a>
    provides command-line
        access to the
    <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
     <code class="literal">
      REPAIR TABLE
     </code>
    </a>
    statement. This can be a more convenient means of repairing
        tables because you can use the
    <a class="link" href="mysqlcheck.html#option_mysqlcheck_databases">
     <code class="option">
      --databases
     </code>
    </a>
    or
    <a class="link" href="mysqlcheck.html#option_mysqlcheck_all-databases">
     <code class="option">
      --all-databases
     </code>
    </a>
    option to
        repair all tables in specific databases or all databases,
        respectively:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa84546195"><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">mysqlcheck <span class="token property">--repair</span> <span class="token property">--databases</span> <em class="replaceable">db_name</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
mysqlcheck <span class="token property">--repair</span> <span class="token property">--all-databases</span></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
