Can I use a Percona Xtrabackup backup to restore a single table?

Question

I've been playing around with the Xtrabackup tool for a month now, and I got to admit : it is really powerful !

I'm still testing it to backup database servers between 5 and 150 GB and the backup/restoration time are impressive. So I'm trying to push the use of this tool to my manager, saying the only inconvenient we could have is the backup archive size (which is 3x/4x time larger than with mysqldump) and on the other part we gain faster times on backup/restoration, no lock during the backup process and the ability to stream backups to servers.

He was quite impressed by the capability of the tool, but the most important part for him in a backup/restore process is to be able to restore a specific table of a database quickly. As for what I know about this tool, I'm still thinking that this is not possible with Xtrabackup right now. Am I right?

We're using the following softwares :

Ubuntu 12.04 MySQL Server 5.5.32 Xtrabackup 2.1.3-608.precise

PS: I've seen some solutions to restore a specific table from a backup created with Xtrabackup, but it always require Percona Server.

If it is not possible to do with Xtrabackup, do someone have another solution to this problematic?

Answer 1

This functionality is currently only supported on Percona Server.

See the official documentation link for more information: http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/importing_exporting_tables_ibk.html

Answer 2

As of now (2021 March) this is possible to restore a single table starting percona 2.3, this is detailed in this document.

In few steps

1. prepare the backup: xtrabackup --prepare --export --target-dir=/data/backup
2. discard tablespace for the table: ALTER TABLE db.table_to_restore DISCARD TABLESPACE;
3. copy the the table files in the directory cp -a /data/backup/db/table_to_restore.* /var/lib/mysql/db/
4. fix the file permissions: chown mysql:mysql /var/lib/mysql/db/table_to_restore.*
5. load the tablespace: ALTER TABLE db.table_to_restore IMPORT TABLESPACE;