# Restoring Individual Tables from xtrabackup

**URL:** <https://forums.percona.com/t/restoring-individual-tables-from-xtrabackup/3749>\
**Category:** Other MySQL® Questions\
**Created:** [September 17, 2014, 2:56am UTC](https://forums.percona.com/t/restoring-individual-tables-from-xtrabackup/3749 "2014-09-17T02:56:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![zorruch](https://avatars.discourse-cdn.com/v4/letter/z/76d3ee/32.png) [@zorruch](https://forums.percona.com/u/zorruch)\
**Post date:** [September 17, 2014, 2:56am UTC](https://forums.percona.com/t/restoring-individual-tables-from-xtrabackup/3749/1 "2014-09-17T02:56:24Z")

</div>

hi.  
I’m trying to move the database to another server without using musqldump.  
Found a way to make it through xtrabackup:  
[URL=“[Restoring Individual Tables](http://www.percona.com/doc/percona-xtrabackup/2.2/xtrabackup_bin/restoring_individual_tables.html)”][http://www.percona.com/doc/percona-x...al\_tables.html[/URL]](http://www.percona.com/doc/percona-x...al_tables.html%5B/URL%5D)

As a server, use pecona: percona server version 5.5.39

When you try to insert tables (ALTER TABLE test.export\_test IMPORT TABLESPACE) to the new server I get a server crash.

The algorithm executed by me:  
old server:

1. mysqldump --no-autocommit --triggers --routines --add-drop-database --result-file=/tmp/1.sql --no-data test
2. mysqladmin shutdown

new server:  
3) mysql -e “create database test”  
4) mysql -D test \< /tmp/1.sql (copy from old server)  
5) run to bash script:  
a=`mysql -D test -e "show tables" `  
mysql -D test -e “SET GLOBAL foreign\_key\_checks=0;”  
for i in $a  
do  
k=“ALTER TABLE $i DISCARD TABLESPACE;”  
mysql -D test -e “$k”  
done

old server:  
5) xtrabackup --prepare --export --target-dir=/var/lib/mysql/  
6) copy .ibd and .exp files to datadir mysql new sever

new server:  
run to bash script:

chown -R mysql:mysql /var/lib/mysql/  
mysql -D test -e “SET GLOBAL foreign\_key\_checks=0;”  
mysql -D test -e “SET GLOBAL innodb\_import\_table\_from\_xtrabackup=1;”

for i in $a  
do  
k=“ALTER TABLE $i import tablespace;”  
echo $k  
mysql -D test -e “$k”  
done  
mysql -D test -e “SET GLOBAL innodb\_import\_table\_from\_xtrabackup=0;”  
mysql -D test -e “SET GLOBAL foreign\_key\_checks=1;”

log running bash script:  
…  
ALTER TABLE abuse\_flow import tablespace;  
ALTER TABLE abuse\_template import tablespace;  
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query  
ALTER TABLE config import tablespace;  
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)  
ALTER TABLE domain import tablespace;  
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)  
ALTER TABLE domain\_bak import tablespace;  
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)  
ALTER TABLE info\_channel import tablespace;  
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)  
ALTER TABLE isp import tablespace;  
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)  
…

cat /var/log/mysql/error.log:  
140917 11:47:18 [Note] /usr/sbin/mysqld: Normal shutdown

140917 11:47:18 [Note] Event Scheduler: Purging the queue. 0 events  
140917 11:47:18 InnoDB: Starting shutdown…  
140917 11:47:22 InnoDB: Shutdown completed; log sequence number 1597971  
140917 11:47:22 [Note] /usr/sbin/mysqld: Shutdown complete

140917 11:47:22 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.  
140917 11:47:22 [Note] Plugin ‘FEDERATED’ is disabled.  
140917 11:47:22 InnoDB: The InnoDB memory heap is disabled  
140917 11:47:22 InnoDB: Mutexes and rw\_locks use GCC atomic builtins  
140917 11:47:22 InnoDB: Compressed tables use zlib 1.2.8  
140917 11:47:22 InnoDB: Using Linux native AIO  
140917 11:47:22 InnoDB: Initializing buffer pool, size = 32.0G  
140917 11:47:23 InnoDB: Completed initialization of buffer pool  
140917 11:47:23 InnoDB: highest supported file format is Barracuda.  
140917 11:47:24 InnoDB: Waiting for the background threads to start  
140917 11:47:25 Percona XtraDB ([http://www.percona.com](http://www.percona.com)) 5.5.39-36.0 started; log sequence number 1597971  
140917 11:47:25 [Note] Event Scheduler: Loaded 0 events  
140917 11:47:25 [Note] /usr/sbin/mysqld: ready for connections.  
Version: ‘5.5.39-36.0-log’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Percona Server (GPL), Release 36.0, Revision 697  
140917 11:59:16 InnoDB: Error: page 0 log sequence number 59642189477  
InnoDB: is in the future! Current system log sequence number 1873693.  
InnoDB: Your database may be corrupt or you may have copied the InnoDB  
InnoDB: tablespace but not the InnoDB log files. See  
InnoDB: [http://dev.mysql.com/doc/refman/5.5/](http://dev.mysql.com/doc/refman/5.5/)…-recovery.html  
InnoDB: for more information.  
InnoDB: Import: The extended import of test/abuse\_flow is being started.  
InnoDB: Import: 3 indexes have been detected.  
InnoDB: Progress in %: 12 25 37 50 62 75 87 100 done.  
140917 11:59:16 InnoDB: Error: page 0 log sequence number 59642212239  
InnoDB: is in the future! Current system log sequence number 1873693.  
InnoDB: Your database may be corrupt or you may have copied the InnoDB  
InnoDB: tablespace but not the InnoDB log files. See  
InnoDB: [http://dev.mysql.com/doc/refman/5.5/](http://dev.mysql.com/doc/refman/5.5/)…-recovery.html  
InnoDB: for more information.  
InnoDB: Import: The extended import of test/abuse\_template is being started.  
InnoDB: Import: 2 indexes have been detected.  
07:59:16 UTC - mysqld got signal 11 ;  
This could be because you hit a bug. It is also possible that this binary  
or one of the libraries it was linked against is corrupt, improperly built,  
or misconfigured. This error can also be caused by malfunctioning hardware.  
We will try our best to scrape up some info that will hopefully help  
diagnose the problem, but since we have already crashed,  
something is definitely wrong and this may fail.  
Please help us make Percona Server better by reporting any  
bugs at [URL][System Dashboard - Percona JIRA](http://bugs.percona.com/%5B/URL%5D)

key\_buffer\_size=1073741824  
read\_buffer\_size=16777216  
max\_used\_connections=1  
max\_threads=202  
thread\_count=1  
connection\_count=1  
It is possible that mysqld could use up to  
key\_buffer\_size + (read\_buffer\_size + sort\_buffer\_size)\*max\_threads = 57313720 K bytes of memory  
Hope that’s ok; if not, decrease some variables in the equation.

Thread pointer: 0x33be6c30  
Attempting backtrace. You can use the following information to find out  
where mysqld died. If you see no messages after this, something went  
terribly wrong…  
stack\_bottom = 7fc50023ce98 thread\_stack 0x30000  
/usr/sbin/mysqld(my\_print\_stacktrace+0x20)[0x7832d0]  
/usr/sbin/mysqld(handle\_fatal\_signal+0x36f)[0x67484f]  
/lib/x86\_64-linux-gnu/libpthread.so.0(+0x10340)[0x7fc504a96340]  
/usr/sbin/mysqld[0x851819]  
/usr/sbin/mysqld[0x7b9040]  
/usr/sbin/mysqld[0x7a142a]  
/usr/sbin/mysqld(\_Z17mysql\_alter\_tableP3THDPcS1\_P24st\_ha\_cre ate\_informationP10TABLE\_LISTP10Alter\_infojP8st\_ord erb+0x429)[0x5ed499]  
/usr/sbin/mysqld(\_ZN21Alter\_table\_statement7executeEP3THD+0x 489)[0x7679b9]  
/usr/sbin/mysqld(\_Z21mysql\_execute\_commandP3THD+0x3cc8)[0x58fdb8]  
/usr/sbin/mysqld(\_Z11mysql\_parseP3THDPcjP12Parser\_state+0x2a b)[0x592eeb]  
/usr/sbin/mysqld(\_Z16dispatch\_command19enum\_server\_commandP3 THDPcj+0x1de5)[0x595465]  
/usr/sbin/mysqld(\_Z24do\_handle\_one\_connectionP3THD+0x186)[0x623986]  
/usr/sbin/mysqld(handle\_one\_connection+0x42)[0x623a12]  
/lib/x86\_64-linux-gnu/libpthread.so.0(+0x8182)[0x7fc504a8e182]  
/lib/x86\_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc503531fbd]

Trying to get some variables.  
Some pointers may be invalid and cause the dump to abort.  
Query (7fbc40078240): is an invalid pointer  
Connection ID (thread ID): 64  
Status: NOT\_KILLED

What could be the problem?

---

<div class="post-metadata">

**Author:** ![niljoshi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/niljoshi/32/16889_2.png) [@niljoshi](https://forums.percona.com/u/niljoshi)\
**Post date:** [September 17, 2014, 11:15pm UTC](https://forums.percona.com/t/restoring-individual-tables-from-xtrabackup/3749/2 "2014-09-17T23:15:38Z")

</div>

Hi,

If you check document. [URL=“[Restoring Individual Tables](http://www.percona.com/doc/percona-xtrabackup/2.2/xtrabackup_bin/restoring_individual_tables.html)”][http://www.percona.com/doc/percona-x...al\_tables.html[/URL]](http://www.percona.com/doc/percona-x...al_tables.html%5B/URL%5D)

“In server versions prior to 5.6, it is not possible to copy tables between servers by copying the files, even with [innodb\_file\_per\_table](http://www.percona.com/doc/percona-xtrabackup/2.2/glossary.html#term-innodb-file-per-table). However, with Percona XtraBackup, you can export individual tables from any [InnoDB](http://www.percona.com/doc/percona-xtrabackup/2.2/glossary.html#term-innodb) database, and import them into Percona Server with [XtraDB](http://www.percona.com/doc/percona-xtrabackup/2.2/glossary.html#term-xtradb) or MySQL 5.6. (The source doesn’t have to be [XtraDB](http://www.percona.com/doc/percona-xtrabackup/2.2/glossary.html#term-xtradb) or or MySQL 5.6, but the destination does.)”

You are doing the same process with 5.5. So, I would suggest at least your destination server should be 5.6

---

<div class="post-metadata">

**Author:** ![zorruch](https://avatars.discourse-cdn.com/v4/letter/z/76d3ee/32.png) [@zorruch](https://forums.percona.com/u/zorruch)\
**Post date:** [September 18, 2014, 1:15am UTC](https://forums.percona.com/t/restoring-individual-tables-from-xtrabackup/3749/3 "2014-09-18T01:15:42Z")

</div>

Thank you.  
Using version 5.6 and works!