XtraBackup Restore specific database since a full backup

MySQL & MariaDB Percona XtraBackup
, ,
1

Hi,

I’m having two problem.

  1. IPreformatted text’m trying to restore a database from a backup full, but only me can restore all databases, Can us restore a database?

    1. I’m trying to restore a table specific of a database with next comman.

      xtrabackup --prepare --export --target-dir=/data/backups/
      ALTER TABLE TMP DISCARD TABLESPACE
      ALTER TABLE TMP IMPORT TABLESPACE

once the files have been transferred to the mysql directory I cannot recover the data, is there any other way to retrieve only one table?

Regards

1 Like
2

Hi @Roderick,
Restoring a single table involves these steps:

  1. xtrabackup --prepare --export --target-dir=/data/backups/
  2. CREATE DATABASE restore;
  3. CREATE TABLE restore.comman LIKE orig.comman;
  4. ALTER TABLE restore.comman DISCARD TABLESPACE;
  5. cp /data/backups/orig/comman.* /var/lib/mysql/restore/
  6. chown mysql:mysql /var/lib/mysql/restore/comman.*
  7. ALTER TABLE restore.comman IMPORT TABLESPACE;
2 Likes
3

Hi @matthewb

Thanks for support, perform the restore of database with the tables of way correctly.

Regards

1 Like
4

I would say your backup is not accurate. You changed the schema after taking the backup. After you CREATE TABLE … LIKE …, drrop the new index before importing tablespace.

3 Likes
5

Hi @matthewb

Thanks, if you had a problem with the indexes but the table structure was recreated and it worked correctly.

Regards

1 Like