innodb_file_per_table

Данные, относящиеся к таблицам формата InnoDB, могут храниться в одном файле ibdata1 или в раздельных файлах формата .ibd. Исторически хранение в одном файле является основным методом, хотя в современных версиях MySQL это изменено.

ibdata1 имеет свойство, котором может усложнить работу. Данные, загруженные в таблицы MySQL, остаются в файле и не удаляются даже после того как удалены сами таблицы. Размер файла при этом может значительно увеличиваться.

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

 

 

Параметр innodb file per table MySQL

Значения параметра, используемые по умолчанию для разных версий пакетов:

  • 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

mcedit /etc/my.cnf

innodb_file_per_table = 1;

 

При этом для существующих таблиц бОльшая часть информации продолжает храниться в ibdata1

 

Здесь есть два варианта выхода из ситуации — оба эффективны и используются на практике (первый несколько чаще):

  1. сделать дамп всех баз данных используя утилиту mysqldump; установить опцию innodb file per table в конфигурационном файле, перезапустить MySQL; загрузить дамп.
  2. использовать команду ALTER TABLE ИМЯ ТАБЛИЦЫ ENGINE=InnoDB'; установить опцию innodb file per table в конфигурационном файле, перезапустить MySQL

 

 

Если происходит перевод таблиц из MyISAM следует убедиться в отсутствии в my.cnf опции skip-innodb.

Перед ней ставят знак комментария или удаляют.

#skip-innodb

 

Также читайте о том, как преобразовать MyISAM таблицы в InnoDB скриптом. Этим же скриптом с небольшой его корректировкой можно выполнять перевод из ibdata1в .ibd созданных таблиц.