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.4. The source doesn’t have to be XtraDB or MySQL 8.4, 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.4. 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.4, 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.