Файл ibdata1 в MySQL

Как же раздражает, когда на сервере место заканчивается. Прямо слов нет.

Причем происходит это постоянно. И заканчивается, главное, "под ноль", до последнего байта, в результате чего все "падает".

Заходишь, чистишь набежавшие логи, системные бэкапы, разросшиеся таблицы в базе данных (в том же Друпал 7 была бага - таблицы с кэшем сами не очищались и за пару месяцев легко до гигабайта разрастались, хотя Друпал 8 в этом плане тоже не сильно лучше). "Окэй, пару гигабайт выгадал, живем." Но через месяц-другой по новому кругу...

Потом хостер накинул сверху еще гигабайт 5 пространства в качестве апгрейта аккаунта - жил спокойно с полгодика. А сейчас вот снова "Free space: 220MB". Как так?

Нет, ну в самом деле, как так? Ось Дебиан, из софта все по-минимуму, сайтов всего 10, из которых большинство заглушки, какое-то реальное место занимают только два-три. И при этом уходит суммарно 30 гигабайт. Так же быть не должно?

Пошел инвестигейтить, хотя надо было этим заняться еще очень давно.

tulvit@tnode:~$ du -hs ~/public/
9.1G	/home/tulvit/public/

Так, сайты весят сколько и должны, чуть меньше 10 гигов. Остается еще 20 гигабайт, не может же Дебиан столько "съесть".

Логи "текут"?

tulvit@tnode:~$ sudo du -hs /var/log
[sudo] password for tulvit:
31M	/var/log

Нет, не логи.

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

tulvit@tnode:~$ sudo du -hs /var/lib/mysql/ibdata1
15G	/var/lib/mysql/ibdata1

15 гигабайт! И что это вообще такое?

Беглое гугление показало, что ibdata1 - системный файл MySQL, в котором хранится вся информация по базам InnoDB. Проблема в том, что этот файлик умеет только в рост. Даже если дропнуть все таблицы подчистую - ibdata1 не уменьшится ни на байт. Создать новую таблицу - ibdata1 вырастет. Удалить ее через минуту - ibdata1 не изменится. Или даже снова увеличится.

Вот он и продолжал расти, день ото дня, месяц к месяцу. По чуть-чуть, но все же. За четыре года с момента изначального разворачивания окружения у меня и "накапало" 15 гигов. Ведь баз данных за это время было создано и удалено десятки, если не сотни.

Как сие пофиксить? Увы, только фактически "реланчем".

И раз: бэкапим все наши InnoDB базы, за исключением системных information_schema, mysql и performance_schema.

И два: дропаем все наши базы (акромя системых information_schema, mysql и performance_schema соответственно).

И три: выключаем MySQL.

И четыре: в конфигурационный файл my.cnf добавляем эту строчку:

innodb_file_per_table

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

И пять: удаляем этот самый файлик ibdata1, а также файлы логов ib_logfile0 и ib_logfile1 (лежат там же).

И шесть: включаем MySQL.

И семь: импортируем наши дампы обратно.

Проверяем освободившееся место. Было 220 мегабайт. Стало 14 гигабайт. Счастье-то какое!

UPDATE

См. комментарии (кратко: MyISAM не имеет никакого отношения к ibdata1, если база состоит только из таких таблиц, то ее можно не трогать, также в зависимости от версии MySQL системная база mysql может содержать таблицы типа InnoDB - в таком случае их имеет смысл тоже бэкапить и восстанавливать).

sudo ls /var/lib/mysql/