# Backing up and restoring a single table

**URL:** <https://forums.percona.com/t/backing-up-and-restoring-a-single-table/3216>\
**Category:** Percona XtraBackup\
**Created:** [January 20, 2014, 2:55am UTC](https://forums.percona.com/t/backing-up-and-restoring-a-single-table/3216 "2014-01-20T02:55:05Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![binhminh07](https://avatars.discourse-cdn.com/v4/letter/b/8edcca/32.png) [@binhminh07](https://forums.percona.com/u/binhminh07)\
**Post date:** [January 20, 2014, 2:55am UTC](https://forums.percona.com/t/backing-up-and-restoring-a-single-table/3216/1 "2014-01-20T02:55:05Z")

</div>

Hi,

Sorry for my bad English.

I have a database system with one master and slave , about 1TB data \>\<  
System not yet backup before.

Now , I want to use xtrabackup to backup single table .  
I read Partial Backup but I have 4 problem as the following:

1. Is it Ok if I run fullbackup with data 1TB?

2. Is it nesscessary to run fullbackup or full database before backup a single table ( I guest no need but…)

3. ALL of my table is innodb but when run backup , the message on screen alway show information ALL TABLE IS LOCKED AND FLUSHED TO DISK.  
it is ok?

4. When I run as the introduction at "partial backup " with single table , on step IMPORT TABLESPACE, it alway occur error like that

140120 19:25:33 InnoDB: Error: tablespace id and flags in file ‘./test\_restore/trn\_users.ibd’ are 321 and 0, but in the InnoDB  
InnoDB: data dictionary they are 327 and 0.

whats happening?

Please help me!!!

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![mirfan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mirfan/32/16890_2.png) [@mirfan](https://forums.percona.com/u/mirfan)\
**Post date:** [January 20, 2014, 6:54am UTC](https://forums.percona.com/t/backing-up-and-restoring-a-single-table/3216/2 "2014-01-20T06:54:57Z")

</div>

1. yes, it’s absolutely fine, i saw xtrabackup running without any problems over terabytes of dataset.
2. No.
3. Xtrabackup requires FLUSH TABLES WITH READ LOCK (FTWRL) to copy non-innodb tables and table structure files (.frm files). If all your tables are innodb and you don’t issue any DDL during course of problem you can avoid FTWRL by using [–no-lock](http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html#cmdoption-innobackupex--no-lock) option to avoid tables being locked. As [–no-lock](http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html#cmdoption-innobackupex--no-lock) describes use this option if you don’t care backup binary log position so backup doesn’t contains xtrabackup\_binlog\_info file if backup is created with [–no-lock](http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html#cmdoption-innobackupex--no-lock) option because [–no-lock](http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html#cmdoption-innobackupex--no-lock) prevents FTWRL which is required to get consistent positions for binary log. However, FTWRL is normally for short period of times if all your tables are InnoDB. You can read more about it here [url][Percona XtraBackup](http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/improved_ftwrl.html%5B/url%5D)
4. It’s probably when copying non-innodb and tables structure files (.frm files) so it should be ok.
5. What is source MySQL version from where you back up ? Can you please post your steps for partial backup and attach backup log too.

---

<div class="post-metadata">

**Author:** ![binhminh07](https://avatars.discourse-cdn.com/v4/letter/b/8edcca/32.png) [@binhminh07](https://forums.percona.com/u/binhminh07)\
**Post date:** [January 20, 2014, 8:36pm UTC](https://forums.percona.com/t/backing-up-and-restoring-a-single-table/3216/3 "2014-01-20T20:36:22Z")

</div>

Thanks for your reply!! ^^

1\> Could you estimate How long time neccessary to backup with 1tb ?  
And I want excute with hot backup, my service can’t maintenance or something like that  
I see that althought backup one table but the ibdata and ib\_logfile data also copy to backup folder.

2\>  
Mysql source version  
5.5.8-log  
Mysql destination vesion  
5.5.34-32.0-log

I copied data from Mysql source version to Mysql destination version and insert to database Test\_Database\_Backup.

Steps executed:

1. Backup one table  
innobackupex --no-lock --include=‘^test\_database\_backup[.]trn\_users’ --no-timestamp /tmp/test\_single\_back

2. Confirmed directory /tmp/test\_single\_back

3.Execute prepare step before restoring  
innobackupex --apply-log --export /tmp/test\_single\_back

4.Confirm /tm/test\_single/back/test\_database\_backup had file  
trn\_users.frm  
trn\_users.ibd  
trn\_users.exp  
trn\_users.cfg

1. Log in to Mysql server ( the same server with TEST\_DATABASE\_BACKUP)

2. Create a database to restore with name : TEST\_RESTORE

7.create table trn\_users with the same schema of Test\_Database\_Backup  
CREATE TABLE `trn_users` (  
`id` bigint(20) NOT NULL COMMENT ‘ユーザID’,  
`as_id` varchar(255) DEFAULT NULL,  
`user_name` varchar(100) NOT NULL COMMENT ‘ユーザ名’,  
`user_status` tinyint(4) NOT NULL DEFAULT ‘2’ COMMENT ‘ユーザ状態 : 1：チュートリアル完了\n2：チュートリアル未完了\n3：不正アクセス’,  
`user_category` tinyint(4) DEFAULT NULL COMMENT ‘ユーザ属性’,  
`tutorial_num` varchar(50) DEFAULT NULL COMMENT ‘チュートリアル済番号’,  
`tutorial_finish_datetime` datetime DEFAULT NULL COMMENT ‘チュートリアル完了日時’,  
`last_login_datetime` datetime DEFAULT NULL COMMENT ‘最終ログイン日時’,  
`continue_login_day` smallint(6) DEFAULT NULL COMMENT ‘連続日数’,  
`af` varchar(255) DEFAULT NULL,  
`create_datetime` datetime DEFAULT NULL COMMENT ‘作成日時’,  
PRIMARY KEY (`id`),  
KEY `condition1` (`last_login_datetime`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘ユーザテーブル’;

1. Discard tablespace of trn\_users;  
ALTER TABLE TEST\_RESTORE.trn\_users DISCARD TABLESPACE;

2. Copy trn\_users.ibd.exp FROM /tm/test\_single/back/test\_database\_backup to /var/lib/mysql/test\_restore  
and change owner to mysql user

10.Import Tablespace  
ALTER TABLE TEST\_RESTORE.trn\_users IMPORT TABLESPACE;

1. [Got error -1 from storage engine] occured

2. Confirm error log file  
140121 11:38:22 InnoDB: Error: tablespace id and flags in file ‘./test\_restore/trn\_users.ibd’ are 321 and 0, but in the InnoDB  
InnoDB: data dictionary they are 329 and 0.  
InnoDB: Have you moved InnoDB .ibd files around without using the  
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?  
InnoDB: Please refer to  
InnoDB: [url][http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html[/url]](http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html%5B/url%5D)  
InnoDB: for how to resolve the issue.  
140121 11:38:22 InnoDB: cannot find or open in the database directory the .ibd file of  
InnoDB: table `test_restore`.`trn_users`  
InnoDB: in ALTER TABLE … IMPORT TABLESPACE

Thanks so much!!!

---

<div class="post-metadata">

**Author:** ![binhminh07](https://avatars.discourse-cdn.com/v4/letter/b/8edcca/32.png) [@binhminh07](https://forums.percona.com/u/binhminh07)\
**Post date:** [January 22, 2014, 5:36am UTC](https://forums.percona.com/t/backing-up-and-restoring-a-single-table/3216/4 "2014-01-22T05:36:24Z")

</div>

I so urgent , please help me.

I edit ibd file with hex editor, but It also error occured…  
When I excute Alter table, below error :  
ERROR 2006 (HY000): MySQL server has gone away  
No connection. Trying to reconnect…

---

<div class="post-metadata">

**Author:** ![mirfan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mirfan/32/16890_2.png) [@mirfan](https://forums.percona.com/u/mirfan)\
**Post date:** [January 22, 2014, 3:31pm UTC](https://forums.percona.com/t/backing-up-and-restoring-a-single-table/3216/5 "2014-01-22T15:31:09Z")

</div>

ibdata1 is shared tablespace and it contains data dictionary information, undo information etc and it should be copied during backup regardless it’s full backup or partial backup. For [partial backup](http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/partial_backups_innobackupex.html) the destination server should be [percona server](http://www.percona.com/software/percona-server) or at minimum it works for Oracle MySQL 5.6 and i can see your destination server is 5.5.34-32.0-log is that percona server or stock oracle mysql ?  
Further on destination server (percona server) [innodb\_import\_table\_from\_xtrabackup](http://www.percona.com/doc/percona-server/5.5/management/innodb_expand_import.html#innodb_import_table_from_xtrabackup) should be enabled prior to table import. Please check here for details [url][Percona XtraBackup](http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/partial_backups_innobackupex.html%5B/url%5D) and [url][Percona XtraBackup](http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_individual_tables_ibk.html%5B/url%5D)

Also, you may want to check this thread about it [url][http://www.percona.com/forums/questions-discussions/percona-xtrabackup/8315-restore-one-table-from-xtrabackup-s-full-backup[/url]](http://www.percona.com/forums/questions-discussions/percona-xtrabackup/8315-restore-one-table-from-xtrabackup-s-full-backup%5B/url%5D)

---

<div class="post-metadata">

**Author:** ![binhminh07](https://avatars.discourse-cdn.com/v4/letter/b/8edcca/32.png) [@binhminh07](https://forums.percona.com/u/binhminh07)\
**Post date:** [January 22, 2014, 11:35pm UTC](https://forums.percona.com/t/backing-up-and-restoring-a-single-table/3216/6 "2014-01-22T23:35:47Z")

</div>

This is my destination server information  
Server version: 5.5.34-32.0-log Percona Server (GPL),

[COLOR=#252C2F]Thank you very much Mirfan. After add [innodb\_import\_table\_from\_xtrabackup](http://www.percona.com/doc/percona-server/5.5/management/innodb_expand_import.html#innodb_import_table_from_xtrabackup)[COLOR=#252C2F] = 1 to my.cnf , restore worked!!!

Thanks for your support!!!

---

<div class="post-metadata">

**Author:** ![mirfan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mirfan/32/16890_2.png) [@mirfan](https://forums.percona.com/u/mirfan)\
**Post date:** [January 23, 2014, 1:52am UTC](https://forums.percona.com/t/backing-up-and-restoring-a-single-table/3216/7 "2014-01-23T01:52:54Z")

</div>

Glad to hear that 🙂