ИТ-поддержка

Сжатие базы данных MySQL и MariaDB

На многих проектах с таблицами InnoDB встречается проблема с огромными размерами файлов ibdata1 и ib_log. Причина в большинстве случаев связана с неправильными настройками сервера MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого не высвобождается само по себе. Корректнее всего хранить данные таблиц в отдельных файлах ibd*.

Изменить существующую настройку невозможно, поэтому необходимо выполнить несколько шагов:

  • делаем резервную копию существующей БД;
  • меняем настройки сервера MySQL/MariaDB;
  • удаляем старые файлы;
  • восстанавливаем БД из резервной копии.

Рассмотрим на примере, как мы можем это сделать.

Имеем следующую структуру: сервер с БД MariaDB, название базы asteriskcdrdb. По умолчанию вся информация хранится в файле ibdata1, при этом в файле с названием БД пусто.

Рисунок 1 - Список баз и файлов в каталоге /var/lib/mysql

Рисунок 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 после операции

Рисунок 2 - Список баз и файлов в каталоге /var/lib/mysql после операции

Рисунок 3 - Список баз и файлов в каталоге /var/lib/mysql после операции в каталоге с базой

Рисунок 3 - Список баз и файлов в каталоге /var/lib/mysql после операции в каталоге с базой

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