Not the answer you need?
Register and ask your own question!

Restoring Individual Tables from xtrabackup

zorruchzorruch EntrantInactive User Role Beginner
hi.
I'm trying to move the database to another server without using musqldump.
Found a way to make it through xtrabackup:
http://www.percona.com/doc/percona-x...al_tables.html

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) 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/...-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/...-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 http://bugs.percona.com/

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?

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    If you check document. http://www.percona.com/doc/percona-x...al_tables.html

    "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. However, with Percona XtraBackup, you can export individual tables from any InnoDB database, and import them into Percona Server with XtraDB or MySQL 5.6. (The source doesn’t have to be 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
  • zorruchzorruch Entrant Inactive User Role Beginner
    Thank you.
    Using version 5.6 and works!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.