Rechercher dans le manuel MySQL
15.6.3.8 Moving Tablespace Files While the Server is Offline
The innodb_directories
option,
which defines directories to scan at startup for tablespace files,
supports moving or restoring tablespace files to a new location
while the server is offline. During startup, discovered tablespace
files are used instead those referenced in the data dictionary,
and the data dictionary is updated to reference the relocated
files. If duplicate tablespace files are discovered by the scan,
startup fails with an error indicating that multiple files were
found for the same tablespace ID.
The directories defined by the
innodb_data_home_dir
,
innodb_undo_directory
, and
datadir
configuration options are
automatically appended to the
innodb_directories
argument
value. These directories are scanned at startup regardless of
whether the innodb_directories
option is specified explicitly. The implicit addition of these
directories permits moving system tablespace files, the data
directory, or undo tablespace files without configuring the
innodb_directories
setting.
However, settings must be updated when directories change. For
example, after relocating the data directory, you must update the
--datadir
setting before
restarting the server.
The innodb_directories
option may
be specified in a startup command or MySQL option file. Quotes are
used around the argument value because otherwise a semicolon (;)
is interpreted as a special character by some command
interpreters. (Unix shells treat it as a command terminator, for
example.)
Startup command:
mysqld --innodb-directories="directory_path_1;directory_path_2"
MySQL option file:
[mysqld]
innodb_directories="directory_path_1;directory_path_2"
The following procedure is applicable to moving individual file-per-table and general tablespace files, system tablespace files, undo tablespace files, or the data directory. Before moving files or directories, review the usage notes that follow.
Stop the server.
Move the tablespace files or directories.
Make the new directory known to
InnoDB
.If moving individual file-per-table or general tablespace files, add unknown directories to the
innodb_directories
value.The directories defined by the
innodb_data_home_dir
,innodb_undo_directory
, anddatadir
configuration options are automatically appended to theinnodb_directories
argument value, so you need not specify these.A file-per-table tablespace file can only be moved to a directory with same name as the schema. For example, if the
actor
table belongs to thesakila
schema, then theactor.ibd
data file can only be moved to a directory namedsakila
.General tablespace files cannot be moved to the data directory or a subdirectory of the data directory.
If moving system tablespace files, undo tablespaces, or the data directory, update the
innodb_data_home_dir
,innodb_undo_directory
, anddatadir
settings, as necessary.
Restart the server.
Usage Notes
Wildcard expressions cannot be used in the
innodb_directories
argument value.The
innodb_directories
scan also traverses subdirectories of specified directories. Duplicate directories and subdirectories are discarded from the list of directories to be scanned.The
innodb_directories
option only supports movingInnoDB
tablespace files. Moving files that belong to a storage engine other thanInnoDB
is not supported. This restriction also applies when moving the entire data directory.The
innodb_directories
option supports renaming of tablespace files when moving files to a scanned directory. It also supports moving tablespaces files to other supported operating systems.When moving tablespace files to a different operating system, ensure that tablespace file names do not include prohibited characters or characters with a special meaning on the destination system.
When moving a data directory from a Windows operating system to a Linux operating system, modify the binary log file paths in the binary log index file to use backward slashes instead of forward slashes. By default, the binary log index file has the same base name as the binary log file, with the extension '
.index
'. The location of the binary log index file is defined by--log-bin
. The default location is the data directory.If moving tablespace files to a different operating system introduces cross-platform replication, it is the responsibility of the Database Administrator to ensure proper replication of DDL statements that contain platform-specific directories. Statements that permit specifying directories include
CREATE TABLE ... DATA DIRECTORY
andCREATE TABLESPACE
.The directory of file-per-table and general tablespace files created with an absolute path or in a location outside of the data directory should be added to the
innodb_directories
argument value. Otherwise,InnoDB
is not able to locate these files during recovery.CREATE TABLE ... DATA DIRECTORY
andCREATE TABLESPACE
permit creation of tablespace files with absolute paths.CREATE TABLESPACE
also permits tablespace file directories that are relative to the data directory. To view tablespace file locations, query theINFORMATION_SCHEMA.FILES
table:CREATE TABLESPACE
requires that the target directory exists and is known toInnoDB
. Known directories include those implicitly and explicitly defined by theinnodb_directories
option.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-moving-data-files-offline.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.