MySQL - разделение и уменьшение файла ibdata1

Файл ibdata1 является частью InnoDB, и хранит в себе данные таблиц, их индексы и другую служебную информацию. Со временем этот файл может увеличиться до совсем неприличных размеров. Что бы упростить работу с этим файлов – можно разделить его, создав отдельный файл для каждой базы данных и таблицы, с помощью опции innodb_file_per_table.

В MySQL версии 5.6 и выше эта опция включена по-умолчанию, в более старых – необходимо выполнить некоторые дополнительные действия.

Создаём директорию для бекапов:

#mkdir -p /var/backup/mysql
#cd backup/mysql/

Создаём резервную копию всех имеющихся баз:

#mysqldump -u root -p -ERA --flush-privileges > alldbs.sql

Удаляем все базы, кроме баз mysql и information_schema:

#mysql -u root -p -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema | grep -v db_4 | grep -v OLD | gawk '{print "drop database " $1 "; select sleep(0.1);"}' | mysql -u root -pPASSWROD

Останавливаем MySQL сервер

Удаляем старые файлы:

#rm /var/db/mysql/ibdata1
#rm /var/db/mysql/ib_logfile0
#rm /var/db/mysql/ib_logfile1

Удалить надо все файлы логов – ib_logfil*.

Редактируем файл /etc/my.cnf, и в блоке [mysqld] добавляем строку:

innodb_file_per_table = 1

Запускаем MySQL

Восстанавливаем наши базы данных:

# mysql -u root -p < alldbs.sql

Запускаем mysql_upgrade, что бы сгенерировать новую базу performance_schema:

# mysql_upgrade -u root -p --force

Так же бывает что пространство на диске забито файлами вида:

mysql-bin.index

mysql-bin.000001

mysql-bin.000002

Для удаления данных файлов выполняем:

mysql -u root -p < "purge binary logs before '2016-06-01 00:00:00'"