<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-migration">
      </a>
      17.6.1.4 Moving or Copying InnoDB Tables
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045166455952">
  </a>
  <p>
   This section describes techniques for moving or copying some or all
   <code class="literal">
    InnoDB
   </code>
   tables to a different server or instance.
    For example, you might move an entire MySQL instance to a larger,
    faster server; you might clone an entire MySQL instance to a new
    replica server; you might copy individual tables to another instance
    to develop and test an application, or to a data warehouse server to
    produce reports.
  </p>
  <p>
   On Windows,
   <code class="literal">
    InnoDB
   </code>
   always stores database and
    table names internally in lowercase. To move databases in a binary
    format from Unix to Windows or from Windows to Unix, create all
    databases and tables using lowercase names. A convenient way to
    accomplish this is to add the following line to the
   <code class="literal">
    [mysqld]
   </code>
   section of your
   <code class="filename">
    my.cnf
   </code>
   or
   <code class="filename">
    my.ini
   </code>
   file
    before creating any databases or tables:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa14488972"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">lower_case_table_names</span><span class="token attr-value"><span class="token punctuation">=</span>1</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    It is prohibited to start the server with a
    <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
     <code class="literal">
      lower_case_table_names
     </code>
    </a>
    setting
      that is different from the setting used when the server was
      initialized.
   </p>
  </div>
  <p>
   Techniques for moving or copying
   <code class="literal">
    InnoDB
   </code>
   tables
    include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-migration.html#copy-tables-import" title="Importing Tables">
       Importing Tables
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-migration.html#copy-tables-meb" title="MySQL Enterprise Backup">
       MySQL Enterprise Backup
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-migration.html#copy-tables-cold-backup" title="Copying Data Files (Cold Backup Method)">
       Copying Data Files (Cold Backup Method)
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-migration.html#copy-tables-logical-backup" title="Restoring from a Logical Backup">
       Restoring from a Logical Backup
      </a>
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="copy-tables-import">
   </a>
   Importing Tables
  </h5>
  <p>
   A table that resides in a file-per-table tablespace can be imported
    from another MySQL server instance or from a backup using the
   <span class="emphasis">
    <em>
     Transportable Tablespace
    </em>
   </span>
   feature. See
   <a class="xref" href="innodb-table-import.html" title="17.6.1.3 Importing InnoDB Tables">
    Section 17.6.1.3, “Importing InnoDB Tables”
   </a>
   .
  </p>
  <h5>
   <a name="copy-tables-meb">
   </a>
   MySQL Enterprise Backup
  </h5>
  <p>
   The MySQL Enterprise Backup product lets you back up a running MySQL database with
    minimal disruption to operations while producing a consistent
    snapshot of the database. When MySQL Enterprise Backup is copying tables, reads and
    writes can continue. In addition, MySQL Enterprise Backup can create compressed backup
    files, and back up subsets of tables. In conjunction with the MySQL
    binary log, you can perform point-in-time recovery. MySQL Enterprise Backup is
    included as part of the MySQL Enterprise subscription.
  </p>
  <p>
   For more details about MySQL Enterprise Backup, see
   <a class="xref" href="mysql-enterprise-backup.html" title="32.1 MySQL Enterprise Backup Overview">
    Section 32.1, “MySQL Enterprise Backup Overview”
   </a>
   .
  </p>
  <h5>
   <a name="copy-tables-cold-backup">
   </a>
   Copying Data Files (Cold Backup Method)
  </h5>
  <p>
   You can move an
   <code class="literal">
    InnoDB
   </code>
   database simply by copying
    all the relevant files listed under "Cold Backups" in
   <a class="xref" href="innodb-backup.html" title="17.18.1 InnoDB Backup">
    Section 17.18.1, “InnoDB Backup”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   data and log files are binary-compatible
    on all platforms having the same floating-point number format. If
    the floating-point formats differ but you have not used
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     FLOAT
    </code>
   </a>
   or
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     DOUBLE
    </code>
   </a>
   data types in your tables,
    then the procedure is the same: simply copy the relevant files.
  </p>
  <p>
   When you move or copy file-per-table
   <code class="filename">
    .ibd
   </code>
   files, the database directory name must be the same on the source
    and destination systems. The table definition stored in the
   <code class="literal">
    InnoDB
   </code>
   shared tablespace includes the database
    name. The transaction IDs and log sequence numbers stored in the
    tablespace files also differ between databases.
  </p>
  <p>
   To move an
   <code class="filename">
    .ibd
   </code>
   file and the associated table
    from one database to another, use a
   <a class="link" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
    <code class="literal">
     RENAME
    TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa34828986"><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">RENAME</span> <span class="token keyword">TABLE</span> <em class="replaceable">db1<span class="token punctuation">.</span>tbl_name</em> <span class="token keyword">TO</span> <em class="replaceable">db2<span class="token punctuation">.</span>tbl_name</em><span class="token punctuation">;</span></code></pre>
  </div>
  <a class="indexterm" name="idm46045166421328">
  </a>
  <a class="indexterm" name="idm46045166420256">
  </a>
  <p>
   If you have a
   <span class="quote">
    “
    <span class="quote">
     clean
    </span>
    ”
   </span>
   backup of an
   <code class="filename">
    .ibd
   </code>
   file, you can restore it to the MySQL
    installation from which it originated as follows:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      The table must not have been dropped or truncated since you
        copied the
      <code class="filename">
       .ibd
      </code>
      file, because doing so
        changes the table ID stored inside the tablespace.
     </p>
    </li>
    <li class="listitem">
     <p>
      Issue this
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement
        to delete the current
      <code class="filename">
       .ibd
      </code>
      file:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa59903192"><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> <em class="replaceable">tbl_name</em> <span class="token keyword">DISCARD</span> <span class="token keyword">TABLESPACE</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Copy the backup
      <code class="filename">
       .ibd
      </code>
      file to the proper
        database directory.
     </p>
    </li>
    <li class="listitem">
     <p>
      Issue this
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement
        to tell
      <code class="literal">
       InnoDB
      </code>
      to use the new
      <code class="filename">
       .ibd
      </code>
      file for the table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47521922"><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> <em class="replaceable">tbl_name</em> <span class="token keyword">IMPORT</span> <span class="token keyword">TABLESPACE</span><span class="token punctuation">;</span></code></pre>
     </div>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       The
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
          ... IMPORT TABLESPACE
        </code>
       </a>
       feature does not enforce
          foreign key constraints on imported data.
      </p>
     </div>
    </li>
   </ol>
  </div>
  <p>
   In this context, a
   <span class="quote">
    “
    <span class="quote">
     clean
    </span>
    ”
   </span>
   <code class="filename">
    .ibd
   </code>
   file backup is one for which the following requirements are
    satisfied:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      There are no uncommitted modifications by transactions in the
      <code class="filename">
       .ibd
      </code>
      file.
     </p>
    </li>
    <li class="listitem">
     <p>
      There are no unmerged insert buffer entries in the
      <code class="filename">
       .ibd
      </code>
      file.
     </p>
    </li>
    <li class="listitem">
     <p>
      Purge has removed all delete-marked index records from the
      <code class="filename">
       .ibd
      </code>
      file.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      has flushed all modified pages of the
      <code class="filename">
       .ibd
      </code>
      file from the buffer pool to the file.
     </p>
    </li>
   </ul>
  </div>
  <p>
   You can make a clean backup
   <code class="filename">
    .ibd
   </code>
   file using the
    following method:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Stop all activity from the
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      server and
        commit all transactions.
     </p>
    </li>
    <li class="listitem">
     <p>
      Wait until
      <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
       <code class="literal">
        SHOW
        ENGINE INNODB STATUS
       </code>
      </a>
      shows that there are no active
        transactions in the database, and the main thread status of
      <code class="literal">
       InnoDB
      </code>
      is
      <code class="literal">
       Waiting for server
        activity
      </code>
      . Then you can make a copy of the
      <code class="filename">
       .ibd
      </code>
      file.
     </p>
    </li>
   </ol>
  </div>
  <p>
   Another method for making a clean copy of an
   <code class="filename">
    .ibd
   </code>
   file is to use the MySQL Enterprise Backup product:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Use MySQL Enterprise Backup to back up the
      <code class="literal">
       InnoDB
      </code>
      installation.
     </p>
    </li>
    <li class="listitem">
     <p>
      Start a second
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      server on the backup
        and let it clean up the
      <code class="filename">
       .ibd
      </code>
      files in the
        backup.
     </p>
    </li>
   </ol>
  </div>
  <h5>
   <a name="copy-tables-logical-backup">
   </a>
   Restoring from a Logical Backup
  </h5>
  <p>
   You can use a utility such as
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   to
    perform a logical backup, which produces a set of SQL statements
    that can be executed to reproduce the original database object
    definitions and table data for transfer to another SQL server. Using
    this method, it does not matter whether the formats differ or if
    your tables contain floating-point data.
  </p>
  <p>
   To improve the performance of this method, disable
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   when importing data.
    Perform a commit only after importing an entire table or segment of
    a table.
  </p>
 </div>
 <br/>
</div>
