Skip to content
Starting November 2023 Percona XtraBackup 2.4 has reached EOL status. If you have 5.7 databases, we encourage you to upgrade to 8.0 and then install Percona XtraBackup 8.0. Learn more

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.29 (2023-12-18)
        • Percona XtraBackup 2.4.28 (2023-04-04)
        • 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 tables
    • Importing tables

    Restoring Individual Tables¶

    In server versions prior to 5.6, it is not possible to copy tables between servers by copying the files, even with innodb_file_per_table. However, with the Percona XtraBackup, you can export individual tables from any InnoDB database, and import them into Percona Server with XtraDB or MySQL 5.6 (The source doesn’t have to be XtraDB or or MySQL 5.6, but the destination does). This only works on individual .ibd files, and cannot export a table that is not contained in its own .ibd file.

    Note

    If you’re running Percona Server version older than 5.5.10-20.1, variable innodb_expand_import should be used instead of innodb_import_table_from_xtrabackup.

    Exporting tables¶

    Exporting is done in the preparation stage, not at the moment of creating the backup. Once a full backup is created, prepare it with the innobackupex --export option:

    $ innobackupex --apply-log --export /path/to/backup
    

    This will create for each InnoDB with its own tablespace a file with .exp extension. An output of this procedure would contain:

    ..
    xtrabackup: export option is specified.
    xtrabackup: export metadata of table 'mydatabase/mytable' to file
    `./mydatabase/mytable.exp` (1 indexes)
    ..
    

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

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

    The result should be 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.6.

    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.6 or Percona Server for MySQL 5.6. 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.

    Each .exp (or .cfg) file will be used for importing that table.

    Note

    InnoDB does a slow shutdown (i.e. full purge + change buffer merge) on –export, otherwise the tablespaces wouldn’t be consistent and thus couldn’t be imported. All the usual performance considerations apply: sufficient buffer pool (i.e. --use-memory, 100MB by default) and fast enough storage, otherwise it can take a prohibitive amount of time for export to complete.

    Importing tables¶

    To import a table to other server, first create a new table with the same structure as the one that will be imported at that server:

    OTHERSERVER|mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;
    

    then discard its tablespace:

    OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
    

    Next, copy mytable.ibd and mytable.exp ( or mytable.cfg if importing to MySQL 5.6) files to database’s home, and import its tablespace:

    OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
    

    Set the owner and group of the files:

    $ chown -R mysql:mysql /datadir/db_name/table_name.*
    

    After running this command, data in the imported table will be available.

    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.

    2022-11-10
    Percona LLC and/or its affiliates, © 2024 Cookie Preferences
    Made with Material for MkDocs