Restore individual tables¶

Percona XtraBackup can export a table that is contained in its own .ibd file. With Percona XtraBackup, you can export individual tables from any InnoDB database, and import them into Percona Server for MySQL with XtraDB or MySQL 8.3. The source doesn’t have to be XtraDB or MySQL 8.3, but the destination does. This method only works on individual .ibd files.

The following example exports and imports the following table:

CREATE TABLE export_test (
a int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Export the table¶

To generate an .ibd file in the target directory, create the table using the innodb_file_per_table mode:

$ find /data/backups/mysql/ -name export_test.*
/data/backups/mysql/test/export_test.ibd

During the --prepare step, add the --export option to the command. For example:

$ xtrabackup --prepare --export --target-dir=/data/backups/mysql/

When restoring an encrypted InnoDB tablespace table, add the keyring file:

$ xtrabackup --prepare --export --target-dir=/tmp/table \
--keyring-file-data=/var/lib/mysql-keyring/keyring

The following files are the only files required to import the table into a server running Percona Server for MySQL with XtraDB or MySQL 8.3. If the server uses InnoDB Tablespace Encryption, add the .cfp file, which contains the transfer key and an encrypted tablespace key.

The files are located in the target directory:

/data/backups/mysql/test/export_test.ibd
/data/backups/mysql/test/export_test.cfg

Import the table¶

On the destination server running Percona Server for MySQL with XtraDB or MySQL 8.3, create a table with the same structure, and then perform the following steps:

Run the ALTER TABLE test.export_test DISCARD TABLESPACE; command. If you see the following error message:
Error message
```
ERROR 1809 (HY000): Table 'test/export_test' in system tablespace
```
enable innodb_file_per_table option on the server and create the table again.
```
$ set global innodb_file_per_table=ON;
```
Copy the exported files to the test/ subdirectory of the destination server’s data directory.
Run ALTER TABLE test.export_test IMPORT TABLESPACE;

The table is imported, and you can run a SELECT to see the imported data.

Get expert help¶

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.

Community Forum Get a Percona Expert

Last update: 2024-05-02