Not the answer you need?
Register and ask your own question!

Restore one table

MouglouMouglou EntrantLegacy User Role Beginner
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 [email protected]_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;
mysql> ALTER TABLE aaa DISCARD TABLESPACE;

Then I rsync the ibd file from the extract backup to the MySQL VM:
rsync -avz aaa.ibd [email protected]_vm:/var/lib/mysql/data/test/

ON MySQL again:
chown mysql: aaa.ibd

mysql> use test;
mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE aaa IMPORT TABLESPACE;

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:
https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_individual_tables_ibk.html
https://www.percona.com/blog/2012/01/25/how-to-recover-a-single-innodb-table-from-a-full-backup/

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 !

Comments

  • mirfanmirfan Database Administrator Inactive User Role Beginner
    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
  • MouglouMouglou Entrant Legacy User Role Beginner
    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!
  • exaexa Entrant Current User Role Beginner
    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.

    Thanks!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.