<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-create-table-external">
      </a>
      17.6.1.2 Creating Tables Externally
     </h4>
    </div>
   </div>
  </div>
  <p>
   There are different reasons for creating
   <code class="literal">
    InnoDB
   </code>
   tables externally; that is, creating tables outside of the data
      directory. Those reasons might include space management, I/O
      optimization, or placing tables on a storage device with
      particular performance or capacity characteristics, for example.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   supports the following methods for
      creating tables externally:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-create-table-external.html#innodb-create-table-external-data-directory" title="Using the DATA DIRECTORY Clause">
       Using the DATA DIRECTORY Clause
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-create-table-external.html#innodb-create-table-external-tablespace-syntax" title="Using CREATE TABLE ... TABLESPACE Syntax">
       Using CREATE TABLE ... TABLESPACE Syntax
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-create-table-external.html#innodb-create-table-external-tablespace" title="Creating a Table in an External General Tablespace">
       Creating a Table in an External General Tablespace
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-create-table-external-data-directory">
       </a>
       Using the DATA DIRECTORY Clause
      </h5>
     </div>
    </div>
   </div>
   <p>
    You can create an
    <code class="literal">
     InnoDB
    </code>
    table in an external
        directory by specifying a
    <code class="literal">
     DATA DIRECTORY
    </code>
    clause in the
    <code class="literal">
     CREATE TABLE
    </code>
    statement.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa81652656"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">DATA</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">/external/directory</em>'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The
    <code class="literal">
     DATA DIRECTORY
    </code>
    clause is supported for
        tables created in file-per-table tablespaces. Tables are
        implicitly created in file-per-table tablespaces when the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    variable
        is enabled, which it is by default.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40542121"><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">SELECT</span> <span class="token variable">@@innodb_file_per_table</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @@innodb_file_per_table <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                       1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    For more information about file-per-table tablespaces, see
    <a class="xref" href="innodb-file-per-table-tablespaces.html" title="17.6.3.2 File-Per-Table Tablespaces">
     Section 17.6.3.2, “File-Per-Table Tablespaces”
    </a>
    .
   </p>
   <p>
    When you specify a
    <code class="literal">
     DATA DIRECTORY
    </code>
    clause in a
    <code class="literal">
     CREATE TABLE
    </code>
    statement, the table's data file
        (
    <code class="filename">
     <em class="replaceable">
      <code>
       table_name
      </code>
     </em>
     .ibd
    </code>
    )
        is created in a schema directory under the specified directory.
   </p>
   <p>
    Tables and table partitions created outside of the data
        directory using the
    <code class="literal">
     DATA DIRECTORY
    </code>
    clause are
        restricted to directories known to
    <code class="literal">
     InnoDB
    </code>
    .
        This requirement permits database administrators to control
        where tablespace data files are created and ensures that data
        files can be found during recovery (see
    <a class="xref" href="innodb-recovery.html#innodb-recovery-tablespace-discovery" title="Tablespace Discovery During Crash Recovery">
     Tablespace Discovery During Crash Recovery
    </a>
    ). Known
        directories are those defined by the
    <a class="link" href="server-system-variables.html#sysvar_datadir">
     <code class="literal">
      datadir
     </code>
    </a>
    ,
    <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
     <code class="literal">
      innodb_data_home_dir
     </code>
    </a>
    , and
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    variables.
        You can use the following statement to check those settings:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa8674343"><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">SELECT</span> <span class="token variable">@@datadir</span><span class="token punctuation">,</span><span class="token variable">@@innodb_data_home_dir</span><span class="token punctuation">,</span><span class="token variable">@@innodb_directories</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If the directory you want to use is unknown, add it to the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    setting
        before you create the table. The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    variable is
        read-only. Configuring it requires restarting the server. For
        general information about setting system variables, see
    <a class="xref" href="using-system-variables.html" title="7.1.9 Using System Variables">
     Section 7.1.9, “Using System Variables”
    </a>
    .
   </p>
   <p>
    The following example demonstrates creating a table in an
        external directory using the
    <code class="literal">
     DATA DIRECTORY
    </code>
    clause. It is assumed that the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    variable
        is enabled and that the directory is known to
    <code class="literal">
     InnoDB
    </code>
    .
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55194128"><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">USE</span> test<span class="token punctuation">;</span>
<span class="token keyword">Database</span> <span class="token keyword">changed</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">DATA</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">/external/directory</em>'</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># MySQL creates the table's data file in a schema directory</span>
<span class="token comment" spellcheck="true"># under the external directory</span>

$<span class="token operator">&gt;</span> cd <span class="token operator">/</span>external<span class="token operator">/</span><span class="token keyword">directory</span><span class="token operator">/</span>test
$<span class="token operator">&gt;</span> ls
t1<span class="token punctuation">.</span>ibd</code></pre>
   </div>
   <h6>
    <a name="idm46045166764688">
    </a>
    Usage Notes:
   </h6>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       MySQL initially holds the tablespace data file open,
            preventing you from dismounting the device, but might
            eventually close the file if the server is busy. Be careful
            not to accidentally dismount an external device while MySQL
            is running, or start MySQL while the device is disconnected.
            Attempting to access a table when the associated data file
            is missing causes a serious error that requires a server
            restart.
      </p>
      <p>
       A server restart might fail if the data file is not found at
            the expected path. In this case, you can restore the
            tablespace data file from a backup or drop the table to
            remove the information about it from the
       <a class="link" href="glossary.html#glos_data_dictionary" title="data dictionary">
        data dictionary
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Before placing a table on an NFS-mounted volume, review
            potential issues outlined in
       <a class="xref" href="disk-issues.html#disk-issues-nfs" title="Using NFS with MySQL">
        Using NFS with MySQL
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If using an LVM snapshot, file copy, or other file-based
            mechanism to back up the table's data file, always use the
       <a class="link" href="flush.html#flush-tables-for-export-with-list">
        <code class="literal">
         FLUSH
            TABLES ... FOR EXPORT
        </code>
       </a>
       statement first to ensure
            that all changes buffered in memory are
       <a class="link" href="glossary.html#glos_flush" title="flush">
        flushed
       </a>
       to disk before the
            backup occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       Using the
       <code class="literal">
        DATA DIRECTORY
       </code>
       clause to create
            a table in an external directory is an alternative to using
       <a class="link" href="symbolic-links.html" title="10.12.2 Using Symbolic Links">
        symbolic links
       </a>
       , which
       <code class="literal">
        InnoDB
       </code>
       does not support.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        DATA DIRECTORY
       </code>
       clause is not
            supported in a replication environment where the source and
            replica reside on the same host. The
       <code class="literal">
        DATA
            DIRECTORY
       </code>
       clause requires a full directory path.
            Replicating the path in this case would cause the source and
            replica to create the table in same location.
      </p>
     </li>
     <li class="listitem">
      <p>
       Tables created in file-per-table tablespaces cannot be
            created in the undo tablespace directory
            (
       <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
        <code class="literal">
         innodb_undo_directory
        </code>
       </a>
       )
            unless that directly is known to
       <code class="literal">
        InnoDB
       </code>
       .
            Known directories are those defined by the
       <a class="link" href="server-system-variables.html#sysvar_datadir">
        <code class="literal">
         datadir
        </code>
       </a>
       ,
       <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
        <code class="literal">
         innodb_data_home_dir
        </code>
       </a>
       , and
       <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
        <code class="literal">
         innodb_directories
        </code>
       </a>
       variables.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-create-table-external-tablespace-syntax">
       </a>
       Using CREATE TABLE ... TABLESPACE Syntax
      </h5>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE ...
        TABLESPACE
     </code>
    </a>
    syntax can be used in combination with the
    <code class="literal">
     DATA DIRECTORY
    </code>
    clause to create a table in an
        external directory. To do so, specify
    <code class="literal">
     innodb_file_per_table
    </code>
    as the tablespace name.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74236038"><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">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> <span class="token operator">=</span> innodb_file_per_table
       <span class="token keyword">DATA</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'/external/directory'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    This method is supported only for tables created in
        file-per-table tablespaces, but does not require the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    variable
        to be enabled. In all other respects, this method is equivalent
        to the
    <code class="literal">
     CREATE TABLE ... DATA DIRECTORY
    </code>
    method
        described above. The same usage notes apply.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-create-table-external-tablespace">
       </a>
       Creating a Table in an External General Tablespace
      </h5>
     </div>
    </div>
   </div>
   <p>
    You can create a table in a general tablespace that resides in
        an external directory.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For information about creating a general tablespace in an
            external directory, see
       <a class="xref" href="general-tablespaces.html#general-tablespaces-creating" title="Creating a General Tablespace">
        Creating a General Tablespace
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       For information about creating a table in a general
            tablespace, see
       <a class="xref" href="general-tablespaces.html#general-tablespaces-adding-tables" title="Adding Tables to a General Tablespace">
        Adding Tables to a General Tablespace
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
