Приветствую.
Возникла необходимость собрать все сервера баз данных в один.
Путем недолгого гугления решил использовать для этого xtrabackup, т к заливать дампом базы будет уж очень долго.
Пытаюсь построить логику работы по мануалу:
www.percona.com/doc/percona-xtrabackup/2.2/xtrabac...
Но в момент ALTER TABLE имя таблицы IMPORT TABLESPACE; получаю crash percona сервера.
Алгоритм работы:
На исходной базе данных делаю дамп базы, без данных(--no-data) гашу mysql и генерирую exp файлы, необходимые для импорта:
xtrabackup --prepare --export --target-dir=/data/backups/
На новом сервере создаю базу с индентичной структурой и выполняю над каждой таблицой ALTER TABLE test.export_test DISCARD TABLESPACE; .
Таблицы используют foreign_key, поэтому перед DISCARD ставлю SET GLOBAL foreign_key_checks=0.
После чего в директорию с базой копирую все ibd и exp файлы со старого сервера и выполняю
ALTER TABLE имя таблицы IMPORT TABLESPACE; для каждой тамблицы.
На одной из таблиц сервер крашится.
Лог выполнения скрипта:
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)
error.log:
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
Видно что импорт фейлится на определенной таблице, причем ошибок в бузе
> 140917 11:59:16 InnoDB: Error: page 0 log sequence number 59642189477
тоже быть не должно.
Как быть? Причина в структуре таблицы? Хочеся получить совета гуру по этому поводу.
Пытался искать обходные варианты в виде импорта тамлиц сразу в mysql 5,6 где эта фича заявлена:
dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html и опять столкнулся с неудачей. Часть индесов для таблиц была битой, некоторые тамблицы вообще не смогли заимпортится. Причем на рабочей базе проблем никаих нет и mysqcheck она проходит без проблем.
Средний
Простой
