Is it possible to restore a Xtrabackup database under a different database name?

MySQL & MariaDB Percona XtraBackup
1

Hello,

We are running Percona MySQL 8.0.22-13 on our primary database server and want to use Percona Xtrabackup 80-8.0.23-16 to backup only our primary database. [MyCommanyDB]

We have a development server that we refresh frequently running our dev database [MyCompanyDB_dev]. Is it possible to use Percona Xtrabackup to backup only our primary database from our primary database [MyCompanyDB] and restore that database on our development server dev database? [MyCompanyDB_dev] If not, this should definitely be a feature request to add to Xtrabackup.

Doing mydumper/mysqldump + importing into the new DB does work but it’s VERY slow.

If the feature isn’t included in Xtrabackup , Are their any good workarounds for to implement this? [and some shell commands to implement this?]

Thanks.

1 Like
2

Hi @boris ,

This is possible already, please check Percona XtraBackup: Backup and Restore of a Single Table or Database - Percona Database Performance Blog In summary you will need :

xtrabackup --backup --databases=MyCompanyDB ...
xtrabackup --prepare --export ...

# For each table

ALTER TABLE <table name> IMPORT TABLESPACE;
1 Like
3

@Marcelo_Altmann

  1. Is it possible to restore a fresh backup of a single database (–backup) in an empty database on our Development mysql server? We have created an Empty database on our development database and we just want to import all the tables into it? After --prepare and --exporting the backed up data and I tried
ALTER TABLE <table name> DISCARD TABLESPACE;

(which said table doesn’t exist which is true since the development database is empty)
and then copied the prepared+export files from the backup to the development new database and tried

ALTER TABLE <table name> IMPORT TABLESPACE;

on the development database, mysql errors out with

'ERROR 1146 (42S02) at line 1: Table 'MyCompanyDB_dev.action_logs' doesn't exist
  1. Is it possible to import all the tables copied over and tablespace import all the files at once without having to know the table names? [Like a wildcard [*] tablespace import?]
1 Like