Rechercher dans le manuel MySQL
13.2.5 IMPORT TABLE Syntax
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):
For the export server,
export_basedir
represents its base directory, and its data directory is
.export_basedir
/dataFor the import server,
import_basedir
represents its base directory, and its data directory is
.import_basedir
/dataTable files are exported from the export server into the
/tmp/export
directory and this directory is secure (not accessible to other users).The import server uses
/tmp/mysql-files
as the directory named by itssecure_file_priv
system variable.
To export tables from the export server, use this procedure:
Ensure a consistent snapshot by executing this statement to lock the tables so that they cannot be modified during export:
While the lock is in effect, the tables can still be used, but only for read access.
At the file system level, copy the
.sdi
and table content files from thehr
schema directory to the secure export directory:The
.sdi
file is located in thehr
schema directory, but might not have exactly the same basename as the table name. For example, the.sdi
files for theemployees
andmanagers
tables might be namedemployees_125.sdi
andmanagers_238.sdi
.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
Unlock the tables:
To import tables into the import server, use this procedure:
The import schema must exist. If necessary, execute this statement to create it:
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 thehr
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
Import the tables by executing an
IMPORT TABLE
statement that names the.sdi
files:
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:
?
matches any single character*
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):
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
Section 13.2.7, “LOAD DATA Syntax”, 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 with
CREATE TEMPORARY
TABLE
, or views.
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
DATA DIRECTORY
or INDEX
DIRECTORY
table options. In that case, modify the
import procedure using one of these alternatives before
executing the IMPORT TABLE
statement:
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.
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
.sdi
file to reflect the different file locations.Put the data and index files into the schema directory on the import server host, and modify the
.sdi
file to remove the data and index directory table options.
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:
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.
Changing the schema name is required to import the table into a different schema on the import server than on the export server.
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
lower_case_table_names
settings. Changing the table names in the.sdi
file may require renaming the table files as well.In some cases, changes to column definitions are permitted. Changing data types is likely to cause problems.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-import-table.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.