Перенос табличек 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 <имя таблицы>