Борьба с разросшимся ibdata1 файлом MySQL
• 3 мин чтения • Автор RAMe0

Борьба с разросшимся ibdata1 файлом MySQL

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

Начал разбираться что происходит. Первая идея — кто то, снова, нашел дыру и пытается спамить. Такое уже бывало. Рабочие папки exim (по моему) росли в размере с невероятной скоростью, пока последний не был отключен.
Теория не подтвердилась.

Пришлось локализовать «распухшее» место программой du:

# ищем переростка в корне директории больше 200 Мб
$ du -sh /*  --threshold=200M | sort -h

# ищем в следующей самой жирной директории, например
$ du -sh /var/*  --threshold=200M | sort -h

# ... и так далее, пока не локализуем виновника

В моем случае это оказался файл ibdata1 — файл с базами данных.
Как выяснилось, до 5.6.6, MySQL по умолчанию не «резала» таблицы и базы на отдельные папки и файлы, а сохраняла все в один файл.

Пошел смотреть какая база разрослась. Все оказалось просто — я не отключил один старый сайт на Magento CMS. Она собирала информацию о пользователях и их устройствах, переходах, действиях и чёрт знает что еще. И, внимание, хранила все это она в БД.
Не поверите, на «мертвом» сайте за 5+ лет побывало то ли 7, то ли 8 тыс человек. Которые сгенерировали по 14 000 000 (четырнадцать миллионов) записей в двух таблицах.
Порядка 7 Gb логов всего в двух табличках!

Данные эти мне не нужны, поэтому TRUNCATE и дело с концом.

И вот тут меня ждал очень неприятный сюрприз — размер файла ibdata1 не уменьшился вообще.
ALTER TABLE <...> ENGINE=innodb не помогло. OPTIMIZE TABLE <...> — тоже.

Решение

Нашел решние на Stackoverflow.
Оказалось, что это, типа, так и должно быть. Файл можно уменьшить только удалив ibdata1 и ib_log.

1. Делаем дамп базы

Надо учесть, что базы mysql и performance_schema мы удалять не будем.
Самый простой способ, если баз не так много:

$ mysqldump  -uroot -proot --databases db_name1 [db_name2 db_name3...] > dump.sql

Если баз много, то можно воспользоваться такой конструкцией:

$ echo 'show databases;' | mysql -uroot -proot | grep -v ^mysql$ | grep -v ^performance_schema$ | xargs mysqldump -uroot -proot  --databases > dump.sql

Если баз, которые хочется исключить, больше двух, то можно, конечно, все их перечислить в примере выше. Но как по мне, так увеличивается вероятность ошибиться.
Поэтому я предлагаю сделать 3 простых шага:

# 1. получаем список всех таблиц в файл
$ echo 'show databases;' | mysql -uroot -proot > dblist.txt
# 2. удалить из этого файлика все ненужные таблицы
# 3. Сделать дамп
$ cat dblist.txt | xargs mysqldump -uroot -proot --databases > dump.sql

2. Удаляем все базы данных.

КРОМЕ mysql и performance_schema

$ mysql -uroot -proot
mysql > DROP DATABASE db_name1;
mysql > DROP DATABASE db_name2;
...
mysql > DROP DATABASE db_nameN;
Важно из справки по DROP DATABASE

Оператор DROP DATABASE удаляет все таблицы в указанной базе данных и саму базу. Если вы выполняете DROP DATABASE на базе данных, символически связанной с другой, то удаляется как ссылка, так и оригинальная база данных. Будьте ОЧЕНЬ внимательны при работе с этой командой!

3. Тормозим mysql

Ubuntu:

$ sudo service mysqld stop
## или
$ sudo service mysql stop

CentOS 7

$ sudo systemctl stop mysql

4. Исправляем конфиг my.cnf

Данный файл лежит, скорее всего, где то тут: /etc/mysql/my.cnf.
Чтобы найти все места откуда забирается конфиг, можно выполнить следующую команду:

$ mysql --help

По идее, вы должны будете увидеть нечто подобное (строки 8-9):

$ mysql --help

mysql  Ver 15.1 Distrib 10.3.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Usage: mysql [OPTIONS] [database]

Default options are read from the following files in the given order: 
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
...
...

После того, как нашли конфиг, в него нужно найти секцию [mysqld] и добавить (или исправить, если есть) значение параметра:

innodb_file_per_table=1

5. Удаляем файлы ibdata1 и ib_log

6. Стартуем mysql

Ubuntu:

$ sudo service mysqld start
## или
$ sudo service mysql start

CentOS 7

$ sudo systemctl start mysql

7. Восстанавливаем дамп

$ mysql -uroot -proot < dump.sql

Profit

Комментарии ()