Как сжать / очистить файл ibdata1 в MySQL

при удалении таблиц innodb MySQL не освобождает пространство внутри файла ibdata, поэтому он продолжает расти. Эти файлы почти никогда не сжимаются.

как сжать существующий файл ibdata файл:

http://dev.mysql.com/doc/refman/5.5/en/innodb-resize-system-tablespace.html

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

Если вы используете параметр конфигурации innodb_file_per_table можно создать несколько табличных пространств. То есть MySQL создает отдельные файлы для каждой таблицы вместо одного общего файла. Эти отдельные файлы хранятся в каталоге базы данных, и они удаляются при удалении этой базы данных. Это должно устранить необходимость сжимать / очищать файлы ibdata в вашем случае.

дополнительная информация о нескольких табличные пространства:

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

добавлять к ответ Джона П,

для системы linux шаги 1-6 могут быть выполнены с помощью следующих команд:

1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
2. DROP DATABASE [database_name];
3. sudo /etc/init.d/mysqld stop
4. sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile (и удалите любой другой ib_logfile, который может быть назван ib_logfile0,ib_logfile1 etc...)
5. sudo /etc/init.d/mysqld start
6. create database [database_name];
7. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

предупреждение: эти инструкции заставят вас потерять другие базы данных, если у вас есть другие базы данных на этом экземпляре mysql. Убедитесь, что шаги 1,2 и 6,7 изменены, чтобы охватить все базы данных, которые вы хотите сохранить.

Если вы используете механизм хранения InnoDB для (некоторых) ваших таблиц MySQL, вы, вероятно, уже столкнулись с проблемой с его конфигурацией по умолчанию. Как вы, возможно, заметили, в каталоге данных вашего MySQL (в Debian/Ubuntu – /var/lib/mysql) лежит файл под названием "ibdata1". Он содержит почти все данные InnoDB (это не журнал транзакций) экземпляра MySQL и может стать довольно большим. По умолчанию этот файл имеет начальный размер 10 МБ и автоматически расширяется. К сожалению, по дизайн файлах данных InnoDB не может быть термоусадочной пленкой. Вот почему удаляет, усекает, отбрасывает и т. д. не будет освобождать пространство, используемое файлом.

Я думаю, что вы можете найти хорошее объяснение и решение есть :

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/

Если ваша цель-контролировать свободное пространство MySQL, и вы не можете остановить MySQL, чтобы сжать файл ibdata, а затем получить его через команды состояния таблицы. Пример:

MySQL > 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print }'

MySQL

mysqlshow --status myInnodbDatabase myTable | awk '{print }'

затем сравните это значение с вашим файлом ibdata:

du -b ibdata1

Источник:http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

в новой версии mysql-server рецепты выше раздавят базу данных "mysql". В старой версии это работает. В новых некоторых таблицах переключается на тип таблицы INNODB, и тем самым вы их повредите. Самый простой способ-это сбросить все ваши базы данных, деинсталлировать mysql-сервер, добавить в осталось мое.cnf:

[mysqld]
innodb_file_per_table=1


erase all in /var/lib/mysql
install mysql-server
restore users and databases

быстро написал процедуру принятого ответа в bash:

#!/usr/bin/env bash
DATABASES="$(mysql -e 'show databases \G' | grep "^Database" | grep -v '^Database: mysql$\|^Database: binlog$\|^Database: performance_schema\|^Database: information_schema' | sed 's/^Database: //g')"
mysqldump --databases $DATABASES -r alldatabases.sql && echo "$DATABASES" | while read -r DB; do
    mysql -e "drop database \`$DB\`"
done && \
    /etc/init.d/mysql stop && \
    find /var/lib/mysql -maxdepth 1 -type f \( -name 'ibdata1' -or -name 'ib_logfile*' \) -delete && \
    /etc/init.d/mysql start && \
    mysql < alldatabases.sql && \
    rm -f alldatabases.sql

Сохранить как purge_binlogs.sh и работать как root.

исключить mysql,information_schema,performance_schema (и ).

предполагается, что у вас есть учетные данные администратора в /root/.my.cnf и что ваша база данных живет в умолчанию

Как уже отмечалось, вы не можете сжать ibdata1 (для этого вам нужно сбросить и перестроить), но также часто нет реальной необходимости.

используя autoextend (вероятно, наиболее распространенная настройка размера) ibdata1 предварительно выделяет хранилище, растущее каждый раз, когда оно почти заполнено. Это делает запись быстрее, так как пространство уже выделено.

при удалении данных он не сжимается, но пространство внутри файла помечается как неиспользуемое. Теперь, когда вы вставляете новые данные, он будет повторно использовать пустое пространство в файл перед дальнейшим ростом файла.

Так что он будет только продолжать расти, если вам действительно нужны эти данные. Если вам действительно не нужно пространство для другого приложения, вероятно, нет причин его сокращать.