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

Question

Я использую MySQL в localhost в качестве "инструмента запросов" для выполнения статистики в R, то есть каждый раз, когда я запускаю скрипт R, я создаю новую базу данных (A), создаю новую таблицу (B), импортирую данные в B, отправьте запрос, чтобы получить то, что мне нужно, а затем я опускаю B и отбрасываю A.

У меня все работает нормально, но я понимаю, что размер файла ibdata быстро увеличивается, я ничего не хранил в MySQL, но файл ibdata1 уже превысил 100 МБ.

Я использую более или менее настройки MySQL по умолчанию для настройки, есть ли способ, которым я могу автоматически сжимать / очищать файл ibdata1 через фиксированный период времени?

Answer 1

Тот ibdata1 не уменьшается - это особенно раздражающая особенность MySQL. ibdata1 Файл не может быть сокращен, если вы не удалите все базы данных, не удалите файлы и не перезагрузите дамп.

Но вы можете настроить MySQL так, чтобы каждая таблица, включая ее индексы, сохранялась как отдельный файл. Таким образом ibdata1 не будет расти как большой. Согласно комментарию Билла Карвина, это включено по умолчанию в версии 5.6.6 MySQL.

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

[mysqld]
innodb_file_per_table=1

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

Как вы хотите вернуть пространство из ibdata1 вы на самом деле должны удалить файл:

1. Сделать mysqldump всех баз данных, процедур, триггеров и т. д., кроме mysql а также performance_schema базы данных
2. Отбросьте все базы данных, кроме двух указанных выше.
3. Стоп MySQL
4. удалять ibdata1 а также ib_log файлы
5. Запустите MySQL
6. Восстановить из дампа

Когда вы запускаете MySQL на шаге 5, ibdata1 а также ib_log файлы будут воссозданы.

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

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

Вы, наверное, видели это:
http://bugs.mysql.com/bug.php?id=1341

С помощью команды ALTER TABLE <tablename> ENGINE=innodb или же OPTIMIZE TABLE <tablename> Можно извлекать данные и индексировать страницы из ibdata1 в отдельные файлы. Однако ibdata1 не будет уменьшаться, если вы не выполните описанные выше действия.

Учитывая information_schema Это не нужно и не возможно отбросить. На самом деле это просто набор представлений только для чтения, а не таблиц. И нет никаких файлов, связанных с ними, даже каталог базы данных. informations_schema использует механизм памяти db и сбрасывается и восстанавливается при остановке / перезапуске mysqld. См. https://dev.mysql.com/doc/refman/5.7/en/information-schema.html.

Answer 2

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

Для системы 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 так далее...)
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 изменены, чтобы охватить все базы данных, которые вы хотите сохранить.

Answer 3

Когда вы удаляете таблицы 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

Answer 4

Быстро запишем процедуру принятого ответа в 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 (а также binlog каталог).

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

Вы также можете очистить двоичные журналы после запуска этого сценария, чтобы восстановить больше места на диске с помощью:

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

Answer 5

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

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

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

Answer 6

Кажется, никто не упоминает о влиянии innodb_undo_log_truncateнастройка может иметь.

Прочитав сообщение в блоге Percona по этой теме, я включил в своей MariaDB 10.6 усечение UNDO LOGзаписей, которые заполнили 95% , и, после полного удаления и восстановления, с этого момента мой больше не рос.

По умолчанию innodb_undo_log_truncate = 0я легко достиг 10% занимаемой базы данных, то есть десятков гигабайт.

С innodb_undo_log_truncate = 1, ibdata1это фирма на 76 Мб.

Answer 7

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

MySQL> 5.1.24:

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

MySQL <5.1.24:

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

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

du -b ibdata1

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

Answer 8

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

[mysqld]
innodb_file_per_table=1


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

Answer 9

Недавно мы столкнулись с ошибкой в ​​MariaDB 10.6.13 (исправленной в 10.6.14), из-за которой наш файл увеличивался на 5+ ГБ/день. К тому времени, когда начали рассылаться оповещения об использовании дискового пространства, размер файла превысил 400 ГБ.

Общий объем наших баз данных составляет около 865 ГБ, поэтому логическое резервное копирование/восстановление осуществляется с помощьюна это ушли бы как минимум дни. Если у вас большая база данных, вы можете использовать ее для гораздо более быстрого выполнения логического дампа/восстановления, поскольку, очевидно, это все еще единственный способ сжать файл.

Вот некоторые примечания/процедуры, которым я следовал, когда перестраивал наши серверы БД, чтобы вернуться к разумному размеру:

• я используюуже.

• Я использовал этот инструмент , чтобы определить использование пространства на страницах журнала отмены (это заняло несколько часов, это было до того, как я узнал об ошибке в 10.6.13 и не знал наверняка, что вызывает такой рост). много).

• У нас есть одна первичная и три реплики.

• Сначала я снял одну реплику с производства, использовал ее для дампа/восстановления, а затем сделал резервную копию восстановленной реплики. Я использовал эту резервную копию для восстановления остальных серверов БД (что намного быстрее).

• У нас уже есть процедура полного резервного копирования с использованием . Прежде чем что-либо делать, убедитесь, что у вас есть заведомо исправная полная резервная копия!

Восстановление первого сервера

Остановить все записи в базу данных.

Я делаючтобы быть на 100% уверенным, что во время резервного копирования в БД ничего не пишется (ина репликах конечноуже установлен на репликах в нашем конфиге).

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

      #!/usr/bin/bash
time mydumper -t "$(nproc)" \
  --compress \
  --triggers \
  --routines \
  --events \
  --rows 10000000 \
  --verbose 3 \
  --build-empty-files \
  --regex '^(?!(test|performance_schema|information_schema|sys))' \
  --logfile /mnt/mysql_backups/ibdatafix_backup.log \
  --outputdir /mnt/mysql_backups/ibdatafix_backup 2>&1

echo "DUMP ON $(hostname) DONE" | mail -s "DUMP ON $(hostname) DONE" root

Создание дампа БД объемом 865 ГБ на довольно мощном корпоративном сервере с 4 твердотельными накопителями в аппаратном RAID 10 заняло 12 минут. На старом сервере это занимает менее одного часа.

Проверьте журнал на наличие ошибок.

Я попробовал просто удалить наши базы данных, закрыть MariaDB и удалить, но это вызвало проблемы, поэтому я закрыл MariaDB и стер весь каталог данных (), и побежалначать все сначала (убедитесь, что у вас есть хорошие резервные копии!).

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

      # About 3GB.
innodb_max_undo_log_size = 3221225472

# I moved the undo logs into a separate volume. You can set this to
# /var/lib/mysql.
innodb_undo_directory = /var/lib/mysql_undo_logs

# This appears to only work if innodb_undo_directory is set and
# the undo logs are separate from ibdata1.
innodb_undo_log_truncate = 1

innodb_undo_tablespaces = 3

Временно добавьте их в конфигурацию для восстановления:

      innodb_flush_log_at_trx_commit=0
net_read_timeout=60
performance_schema=off
innodb_adaptive_hash_index=off
sql_mode=NO_ENGINE_SUBSTITUTION
innodb_doublewrite=off
slow_query_log = 0

Теперь запустите MySQL/MariaDB и восстановите:

      #!/usr/bin/bash

time myloader \
  --compress-protocol \
  --directory /mnt/mysql_backups/ibdatafix_backup \
  --overwrite-tables \
  --queries-per-transaction 1000 \
  # The recommendation here is half of your available cores. I set this 
  # too high and it lead to timeouts and a botched restore.
  --threads 4 \
  --verbose 3 > restore.log 2>&1

echo "RESTORE ON $(hostname) DONE" | mail -s "RESTORE ON $(hostname) DONE" root

На это ушло около 12 часов.

Обязательно проверьтеза ошибки(). Мое первое восстановление было полностью провалено из-за ошибок, которые я не уловил.

Удалите временную конфигурацию и перезапустите MySQL/MariaDB.

Прежде чем сделать что-нибудь еще, я побежалчтобы убедиться, что основная и все реплики имеют идентичные данные (особенно недавно восстановленная реплика).

Если вам нужно перестроить другие серверы (другие реплики/основные), вы можете использовать(или Percona XtraBackup), что намного быстрее. Примерно так (я запустил это на реплике, поэтому добавил все параметры подчиненного устройства):

      # On the freshly rebuilt server:
rm -rf /mnt/mysql_backups/ibdatafix_backup/*

mariabackup --defaults-file=/etc/mysql/my.cnf --parallel=4 \
  --compress --compress-threads=4 --safe-slave-backup --slave-info \
  --user=root --backup --target-dir=/mnt/mysql_backups/ibdatafix_backup

# Then on the other server(s), shut down the DB, wipe /var/lib/mysql, and copy the backup back over:
systemctl stop mariadb/mysql

cd /var/lib/mysql

rm -rf *

# This is an NFS share mounted on all DB servers.
rsync -av /mnt/mysql_backups/ibdatafix_backup .

mariabackup --decompress --parallel=8 --remove-original --target-dir=.

mariabackup --prepare --use-memory=32G --target-dir=.

chown -R mysql:mysql .

systemctl start mariadb/mysql

# Set up replication etc.

Все это заняло около двух часов на каждый сервер (865 ГБ в базах данных, корпоративных серверах, 10-гигабайтной сети, 4 твердотельных накопителя в RAID 10).

Усечение журнала отмены теперь определенно работает. я установилизначально до 10 МБ, и MariaDB постоянно их усекала. Когда это произойдет, он зарегистрируется.

теперь стабильно держится на наших высокоактивных серверах и составляет всего 12 МБ.

Answer 10

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

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

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

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