Ремонт MySQL таблиц с помощью myisamchk

В этой статье я собрал рекомендации по восстановлению MyISAM таблиц базы данных MySQL с помощью утилиты myisamchk..

Сегодня вновь потребовалось восстанавливать большую базу (300+ Гб), поэтому решил тут написать немного про ключи. Разберем такую команду:

• -r = означает что необходимо восстановить базу
• -q = означает что необходимо использовать "быстрый режим"
• -v = означает выводить информацию о работе
• --sort_buffer_size = задает размер буфера который будет использоваться для сортировки. Нужно устанавливать побольше, рекомендуемое значение 1/4 от ОЗУ.
  • для 32 битных систем или MySQL <= 5.6.4 максимальное значение 4 Гб
  • для 64 битных систем и MySQL > 5.6.4 максимальное значение не ограничено (более 16.000.000 TB).
  • справка: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_myisam_sort_buffer_size
• --key_buffer_size = задает размер буфера для хранения ключей во время safe-recover или extended-check. Нужно устанавливать побольше, рекомендуемое значение 1/4 от ОЗУ.
  • для 32 битных максимальное значение 4 Гб
  • для 64 ограничено переменной ОС: OS_PER_PROCESS_LIMIT
  • справка: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_key_buffer_size
• согласно справки, можно устанавливать оба параметра большого размером (в сумме больше чем ОЗУ), т.к. во время восстановления будет использоваться только один из них.

Выдержка из справки: https://dev.mysql.com/doc/refman/5.6/en/myisamchk-general-options.html

myisam_sort_buffer_size is used when the keys are repaired by sorting keys, which is the normal case when you use --recover. sort_buffer_size is a deprecated synonym for myisam_sort_buffer_size.

key_buffer_size is used when you are checking the table with --extend-check or when the keys are repaired by inserting keys row by row into the table (like when doing normal inserts). Repairing through the key buffer is used in the following cases:

• You use --safe-recover.
• The temporary files needed to sort the keys would be more than twice as big as when creating the key file directly. This is often the case when you have large key values for CHAR, VARCHAR, or TEXT columns, because the sort operation needs to store the complete key values as it proceeds. If you have lots of temporary space and you can force myisamchk to repair by sorting, you can use the --sort-recover option.

Repairing through the key buffer takes much less disk space than using sorting, but is also much slower.

If you want a faster repair, set the key_buffer_size and myisam_sort_buffer_size variables to about 25% of your available memory. You can set both variables to large values, because only one of them is used at a time.

Собственно, это и все что хотел дополнить. Подробнее по ключам, смотрите в man-е, например тут: myisamchk(1) - Linux man page, http://linux.die.net/man/1/myisamchk

--[дополнено 18.07.2013]--

Еще одно описание нашел в справочном руководстве по MySQL: http://www.mysql.ru/docs/man/Repair.html

*где 512M = 1/4 Доступной ОЗУ

--[дополнено 26.03.2015]--

Чтобы не ходить подсматривать значения опций выложу их тут (источник: http://www.mysql.ru/docs/man/myisamchk_repair_options.html):

Опции исправления для myisamchk

Следующие опции используются, если myisamchk запускается с -r или -o:

• -D # или --data-file-length=# Максимальная длина файла данных (когда файл данных пересоздается при его переполнении'').
• -e или --extend-check Пробовать исправлять каждую возможную строку из файла данных. Обычно при этом обнаруживается масса замусоренных строк. Использовать эту опцию следует только в самом крайнем случае, когда больше ничего не остается.
• -f или --force Писать поверх старых временных файлов (`table_name.TMD') вместо аварийного прекращения.
• -k # или keys-used=# Если используется ISAM, то данный параметр предписывает обработчику таблиц ISAM на необходимость обновить только первые # индексов. Если используется MyISAM, то определяет, какие ключи использовать, при этом каждый двоичный бит соответствует одному ключу (первый ключ - это бит 0). Может использоваться для ускорения вставок! Отключенные индексы можно снова активизировать с помощью myisamchk -r. keys.
• -l или --no-symlinks Не рассматривать символические ссылки. Обычно myisamchk исправляет таблицы, на которые указывают символические ссылки. Данная опция отсутствует в MySQL 4.0, в связи с тем, что MySQL 4.0 не удаляет символические ссылки во время восстановления.
• -r или --recover При указании этой опции можно исправить практически все, кроме уникальных ключей, в которых есть повторения (ошибка, вероятность которой мизерна для таблиц ISAM/MyISAM). Если необходимо восстановить таблицу, то начинать надо с этой опции. Только если myisamchk сообщит, что таблица не может быть восстановлена с помощью -r, тогда следует пытаться применять -o (отметим, что в тех маловероятных случаях, когда -r не срабатывает, файл данных остается неизменным), В случае большого объема памяти следует увеличить размер sort_buffer_size!

-o или --safe-recover
Используется старый метод восстановления (читаются подряд все строки и обновляются все деревья индексов на основе найденных строк); такой алгоритм работает на порядок медленнее -r, но метод справляется с несколькими редкими случаями, непосильными для -r. При этом методе восстановления также используется значительно меньше дискового пространства, нежели в случае -r. Обычно всегда следует начинать с исправления посредством -r, и только если результат не будет достигнут, использовать -o. Для систем с большим объемом памяти следует увеличить размер key_buffer_size!

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

--character-sets-dir=...
Каталог, где хранятся кодировки.

--set-character-set=name
Изменить используемую для индекса кодировку

-t или --tmpdir=path
Путь для хранения временных файлов. Если не задан, myisamchk использует для пути переменную окружения TMPDIR.

-q или --quick
Быстрый ремонт без изменения файла данных. Можно добавить вторую -q, чтобы дать myisamchk санкцию на изменение исходного файла данных в случае дублирования ключей

-u или --unpack
Распаковать файл, упакованный в myisampack.

--[дополнено 31.10.2016]--

Расширения файлов MyISAM баз:

• FRM - структура таблицы или определение таблицы
• MYI - файл индексов таблицы
• MYD - файл с данными таблицы
• TMD - временный файл данных, который создается при запуске myisamchk без параметра -q

Иногда бывают такие моменты, когда не хватает места для временного файла востановления (*.TMD) рядом с основными файлами базы. Например:

•  файлы базы занимают 500 Гб, база хранится в /var/lib/mysql/mydb/*
• диск разбит таким образом, что в /var ( или в корне /) осталось свободных всего 200 Гб
• для временного файла необходимо столько же, сколько занимает таблица, т.е. свободных 500 Гб.
• в /home у вас доступно 600 Гб

Хотелось бы сделать как-то так, чтобы поломанная таблица была в одном разделе (файлы frm, myi, myd), а временный файл создающийся во время восстановления (tmd) в другом. Простого способа задать путь к файлу в параметрах - нет. Однако, можно поступить так:

• создаете пустой файл /home/tmp/mydb/table.TMD
• помещаете символическую ссылку в папку базы, т.е. символическая ссылка должны быть тут: /var/lib/mysql/mydb/table.TMD
• запускаете myisamchk с параметром -f
• данные пишутся по символической ссылке в раздел где достаточно места

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

--[дополнено 05.01.2020]--

Заметка о том, как лучше копировать большие файлы на другой сервер, в случае если восстановление будет происходить не на том же сервере где лежат файлы: Копирование больших файлов между серверами