This procedure describes how to restore orphan
file-per-table
.ibd
files to another MySQL instance. You
might use this procedure if the system tablespace is lost or
unrecoverable and you want to restore .ibd
file backups on a new MySQL instance.
The procedure is not supported for
general
tablespace .ibd
files.
The procedure assumes that you only have
.ibd
file backups, you are recovering to
the same version of MySQL that initially created the orphan
.ibd
files, and that
.ibd
file backups are clean. See
Section 14.6.1.4, “Moving or Copying InnoDB Tables” for information about
creating clean backups.
Table import limitations outlined in Section 14.6.1.3, “Importing InnoDB Tables” are applicable to this procedure.
On the new MySQL instance, recreate the table in a database of the same name.
mysql> CREATE DATABASE sakila; mysql> USE sakila; mysql> CREATE TABLE actor ( actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (actor_id), KEY idx_actor_last_name (last_name) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Discard the tablespace of the newly created table.
mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;
Copy the orphan
.ibd
file from your backup directory to the new database directory.$> cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/
Ensure that the
.ibd
file has the necessary file permissions.Import the orphan
.ibd
file. A warning is issued indicating thatInnoDB
tries to import the file without schema verification.mysql> ALTER TABLE sakila.actor IMPORT TABLESPACE; SHOW WARNINGS; Query OK, 0 rows affected, 1 warning (0.15 sec) Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './sakila/actor.cfg', will attempt to import without schema verification
Query the table to verify that the
.ibd
file was successfully restored.
mysql> SELECT COUNT(*) FROM sakila.actor;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
source
Niciun comentariu:
Trimiteți un comentariu