Restoring Individual Tables and Partitions with Mariabackup - Source

Revision: 148700
User: Geoff Montee
Date: 2025-03-13 12:22

<<toc>>

When using Mariabackup, you don't necessarily need to restore every table and/or partition that was backed up. Even if you're starting from a [[full-backup-and-restore-with-mariabackup|full backup]], it is certainly possible to restore only certain tables and/or partitions from the backup, as long as the table or partition involved is in an [[innodb-file-per-table-tablespaces|InnoDB file-per-table tablespace]]. This page documents how to restore individual tables and partitions.

== Preparing the Backup

Before you can restore from a backup, you first need to **prepare** it to make the data files consistent. You can do so with the ##[[mariabackup-options#-prepare|--prepare]]## command option.

The ability to restore individual tables and partitions relies on [[innodb-file-per-table-tablespaces/#copying-transportable-tablespaces|InnoDB's transportable tablespaces]]. For MariaDB to import tablespaces like these, [[xtradb-and-innodb|InnoDB]] looks for a file with a ##.cfg## extension. For Mariabackup to create these files, you also need to add the ##[[mariabackup-options/#-export|--export]]## option during the prepare step.

For example, you might execute the following command:

<<code>>
$ mariabackup --prepare --export \
   --target-dir=/var/mariadb/backup/ \
   --user=mariabackup --password=mypassword
<</code>>

If this operation completes without error, then the backup is ready to be restored.

<<style class=greenbox>>
==== **Note**
Mariabackup did not support the ##[[mariabackup-options/#-export|--export]]## option to begin with. See MDEV-13466 about that. In earlier versions of MariaDB, this means that Mariabackup could not create ##.cfg## files for [[innodb-file-per-table-tablespaces|InnoDB file-per-table tablespaces]] during the ##--prepare## stage. You can still [[innodb-file-per-table-tablespaces/#copying-transportable-tablespaces|import file-per-table tablespaces]] without the ##.cfg## files in many cases, so it may still be possible in those versions to [[partial-backup-and-restore-with-mariabackup|restore partial backups]] or to [[restoring-individual-tables-and-partitions-with-mariabackup|restore individual tables and partitions]] with just the ##.ibd## files. If you have a [[full-backup-and-restore-with-mariabackup|full backup]] and you need to create ##.cfg## files for [[innodb-file-per-table-tablespaces|InnoDB file-per-table tablespaces]], then you can do so by preparing the backup as usual without the ##--export## option, and then restoring the backup, and then starting the server. At that point, you can use the server's built-in features to [[innodb-file-per-table-tablespaces/#copying-transportable-tablespaces|copy the transportable tablespaces]].
<</style>>

== Restoring the Backup

The restore process for restoring individual tables and/or partitions is quite different than the process for [[full-backup-and-restore-with-mariabackup|full backups]].

Rather than using the ##[[mariabackup-options#-copy-back|--copy-back]]## or the ##[[mariabackup-options#-move-back|--move-back]]##, each individual [[innodb-file-per-table-tablespaces|InnoDB file-per-table tablespace]] file will have to be manually imported into the target server. The process that is used to restore the backup will depend on whether partitioning is involved.

=== Restoring Individual Non-Partitioned Tables

To restore individual non-partitioned tables from a backup, find the ##.ibd## and ##.cfg## files for the table in the backup, and then import them using the [[innodb-file-per-table-tablespaces/#importing-transportable-tablespaces-for-non-partitioned-tables|Importing Transportable Tablespaces for Non-partitioned Tables]] process.

=== Restoring Individual Partitions and Partitioned Tables

To restore individual partitions or partitioned tables from a backup, find the ##.ibd## and ##.cfg## files for the partition(s) in the backup, and then import them using the [[innodb-file-per-table-tablespaces/#importing-transportable-tablespaces-for-partitioned-tables|Importing Transportable Tablespaces for Partitioned Tables]] process.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Restoring Individual Tables and Partitions with Mariabackup - Source

Products

Solutions

Resources

Company

Pricing

Download MariaDB