MySQL :: MySQL 8.4 Reference Manual :: 15.2.6 IMPORT TABLE Statement (original) (raw)

15.2.6 IMPORT TABLE Statement

IMPORT TABLE FROM sdi_file [, sdi_file] ...

The IMPORT TABLE statement importsMyISAM 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 theCREATE privilege for the table to be created.

Tables can be exported from one server usingmysqldump 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 (ifsecure_file_priv is empty) in a directory under the server data directory.

The following example describes how to exportMyISAM tables namedemployees 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):

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: 

$> cd export_basedir/data/hr  
$> cp employees_125.sdi /tmp/export  
$> cp managers_238.sdi /tmp/export  
$> cp employees.{MYD,MYI} /tmp/export  
$> cp managers.{MYD,MYI} /tmp/export
  1. 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;
  1. At the file system level, copy the .sdi files to the import serversecure_file_priv directory,/tmp/mysql-files. Also, copy the table content files to the hr schema directory:
$> cd /tmp/export  
$> cp employees_125.sdi /tmp/mysql-files  
$> cp managers_238.sdi /tmp/mysql-files  
$> cp employees.{MYD,MYI} import_basedir/data/hr  
$> cp managers.{MYD,MYI} import_basedir/data/hr
  1. 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 thesecure_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 overwrites the old .sdi file if the operation uses the same file name for the new file.

Each sdifile 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:

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 forIMPORT TABLE as the server-side rules for LOAD DATA (that is, the non-LOCAL rules). SeeSection 15.2.9, “LOAD DATA Statement”, 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.

Restrictions and Limitations

IMPORT TABLE applies only to non-TEMPORARY MyISAM tables. It does not apply to tables created with a transactional storage engine, tables created withCREATE TEMPORARY TABLE, or views.

An .sdi 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.sdi file:

{
   "mysqld_version_id":80019,
   "dd_version":80017,
   "sdi_version":80016,
   ...
}

To determine the data dictionary and sdi version of the import server, you can check the .sdi file of a recently created table on the import server.

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 theDATA DIRECTORY or INDEX DIRECTORY table options. In that case, modify the import procedure using one of these alternatives before executing the IMPORT TABLE statement:

Any collation IDs stored in the .sdi file must refer to the same collations on the export and import servers.

Trigger information for a table is not serialized into the table.sdi file, so triggers are not restored by the import operation.

Some edits to an .sdi file are permissible prior to executing the IMPORT TABLE statement, whereas others are problematic or may even cause the import operation to fail: