MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

15.6.3.6 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.

  1. Stop the server.

  2. Move the tablespace files or directories.

  3. 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, and datadir configuration options are automatically appended to the innodb_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 the sakila schema, then the actor.ibd data file can only be moved to a directory named sakila.

      • 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, and datadir settings, as necessary.

  4. Restart the server.

Usage Notes