Топ-100
 

IMPORT TABLE

RDBMS Type: MySQL
Topic

Syntax:
IMPORT TABLE FROM sdi_file [, sdi_file] ...

The IMPORT TABLE statement imports MyISAM tables based on information
contained in .sdi (serialized dictionary information) metadata files.
IMPORT TABLE requires the FILE privilege to read the .sdi and table
content files, and the CREATE privilege for the table to be created.

Tables can be exported from one server using mysqldump to write a file
of SQL statements and imported into another server using mysql to
process the dump file. IMPORT TABLE provides a faster alternative using
the "raw" table files.

Prior to import, the files that provide the table content must be
placed in the appropriate schema directory for the import server, and
the .sdi file must be located in a directory accessible to the server.
For example, the .sdi file can be placed in the directory named by the
secure_file_priv system variable, or (if secure_file_priv is empty) in
a directory under the server data directory.

The following example describes how to export MyISAM tables named
employees and managers from the hr schema of one server and import them
into the hr schema of another server. The example uses these
assumptions (to perform a similar operation on your own system, modify
the path names as appropriate):

o For the export server, export_basedir represents its base directory,
and its data directory is export_basedir/data.

o For the import server, import_basedir represents its base directory,
and its data directory is import_basedir/data.

o Table files are exported from the export server into the /tmp/export
directory and this directory is secure (not accessible to other
users).

o The import server uses /tmp/mysql-files as the directory named by its
secure_file_priv system variable.

To export tables from the export server, use this procedure:

1. Ensure a consistent snapshot by executing this statement to lock the
tables so that they cannot be modified during export:

mysql> FLUSH TABLES hr.employees, hr.managers WITH READ LOCK;

While the lock is in effect, the tables can still be used, but only
for read access.

2. At the file system level, copy the .sdi and table content files from
the hr schema directory to the secure export directory:

o The .sdi file is located in the hr schema directory, but might not
have exactly the same basename as the table name. For example, the
.sdi files for the employees and managers tables might be named
employees_125.sdi and managers_238.sdi.

o For a MyISAM table, the content files are its .MYD data file and
.MYI index file.

Given those file names, the copy commands look like this:

shell> cd export_basedir/data/hr
shell> cp employees_125.sdi /tmp/export
shell> cp managers_238.sdi /tmp/export
shell> cp employees.{MYD,MYI} /tmp/export
shell> cp managers.{MYD,MYI} /tmp/export

3. Unlock the tables:

mysql> UNLOCK TABLES;

To import tables into the import server, use this procedure:

1. The import schema must exist. If necessary, execute this statement
to create it:

mysql> CREATE SCHEMA hr;

2. At the file system level, copy the .sdi files to the import server
secure_file_priv directory, /tmp/mysql-files. Also, copy the table
content files to the hr schema directory:

shell> cd /tmp/export
shell> cp employees_125.sdi /tmp/mysql-files
shell> cp managers_238.sdi /tmp/mysql-files
shell> cp employees.{MYD,MYI} import_basedir/data/hr
shell> cp managers.{MYD,MYI} import_basedir/data/hr

3. Import the tables by executing an IMPORT TABLE statement that names
the .sdi files:

mysql> IMPORT TABLE FROM
'/tmp/mysql-files/employees.sdi',
'/tmp/mysql-files/managers.sdi';

The .sdi file need not be placed in the import server directory named
by the secure_file_priv 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 .sdi file is placed in that
directory, however, it may be rewritten; the import operation creates a
new .sdi file for the table, which will overwrite the old .sdi file if
the operation uses the same file name for the new file.

Each sdi_file value must be a string literal that names the .sdi file
for a table or is a pattern that matches .sdi files. If the string is a
pattern, any leading directory path and the .sdi file name suffix must
be given literally. Pattern characters are permitted only in the base
name part of the file name:

o ? matches any single character

o * matches any sequence of characters, including no characters

Using a pattern, the previous IMPORT TABLE statement could have been
written like this (assuming that the /tmp/mysql-files directory
contains no other .sdi files matching the pattern):

IMPORT TABLE FROM '/tmp/mysql-files/*.sdi';

To interpret the location of .sdi file path names, the server uses the
same rules for IMPORT TABLE as the server-side rules for LOAD DATA
(that is, the non-LOCAL rules). See [HELP LOAD DATA], paying particular
attention to the rules used to interpret relative path names.

IMPORT TABLE fails if the .sdi 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 REPAIR TABLE.

IMPORT TABLE is not written to the binary log.

URL: https://dev.mysql.com/doc/refman/8.0/en/import-table.html

Example