На многих проектах с таблицами InnoDB встречается проблема с огромными размерами файлов ibdata1 и ib_log. Причина в большинстве случаев связана с неправильными настройками сервера MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого не высвобождается само по себе. Корректнее всего хранить данные таблиц в отдельных файлах ibd*.
Изменить существующую настройку невозможно, поэтому необходимо выполнить несколько шагов:
- делаем резервную копию существующей БД;
- меняем настройки сервера MySQL/MariaDB;
- удаляем старые файлы;
- восстанавливаем БД из резервной копии.
Рассмотрим на примере, как мы можем это сделать.
Имеем следующую структуру: сервер с БД MariaDB, название базы asteriskcdrdb. По умолчанию вся информация хранится в файле ibdata1, при этом в файле с названием БД пусто.
Рисунок 1 – Список баз и файлов в каталоге /var/lib/mysql
Для создания резервной копии существующей БД можно воспользоваться средствами MySQL. Дамп баз можно снять следующей командой:
# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
Если у вас имеется несколько рабочих БД, нужно сделать дамп каждой БД отдельно. Также следует учитывать, что необходимо свободное место под создание дампов. В нашем случае дамп одной базы 24,5Гб занимал порядка 6Гб.
После создания резервной копии, меняем настройки в конфигурационном файле my.cnf. Для этого сначала останавливаем сервер MySQL/MariaDB и вносим изменения в файл. В нашем случае он располагается по пути: /etc/my.cnf. Открываем файл и добавляем строку innodb_file_per_table=1
Теперь необходимо удалить старые файлы ibdata1 и все файлы с названием ib_log. После этого можно запустить сервер MySQL/MariaDB.
Для восстановления БД из резервной копии, воспользуемся следующей командой:
# mysql -u [username] –p[password] [database_name] < [dump_file.sql]
После успешного восстановления БД, можем проверить конечный результат:
Рисунок 2 - Список баз и файлов в каталоге /var/lib/mysql после операции
Рисунок 3 - Список баз и файлов в каталоге /var/lib/mysql после операции в каталоге с базой
