Announcement

Announcement Module
Collapse
No announcement yet.

restore one table from xtrabackup's full backup

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • restore one table from xtrabackup's full backup

    Is there anyway to restore only one table or schema from xtrabackup's full backup?

    It seems that in order to restore one table only, I need to restore the whole database and extract the table out after that

  • #2
    found the solution.

    Comment


    • #3
      Can you please let me know your solution to restore single schema with xtrabackup

      Comment


      • #4
        Tables should be created with innodb_file_per_table option enabled. i.e. each table data should reside on it's own tablespace (.ibd) file and this feature is only available on percona server. Further, those options need to be enable where you want to import the schema/tables. Options are innodb_file_per_table and innodb_expand_import or innodb_import_table_from_xtrabackup depends on percona server version for the the last option.

        To restore single table from full backup. Follow these steps.


        Backup data as usual. Apply log with --export option. This will create prepare each table with .exp and .ibd files.
        1) innobackupex --apply-log --export /path/to/backup

        On importing server create table manually which need to restore
        2) CREATE TABLE test ...

        Discard the tablespace
        3) ALTER TABLE test DISCARD TABLESPACE;

        Copy the .exp & .ibd files to the datadir where need to import
        4) ALTER TABLE test IMPORT TABLESPACE;

        Comment


        • #5
          Thank you very much Mirfan

          Comment

          Working...
          X