InnoDB Пространства имен таблиц по файлам на таблицу

При создании таблицы с помощью движка InnoDB, данные, записанные в эту таблицу, хранятся в файловой системе в файле данных, называемом пространством имен таблиц. Файлы пространства имен таблиц содержат как данные, так и индексы.

Когда innodb_file_per_table=ON, InnoDB использует один файл пространства имен таблиц на одну таблицу InnoDB. Эти файлы пространства имен таблиц имеют расширение .ibd. Когда innodb_file_per_table=OFF, InnoDB хранит все таблицы в системном пространстве имен таблиц InnoDB.

Версии InnoDB в MySQL 5.7 и выше также поддерживают дополнительный тип пространства имен таблиц, называемый общими пространствами имен таблиц, которые создаются с помощью CREATE TABLESPACE. Однако версии InnoDB в MariaDB Server не поддерживают общие пространства имен таблиц или CREATE TABLESPACE.

Расположение пространств имен таблиц по файлам на таблицу

По умолчанию пространства имен таблиц InnoDB по файлам на таблицу создаются в каталоге данных системы, который определяется системной переменной datadir. Системная переменная innodb_data_home_dir не изменит расположение пространств имен таблиц по файлам на таблицу.

В случае, если вам нужно хранить конкретное пространство имен таблиц в отдельном пути, вы можете установить расположение с помощью параметра таблицы DATA DIRECTORY при создании таблицы.

Например,

CREATE TABLE test.t1 (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(50)
) ENGINE=InnoDB
DATA DIRECTORY = "/data/contact";

MariaDB затем создает каталог базы данных по указанному пути, и пространство имен таблиц по файлам на таблицу будет создано внутри этого каталога. В операционных системах семейства Unix вы можете просмотреть файл с помощью команды ls:

# ls -al /data/contact/test
drwxrwx--- 2 mysql mysql  4096 Dec 8 18:46 .
drwxr-xr-x 3 mysql mysql  4096 Dec 8 18:46 ..
-rw-rw---- 1 mysql mysql 98304 Dec 8 20:41 t1.ibd

Обратите внимание, что системный пользователь, который запускает процесс MariaDB Server (который обычно mysql), должен иметь права записи на указанный путь.

Копирование переносимых пространств имен таблиц

Пространства имен таблиц InnoDB по файлам на таблицу переносимы, что означает, что вы можете скопировать пространство имен таблиц по файлам на таблицу с одного MariaDB Server на другой сервер. Это может быть полезно в тех случаях, когда вам необходимо перенести полные таблицы между серверами и не хотите использовать инструменты резервного копирования, такие как mariabackup или mariadb-dump. На самом деле, этот процесс можно даже использовать с mariabackup в некоторых случаях, например, при восстановлении частичных резервных копий или при восстановлении отдельных таблиц или разделов из резервной копии.

Копирование переносимых пространств имен таблиц для таблиц без разделения

Вы можете скопировать переносимое пространство имен таблиц для таблицы без разделения с одного сервера на другой, экспортировав файл пространства имен таблиц с исходного сервера, а затем импортировав файл пространства имен таблиц на новый сервер.

Экспорт переносимых пространств имен таблиц для таблиц без разделения

Вы можете экспортировать таблицу без разделения, заблокировав таблицу и скопировав файлы .ibd и .cfg таблицы из соответствующего местоположения пространства имен таблиц для таблицы в место резервной копии. Например, процесс будет таким:

• Сначала используйте оператор FLUSH TABLES ... FOR EXPORT для целевой таблицы:

FLUSH TABLES test.t1 FOR EXPORT;

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

• Затем, пока ваше подключение по-прежнему держит блокировку на таблице, скопируйте файл пространства имен таблиц и файл метаданных в безопасный каталог:

# cp /data/contacts/test/t1.ibd /data/saved-tablespaces/
# cp /data/contacts/test/t1.cfg /data/saved-tablespaces/

• Затем, после копирования файлов, вы можете освободить блокировку с помощью UNLOCK TABLES:

UNLOCK TABLES;

Импорт переносимых пространств имен таблиц для таблиц без разделения

Вы можете импортировать таблицу без разделения, удалив исходное пространство имен таблиц таблицы, скопировав файлы .ibd и .cfg таблицы из места резервной копии в соответствующее местоположение пространства имен таблиц для таблицы, а затем сообщив серверу об импорте пространства имен таблиц. Например, процесс будет таким:

• Сначала на целевом сервере вам нужно создать копию таблицы. Используйте тот же оператор CREATE TABLE, что использовался для создания таблицы на исходном сервере:

CREATE TABLE test.t1 (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(50)
) ENGINE=InnoDB;

• Затем используйте ALTER TABLE ... DISCARD TABLESPACE для удаления пространства имен таблиц новой таблицы:

ALTER TABLE test.t1 DISCARD TABLESPACE;

• Затем скопируйте файлы .ibd и .cfg с исходного сервера в соответствующий каталог на целевом MariaDB Server:

# scp /data/tablespaces/t1.ibd target-server.com:/var/lib/mysql/test/
# scp /data/tablespaces/t1.cfg target-server.com:/var/lib/mysql/test/

• Затем, после того как файлы окажутся в нужном каталоге на целевом сервере, используйте ALTER TABLE ... IMPORT TABLESPACE для импорта пространства имен таблиц новой таблицы:

ALTER TABLE test.t1 IMPORT TABLESPACE;

Копирование переносимых пространств имен таблиц для таблиц с разделением

В настоящее время MariaDB не поддерживает прямое перемещение пространств имен таблиц из таблиц с разделением. См. MDEV-10568 для получения дополнительной информации об этом. Тем не менее, перенести таблицы с разделением все еще возможно, если использовать обходной путь. Вы можете скопировать переносимые пространства имен таблиц для таблицы с разделением с одного сервера на другой, экспортировав файл пространства имен таблиц для каждого раздела с исходного сервера, а затем импортировав файл пространства имен таблиц для каждого раздела на новый сервер.

Экспорт переносимых пространств имен таблиц для таблиц с разделением

Вы можете экспортировать таблицу с разделением, заблокировав таблицу и скопировав файлы .ibd и .cfg каждого раздела из соответствующего местоположения пространства имен таблиц для раздела в место резервной копии. Например, процесс будет таким:

• Сначала давайте создадим тестовую таблицу с данными на исходном сервере:

CREATE TABLE test.t2 (
   employee_id INT,
   name VARCHAR(50)
) ENGINE=InnoDB
PARTITION BY RANGE (employee_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 test.t2 (name, employee_id) VALUES
   ('Geoff Montee', 1), 
   ('Chris Calendar', 6),
   ('Kyle Joiner', 11), 
   ('Will Fong', 16);

• Затем нам нужно экспортировать пространство имен таблиц с разделением с исходного сервера, что соответствует процессу экспорта пространств имен таблиц без разделения. Это означает, что нам нужно использовать оператор FLUSH TABLES ... FOR EXPORT для целевой таблицы:

FLUSH TABLES test.t2 FOR EXPORT;

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

• Затем, если мы используем команду grep для поиска в каталоге базы данных в каталоге данных для недавно созданной таблицы t2, мы увидим несколько файлов .ibd и .cfg для таблицы:

# ls -l /var/lib/mysql/test/ | grep t2
total 428
-rw-rw---- 1 mysql mysql 827 Dec 5 16:08 t2.frm
-rw-rw---- 1 mysql mysql 48 Dec 5 16:08 t2.par
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 t2#P#p0.cfg
-rw-r----- 1 mysql mysql 98304 Dec 5 16:43 t2#P#p0.ibd
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 t2#P#p1.cfg
-rw-rw---- 1 mysql mysql 98304 Dec 5 16:08 t2#P#p1.ibd
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 t2#P#p2.cfg
-rw-rw---- 1 mysql mysql 98304 Dec 5 16:08 t2#P#p2.ibd
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 t2#P#p3.cfg
-rw-rw---- 1 mysql mysql 98304 Dec 5 16:08 t2#P#p3.ibd

• Затем, пока наше подключение по-прежнему держит блокировку на таблице, нам нужно скопировать файлы пространства имен таблиц и файлы метаданных в безопасный каталог:

$ mkdir /tmp/backup
$ sudo cp /var/lib/mysql/test/t2*.ibd /tmp/backup
$ sudo cp /var/lib/mysql/test/t2*.cfg /tmp/backup

• Затем, после копирования файлов, мы можем освободить блокировку с помощью UNLOCK TABLES:

UNLOCK TABLES;

Импорт переносимых пространств имен таблиц для таблиц с разделением

Вы можете импортировать таблицу с разделением, создав таблицу-заглушку, удалив исходное пространство имен таблиц таблицы-заглушки, скопировав файлы раздела .ibd и .cfg из места резервной копии в соответствующее местоположение пространства имен таблиц для таблицы-заглушки, а затем сообщив серверу об импорте пространства имен таблиц. В этот момент сервер может обменять пространство имен таблиц для таблицы-заглушки на пространство имен таблиц для раздела. Например, процесс будет таким:

• Сначала нам нужно скопировать сохраненные файлы пространства имен таблиц с исходного сервера на целевой сервер:

$ scp /tmp/backup/t2* user@target-host:/tmp/backup

• Затем нам нужно импортировать пространства имен таблиц с разделением на целевой сервер. Процесс импорта таблиц с разделением сложнее, чем процесс импорта таблиц без разделения. Для начала, если он еще не существует, нам нужно создать таблицу с разделением на целевом сервере, соответствующую таблице с разделением на исходном сервере:

CREATE TABLE test.t2 (
   employee_id INT,
   name VARCHAR(50)
) ENGINE=InnoDB
PARTITION BY RANGE (employee_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
);

• Затем, используя эту таблицу в качестве модели, нам нужно создать заглушку этой таблицы с той же структурой, которая не использует разделение. Это можно сделать с помощью оператора CREATE TABLE... AS SELECT:

CREATE TABLE test.t2_placeholder LIKE test.t2;
ALTER TABLE test.t2_placeholder REMOVE PARTITIONING;

Этот оператор создаст новую таблицу под названием t2_placeholder, которая имеет ту же схему структуры, что и t2, но не использует разделение и не содержит строк.

Для каждого раздела

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

• Сначала нам нужно использовать ALTER TABLE ... DISCARD TABLESPACE для удаления пространства имен таблиц таблицы-заглушки:

ALTER TABLE test.t2_placeholder DISCARD TABLESPACE;

• Затем скопировать файлы .ibd и .cfg для следующего раздела в соответствующий каталог для таблицы t2_placeholder на целевом MariaDB Server:

# cp /tmp/backup/t2#P#p0.cfg /var/lib/mysql/test/t2_placeholder.cfg
# cp /tmp/backup/t2#P#p0.ibd /var/lib/mysql/test/t2_placeholder.ibd
# chown mysql:mysql /var/lib/mysql/test/t2_placeholder*

• Затем, после того как файлы окажутся в правильной директории на целевом сервере, нам нужно использовать ALTER TABLE ... IMPORT TABLESPACE, чтобы импортировать табличное пространство новой таблицы:

ALTER TABLE test.t2_placeholder IMPORT TABLESPACE;

Замещающая таблица теперь содержит данные из p0 раздела на сервере источника.

SELECT * FROM test.t2_placeholder;

+-------------+--------------+
| employee_id | name         |
+-------------+--------------+
|           1 | Geoff Montee |
+-------------+--------------+

• Затем, пришло время перенести раздел из замещающей таблицы в целевую таблицу. Это можно сделать с помощью оператора ALTER TABLE... EXCHANGE PARTITION:

ALTER TABLE test.t2 EXCHANGE PARTITION p0 WITH TABLE test.t2_placeholder;

Целевая таблица теперь содержит первый раздел из исходной таблицы.

SELECT * FROM test.t2;

+-------------+--------------+
| employee_id | name         |
+-------------+--------------+
|           1 | Geoff Montee |
+-------------+--------------+

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

Когда этот процесс завершится для всех разделов, целевая таблица будет содержать импортированные данные:

SELECT * FROM test.t2;

+-------------+----------------+
| employee_id | name           |
+-------------+----------------+
|           1 | Geoff Montee   |
|           6 | Chris Calendar |
|          11 | Kyle Joiner    |
|          16 | Will Fong      |
+-------------+----------------+

• Затем, мы можем удалить замещающую таблицу из базы данных:

DROP TABLE test.t2_placeholder;

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

Различные форматы хранения для временных столбцов

MariaDB 10.1.2 добавила системную переменную mysql56_temporal_format, которая включает новый формат хранения, совместимый с MySQL 5.6, для типов данных TIME, DATETIME и TIMESTAMP.

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

ALTER TABLE dt_test IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Column dt precise type mismatch.)

См. MDEV-15225 для получения дополнительной информации.

См. страницы типов данных TIME, DATETIME и TIMESTAMP, чтобы узнать, как обновить формат хранения для временных столбцов в таблицах, созданных до MariaDB 10.1.2 или созданных с mysql56_temporal_format=OFF.

Различные значения ROW_FORMAT

Табличные пространства InnoDB на основе файла могут использовать различные форматы строк. Конкретный формат строки можно указать при создании таблицы, либо установив параметр таблицы ROW_FORMAT, либо установив системную переменную innodb_default_row_format. См. Установка формата строк таблицы для получения дополнительной информации о том, как установить формат строки таблицы InnoDB.

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

ALTER TABLE t0 IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Expected FSP_SPACE_FLAGS=0x21, .ibd file contains 0x0.)

Сообщение об ошибке более описательно в MariaDB 10.2.17 и более поздних версиях:

ALTER TABLE t0 IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT)

Убедитесь, что вы проверили формат строки табличного пространства перед перемещением его с одного сервера на другой. Имейте в виду, что формат строки по умолчанию может меняться между основными версиями MySQL или MariaDB. См. Проверка формата строки таблицы для получения информации о том, как проверить формат строки таблицы InnoDB.

См. MDEV-15049 и MDEV-16851 для получения дополнительной информации.

Ограничения внешнего ключа

DISCARD в таблице с ограничениями внешнего ключа возможен только после отключения foreign_key_checks:

SET SESSION foreign_key_checks=0;
ALTER TABLE t0 DISCARD TABLESPACE;

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

Шифрование табличного пространства

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

Для получения дополнительной информации о шифровании данных см. Шифрование данных для InnoDB.

См. также

• Geoff Montee:Импорт разделов InnoDB в MySQL 5.6 и MariaDB 10.0/10.1