Exclude tables during restore from full backup

MySQL & MariaDB Percona XtraBackup
1

Is there a possibility to exclude tables from being restored from a full backup, or at least removing them before the backup is copied back? As far as I understand the procedure to restore particular tables this still requires to prepare the full backup, and also requires these tables to already exist in the target database, and the --tables-exclude option does only work during backup creation.
My use case is to restore only a set of required tables from rather large production backups for testing and analysis. Currently this requires doing a full restore and deleting unnecessary tables afterwards, consuming a lot of time and disk space.
Alternatives would be to either do additional partial backups or to partially replicate the production database to another server. Both approaches have the disadvantage that you have to know exactly which tables are needed later on.
A “partial restore” by excluding tables during restore on the other hand could simply use an existing full backup, but requiring less time and disk space, and providing more flexibility.

Thanks!
Rainer

2

Hi Rainer,

I’ve tried Percona Server 8.0.34-26 with Xtrabackup 8.0.34-29

xtrabackup --backup --target-dir=$PWD/backup1
xtrabackup --prepare --target-dir=$PWD/backup1
rm -rf backup1/world/city.ibd 
xtrabackup --prepare --target-dir=$PWD/backup1
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=$PWD/backup1
chown mysql:mysql -R /var/lib/mysql

And the --prepare command after removing redundant table is not removing all missing ibd files from the dictionary.

Fortunately the instance starts with corresponding warnings:

2023-12-08T10:15:48.884560Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `world`.`city` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.

This issue easily resolved by drop:

use world
drop table city;

This is a known issue [PXB-2589] Xtrabackup 8 is not able to remove missing tables from the InnoDB data dictionary during prepare - Percona JIRA and you may vote for this feature at jira to pin developers attention.

3

Thanks for the fast answer! This will make life much easier - and even easier, when [PXB-2589] is implemented.