Импорт секций InnoDB в MySQL 5.6 и MariaDB 10.0/10.1

В MySQL 5.6 и MariaDB 10.0 добавлена очень удобная функциональность - переносимые табличные пространства (transportable tablespaces) для InnoDB таблиц. Теперь в режиме innodb_file_per_table (когда для каждой таблицы создается отдельное табличное пространство) можно копировать файлы табличного пространства с одного сервера на другой.

К сожалению в MySQL 5.6 и MariaDB 10.0 эта возможность не работает для секционированных таблиц. Поддержка секционированных таблиц была добавлена в MySQL 5.7. Вероятно она будет доступна и в MariaDB 10.2, так как в эту версию планируют включить улучшения InnoDB, реализованные в MySQL 5.7. Однако, улучшения в новых версиях не помогут нам, если мы используем старые версии MySQL или MariaDB.

Уточнение: в MDEV-10568 изменена версия на 10.3, т.е. пользователям MariaDB придется ждать версии MariaDB 10.3 чтобы использовать импорт/экспорт секций реализованный в MySQL 5.7.

Хорошая новость в том, что существует обходной путь, позволяющий в MySQL 5.6 и MariaDB 10.0/10.1 копировать секционированные таблицы с одного сервера на другой путем переноса табличных пространств. В этой статье я детально опишу как это сделать. Процесс может быть утомительным, поэтому я рекомендую написать скрипт для его автоматизации.

Тестовые данные

Для демонстрации того, как всё это работает, я буду использовать тестовую таблицу следующего вида:

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employees VALUES
(1, 'Geoff', 'Montee', 1),
(2, 'Chris', 'Calendar', 6),
(3, 'Kyle', 'Joiner', 11),
(4, 'Will', 'Fong', 16);

Экспорт табличных файлов с исходного сервера

Процесс экспорта табличных пространств секционированных таблиц с исходного сервера точно такой же как и для не секционированных таблиц.

Первым шагом выполняем следующую FLUSH команду:

MariaDB [db1]> FLUSH TABLES employees FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

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

Далее, вы должны увидеть несколько .ibd и .cfg файлов для таблицы в каталоге базы данных:

$ sudo ls -l /var/lib/mysql/db1/
total 428
-rw-rw---- 1 mysql mysql 827 Dec 5 16:08 employees.frm
-rw-rw---- 1 mysql mysql 48 Dec 5 16:08 employees.par
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 employeesp0.cfg
-rw-r----- 1 mysql mysql 98304 Dec 5 16:43 employeesp0.ibd
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 employeesp1.cfg
-rw-rw---- 1 mysql mysql 98304 Dec 5 16:08 employeesp1.ibd
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 employeesp2.cfg
-rw-rw---- 1 mysql mysql 98304 Dec 5 16:08 employeesp2.ibd
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 employeesp3.cfg
-rw-rw---- 1 mysql mysql 98304 Dec 5 16:08 employeesp3.ibd

$ mkdir /tmp/backup
$ cp /var/lib/mysql/db1/employees*ibd /tmp/backup/
$ cp /var/lib/mysql/db1/employees*cfg /tmp/backup/
$ ls -l /tmp/backup/
total 400
-rw-r----- 1 root root 579 Dec 5 18:52 employeesp0.cfg
-rw-r----- 1 root root 98304 Dec 5 18:52 employeesp0.ibd
-rw-r----- 1 root root 579 Dec 5 18:52 employeesp1.cfg
-rw-r----- 1 root root 98304 Dec 5 18:52 employeesp1.ibd
-rw-r----- 1 root root 579 Dec 5 18:52 employeesp2.cfg
-rw-r----- 1 root root 98304 Dec 5 18:52 employeesp2.ibd
-rw-r----- 1 root root 579 Dec 5 18:52 employeesp3.cfg
-rw-r----- 1 root root 98304 Dec 5 18:52 employeesp3.ibd

Теперь, когда файлы скопированы, мы можем разблокировать таблицу в сессии, которая у нас всё ещё открыта:

MariaDB [db1]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Импорт табличных файлов на новый сервер

Первым делом нужно разместить полученные .ibd и .cfg файлы в месте где они будут доступны на новом сервере.

Затем создать пустую копию секционированной таблицы:

MariaDB [newdb]> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> store_id INT NOT NULL
-> )
-> PARTITION BY RANGE (store_id) (
-> PARTITION p0 VALUES LESS THAN (6),
-> PARTITION p1 VALUES LESS THAN (11),
-> PARTITION p2 VALUES LESS THAN (16),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.06 sec)

Теперь нам потребуется пустая не секционированная таблица, имеющая точно такую же структуру как и наша секционированная таблица, для использования в качестве промежуточного заполнителя. Мы можем создать её с помощью следующего запроса:

MariaDB [newdb]> CREATE TABLE placeholder AS SELECT * FROM employees WHERE NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Приведенный выше запрос создаст пустую не секционированную таблицу с нужной нам структурой:

MariaDB [newdb]> SHOW CREATE TABLE placeholder;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| placeholder | CREATE TABLE `placeholder` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [newdb]> SELECT * FROM placeholder;
Empty set (0.00 sec)

Дальнейший процесс может быть утомительным, если ваша таблица имеет много секций. Для каждой секции необходимо сделать следующее:

Удалим табличное пространство для таблицы placeholder:

MariaDB [newdb]> ALTER TABLE placeholder DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

Скопируем .ibd и .cfg файлы нулевой секции в каталог базы данных, но при том переименуем их так, чтобы они соответствовали таблице placeholder:

Импортируем табличное пространство для таблицы placeholder:

MariaDB [newdb]> ALTER TABLE placeholder IMPORT TABLESPACE;
Query OK, 0 rows affected (0.04 sec)

Теперь таблица placeholder содержит данные из секции p0 исходной секционированной таблицы:

Заменим секцию p0 нашей секционированной таблицы на табличное пространство таблицы placeholder:

MariaDB [newdb]> ALTER TABLE employees EXCHANGE PARTITION p0 WITH TABLE placeholder;
Query OK, 0 rows affected (0.02 sec)

Теперь наша секционированная таблица на новом сервере содержит данные из секции p0:

Если мы повторим описанный выше процесс для секций p1, p2, и p3, то наша секционированная таблица на новом сервере будет содержать все данные из исходной таблицы на старом сервере:

MariaDB [newdb]> SELECT * FROM employees;
+----+-------+----------+----------+
| id | fname | lname | store_id |
+----+-------+----------+----------+
| 1 | Geoff | Montee | 1 |
| 2 | Chris | Calendar | 6 |
| 3 | Kyle | Joiner | 11 |
| 4 | Will | Fong | 16 |
+----+-------+----------+----------+
4 rows in set (0.00 sec)

Кто-нибудь успешно выполнял такую процедуру в прошлом?

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.