Skip to content
logo
Percona XtraBackup
Restoring Individual Tables
Initializing search
    percona/pxb-docs
    percona/pxb-docs
    • Home
      • About Percona XtraBackup
      • How Percona XtraBackup Works
      • Understand version numbers
      • Installing Percona XtraBackup 2.4
      • Installing Percona XtraBackup on Debian and Ubuntu
      • Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS
      • Installing Percona XtraBackup from a Binary Tarball
      • Compiling and Installing from Source Code
      • Running Percona XtraBackup in a Docker container
      • Connection and Privileges Needed
      • Configuring xtrabackup
      • The Backup Cycle - Full Backups
      • Incremental Backup
      • Compressed Backup
      • Encrypted Backup
      • Percona XtraBackup User Manual
      • Throttling Backups
      • Lockless binary log information
      • Encrypted InnoDB Tablespace Backups
      • `lock-ddl-per-table` Option Improvements
      • How-tos and Recipes
        • Release notes index
        • Percona XtraBackup 2.4.27 (2022-12-06)
        • Percona XtraBackup 2.4.26
        • Percona XtraBackup 2.4.25
        • Percona XtraBackup 2.4.24
        • Percona XtraBackup 2.4.23
        • Percona XtraBackup 2.4.22
        • Percona XtraBackup 2.4.21
        • Percona XtraBackup 2.4.20
        • Percona XtraBackup 2.4.19
        • Percona XtraBackup 2.4.18
        • Percona XtraBackup 2.4.17
        • Percona XtraBackup 2.4.16
        • Percona XtraBackup 2.4.15
        • Percona XtraBackup 2.4.14
        • Percona XtraBackup 2.4.13
        • Percona XtraBackup 2.4.12
        • Percona XtraBackup 2.4.11
        • Percona XtraBackup 2.4.10
        • Percona XtraBackup 2.4.9
        • Percona XtraBackup 2.4.8
        • Percona XtraBackup 2.4.7-2
        • Percona XtraBackup 2.4.7
        • Percona XtraBackup 2.4.6
        • Percona XtraBackup 2.4.5
        • Percona XtraBackup 2.4.4
        • Percona XtraBackup 2.4.3
        • Percona XtraBackup 2.4.2
        • Percona XtraBackup 2.4.1
      • The xtrabackup Option Reference
      • The innobackupex Option Reference
      • The xbcloud Binary
      • Exponential Backoff
      • Using the xbcloud binary with Microsoft Azure Cloud Storage
      • The xbcrypt binary
      • The xbstream binary
      • Known issues and limitations
      • Frequently Asked Questions
      • Glossary
      • Index of files created by Percona XtraBackup
      • Trademark policy
      • Copyright and licensing information
      • Version Checking

    • Exporting the Table
    • Importing the Table

    Restoring Individual Tables¶

    With Percona XtraBackup, you can export individual tables from any InnoDB database, and import them into Percona Server for MySQL with XtraDB or MySQL 5.7. The source does not need to be XtraDB or MySQL 5.7 but the destination must be. This operation only works on individual .ibd files. A table that is not contained in its own .ibd file cannot be exported.

    Let’s see how to export and import the following table:

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

    Exporting the Table¶

    This table should have been created in innodb_file_per_table mode, so after taking a backup as usual with xtrabackup --backup, the .ibd file should exist in the target directory:

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

    when you prepare the backup, add the extra parameter xtrabackup --export to the command. Here is an example:

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

    Note

    If you’re trying to restore encrypted InnoDB tablespace table you must specify the keyring file as well:

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

    Now you should see a .exp file in the target directory:

    $ find /data/backups/mysql/ -name export_test.*
    
    The result is similar to the following:

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

    These three files are all you need to import the table into a server running Percona Server for MySQL with XtraDB or MySQL 5.7. In case server is using InnoDB Tablespace Encryption additional .cfp file be listed for encrypted tables.

    Note

    MySQL uses .cfg file which contains InnoDB dictionary dump in special format. This format is different from the .exp\ one which is used in XtraDB for the same purpose. Strictly speaking, a .cfg\ file is not required to import a tablespace to MySQL 5.7 or Percona Server for MySQL 5.7. A tablespace will be imported successfully even if it is from another server, but InnoDB will do schema validation if the corresponding .cfg file is present in the same directory.

    Importing the Table¶

    On the destination server, create a table with the same structure, and then perform the following steps:

    • Execute ALTER TABLE test.export_test DISCARD TABLESPACE;

      • If you see the ERROR 1030 (HY000): Got error -1 from storage engine message, then enable innodb_file_per_table and create the table again:
    • Copy the exported files to the test/ subdirectory of the destination server’s data directory

    • Execute ALTER TABLE test.export_test IMPORT TABLESPACE;

    The table should now be imported, and you should be able to SELECT from it and see the imported data.

    Note

    Persistent statistics for imported tablespace will be empty until you run the ANALYZE TABLE on the imported table. They are empty because they are stored in the system tables mysql.innodb_table_stats and mysql.innodb_index_stats and they are not updated by server during the import. This is due to upstream bug #72368.

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: 2022-11-10
    Back to top
    Percona LLC, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better.