Данные, относящиеся к таблицам формата InnoDB, могут храниться в одном файле ibdata1 или в раздельных файлах формата .ibd. Исторически хранение в одном файле является основным методом, хотя в современных версиях MySQL это изменено. Поменять тип хранения данных позволяет параметр innodb_file_per_table.
Зачем использовать опцию innodb_file_per_table
ibdata1 имеет свойство, котором может усложнить работу. Данные, загруженные в таблицы MySQL, остаются в файле и не удаляются даже после того как удалены сами таблицы. Размер файла при этом может значительно увеличиваться. Файл растет постоянно и уменьшить его не позволит даже удалении ненужных больше объемных таблиц.
Чтобы этого избежать в конфигурацию сервера баз данных добавляют директиву innodb_file_per_table, благодаря ей данные вновь создаваемых таблиц сохраняются в отдельные файлы.
Значения параметра, используемые по умолчанию для разных версий пакетов:
- MySQL версии до 5.6.6 и MariaDB 5.5 — innodb_file_per_table = 0 (данные хранятся в одном файле ibdata1)
- MySQL 5.6.6 и старше + MariaDB 10.x — innodb_file_per_table = 1 (данные хранятся в разных файлах с расширением .ibd)
О том как проверить используемую версию.
Основное неудобство ibdata1 в том, что файл разрастается занимая место на диске сервера.
Если такая ситуация имеет место всегда можно изменить принцип хранения данных. Помимо этого изменением можно добиться того, что быстро и эффективно будут выполняться команды вроде TRUNCATE TABLE и ALTER TABLE. Самое главное с таблицами появится возможность работать индивидуально, без необходимости делать это со всем, что загружено в MySQL.
Как добавить параметр
Для текущей сессии (до перезапуска MySQL) можно поменять значение переменной так:
SET GLOBAL innodb_file_per_table = 1;
Все новые таблицы при этом будут использовать .idb.
Для того чтобы изменения внести на постоянной основе корректируется файл /etc/my.cnf или другой конфигурационный файл подключаемый в нем.
Директиву нужно добавлять в секцию [mysqld]
mcedit /etc/my.cnf
innodb_file_per_table = 1;
При этом для существующих таблиц большая часть информации продолжает храниться в ibdata1
Здесь есть два варианта выхода из ситуации. Оба эффективны и используются на практике (первый чаще):
- создать SQL дамп всех баз данных используя утилиту mysqldump; установить опцию innodb_file_per_table в конфигурационном файле, перезапустить MySQL; загрузить дамп.
- использовать команду ALTER TABLE ИМЯ ТАБЛИЦЫ ENGINE=InnoDB'; установить опцию innodb file per table в конфигурационном файле, перезапустить MySQL
Пример конфигурационного файла с добавленной опцией innodb_file_per_table:
Если происходит перевод таблиц из MyISAM следует убедиться в отсутствии в my.cnf опции skip-innodb.
Перед ней нужно поставить знак комментария или удалить её.
#skip-innodb
Процесс перезапуска сервиса после внесения изменений:
Также читайте о том, как преобразовать MyISAM таблицы в InnoDB скриптом. Этим же скриптом с небольшой его корректировкой можно выполнять перевод из ibdata1в .ibd созданных таблиц.