Import a Single Table from Xtrabackup ( Partial backup )

In this blog post, we will discuss how to restore a single InnoDB table from a full backup after dropping the table.


Here we discuss about Transportable tablespace concept, which was introduced in MySQL 5.6. So when you have deleted the data from a table, you are going to quickly restore this table.

  • Prepare the backup
  • Discard the tablespace of the original table
  • Copy .ibd from the backup to the original table path
  • Import the tablespace

Create an Xtrabackup :

$ innobackupex --user=dbuser --password=db_userpassword  --target-dir=/path/to/BACKUP-DIR/

Prepare a backup:

Prepare the backups with –export option. This will create each innodb table with its own tablespace and a file with .exp extension

$ innobackupex --apply-log --export --use-memory=4G /path/to/BACKUP-DIR/

NOTE: –use-memory variable depends upon the total RAM memory.

Running the above command should generate output something like as below. You can see there is a .exp file getting generated which contains the data dictionary dump in a format understandable only by XtraDB.

 [root@prodserv BACKUP-DIR]# ls -ltrh BACKUP-DIR.*
-rw-r----- 1 root root 112K Aug 7 09:06 testtable.ibd
-rw-r----- 1 root root 8.5K Aug 7 09:06 testtable.frm
-rw-r--r-- 1 root root 16K Aug 7 09:19 testtable.exp
-rw-r--r-- 1 root root 521 Aug 7 09:19 testtable.cfg

Now we have the prepared backup with all the innodb tables with their respective .cfg and .exp files

Create table 

To import a innodb we would require the table to be available in the destination or we need to create the table if it does not exists.

 mysql> \u test
Database changed
mysql> show tables like 'testtable';
Empty set (0.00 sec)

mysql> CREATE TABLE `testtable` (
-> `dept_no` char(4) NOT NULL,
-> `dept_name` varchar(64) DEFAULT NULL,
-> PRIMARY KEY (`dept_no`),
-> UNIQUE KEY `dept_name` (`dept_name`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.59 sec)

Below are the files under data directory after creating the testtable table

[root@prodserv mysql]# ls -ltrh /data/data01/mysql/test/testtable.*
-rw-rw---- 1 mysql mysql 8.5K Aug 7 10:21 /data/data01/mysql/test/testtable.frm
-rw-rw---- 1 mysql mysql 112K Aug 7 10:21 /data/data01/mysql/test/testtable.ibd

Discard and Import Tablespace 

Discard the existing tablespace and import the tablespace from xtrabackup

mysql> \u test
Database changed
mysql> ALTER TABLE `testtable` DISCARD TABLESPACE;
Query OK, 0 rows affected (0.07 sec)
[root@prodserv mysql]# ls -ltrh /var/lib/mysql/test/testtable.*
-rw-rw---- 1 mysql mysql 8.5K Aug 7 10:21 /var/lib/mysql/test/testtable.frm

Now Copy the files from the backup directory (testtable.exp,testtable.cfg and testtable.ibd ) to the data directory

[root@prodserv mysql]# cp /path/to/BACKUP-DIR/test/testtable.ibd /var/lib/mysql/test/
[root@prodserv mysql]# cp /path/to/BACKUP-DIR/test/testtable.exp /var/lib/mysql/test/
[root@prodserv mysql]# cp /path/to/BACKUP-DIR/test/testtable.cfg /var/lib/mysql/test/

Verify the files in the data directory

[root@prodserv data]# ls -ltrh /var/lib/mysql/test/testtable.*
-rw-rw---- 1 mysql mysql 8.5K Aug 7 10:47 /var/lib/mysql/test/testtable.frm
-rw-r----- 1 mysql mysql 112K Aug 7 10:49 /var/lib/mysql/test/testtable.ibd
-rw-r--r-- 1 mysql mysql 16K Aug 7 10:49 /var/lib/mysql/test/testtable.exp
-rw-r--r-- 1 mysql mysql 521 Aug 7 10:49 /var/lib/mysql/test/testtable.cfg

Change ownership of the testtable table files to mysql.mysql to allow mysql server to access it.

 Import the tablespace from MySQL

mysql> ALTER TABLE `testtable` IMPORT TABLESPACE;
Query OK, 0 rows affected (0.21 sec)

 In 5.5 there is an important variable we need to enable

mysql> alter table testtable import tablespace;
ERROR 1030 (HY000): Got error -1 from storage engine

mysql> show global variables like '%import%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| innodb_import_table_from_xtrabackup | 0 |
+-------------------------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_import_table_from_xtrabackup=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table testtable import tablespace;
Query OK, 0 rows affected (0.00 sec)

Verify the import from error log status.

2014-08-09 11:51:59 2336 [Note] InnoDB: Importing tablespace for table 'test/testtable' that was exported from host 'Hostname unknown'
2014-08-09 11:51:59 2336 [Note] InnoDB: Phase I - Update all pages
2014-08-09 11:51:59 2336 [Note] InnoDB: Sync to disk
2014-08-09 1:51:59 2336 [Note] InnoDB: Sync to disk - done!
2014-08-09 11:51:59 2336 [Note] InnoDB: Phase III - Flush changes to disk
2014-08-09 11:51:59 2336 [Note] InnoDB: Phase IV - Flush complete

Refer below link for more details :

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: