Percona XtraBackup: Backup and Restore of a Single Table or Database

Percona XtraBackup of Single TableThe backup and restore of a complete database is an extensive exercise. But what if you need to restore just one table which has been mistakenly modified by an incorrect query? Help is at hand with Percona XtraBackup.

For our purpose, we will take a test database having tables created by the sysbench tool. The 8.0 versions of Percona XtraBackup and Percona Server for MySQL have been used in this test.

Restore Single Table

Here we will take the backup of the sbtest2 table and restore it. The initial checksum of the table is given below:

Take a backup of a single InnoDB table using the option: --tables

XtraBackup copies the table file sbtest2.ibd in the backup directory (dbbackup_PS8_table/test) along with other files required to prepare the backup.

You can also give patterns in the --tables option and XtraBackup will take backups of all tables matching the pattern. If there are many tables to be backed up, then these can be specified as a list in a text file with the option --tables-file. There is also an option to exclude tables using the --tables-exclude option.

Now prepare the backup with the extra --export option. This is a special option that will prepare the table configuration.

After preparation, the files sbtest2.ibd and sbtest.cfg are available in the backup directory. To restore this table, we have to first remove the existing tablespace from the database.

Now copy the table files from the backup directory (dbbackup_PS8_table/test/sbtest2.*) to Percona Server data directory (PS130320_8_0_19_10_debug/data/test). 

Note: Before copying the files, disable selinux. After the files are copied, change the ownership of the copied files to the mysql user, if the backup user is different.

Finally, import the tablespace.

The checksum of the table after the restore is:

The table is restored successfully.

Another method is to take the backup of the whole database and use it to restore one or more tables. Here, backup is simply done using the--backup option.

Prepare the backup using--export option.

Next, discard the table’s tablespace, copy the table files from backup directory to the Percona Server data directory, and import the tablespace.

For MyISAM tables, the backup and prepare process is the same as above, and the only difference is that the table needs to be dropped and then restored using the IMPORT TABLE statement.

Restore Entire Schema/Database

We can take the backup of a database schema and restore it using the same process as described above.

Take a backup of the database using the --databases option.

For more than one database, specify the databases as a list, such as --databases="db1 db2 db3". The databases can also be specified in a text file and used with the option --databases-file. To exclude a database from the backup, use the option --databases-exclude.

Prepare the backup using --export option.

Now remove the tablespace of all InnoDB tables in the database using ALTER TABLE <table name> DISCARD TABLESPACE.

Copy all table files from the backup dir (dbbackup_PS8_db/test/*) to the mysql data dir(PS130320_8_0_19_10_debug/data/test).

Note: Before copying the files, disable selinux. After the files are copied change the ownership of the copied files to mysql user if the backup user is different.

Finally, restore the tables using ALTER TABLE <table name> IMPORT TABLESPACE;.

This will restore the tables to the time of the backup. For a point in time recovery, binlogs can be further applied to the database, though care should be taken to apply only those transactions which affect the tables being restored.

The advantage of using this method is that the database server need not be stopped. A slight disadvantage is that each table needs to be restored individually, though it can be overcome with the help of a script.

Conclusion

It is easy to backup and restore a table or a database using Percona XtraBackup, by using just a few commands.

Share this post

Comments (6)

  • imsukan Reply

    Interesting, can’t we simple do transportable table space.

    I mean, use export table space on source and copy the ibd,cfg
    create table, discard table space , import the tablespace copied?

    April 10, 2020 at 11:48 am
  • Quepasa Reply

    Is it compatible with xtradb cluster 5.7?

    April 10, 2020 at 1:19 pm
  • Phil Stracchino Reply

    What is often needed, but is difficult to accomplish using XtraBackup, is to restore a single table of a few tables from a full DB backup that was not intended for that purpose. When you need to restore a single table, you often don’t have the luxury of doing single-table backup first.

    Do you have any tips for restoring a single table or a few tables from XtraBackup without having to restore a full database copy first and then exporting the tables you want?

    April 10, 2020 at 3:41 pm
    • James Wang Reply

      The same question as Phil here

      April 12, 2020 at 5:38 am
  • Luca (Sengerio) Reply

    Very interesting article, I was looking for a such clear “step by step guide” from a while.
    Unfortunatly I executed all the steps described above to create and restore a single database backup, but I got an error when I tried to import the tablespace again on some tables of my db. The error was “Schema mismatch (Column precise type mismatch.)”.
    Has anyone had the same problem? Suggestions?
    Hope someone will help me.
    Thank you.

    April 11, 2020 at 5:55 am
  • Narendra Reply

    Interesting article. Is it possible to restore table with other name or taking backup of table in one schema and restore in another schema?

    May 21, 2020 at 11:01 pm

Leave a Reply