<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="import-table">
      </a>
      15.2.6 IMPORT TABLE Statement
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045182664576">
  </a>
  <a class="indexterm" name="idm46045182663504">
  </a>
  <a class="indexterm" name="idm46045182662464">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa60644089"><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">IMPORT</span> <span class="token keyword">TABLE</span> <span class="token keyword">FROM</span> <em class="replaceable">sdi_file</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">sdi_file</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
    <code class="literal">
     IMPORT TABLE
    </code>
   </a>
   statement imports
   <code class="literal">
    MyISAM
   </code>
   tables based on information contained in
   <code class="filename">
    .sdi
   </code>
   (serialized dictionary information)
      metadata files.
   <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
    <code class="literal">
     IMPORT TABLE
    </code>
   </a>
   requires the
   <a class="link" href="privileges-provided.html#priv_file">
    <code class="literal">
     FILE
    </code>
   </a>
   privilege to read
      the
   <code class="filename">
    .sdi
   </code>
   and table content files, and the
   <a class="link" href="privileges-provided.html#priv_create">
    <code class="literal">
     CREATE
    </code>
   </a>
   privilege for the table to
      be created.
  </p>
  <p>
   Tables can be exported from one server using
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   to write a file of SQL statements and
      imported into another server using
   <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
      process the dump file.
   <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
    <code class="literal">
     IMPORT TABLE
    </code>
   </a>
   provides a faster alternative using the
   <span class="quote">
    “
    <span class="quote">
     raw
    </span>
    ”
   </span>
   table
      files.
  </p>
  <p>
   Prior to import, the files that provide the table content must be
      placed in the appropriate schema directory for the import server,
      and the
   <code class="filename">
    .sdi
   </code>
   file must be located in a
      directory accessible to the server. For example, the
   <code class="filename">
    .sdi
   </code>
   file can be placed in the directory
      named by the
   <a class="link" href="server-system-variables.html#sysvar_secure_file_priv">
    <code class="literal">
     secure_file_priv
    </code>
   </a>
   system variable, or (if
   <a class="link" href="server-system-variables.html#sysvar_secure_file_priv">
    <code class="literal">
     secure_file_priv
    </code>
   </a>
   is empty) in a
      directory under the server data directory.
  </p>
  <p>
   The following example describes how to export
   <code class="literal">
    MyISAM
   </code>
   tables named
   <code class="literal">
    employees
   </code>
   and
   <code class="literal">
    managers
   </code>
   from
      the
   <code class="literal">
    hr
   </code>
   schema of one server and import them
      into the
   <code class="literal">
    hr
   </code>
   schema of another server. The
      example uses these assumptions (to perform a similar operation on
      your own system, modify the path names as appropriate):
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For the export server,
      <em class="replaceable">
       <code>
        export_basedir
       </code>
      </em>
      represents its base
          directory, and its data directory is
      <code class="filename">
       <em class="replaceable">
        <code>
         export_basedir
        </code>
       </em>
       /data
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For the import server,
      <em class="replaceable">
       <code>
        import_basedir
       </code>
      </em>
      represents its base
          directory, and its data directory is
      <code class="filename">
       <em class="replaceable">
        <code>
         import_basedir
        </code>
       </em>
       /data
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Table files are exported from the export server into the
      <code class="filename">
       /tmp/export
      </code>
      directory and this directory
          is secure (not accessible to other users).
     </p>
    </li>
    <li class="listitem">
     <p>
      The import server uses
      <code class="filename">
       /tmp/mysql-files
      </code>
      as the directory named by its
      <a class="link" href="server-system-variables.html#sysvar_secure_file_priv">
       <code class="literal">
        secure_file_priv
       </code>
      </a>
      system
          variable.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To export tables from the export server, use this procedure:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Ensure a consistent snapshot by executing this statement to
          lock the tables so that they cannot be modified during export:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa94764866"><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">FLUSH</span> <span class="token keyword">TABLES</span> hr<span class="token punctuation">.</span>employees<span class="token punctuation">,</span> hr<span class="token punctuation">.</span>managers <span class="token keyword">WITH</span> <span class="token keyword">READ</span> <span class="token keyword">LOCK</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      While the lock is in effect, the tables can still be used, but
          only for read access.
     </p>
    </li>
    <li class="listitem">
     <p>
      At the file system level, copy the
      <code class="filename">
       .sdi
      </code>
      and table content files from the
      <code class="literal">
       hr
      </code>
      schema
          directory to the secure export directory:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: disc; ">
       <li class="listitem">
        <p>
         The
         <code class="literal">
          .sdi
         </code>
         file is located in the
         <code class="literal">
          hr
         </code>
         schema directory, but might not have
              exactly the same basename as the table name. For example,
              the
         <code class="filename">
          .sdi
         </code>
         files for the
         <code class="literal">
          employees
         </code>
         and
         <code class="literal">
          managers
         </code>
         tables might be named
         <code class="filename">
          employees_125.sdi
         </code>
         and
         <code class="filename">
          managers_238.sdi
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         For a
         <code class="literal">
          MyISAM
         </code>
         table, the content files
              are its
         <code class="filename">
          .MYD
         </code>
         data file and
         <code class="filename">
          .MYI
         </code>
         index file.
        </p>
       </li>
      </ul>
     </div>
     <p>
      Given those file names, the copy commands look like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa37222222"><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"><span class="token prompt">$&gt; </span><span class="token command">cd</span> <em class="replaceable">export_basedir</em>/data/hr
<span class="token prompt">$&gt; </span><span class="token command">cp</span> employees_125<span class="token punctuation">.</span>sdi /tmp/export
<span class="token prompt">$&gt; </span><span class="token command">cp</span> managers_238<span class="token punctuation">.</span>sdi /tmp/export
<span class="token prompt">$&gt; </span><span class="token command">cp</span> employees<span class="token punctuation">.</span><span class="token punctuation">{</span>MYD<span class="token punctuation">,</span>MYI<span class="token punctuation">}</span> /tmp/export
<span class="token prompt">$&gt; </span><span class="token command">cp</span> managers<span class="token punctuation">.</span><span class="token punctuation">{</span>MYD<span class="token punctuation">,</span>MYI<span class="token punctuation">}</span> /tmp/export</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Unlock the tables:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa27656817"><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">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
  <p>
   To import tables into the import server, use this procedure:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      The import schema must exist. If necessary, execute this
          statement to create it:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa7289220"><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">SCHEMA</span> hr<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      At the file system level, copy the
      <code class="filename">
       .sdi
      </code>
      files to the import server
      <a class="link" href="server-system-variables.html#sysvar_secure_file_priv">
       <code class="literal">
        secure_file_priv
       </code>
      </a>
      directory,
      <code class="filename">
       /tmp/mysql-files
      </code>
      . Also, copy the table
          content files to the
      <code class="literal">
       hr
      </code>
      schema directory:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa46325102"><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"><span class="token prompt">$&gt; </span><span class="token command">cd</span> /tmp/export
<span class="token prompt">$&gt; </span><span class="token command">cp</span> employees_125<span class="token punctuation">.</span>sdi /tmp/mysql-files
<span class="token prompt">$&gt; </span><span class="token command">cp</span> managers_238<span class="token punctuation">.</span>sdi /tmp/mysql-files
<span class="token prompt">$&gt; </span><span class="token command">cp</span> employees<span class="token punctuation">.</span><span class="token punctuation">{</span>MYD<span class="token punctuation">,</span>MYI<span class="token punctuation">}</span> <em class="replaceable">import_basedir</em>/data/hr
<span class="token prompt">$&gt; </span><span class="token command">cp</span> managers<span class="token punctuation">.</span><span class="token punctuation">{</span>MYD<span class="token punctuation">,</span>MYI<span class="token punctuation">}</span> <em class="replaceable">import_basedir</em>/data/hr</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Import the tables by executing an
      <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
       <code class="literal">
        IMPORT
          TABLE
       </code>
      </a>
      statement that names the
      <code class="filename">
       .sdi
      </code>
      files:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90053315"><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">IMPORT</span> <span class="token keyword">TABLE</span> <span class="token keyword">FROM</span>
       <span class="token string">'/tmp/mysql-files/employees.sdi'</span><span class="token punctuation">,</span>
       <span class="token string">'/tmp/mysql-files/managers.sdi'</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
  <p>
   The
   <code class="filename">
    .sdi
   </code>
   file need not be placed in the
      import server directory named by the
   <a class="link" href="server-system-variables.html#sysvar_secure_file_priv">
    <code class="literal">
     secure_file_priv
    </code>
   </a>
   system variable
      if that variable is empty; it can be in any directory accessible
      to the server, including the schema directory for the imported
      table. If the
   <code class="filename">
    .sdi
   </code>
   file is placed in that
      directory, however, it may be rewritten; the import operation
      creates a new
   <code class="filename">
    .sdi
   </code>
   file for the table, which
      overwrites the old
   <code class="filename">
    .sdi
   </code>
   file if the operation
      uses the same file name for the new file.
  </p>
  <p>
   Each
   <em class="replaceable">
    <code>
     sdi_file
    </code>
   </em>
   value must be a string
      literal that names the
   <code class="filename">
    .sdi
   </code>
   file for a table
      or is a pattern that matches
   <code class="filename">
    .sdi
   </code>
   files. If
      the string is a pattern, any leading directory path and the
   <code class="filename">
    .sdi
   </code>
   file name suffix must be given
      literally. Pattern characters are permitted only in the base name
      part of the file name:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       ?
      </code>
      matches any single character
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       *
      </code>
      matches any sequence of characters,
          including no characters
     </p>
    </li>
   </ul>
  </div>
  <p>
   Using a pattern, the previous
   <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
    <code class="literal">
     IMPORT
      TABLE
    </code>
   </a>
   statement could have been written like this
      (assuming that the
   <code class="filename">
    /tmp/mysql-files
   </code>
   directory
      contains no other
   <code class="filename">
    .sdi
   </code>
   files matching the
      pattern):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa89745063"><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">IMPORT</span> <span class="token keyword">TABLE</span> <span class="token keyword">FROM</span> <span class="token string">'/tmp/mysql-files/*.sdi'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To interpret the location of
   <code class="filename">
    .sdi
   </code>
   file path
      names, the server uses the same rules for
   <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
    <code class="literal">
     IMPORT TABLE
    </code>
   </a>
   as the server-side
      rules for
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   (that is, the
      non-
   <code class="literal">
    LOCAL
   </code>
   rules). See
   <a class="xref" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    Section 15.2.9, “LOAD DATA Statement”
   </a>
   , paying particular attention to the
      rules used to interpret relative path names.
  </p>
  <p>
   <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
    <code class="literal">
     IMPORT TABLE
    </code>
   </a>
   fails if the
   <code class="literal">
    .sdi
   </code>
   or table files cannot be located. After
      importing a table, the server attempts to open it and reports as
      warnings any problems detected. To attempt a repair to correct any
      reported issues, use
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR TABLE
    </code>
   </a>
   .
  </p>
  <p>
   <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
    <code class="literal">
     IMPORT TABLE
    </code>
   </a>
   is not written to the
      binary log.
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="import-table-restrictions">
       </a>
       Restrictions and Limitations
      </h4>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
     <code class="literal">
      IMPORT TABLE
     </code>
    </a>
    applies only to
        non-
    <code class="literal">
     TEMPORARY
    </code>
    <code class="literal">
     MyISAM
    </code>
    tables. It does not apply to tables created with a transactional
        storage engine, tables created with
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TEMPORARY
        TABLE
     </code>
    </a>
    , or views.
   </p>
   <p>
    An
    <code class="filename">
     .sdi
    </code>
    file used in an import operation
        must be generated on a server with the same data dictionary
        version and sdi version as the import server. The version
        information of the generating server is found in the
    <code class="filename">
     .sdi
    </code>
    file:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-json"><div class="docs-select-all right" id="sa90261876"><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-json"><span class="token punctuation">{</span>
   <span class="token property">"mysqld_version_id"</span><span class="token operator">:</span><span class="token number">80019</span><span class="token punctuation">,</span>
   <span class="token property">"dd_version"</span><span class="token operator">:</span><span class="token number">80017</span><span class="token punctuation">,</span>
   <span class="token property">"sdi_version"</span><span class="token operator">:</span><span class="token number">80016</span><span class="token punctuation">,</span>
   ...
<span class="token punctuation">}</span></code></pre>
   </div>
   <p>
    To determine the data dictionary and sdi version of the import
        server, you can check the
    <code class="filename">
     .sdi
    </code>
    file of a
        recently created table on the import server.
   </p>
   <p>
    The table data and index files must be placed in the schema
        directory for the import server prior to the import operation,
        unless the table as defined on the export server uses the
    <code class="literal">
     DATA DIRECTORY
    </code>
    or
    <code class="literal">
     INDEX
        DIRECTORY
    </code>
    table options. In that case, modify the
        import procedure using one of these alternatives before
        executing the
    <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
     <code class="literal">
      IMPORT TABLE
     </code>
    </a>
    statement:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Put the data and index files into the same directory on the
            import server host as on the export server host, and create
            symlinks in the import server schema directory to those
            files.
      </p>
     </li>
     <li class="listitem">
      <p>
       Put the data and index files into an import server host
            directory different from that on the export server host, and
            create symlinks in the import server schema directory to
            those files. In addition, modify the
       <code class="filename">
        .sdi
       </code>
       file to reflect the different file
            locations.
      </p>
     </li>
     <li class="listitem">
      <p>
       Put the data and index files into the schema directory on
            the import server host, and modify the
       <code class="filename">
        .sdi
       </code>
       file to remove the data and index
            directory table options.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Any collation IDs stored in the
    <code class="filename">
     .sdi
    </code>
    file
        must refer to the same collations on the export and import
        servers.
   </p>
   <p>
    Trigger information for a table is not serialized into the table
    <code class="filename">
     .sdi
    </code>
    file, so triggers are not restored by
        the import operation.
   </p>
   <p>
    Some edits to an
    <code class="filename">
     .sdi
    </code>
    file are permissible
        prior to executing the
    <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
     <code class="literal">
      IMPORT
        TABLE
     </code>
    </a>
    statement, whereas others are problematic or may
        even cause the import operation to fail:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Changing the data directory and index directory table
            options is required if the locations of the data and index
            files differ between the export and import servers.
      </p>
     </li>
     <li class="listitem">
      <p>
       Changing the schema name is required to import the table
            into a different schema on the import server than on the
            export server.
      </p>
     </li>
     <li class="listitem">
      <p>
       Changing schema and table names may be required to
            accommodate differences between file system case-sensitivity
            semantics on the export and import servers or differences in
       <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
        <code class="literal">
         lower_case_table_names
        </code>
       </a>
       settings. Changing the table names in the
       <code class="filename">
        .sdi
       </code>
       file may require renaming the
            table files as well.
      </p>
     </li>
     <li class="listitem">
      <p>
       In some cases, changes to column definitions are permitted.
            Changing data types is likely to cause problems.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
