Перенос табличек InnoDB Mysql

Перенос табличек InnoDB Mysql

Во времена господствования MYISAM перенос табличек из базы в базу, даже если база была на другом носителе, труда не представлял. Для этого было достаточно выполнить flush table <table name>, скопировать три файла с расширением «MYD, MYI, frm» в папку нужной базы и дать команду drop table <table name> на старой базе. И все, никаких проблем.

Но так как в MYSQL движок MYISAM используется все реже, и его вытесняет более «прогрессивный» InnoDB, то перенос табличек стал небольшой проблемой. Но и прогресс не стоит на месте, начиная с MYSQL 5.6.6, имеется возможность практически «безболезненно» переносить таблички, используя движок InnoDB.

Моя ситуация: имеет сервер баз данных,  сама база данных находится на одном, строго определенном разделе. Имеет другой раздел, смонтированный по ocfs2 через iscsi, на котором будут храниться старые таблички базы данных (а-ля архивные).

Задача: переносить таблички в данный раздел.

Хоть и инстанс базы данных один, использовать rename не получается:

(root@localhost) [(none)]>RENAME TABLE workdb.DataRecord TO arch.DataRecord;
ERROR 1025 (HY000): Error on rename of './workdb/DataRecord' to './arch/DataRecord' (errno: -1 - Unknown error 18446744073709551615)

Связанно это с:

«As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:»

Источник: http://dev.mysql.com/doc/refman/5.6/en/rename-table.html

Поэтому пришлось искать другое решение. И оно было найдено в стандартных средствах MYSQL.

Источник: http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html

По примеру этого мануала,  в большей степени предназначенного для переноса таблиц из одного инстанса базы в другую, я перенесу пару табличек из одной базы в другую в одном инстансе, но на разных носителях.

Сказано — сделано. Приступим.

База расположена в /var/lib/mysql/workdb. Нужно перенести табличку DataRecord в базу arch, расположенной в /mnt/archdb/ и имеется симлинк в /var/lib/mysql/arch.

Вот и наши базы:

(root@localhost) [workdb]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| arch               |
| mysql              |
| performance_schema |
| workdb             |
+--------------------+
5 rows in set (0,01 sec)

Заходим в базу-источник:

(root@localhost) [workdb]>use workdb
Database changed

Проверяем «движок»:

(root@localhost) [workdb]>SHOW TABLE STATUS WHERE Name = 'DataRecord';
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name            | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| DataRecord | InnoDB |      10 | Compact    |   25 |            655 |       16384 |               0 |        65536 |         0 |             26 | 2014-06-10 11:32:54 | NULL        | NULL       | cp1251_general_ci |     NULL |                |         |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

Смотрим, что есть в базе-приемнике:

(root@localhost) [workdb]>show tables from arch;
Empty set (0,00 sec)

Создаем в базе-приемнике аналогичную табличку:

(root@localhost) [workdb]>create table arch.DataRecord like DataRecord;
Query OK, 0 rows affected (0,04 sec)
(root@localhost) [workdb]>show tables from arch;
+-----------------+
| Tables_in_arch  |
+-----------------+
| DataRecord      |
+-----------------+
1 rows in set (0,00 sec)

Подготавливаем ее к импорту:

(root@localhost) [workdb]>alter table arch.DataRecord discard tablespace;
Query OK, 0 rows affected (0,00 sec)

Экспортируем таблицу в базе-источнике:

(root@localhost) [workdb]>flush tables DataRecord for export;
Query OK, 0 rows affected (0,00 sec)

Копируем два файла — с расширением .ibd и .cfg:

cp -av /var/lib/mysql/workdb/DataRecord.ibd /var/lib/mysql/arch/
cp -av /var/lib/mysql/workdb/DataRecord.cfg /var/lib/mysql/arch/

Снимаем блокировку:

(root@localhost) [workdb]>UNLOCK TABLES;
Query OK, 0 rows affected (0,00 sec)

И импортируем:

(root@localhost) [arch]>alter table DataRecord import tablespace;
Query OK, 0 rows affected (0,05 sec)

Все, готово. Табличка перенесена. Теперь можно удалить исходную, если нужно.

Для интереса — посмотрим лог:

InnoDB: Sync to disk of '"workdb"."DataRecord"' started.
InnoDB: Stopping purge
InnoDB: Writing table metadata to './workdb/DataRecord.cfg'
InnoDB: Table '"workdb"."DataRecord"' flushed to disk
InnoDB: Deleting the meta-data file './workdb/DataRecord.cfg'
InnoDB: Resuming purge
InnoDB: Importing tablespace for table 'workdb/DataRecord' that was exported from host 'centos1'
InnoDB: Phase I - Update all pages
InnoDB: Sync to disk
InnoDB: Sync to disk - done!
InnoDB: Phase III - Flush changes to disk
InnoDB: Phase IV - Flush complete
InnoDB: "arch"."DataRecord" autoinc value set to 26

Забавно, что после фазы1, идет сразу фаза 3.

Напоминаю, данная фича работает только с mysql 5.6.6.

PS. Был найден «косяк» — сбиваются индексы в табличке. Лечить так — ANALYZE TABLE <имя таблицы>