Restore one table

Hello everybody,

I’m new on Xtrabackup and we have to test it before use it in production.

I’ve a problem when I try a restore. I create a “aaa” table with 2 records inside the “test” database.

The backup is done successfuly on another server as that:
innobackupex --user=user_backup --password=user_password --stream=tar ./ | ssh root@remote_host “cat - > /var/tmp/backup.tar”

Then I would like to try a restore of a test table name “aaa”.

So here are my command:
tar -xif backup.tar
innobackupex --apply-log --export /var/tmp/backup

Then I go to my MySQL VM and do that:

I remove and create again the table with the same structure.

mysql> use test;
mysql> set FOREIGN_KEY_CHECKS=0;

Then I rsync the ibd file from the extract backup to the MySQL VM:
rsync -avz aaa.ibd root@mysql_vm:/var/lib/mysql/data/test/

ON MySQL again:
chown mysql: aaa.ibd

mysql> use test;
mysql> set FOREIGN_KEY_CHECKS=0;

But after the IMPORT command I get this error:
#1030 - Got error -1 from storage engine

I try the same process with adding the .exp file but I got same error.

I use MySQL 5.5.41.

I’m helping with heses link:

Somebody get the same problem before ?

I don’t find why and how to solve this error…

It seams to be easy when we would like to restore a complete database, but sometimes we have to restore only on table and not the complete database…

Thanks a lot if you can help me !

Did you copied table.cfg and table.exp files along with table.ibd file ? Do you have innodb_import_table_from_xtrabackup option enabled on destination server ? Make sure you set right permission for test database usually mysql

Hello Mirfan

Sorry for the long time, I was not available…

No I copy only .ibd and .exp file cause of 5.5 version.
But I think you solve my problem…

The specific option is not present into my.cnf file. I try to add it but MySQL didn’t restart cause this option is unknown…

I forget to give one information, is that we use MariaDB… So I think this option is available only on Percona Server.

Is there any solution to restore only one table with Xtrabackup with MariaDB Server ?

Thanks for you answear mirfan!

Hi MIrfan,

I am using Percona Server 5.6.28-76.1 and it gives me an error while mysql restart when i try to include in my.cnf.

Could you please confirm if this option is supported in Percona 5.6?

Also is there any alternative to restore individual DB using individual .ibd, .cfg,.exp files?

Awaiting your reply.