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
Partial Backups
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

    • Creating Partial Backups
      • Using innobackupex --include
      • Using innobackupex --tables-file
      • Using innobackupex --databases
    • Preparing Partial Backups
    • Restoring Partial Backups

    Partial Backups¶

    Percona XtraBackup features partial backups, which means that you may backup only some specific tables or databases. The tables you back up must be in separate tablespaces, as a result of being created or altered after you enabled the innodb_file_per_table option on the server.

    There is only one caveat about partial backups: do not copy back the prepared backup. Restoring partial backups should be done by importing the tables, 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.

    Creating Partial Backups¶

    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).

    Using innobackupex --include¶

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

    For example,

    $ innobackupex --include='^mydatabase[.]mytable' /path/to/backup
    

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

    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.

    Using innobackupex --tables-file¶

    The text file provided (the path) to this option can contain multiple table names, one per line, in the databasename.tablename format.

    For example,

    $ echo "mydatabase.mytable" > /tmp/tables.txt
    $ innobackupex --tables-file=/tmp/tables.txt /path/to/backup
    

    The command above will create a timestamped directory with the usual files that innobackupex creates, but only containing the data-files related to the tables specified in the file.

    This option is passed to xtrabackup --tables-file and, unlike the --tables <xtrabackup –tables> option, only directories of databases of the selected tables will be created.

    Using innobackupex --databases¶

    This option accepts either a space-separated list of the databases and tables to backup - in the databasename[.tablename] form.

    For example,

    $ innobackupex --databases="mydatabase.mytable mysql" /path/to/backup
    

    The command above will create a timestamped directory with the usual files that innobackupex creates, but only containing the datafiles related to mytable in the mydatabase directory and the mysql directory with the entire mysql database.

    The --databases-file option specifies the path to a text file which contains a case-sensitive list of databases and tables to be backed up. The file can contain the names of multiple databases and tables in a databasename[.tablename] format with one element for each line. Only the named databases and tables are backed up. The names must match exactly. There is no pattern matching or regular expression matching.

    Preparing Partial Backups¶

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

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

    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 who weren’t selected in the partial backup) from the data dictionary in order to avoid future warnings or errors:

    111225  0:54:06  InnoDB: Error: table 'mydatabase/mytablenotincludedinpartialb'
    InnoDB: in InnoDB data dictionary has tablespace id 6,
    InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.
    

    You should also see the notification of the creation of a file needed for importing (.exp file) for each table included in the partial backup:

    xtrabackup: export option is specified.
    xtrabackup: export metadata of table 'employees/departments' to file `.//departments.exp` (2 indexes)
    xtrabackup:     name=PRIMARY, id.low=80, page=3
    xtrabackup:     name=dept_name, id.low=81, page=4
    

    Note that you can use the innobackupex --export option with innobackupex --apply-log to an already-prepared backup in order to create the .exp files.

    Finally, check for the confirmation message in the output:

    111225 00:54:18  innobackupex: completed OK!
    

    Restoring Partial Backups¶

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

    It can also be done by copying back the prepared backup to a “clean” datadir (in that case, make sure to include the mysql database). System database can be created with:

    $ sudo mysql_install_db --user=mysql
    

    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