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
Back up and restore individual partitions
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

    • Create the backup
    • Prepare the backup
    • Restore from the backups
      • Restoring from the backups in version 5.6

    Back up and restore individual partitions¶

    Percona XtraBackup features partial backups, which means that you may backup individual partitions as well because from the storage engines perspective partitions are regular tables with specially formatted names. The only requirement for this feature is having the innodb_file_per_table option enabled in the server.

    There is only one caveat about using this kind of backup: you can’t copy back the prepared backup. Restoring partial backups should be done by importing the tables, and not by using the traditional innobackupex --copy-back option. Although there are some scenarios where restoring can be done by copying back the files, this may be lead to database inconsistencies in many cases and it is not the recommended way to do it.

    Create the backup¶

    There are three ways of specifying which part of the whole data will be backed up: regular expressions (innobackupex --include), enumerating the tables in a file (innobackupex --tables-file) or providing a list of databases (innobackupex --databases). In this example innobackupex --include option will be used.

    The regular expression provided to this option will be matched against the fully qualified tablename, including the database name, in the form databasename.tablename.

    For example, this will back up the partition p4 from the table name located in the database imdb:

    $ innobackupex --include='^imdb[.]name#P#p4' /mnt/backup/
    

    This will create a timestamped directory with the usual files that innobackupex creates, but only the data files related to the tables matched.

    Output of the innobackupex will list the skipped tables

    ...
    [01] Skipping ./imdb/person_info.ibd
    [01] Skipping ./imdb/name#P#p5.ibd
    [01] Skipping ./imdb/name#P#p6.ibd
    ...
    imdb.person_info.frm is skipped because it does not match ^imdb[.]name#P#p4.
    imdb.title.frm is skipped because it does not match ^imdb[.]name#P#p4.
    imdb.company_type.frm is skipped because it does not match ^imdb[.]name#P#p4.
    ...
    

    Note that this option is passed to xtrabackup --tables and is matched against each table of each database, the directories of each database will be created even if they are empty.

    Prepare the backup¶

    For preparing partial backups, the procedure is analogous to restoring individual tables: apply the logs and use innobackupex --export:

    $ innobackupex --apply-log --export /mnt/backup/2012-08-28_10-29-09
    

    You may see warnings in the output about tables that don’t exist. This is because InnoDB-based engines stores its data dictionary inside the tablespace files besides the .frm files. innobackupex will use xtrabackup to remove the missing tables (those that haven’t been selected in the partial backup) from the data dictionary in order to avoid future warnings or errors:

    InnoDB: in InnoDB data dictionary has tablespace id 51,
    InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.
    120828 10:25:28  InnoDB: Waiting for the background threads to start
    120828 10:25:29 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 10098323731
    xtrabackup: export option is specified.
    xtrabackup:     name=PRIMARY, id.low=73, page=3
    

    Finally, check the for the confirmation message in the output:

    120828 19:25:38  innobackupex: completed OK!
    

    Restore from the backups¶

    Restoring should be done by importing the tables in the partial backup to the server.

    Note

    Improved table/partition import is only available in Percona Server for MySQL and MySQL 5.6, this means that partitions which were backed up from different server can be imported as well. For versions older than MySQL 5.6 only partitions from that server can be imported with some important limitations. There should be no DROP/CREATE/TRUNCATE/ALTER TABLE commands issued between taking the backup and importing the partition.

    First step is to create new table in which data will be restored

    mysql> CREATE TABLE name_p4 (
    id int(11) NOT NULL AUTO_INCREMENT,
    name text NOT NULL,
    imdb_index varchar(12) DEFAULT NULL,
    imdb_id int(11) DEFAULT NULL,
    name_pcode_cf varchar(5) DEFAULT NULL,
    name_pcode_nf varchar(5) DEFAULT NULL,
    surname_pcode varchar(5) DEFAULT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=2812744 DEFAULT CHARSET=utf8
    

    To restore the partition from the backup tablespace needs to be discarded for that table:

    mysql>  ALTER TABLE name_p4 DISCARD TABLESPACE;
    

    The next step is to copy the ibd file from the backup to MySQL data directory:

    $ cp /mnt/backup/2012-08-28_10-29-09/imdb/name#P#p4.ibd /var/lib/mysql/imdb/name_p4.ibd
    

    Note

    Make sure that the copied files can be accessed by the user running the MySQL.

    The last step is to import the tablespace:

    mysql>  ALTER TABLE name_p4 IMPORT TABLESPACE;
    

    Restoring from the backups in version 5.6¶

    The problem with server versions up to 5.5 is that there is no server support to import either individual partitions or all partitions of a partitioned table, so partitions could only be imported as independent tables. In MySQL and Percona Server for MySQL 5.6 it is possible to exchange individual partitions with independent tables through ALTER TABLE … EXCHANGE PARTITION command.

    Note

    In Percona Server for MySQL 5.6, the variable innodb_import_table_from_xtrabackup was removed in favor of MySQL Transportable Tablespaces implementation.

    When importing an entire partitioned table, first import all (sub)partitions as independent tables:

    mysql> CREATE TABLE `name_p4` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` text NOT NULL,
    `imdb_index` varchar(12) DEFAULT NULL,
    `imdb_id` int(11) DEFAULT NULL,
    `name_pcode_cf` varchar(5) DEFAULT NULL,
    `name_pcode_nf` varchar(5) DEFAULT NULL,
    `surname_pcode` varchar(5) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2812744 DEFAULT CHARSET=utf8
    

    To restore the partition from the backup tablespace needs to be discarded for that table:

    mysql>  ALTER TABLE name_p4 DISCARD TABLESPACE;
    

    The next step is to copy the .cfg and .ibd files from the backup to MySQL data directory:

    $ cp /mnt/backup/2013-07-18_10-29-09/imdb/name#P#p4.cfg /var/lib/mysql/imdb/name_p4.cfg
    $ cp /mnt/backup/2013-07-18_10-29-09/imdb/name#P#p4.ibd /var/lib/mysql/imdb/name_p4.ibd
    

    The last step is to import the tablespace:

    mysql>  ALTER TABLE name_p4 IMPORT TABLESPACE;
    

    We can now create the empty partitioned table with exactly the same schema as the table being imported:

    mysql> CREATE TABLE name2 LIKE name;
    

    Then swap empty partitions from the newly created table with individual tables corresponding to partitions that have been exported/imported on the previous steps:

    mysql> ALTER TABLE name2 EXCHANGE PARTITION p4 WITH TABLE name_p4;
    

    In order for this operation to be successful following conditions have to be met.

    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.

    2023-02-23
    Percona LLC and/or its affiliates, © 2024 Cookie Preferences
    Made with Material for MkDocs