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

    • Using xtrabackup –tables
    • Using xtrabackup --tables-file
    • Using xtrabackup --databases and xtrabackup --databases-file
    • Preparing the Backup

    Partial Backups¶

    xtrabackup supports taking partial backups when the innodb_file_per_table option is enabled. There are three ways to create partial backups:

    1. matching the tables names with a regular expression

    2. providing a list of table names in a file

    3. providing a list of databases

    Warning

    If any of the matched or listed tables is deleted during the backup, xtrabackup will fail.

    Do not copy back the prepared backup. Restoring partial backups should be done by importing the tables, not by using the --copy-back option. It is not recommended to run incremental backups after running a partial backup.

    Although there are some scenarios where restoring can be done by copying back the files, this may lead to database inconsistencies in many cases and it is not a recommended way to do it.

    For the purposes of this manual page, we will assume that there is a database named test which contains tables named t1 and t2.

    Using xtrabackup –tables¶

    The first method involves the xtrabackup --tables option. The option’s value is a regular expression that is matched against the fully qualified tablename, including the database name, in the form databasename.tablename.

    To back up only tables in the test database, you can use the following command:

    $ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
    --tables="^test[.].*"
    

    To back up only the table test.t1, you can use the following command:

    $ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
    --tables="^test[.]t1"
    

    Using xtrabackup --tables-file¶

    xtrabackup --tables-file specifies a file that can contain multiple table names, one table name per line in the file. Only the tables named in the file will be backed up. Names are matched exactly, case-sensitive, with no pattern or regular expression matching. The table names must be fully qualified, in databasename.tablename format.

    $ echo "mydatabase.mytable" > /tmp/tables.txt
    $ xtrabackup --backup --tables-file=/tmp/tables.txt
    

    Using xtrabackup --databases and xtrabackup --databases-file¶

    xtrabackup --databases accepts a space-separated list of the databases and tables to backup in the format databasename[.tablename]. In addition to this list make sure to specify the mysql, sys, and performance_schema databases. These databases are required when restoring the databases using xtrabackup --copy-back.

    Note

    Tables processed during the –prepare step may also be added to the backup even if they are not explicitly listed by the parameter if they were created after the backup started.

    $ xtrabackup --databases='mysql sys performance_schema ...'
    

    xtrabackup --databases-file specifies a file that can contain multiple databases and tables in the databasename[.tablename] form, one element name per line in the file. Names are matched exactly, case-sensitive, with no pattern or regular expression matching.

    Note

    Tables processed during the –prepare step may also be added to the backup even if they are not explicitly listed by the parameter if they were created after the backup started.

    Preparing the Backup¶

    When you use the xtrabackup --prepare option on a partial backup, you will see warnings about tables that don’t exist. This is because these tables exist in the data dictionary inside InnoDB, but the corresponding .ibd files don’t exist. They were not copied into the backup directory. These tables will be removed from the data dictionary, and when you restore the backup and start InnoDB, they will no longer exist and will not cause any errors or warnings to be printed to the log file.

    An example of the error message you will see during the prepare phase follows.

    InnoDB: Reading tablespace information from the .ibd files...
    101107 22:31:30  InnoDB: Error: table 'test1/t'
    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.
    

    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