Skip to content
logo
Percona XtraBackup
Create a partial backup
Initializing search
    percona/pxb-docs
    percona/pxb-docs
    • Home
      • Release notes index
      • Percona XtraBackup 8.0.35-30 (2023-12-04)
      • Percona XtraBackup 8.0.34-29 (2023-08-21)
      • Percona XtraBackup 8.0.33-28 (2023-07-19)
      • Percona XtraBackup 8.0.33-27 (2023-05-25)
      • Percona XtraBackup 8.0.32-26 (2023-04-04)
      • Percona XtraBackup 8.0.32-25 (2023-02-27)
      • Percona XtraBackup 8.0.31-24 (2023-02-07)
        • Percona XtraBackup 8.0.30-23 (2022-11-14)
        • Percona XtraBackup 8.0.29-22 (2022-07-19)
        • Percona XtraBackup 8.0.28-21 (2022-05-25)
        • Percona XtraBackup 8.0.28-20
        • Percona XtraBackup 8.0.27-19
        • Percona XtraBackup 8.0.26-18.0
        • Percona XtraBackup 8.0.25-17.0
        • Percona XtraBackup 8.0.23-16.0
        • Percona XtraBackup 8.0.22-15.0
        • Percona XtraBackup 8.0.14
        • Percona XtraBackup 8.0.13
        • Percona XtraBackup 8.0.12
        • Percona XtraBackup 8.0.11
        • Percona XtraBackup 8.0.10
        • Percona XtraBackup 8.0.9
        • Percona XtraBackup 8.0.8
        • Percona XtraBackup 8.0.7
        • Percona XtraBackup 8.0.6
        • Percona XtraBackup 8.0.5
        • Percona XtraBackup 8.0.4
        • Percona XtraBackup 8.0-3-rc1
      • About Percona XtraBackup
      • How Percona XtraBackup works
      • Understand version numbers
        • LRU dump backup
        • Throttling backups
        • Store backup history on the server
        • Dictionary cache
        • Point-in-time recovery
        • Restore individual tables
      • Encrypted InnoDB tablespace backups
      • FLUSH TABLES WITH READ LOCK option
      • Improved log statements
      • lock-ddl-per-table option improvements
      • Smart memory estimation
      • Work with binary logs
      • Overview
        • The xtrabackup binary overview
        • xtrabackup implementation details
        • Configure xtrabackup
        • Analyze table statistics
      • The xbcrypt binary overview
        • The xbstream binary overview
        • Take a streaming backup
        • Accelerate the backup process
        • Encrypt backups
        • The xbcloud binary overview
          • Use the xbcloud binary with Amazon S3
          • Use the xbcloud binary with an IAM instance profile
          • Use the xbcloud binary with Swift
          • Use the xbcloud binary with Google Cloud Storage
          • Use the xbcloud binary with Microsoft Azure Cloud Storage
          • Use the xbcloud binary with MinIO
        • Update curl utility
        • FIFO data sink
        • Exponential backoff
      • Quickstart Guide for Percona XtraBackup
      • Install Percona XtraBackup 8.0 overview
        • Server version and backup version comparison
        • Connection and privileges needed
        • Permissions needed
        • Use APT repositories
        • Files in DEB package
        • Downloaded DEB packages
        • Apt pinning
        • Work with AppArmor
        • Uninstall
        • Use RPM repositories
        • Files in RPM package
        • Downloaded RPM packages
        • Work with SELinux
        • Uninstall
        • Install with binary tarballs
        • Binary tarballs available
        • Compile and install Percona XtraBackup 8.0 from source
        • Run Percona XtraBackup 8.0 in a Docker container
        • Create a full backup
        • Prepare a full backup
        • Create an incremental backup
        • Prepare an incremental backup
        • Take an incremental backup using page tracking
        • Create a compressed backup
        • Decompress and prepare a compressed backup
        • Create a partial backup
          • The –-tables option
          • The -–tables-file option
          • The --databases and -–databases-file options
          • The --databases-file option
        • Prepare a partial backup
        • Create an individual partitions backup
        • Prepare an individual partitions backup
        • Restore full, incremental, compressed backups
        • Restore a partial backup
        • Restore an individual partitions backup
        • How to set up a replica for replication in 6 simple steps with Percona XtraBackup
        • How to create a new (or repair a broken) GTID-based replica
        • Make backups in replication environments
        • Verify backups with replication and pt-checksum
      • Error Message: Found tables with row versions due to INSTANT ADD/DROP columns
      • xtrabackup exit codes
      • Topic index
      • The xtrabackup option reference
      • Index of files created by Percona XtraBackup
      • Frequently asked questions
      • Glossary
      • Percona Toolkit version checking
      • Trademark policy
      • Copyright and licensing information

    • The –-tables option
    • The -–tables-file option
    • The --databases and -–databases-file options
    • The --databases-file option

    Create a partial backup¶

    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

    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.

    Warning

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

    There are multiple ways of specifying which part of the whole data is backed up:

    • Use the --tables option to list the table names

    • Use the --tables-file option to list the tables in a file

    • Use the --databases option to list the databases

    • Use the --databases-file option to list the databases

    The –-tables option¶

    The first method involves the xtrabackup –tables option. The option’s value is a regular expression that is matched against the fully-qualified database name and table name using the databasename.tablename format.

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

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

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

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

    The -–tables-file option¶

    The --tables-file option 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
    

    The --databases and -–databases-file options¶

    The ` –databases` option accepts a space-separated list of the databases and tables to backup in the databasename[.tablename] format. 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 test ...'
    

    The --databases-file option¶

    The –databases-file option specifies a file that can contain multiple databases and tables in the databasename[.tablename] format, 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.

    The next step is to prepare the backup in order to restore it.

    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: 2023-07-10
    Percona LLC and/or its affiliates, © 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. Read more about Percona Cookie Policy.