The OpenNET Project / Index page

[ новости /+++ | форум | теги | ]

Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа

4.4 Предотвращение повреждений и ремонтно-восстановительные работы

4.4.1 Резервирование баз данных

Поскольку таблицы MySQL сохранены как файлы, просто делайте копию. Чтобы получать непротиворечивую копию, скомандуйте LOCK TABLES на релевантных таблицах и дополните это командой FLUSH TABLES для них, дабы все данные были гарантированно сброшены на диск. Вам нужна только блокировка записи. Это позволяет другим потокам продолжать делать запросы к таблицам в то время, как Вы делаете копию файлов в каталоге баз данных. Команда FLUSH TABLE необходима, чтобы гарантировать, что все активные индексные страницы записаны на диск прежде, чем Вы запускаете процесс копирования.

Если Вы хотите делать копию уровня SQL из таблицы, Вы можете использовать SELECT INTO OUTFILE или BACKUP TABLE. Подробности в разделе "4.4.2 Синтаксис BACKUP TABLE".

Другой способ поддержать базу данных состоит в том, чтобы использовать программу mysqldump или скрипт mysqlhotcopy. Подробности в разделах "4.8.5 mysqldump, Дамп структур таблиц и данных" и "4.8.6 mysqlhotcopy, Копирование баз данных и таблиц MySQL".

  1. Для полного резервирования баз данных надо скомандовать:
    shell> mysqldump --tab=/path/to/some/dir --opt --full
    
    или
    shell> mysqlhotcopy database /path/to/some/dir
    
    Вы можете также просто копировать все файлы таблицы (*.frm, *.MYD и *.MYI), пока сервер что-нибудь не модифицирует. Скрипт mysqlhotcopy использует этот метод.
  2. Если mysqld запущен, остановите его, а затем запустите с опцией --log-update[=file_name]. Подробнее об этой опции можно узнать в разделе "4.9.3 Файл регистрации модификаций". Файлы протоколов предоставляют Вам информацию относительно того, что изменилось со времени последнего вызова mysqldump.

Если Вы должны восстановить что-либо, попробуйте восстанавливать Ваши таблицы, используя REPAIR TABLE или myisamchk -r. Это эффективно в 99.9% случаев. Если myisamchk ничего хорошего не сделал, попробуйте следующую процедуру (это будет работать только, если Вы запустили MySQL с опцией --log-update):

  1. Восстановите оригинальную копию mysqldump.
  2. Выполните следующую команду, чтобы заново выполнить модификации в двоичном файле регистрации:
    shell> mysqlbinlog hostname-bin.[0-9]* | mysql
    
    Если Вы используете файл регистрации модификации, Вы можете использовать:
    shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql
    

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

Вы можете также делать выборочные копии: SELECT * INTO OUTFILE 'file_name' FROM tbl_name и восстанавливать их: LOAD DATA INFILE 'file_name' REPLACE .... Чтобы избежать двойных записей, Вам нужен PRIMARY KEY или UNIQUE в таблице. Ключевое слово REPLACE заменяет старые записи на новые, когда новая запись дублирует старую запись на уникальном значении ключа.

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

Если Вы используете файловую систему Veritas, Вы можете делать следующее:

  1. Выполните в клиенте (perl?) FLUSH TABLES WITH READ LOCK
  2. Выполните в другом клиенте или в копии оболочки команду mount vxfs snapshot.
  3. Выполните в первом клиенте UNLOCK TABLES
  4. Копируйте файлы из snapshot
  5. Размонтируйте snapshot

4.4.2 Синтаксис BACKUP TABLE

BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'

Эта команда делает копию всех файлов таблицы в резервный каталог, что является минимумом, необходимым, чтобы восстановить ее. Сейчас это работает только для таблиц MyISAM. Для них копируются файлы .frm (определение) и .MYD (данные). Индексный файл может быть восстановлен из этих двух.

Перед использованием этой команды, пожалуйста, ознакомьтесь с разделом "4.4.1 Резервирование баз данных".

Пока таблица резервируется, она блокируется. Если нужно резервировать сразу несколько таблиц, выполните команду LOCK TABLES для каждой таблицы в этой группе.

Команда возвращает таблицу со следующими столбцами:

СтолбецЗначение
TableИмя таблицы
OpОбязательно ``backup''
Msg_typeОдно из status, error, info или warning.
Msg_textСобственно сообщение.

Обратите внимание, что команда BACKUP TABLE доступна только в MySQL версии 3.23.25 и старше.

4.4.3 Синтаксис RESTORE TABLE

RESTORE TABLE tbl_name[,tbl_name...] FROM '/path/to/backup/directory'

Восстанавливает таблицу из копии, которая была сделана с помощью BACKUP TABLE. Существующие таблицы не будут перезаписаны, если Вы попробуете восстанавливать существующую таблицу, получите ошибку. Восстановление занимает больше времени, чем резервирование из-за необходимости сгенерировать индексный файл. Чем больше имеется ключей, тем дольше система будет его строить. Также, как и BACKUP TABLE, эта функция работает только с таблицами MyISAM.

Команда возвращает таблицу со следующими столбцами:

СтолбецЗначение
TableИмя таблицы
OpОбязательно ``restore''
Msg_typeОдно из status, error, info или warning.
Msg_textСобственно сообщение.

4.4.4 Синтаксис CHECK TABLE

CHECK TABLE tbl_name[,tbl_name...] [option [option...]]
option = QUICK | FAST | MEDIUM | EXTENDED | CHANGED

CHECK TABLE работает только с таблицами типа MyISAM. На них данная команда эквивалентна myisamchk -m table_name.

Если Вы не определяете опций, используется MEDIUM.

Данная команда проверяет таблицу на ошибки. Для таблиц типа MyISAM модифицируется статистика ключа. Команда возвращает таблицу со следующими столбцами:

СтолбецЗначение
TableИмя таблицы.
OpОбязательно ``check''.
Msg_typeОдно из status, error, info или warning.
Msg_textСобственно сообщение.

Обратите внимание, что Вы можете получать много строк информации для каждой проверенной таблицы. Последняя строка будет иметь тип Msg_type status и значение OK, если все хорошо. Если ответов OK или Not checked нет, Вы должны выполнить ремонт таблицы. Подробности в разделе " 4.4.6 Использование myisamchk для устранения повреждений". Сообщение Not checked говорит о том, что для данной таблицы TYPE сообщил MySQL, что там не было никакой потребности проверять таблицу.

Различные типы проверки:
ТипЗначение
QUICKНе просматривать строки, чтобы проверить неправильные связи.
FASTПроверить только те таблицы, которые не были закрыты правильно.
CHANGEDПроверить только те таблицы, которые не были закрыты правильно, и те, которые изменились со времен последней проверки.
MEDIUMСканировать строки для проверки правильности удаленных связей. Это также вычисляет контрольную сумму ключа для строк и проверяет ее правильность.
EXTENDEDВыполнить полную проверку всех ключей для всех строк в таблице. Это гарантирует, что таблица на 100% непротиворечива, но требует много времени!

Для динамических таблиц MyISAM запущенная проверка будет всегда использовать опцию MEDIUM. Для статических строк не выполняется просмотр строки для режимов QUICK и FAST, поскольку строки очень редко разрушаются.

Вы можете объединять параметры проверки:

CHECK TABLE test_table FAST QUICK;

Это предписывает провести быструю проверку на таблице, если она не была закрыта правильно.

ОБРАТИТЕ ВНИМАНИЕ: в ряде случаев CHECK TABLE изменяет таблицу! Это случается, если таблица отмечена как 'corrupted' (повреждена) или 'not closed properly' (не закрыта правильно), но команда CHECK TABLE не нашла проблем в таблице. В этой ситуации CHECK TABLE отметит таблицу как ok.

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

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

FAST и CHANGED обычно предназначены, чтобы использоваться из скрипта (например, из cron), если Вы хотите проверять таблицу время от времени. В большинстве случаев FAST имеет приоритет перед CHANGED.

EXTENDED должен использоваться после того, как Вы выполнили нормальную проверку, но все еще получаете странные ошибки из таблицы, когда MySQL пробует модифицировать строку или найти строку по ключу (это ОЧЕНЬ маловероятно, если нормальная проверка прошла спокойно!).

Некоторые вещи, сообщаемые проверкой таблиц, не могут быть исправлены в автоматическом режиме:

4.4.5 Синтаксис REPAIR TABLE

REPAIR TABLE tbl_name[,tbl_name...] [QUICK] [EXTENDED]

REPAIR TABLE тоже работает только на таблицах типа MyISAM и аналогичен вызову myisamchk -r table_name.

Обычно Вам никогда не придется выполнять эту команду, но в случае ошибок Вы, очень вероятно, вернете все Ваши данные из таблицы MyISAM с помощью команды REPAIR TABLE. Если Ваши таблицы разрушаются, Вы должны попробовать нахйти причину этого! Подробности по этому вопросу есть в разделе "8.4.1 Что делать, если MySQL рухнул".

REPAIR TABLE восстанавливает разрушенную таблицу. Команда возвращает таблицу со следующими столбцами:

СтолбецЗначение
TableИмя таблицы
OpОбязательно ``repair''
Msg_typeОдно из status, error, info или warning.
Msg_textСобственно сообщение.

Обратите внимание, что Вы можете получать много строк информации для каждой восстановленной таблицы. Последняя будет иметь тип Msg_type status и значение OK, если все в порядке. Если значение OK так и не появилось, Вы должны попробовать восстанавливать таблицу с помощью myisamchk -o, поскольку REPAIR TABLE еще не выполняет все параметры myisamchk. В ближайшем будущем планируется сделать ее более гибкой.

Если задан QUICK, MySQL пробует делать REPAIR только для индексного дерева.

Если Вы используете EXTENDED, MySQL создаст индекс по строкам вместо того, чтобы создать индекс одновременно с сортировкой: это может быть лучше, чем сортировка по ключам фиксированной длины, если Вы имеете длинные ключи типа char(), которые сжимаются очень хорошо.

4.4.6 Использование myisamchk для устранения повреждений

Начиная с MySQL 3.23.13, можно проверять таблицы MyISAM командой CHECK TABLE. Подробности в разделе "4.4.4 Синтаксис CHECK TABLE ". Ремонтировать таблицы можно командой REPAIR TABLE. Она подробно рассмотрена в разделе "4.4.5 Синтаксис REPAIR TABLE".

Для проверки и ремонта таблиц MyISAM (файлы с расширениями .MYI и .MYD) можно использовать утилиту myisamchk. Для проверки и ремонта таблиц ISAM (файлы с расширениями .ISM и .ISD) следует пользоваться утилитой isamchk. Подробности в разделе "7 Типы таблиц MySQL".

Далее обсуждается myisamchk, но вся информация также применима к старой программе isamchk.

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

Вы в большинстве случаев можете также использовать команду OPTIMIZE TABLES, чтобы оптимизировать и ремонтировать таблицы, но это не так быстро и надежно (в случае реальных фатальных ошибок) как myisamchk. С другой стороны, OPTIMIZE TABLE проще. Подробности в разделе "4.5.1 Синтаксис OPTIMIZE TABLE".

4.4.6.1 Синтаксис обращения к myisamchk

myisamchk вызывается в общем виде так:

shell> myisamchk [options] tbl_name

Здесь options указывает требуемые действия. Они описаны ниже (Вы также можете получить список параметров вызовом myisamchk --help). Без параметров myisamchk просто проверяет таблицу. Чтобы получить большее количество информации или сообщить, чтобы myisamchk занимался корректировкой информации, определите параметры так, как описано ниже в следующих разделах.

tbl_name таблица базы данных, которую Вы хотите проверять. Если Вы выполняете myisamchk не в каталоге баз данных, Вы должны определить путь к файлу, потому что myisamchk понятия не имеет, где размещена база данных. Фактически myisamchk не заботит, размещены или нет файлы, на которых Вы работаете, в каталоге баз данных: Вы можете копировать файлы, которые соответствуют таблице базы данных, в другое место и выполнять операции восстановления на них там.

Вы можете называть несколько таблиц в командной строке myisamchk. Вы можете также указать имя индексного файла (с суффиксом .MYI), что позволяет Вам определять все таблицы в каталоге, используя образец *.MYI. Например, если Вы находитесь в каталоге баз данных, Вы можете проверять все таблицы в каталоге так:

shell> myisamchk *.MYI

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

shell> myisamchk /path/to/database_dir/*.MYI

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

shell> myisamchk /path/to/datadir/*/*.MYI

Для быстрой проверки всех таблиц рекомендуется такой способ:

myisamchk --silent --fast /path/to/datadir/*/*.MYI
isamchk --silent /path/to/datadir/*/*.ISM

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

myisamchk --silent --force --fast --update-state -O key_buffer=64M \
          -O sort_buffer=64M -O read_buffer=1M \
          -O write_buffer=1M /path/to/datadir/*/*.MYI
isamchk --silent --force -O key_buffer=64M -O sort_buffer=64M \
        -O read_buffer=1M -O write_buffer=1M /path/to/datadir/*/*.ISM

Здесь считается, что Вы имеете больше, чем 64 М свободного места.

Обратите внимание, что, если Вы получаете ошибку подобную:

myisamchk: warning: 1 clients is using or hasn't closed the table properly

Это означает, что Вы пробуете проверять таблицу, которая модифицируется другой программой (подобно серверу mysqld), которая не закрыла файл или не может закрыть файл правильно.

Если запущен mysqld, следует принудительно закрыть и сбросить на диск все таблицы командой FLUSH TABLES и гарантировать, что никто не использует таблицы в то время, как Вы выполняете myisamchk. В MySQL Version 3.23 самый простой способ избежать этой проблемы состоит в том, чтобы использовать CHECK TABLE вместо myisamchk, чтобы проверить таблицы.

4.4.6.2 Основные опции myisamchk

Утилита myisamchk поддерживает опции:

-# или --debug=debug_options
Выводить отладочные сообщения. Параметр debug_options обычно является строкой в формате d:t:o,filename.
-? или --help
Вывести справочное сообщение.
-O var=option, --set-variable var=option
Установить значение переменной. Допустимые переменные и их значения по умолчанию можно узнать командой myisamchk --help.
key_buffer_size523264
read_buffer_size262136
write_buffer_size262136
sort_buffer_size2097144
sort_key_blocks16
decode_bits9

key_buffer_size используется только при проверке таблицы с опцией --extended-check или восстановлении с опцией -o. sort_buffer_size используется при восстановлении таблицы с опцией --recover.

Если Вы хотите ускорить восстановление, установите вышеупомянутые переменные в 1/4 доступной памяти. Вы можете устанавливать обе переменные в большие значения, так как только один из вышеупомянутых буферов будет использоваться в каждый момент времени. Восстановление через буфер ключей используется в следующих случаях:

Восстановление через буфер ключей требует намного меньше дискового пространства, чем использование сортировки, но оно также и намного медленнее.
-s или --silent
Молчаливый режим. Выводиться будут только сообщения об ошибках. Для включения очень молчаливого режима используйте опцию дважды: -ss.
-v или --verbose
Подробный вывод. Выводиться много дополнительных данных. Объем вывода регулируется длиной параметра (например, -vv или -vvv). Удобно использовать вместе с опциями -d и -e.
-V или --version
Показать версию myisamchk.
-w или --wait
Вместо того, чтобы выдать ошибку, если таблица блокирована, ждать, пока таблицу не разблокируют перед продолжением. Обратите внимание, что, если Вы запустили mysqld на таблице с параметром --skip-locking, таблица может быть блокирована только другой командой myisamchk.

4.4.6.3 Опции проверки для myisamchk

-c или --check
Проверить таблицу на ошибки. Это заданная по умолчанию операция, если Вы не даете myisamchk никаких параметров, которые ее отменят.
-e или --extended-check
Тщательно проверять таблицы (если в них много индексов, это ОЧЕНЬ медленно). Обычно myisamchk или myisamchk --medium-check может выяснить, имеются ли ошибки в таблице, так что обычно таких крайностей не требуется. Если Вы используете --extended-check на машине с большим объемом памяти, можно увеличить значение key_buffer_size.
-F или --fast
Быстро проверить только те таблицы, которые не были закрыты правильно..
-C или --check-only-changed
Быстро проверить только те таблицы, которые были изменены после прошлой проверки базы данных.
-f или --force
Перезапустить myisamchk с параметром -r на таблице, если myisamchk находит любые ошибки в таблице.
-i или --information
Вывести информационную статистику о таблице, которая проверена.
-m или --medium-check
Быстрее, чем extended-check, но находит только 99.99% всех ошибок. Но в большинстве случаев этого вполне достаточно.
-U или --update-state
Сохранить в файле .MYI сведения о том, когда таблица была проверена. Это должно использоваться, чтобы получить полный результат с опцией --check-only-changed, но Вы не должны использовать это, если mysqld использует таблицу и запущен с параметром --skip-locking.
-T или --read-only
Не отмечать таблицу как проверенную. Это полезно, если Вы используете myisamchk, чтобы проверить таблицу, которая находится в использовании некоторой другой прикладной программой, которая не использует блокировку (подобно mysqld --skip-locking).

4.4.6.4 Ремонтные опции для 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.
-l или --no-symlinks
Не следовать за символическими ссылками. Обычно myisamchk восстанавливает таблицу, на которую указывают ссылки.
-r или --recover
Обычно этого параметра хватает для восстановления всех повреждений, за исключением уникальных ключей, которые стали не уникальными (что редко бывает с ISAM/MyISAM таблицами). Если Вы хотите восстанавливать таблицу, начните с этой опции. Если ничего не выйдет, попробуйте опцию -o. Обратите внимание, что в том маловероятном случае, когда -r не может помочь, файл данных все еще цел). Если Вы имеете много памяти, увеличьте sort_buffer_size!
-o или --safe-recover
Использовать старый метод восстановления (читает все строки и модифицирует все индексные деревья, основанные на найденных строках), это куда медленнее, чем -r, но может обрабатывать ряд очень маловероятных случаев, которые -r не по зубам. Этот метод восстановления также использует намного меньше дискового пространства, чем -r. Обычно нужно всегда сначала попробовать -r и только, если эта попытка провалилась, использовать -o. Если Вы имеете много памяти, увеличьте key_buffer_size!
-n или --sort-recover
Использовать сортировку для того, чтобы разрешить ключи, даже если временные файлы должны быть очень большими. Это не будет иметь никакого эффекта, если Вы имеете полнотекстовые ключи в таблице.
--character-sets-dir=...
Каталог, где сохранены наборы символов.
--set-character-set=name
Изменить набор символов, используемый индексом.
-t или --tmpdir=path
Путь, где хранить временные файлы. Если не установлено, myisamchk использует системную переменную TMPDIR.
-q или --quick
Более быстрый ремонт, не изменяя файл данных. Можно давать второй параметр -q, чтобы myisamchk изменял первоначальный файл данных в случае двойных ключей.
-u или --unpack
Распаковать файл, ранее упакованный myisampack.

4.4.6.5 Прочие опции для myisamchk

Другие действия, которые myisamchk может делать, помимо ремонта и проверки таблиц:

-a или --analyze
Анализируют распределение ключей. Это улучшает эффективность объединения, разрешая оптимизатору объединения выбрать, в каком порядке он должен соединить таблицы, и какие ключи должен использовать.
-d или --description
Вывести информацию о таблице.
-A или --set-auto-increment[=value]
Вынуждает auto_increment начинаться в этом или более высоком значении. Если значение не задано, то устанавливается следующее значение auto_increment в наибольшее используемое значение для auto key + 1.
-S или --sort-index
Сортирует индексные блоки дерева. Это оптимизирует установки и делает просмотр таблицы по ключу быстрее.
-R или --sort-records=#
Сортировать записи согласно индексу. Это делает данные намного более компактно собранными внутри базы данных, что приведет к ускорению ограниченных операций SELECT и ORDER BY на этом индексе. Правда, первая сортировка ОЧЕНЬ медленная. Чтобы узнать индексные числа таблицы, используйте SHOW INDEX, эта команда показывает индексы таблицы в том порядке, в каком их видит myisamchk. Индексы пронумерованы, начиная с 1, а не с 0.

4.4.6.6 Использование памяти и myisamchk

Распределение памяти важно, когда Вы выполняете myisamchk. myisamchk использует не больше памяти, чем ему разрешено параметром -O. Если Вы собираетесь использовать myisamchk на очень больших файлах, Вы должны сначала решить, сколько памяти Вы можете ему выделить (лучше по максимуму). Значение по умолчанию составляет около 3 мегабайт для исправления ошибок. Используя большие значения, Вы можете ускорить работу myisamchk. Например, если Вы имеете больше, чем 32M RAM, Вы могли бы использовать параметры типа этих (в дополнение к любым другим параметрам, которые Вы определили):

shell> myisamchk -O sort=16M -O key=16M -O read=1M -O write=1M ...

Использование -O sort=16M достаточно для большинства случаев.

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

При ремонте myisamchk также требует много свободного места:

Если есть проблемы с местом на диске при ремонте, Вы можете попробовать использовать --safe-recover вместо --recover.

4.4.6.7 Использование myisamchk для ремонта

Если Вы выполняете mysqld с опцией --skip-locking (это является значением по умолчанию на некоторых системах, подобно Linux), Вы не может надежно использовать myisamchk, чтобы проверить таблицу, когда mysqld использует ту же самую таблицу. Если Вы можете убедиться, что никто не обращается к таблицам через mysqld в то время, как Вы выполняете myisamchk, Вы должны сделать только mysqladmin flush-tables прежде, чем начать проверять таблицы. Если Вы не можете гарантировать вышеупомянутое, следует завершить mysqld перед проверкой таблиц. Если Вы выполняете myisamchk в то время, как mysqld модифицирует таблицы, Вы можете получить предупреждение, что таблица разрушена, даже если это не так.

Если Вы не используете опцию --skip-locking, Вы можете использовать myisamchk, чтобы проверить таблицы в любое время. Пока Вы это делаете, вся клиентура, которая пробует модифицировать таблицу, будет ждать завершения проверки.

Если Вы используете myisamchk для ремонта или оптимизируете таблицы, Вы ДОЛЖНЫ всегда гарантировать, что сервер mysqld не использует таблицу (это также применяется, если Вы используете --skip-locking). Если Вы не завершаете mysqld, нужно по крайней мере скомандовать mysqladmin flush-tables перед запуском myisamchk.

Эта глава описывает, как проверять таблицы и иметь дело с нарушением целостности данных (не индексов) в базах данных MySQL. Если Ваши таблицы разрушились, Вы должны попробовать найти причину этого! Подробности в разделе "8.4.1 Что делать, если MySQL рухнул".

Формат файлов, в которых MySQL хранит свои таблицы, очень надежен, но всегда бывают случаи, когда таблицы будут подпорчены:

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

ФайлЗачем он нужен
tbl_name.frmОпределение таблицы (форма)
tbl_name.MYDДанные таблицы
tbl_name.MYIИндексный файл таблицы

Каждый из этих трех файлов портится различными способами, но проблемы происходят наиболее часто в файлах данных и индексных файлах.

myisamchk работает, создавая копию .MYD (файлов с данными) строка в строку. Он заканчивает стадию ремонта, удаляя старый файл .MYD и переименовывая новый файл к первоначальному имени файла. Если Вы используете опцию --quick, myisamchk не создает временный файл .MYD, а принимает, что файл .MYD правилен и только генерирует новый индексный файл без того, чтобы затронуть соответствующий ему файл .MYD. Это безопасно потому, что myisamchk автоматически обнаруживает повреждение файла .MYD и прерывает ремонт в этом случае. Вы можете также давать два параметра --quick. В этом случае myisamchk не прерывается на некоторых ошибках (подобно двойному ключу), но взамен пробует решать их, изменяя файл .MYD. Обычно использование двух параметров --quick нужно только, если Вы имеете слишком мало свободного дискового пространства, чтобы выполнить нормальный ремонт. В этом случае Вы должны по крайней мере сделать копию перед запуском myisamchk.

4.4.6.8 Как проверять таблицы на ошибки

Для проверки таблиц MyISAM используйте следующие команды:

myisamchk tbl_name
Это находит 99.99% всех ошибок. Единственное, что это не может находить, так это искажение, которое затрагивает ТОЛЬКО файл данных (такое дело является очень необычным). Если Вы хотите проверить таблицу, обычно надо выполнять myisamchk без параметров или с параметрами -s или --silent.
myisamchk -m tbl_name
А вот это находит уже 99.999% всех ошибок! Это проверяет сначала весь индекс на предмет ошибок, а затем читает все строки. Этот способ вычисляет контрольную сумму для всех ключей в строках и проверяет, что эта контрольная сумма соответствует контрольной сумме для ключей в индексном дереве.
myisamchk -e tbl_name
Это полная проверка всех данных (-e означает "extended check" или расширенную проверку). Выполняется тестовое чтение каждого ключа для каждой строки, чтобы проверить, что они в самом деле указывают на правильную строку. Это может занять МНОГО времени на большой таблице с богатым ассортиментом ключей. Здесь надо отметить, что myisamchk будет обычно останавливаться после первой ошибки, которую он найдет. Если Вы хотите получить большее количество информации, Вы можете добавлять опцию --verbose (-v). Это заставляет myisamchk работать первые 20 ошибок. При нормальном использовании простого вызова myisamchk (без параметров, кроме имени таблицы) достаточно.
myisamchk -e -i tbl_name
Подобно предыдущей команде, но опция -i предписывает, чтобы myisamchk напечатал также и некоторую информационную статистику.

4.4.6.9 Как ремонтировать поврежденные таблицы

Здесь я расскажу о ремонте таблиц системы MyISAM (расширения файлов .MYI и .MYD). Для этого потребуется программа myisamchk. При использовании таблиц системы ISAM (их файлы имеют расширения .ISM и .ISD), следует использовать программу isamchk.

Признаки разрушенной таблицы обычно выглядят как неожиданное завершение работы во время запроса и появление ошибок типа:

Во всех остальных случаях Вы должны отремонтировать Ваши таблицы. Программа myisamchk может обычно обнаружить и устранить большинство встречающихся ошибок.

Процесс ремонта включает до четырех стадий, описанных ниже. Прежде, чем Вы начнете ремонт, надлежит перейти командой cd в каталог баз данных и проверить разрешения файлов таблицы. Удостоверьтесь, что они читаемы тем пользователем, от имени которого выполняется mysqld (и Вами потому, что Вы должны обратиться к файлам, которые Вы проверяете). Если окажется, что Вы должны изменить файлы, они должны также быть перезаписываемы Вами, то есть Вы должны иметь право на запись.

Если Вы используете MySQL 3.23.16 и выше, то Вы можете пользоваться командами CHECK и REPAIR для проверки и восстановления таблиц системы MyISAM соответственно. Подробности в разделах "4.4.4 Синтаксис CHECK TABLE" и "4.4.5 Синтаксис REPAIR TABLE".

Раздел об администрировании таблиц включает в себя подробное описание всех необходимых для ремонта и проверки параметров. Подробности в разделе "4.4.6 Использование myisamchk для устранения повреждений".

Следующий раздел предназначен для случаев, когда вышеупомянутая команда терпит неудачу, или если Вы хотите использовать расширенные свойства, которые предоставляются утилитами isamchk/myisamchk.

Если Вы выполняете ремонт таблиц из командной строки, сначала завершите сервер mysqld. Обратите внимание, что, когда Вы даете команду mysqladmin shutdown на удаленной системе, mysqld будет работать некоторое время после mysqladmin, пока все запросы не будут остановлены, и все ключи сброшены на диск.

Стадия 1: Проверка Ваших таблиц

Запустите myisamchk *.MYI или (myisamchk -e *.MYI, если Вы имеете много времени). Используйте опцию -s (тихий режим работы), чтобы подавить вывод ненужной технической информации.

Если сервер mysqld выполнен, Вы должны использовать опцию --update, чтобы myisamchk отметил таблицу как 'проверено'.

Вы должны ремонтировать только те таблицы, для которых myisamchk объявляет ошибку. Для таких таблиц выполните стадию 2.

Если Вы получаете неожиданные ошибки при проверке (типа out of memory), или если есть сбои myisamchk, идите к стадии 3.

Стадия 2: Облегченный ремонт

Замечание: Если Вы хотите, чтобы восстановление шло намного быстрее, Вы должны добавить: -O sort_buffer=# -O key_buffer=# (здесь # около 1/4 доступной памяти) ко всем командам isamchk/myisamchk.

Сначала попробуйте myisamchk -r -q tbl_name (-r -q означает "быстрый режим восстановления"). Будет предпринята попытка отремонтировать индексный файл без того, чтобы затронуть файл данных. Если файл данных содержит все, что он должен содержать, и удаленные связи указывают на корректные координаты внутри файла данных, это должно сработать, и таблица будет выправлена. Если все получилось удачно, запустите восстанавление следующей таблицы. Иначе, используйте следующую процедуру ремонта:

  1. Сделайте копию файла данных перед продолжением ремонта.
  2. Используйте myisamchk -r tbl_name (-r означает "режим восстановления"). Это удалит неправильные записи и удаленные записи из файла данных и восстановит индексный файл.
  3. Если на предыдущих шагах произошли сбои, используйте myisamchk --safe-recover tbl_name. Безопасный режим восстановления использует старый метод восстановления, который обрабатывает несколько случаев, не поддающихся обычной процедуре ремонта (зато работает куда медленнее).

Стадия 3: Трудный ремонт

Если уж Вы дошли до этой стадии, значит первый блок длиной в 16 КБ в индексном файле разрушен или содержит неправильную информацию, или индексный файл отсутствует вообще. В этом случае необходимо создать новый индексный файл. Для этого:

  1. Переместите файл данных в какое-нибудь безопасное место.
  2. Используйте файл описания таблицы, чтобы создать новые (пустые) файлы данных и индекса:
    shell> mysql db_name
    mysql> SET AUTOCOMMIT=1;
    mysql> TRUNCATE TABLE table_name;
    mysql> quit
    
    Если используемая Вами версия языка SQL не поддерживает команду TRUNCATE TABLE, используйте вместо нее команду DELETE FROM table_name
  3. Скопируйте старый файл данных обратно на недавно созданный файл данных. Только не переместите старый файл обратно на новый файл: Вы сохраните копию в случае, если что-то пойдет не в ту сторону.

А теперь идите на стадию 2. Теперь myisamchk -r -q должен бы сработать нормально (это не должно быть бесконечным циклом).

Стадия 4: Очень трудный ремонт

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

  1. Восстановите файл описания из копии и идите на стадию 3. Вы можете также восстанавливать индексный файл и идти на стадию 2. В последнем случае Вы должны запустить myisamchk -r.
  2. Если Вы не имеете копии, но точно знаете, как таблица была создана, создайте копию таблицы в другой базе данных. Удалите новый файл данных, затем переместите описание и индексный файл из другой базы данных в ремонтируемую. Это дает Вам новое описание и индексные файлы, но оставляет единственный файл данных. Теперь идите на стадию 2, чтобы восстановить индексный файл.

4.4.6.10 Оптимизация таблиц

Из-за фрагментации записей и удаления данных возникает много пустого места в файле таблиц. Запустите myisamchk в режиме восстановления для его зачистки:

shell> myisamchk -r tbl_name

Вы можете оптимизировать таблицу, используя инструкцию SQL OPTIMIZE TABLE. OPTIMIZE TABLE ремонтирует таблицы, анализирует ключи и сортирует индексное дерево, чтобы создать более быстрые поисковые таблицы ключей. Совместная работа утилиты и сервера ничему не повредит, так как сервер имеет доступ к таблице, а ее сортировка идет в фоновом режиме. Подробности в разделе "4.5.1 Синтаксис OPTIMIZE TABLE".

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

-S, --sort-index
-R index_num, --sort-records=index_num
-a, --analyze

Полное описание опций приведено в разделе "4.4.6.1 Синтаксис обращения к myisamchk".

4.4.7 Установка режима сопровождения таблицы

Начиная с MySQL 3.23.13 Вы можете проверять таблицы MyISAM командой CHECK TABLE. Подробности в разделе "4.4.4 Синтаксис CHECK TABLE". Ремонтировать таблицы можно командой REPAIR TABLE. Подробности в разделе "4.4.5 Синтаксис REPAIR TABLE".

Неплохо проверять таблицы время от времени в целях профилактики, не дожидаясь, пока Ваша база данных взорвется. В этих целях рекомендуется использовать myisamchk -s для проверки таблиц. Опция -s предписывает myisamchk докладывать только об ошибках в случае их обнаружения.

Имеет смысл выполнять проверку при запуске сервера. Например, если компьютер перезагрузили посреди обращения к базе данных, надо при загрузке сервера проверить все таблицы, на которые можно было бы воздействовать (известны как ``expected crashed table''). Вы могли бы добавлять тест к safe_mysqld, который выполняет myisamchk, чтобы проверить все таблицы, которые изменились в течение последних 24 часов, если имеется старый файл `.pid' (process ID), оставшийся после перезагрузки. Файл `.pid' создается при запуске mysqld и автоматически удаляется при нормальном завершении. Присутствие `.pid' при запуске системы указывает, что mysqld был завершен неправильно.

Нужно проверить все таблицы, чье время модификации позже, чем у этого файла `.pid'.

Вы должны также проверять таблицы регулярно при нормальной работе системы. Например, еженедельную проверку можно поручить планировщику задач cron, добавив строку в файл crontab:

35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI

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

Для полной уверенности рекомендуется командой myisamchk -s проверять раз в сутки все таблицы, которые изменились за это время.

4.4.8 Получение информации о таблицах

Чтобы получить описание таблицы или статистику по ней, используйте команды, показанные ниже.

myisamchk -d tbl_name
Запустите myisamchk в режиме описания (``describe mode''), чтобы произвести описание Вашей таблицы. Если Вы запускаете сервер MySQL с опцией --skip-locking, myisamchk может выдать ошибку для таблицы, которая модифицируется во время его выполнения. Однако, поскольку myisamchk не вносит изменений в таблицу в режиме описания, то повредить ее он не может.
myisamchk -d -v tbl_name
Выдать более подробные сведения о таблице.
myisamchk -eis tbl_name
Показывает только наиболее важную информацию из таблицы. Медленно, потому что в этом случае приходится читать целую таблицу.
myisamchk -eiv tbl_name
Подобно -eis, но сообщает Вам, что сделается.

Пример вывода myisamchk -d:

MyISAM file:     company.MYI
Record format:   Fixed length
Data records:    1403698  Deleted blocks:         0
Recordlength:    226

table description:
Key Start Len Index   Type
1   2     8   unique  double
2   15    10  multip. text packed stripped
3   219   8   multip. double
4   63    10  multip. text packed stripped
5   167   2   multip. unsigned short
6   177   4   multip. unsigned long
7   155   4   multip. text
8   138   4   multip. unsigned long
9   177   4   multip. unsigned long
    193   1           text

Пример вывода myisamchk -d -v:

MyISAM file:         company
Record format:       Fixed length
File-version:        1
Creation time:       1999-10-30 12:12:51
Recover time:        1999-10-31 19:13:01
Status:              checked
Data records:           1403698  Deleted blocks:              0
Datafile parts:         1403698  Deleted data:                0
Datafilepointer (bytes):      3  Keyfile pointer (bytes):     3
Max datafile length: 3791650815  Max keyfile length: 4294967294
Recordlength:               226

table description:
Key Start Len Index   Type                  Rec/key     Root Blocksize
1   2     8   unique  double                      1 15845376      1024
2   15    10  multip. text packed stripped        2 25062400      1024
3   219   8   multip. double                     73 40907776      1024
4   63    10  multip. text packed stripped        5 48097280      1024
5   167   2   multip. unsigned short           4840 55200768      1024
6   177   4   multip. unsigned long            1346 65145856      1024
7   155   4   multip. text                     4995 75090944      1024
8   138   4   multip. unsigned long              87 85036032      1024
9   177   4   multip. unsigned long             178 96481280      1024
    193   1           text

Пример вывода myisamchk -eis:

Checking MyISAM file: company
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:  98%  Packed:   17%

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Record blocks:    1403698    Delete blocks:        0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1626.51, System time 232.36
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 627, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 639, Involuntary context switches 28966

Пример вывода myisamchk -eiv:

Checking MyISAM file: company
Data records: 1403698   Deleted blocks:       0
- check file-size
- check delete-chain
block_size 1024:
index  1:
index  2:
index  3:
index  4:
index  5:
index  6:
index  7:
index  8:
index  9:
No recordlinks
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 2
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
- check data record references index: 3
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
- check data record references index: 5
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 6
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 7
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 8
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 9
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:   9%  Packed:   17%

- check records and index references
[LOTS OF ROW NUMBERS DELETED]

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Record blocks:    1403698    Delete blocks:        0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1639.63, System time 251.61
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
Blocks in 4 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 10604, Involuntary context switches 122798

Размеры файлов данных и индекса, использованных в этих примерах:

-rw-rw-r--   1 monty    tcx     317235748 Jan 12 17:30 company.MYD
-rw-rw-r--   1 davida   tcx      96482304 Jan 12 18:35 company.MYM

Теперь немного о терминологии, использованной в этом выводе. Здесь "keyfile" означает индексный файл. "Record" и "row" являются синонимами.

ISAM file
Имя файла ISAM (индексного).
Isam-version
Версия формата ISAM. Сейчас обязательно 2.
Creation time
Время создания файла.
Recover time
Когда файл индекса или данных был восстановлен в последний раз.
Data records
Сколько записей в таблице.
Deleted blocks
Сколько удаленных блоков все еще занимают место. Вы можете оптимизировать таблицу, чтобы минимизировать это место. Подробности в разделе "5.4.3 Оптимизация таблиц".
Datafile: Parts
Для динамического формата записи, это указывает, сколько там блоков данных. Для оптимизированной таблицы без фрагментированных записей это равно Data records.
Deleted data
Сколько байт неисправленных удаленных данных. Вы можете оптимизировать таблицу, чтобы минимизировать это место. Подробности в разделе "4.4.6.10 Оптимизация таблиц".
Datafile pointer
Размер указателя файла данных в байтах. Обычно это от 2 до 5 байт. Большинство таблиц работает с 2 байтами. Для фиксированных таблиц это адрес записи. Для динамических таблиц это адрес байта.
Keyfile pointer
Размер указателя индексного файла в байтах. Это обычно от 1 до 3 байт. Большинство таблиц обходится 2 байтами, но это значение вычисляется MySQL автоматически. Это всегда адрес блока.
Max datafile length
Максимальная длина табличного файла данных (файла .MYD).
Max keyfile length
Максимальная длина табличного файла индексов (файла .MYI).
Recordlength
Сколько места занимает каждая запись в байтах.
Record format
Формат, используемый, чтобы хранить строки таблиц. Примеры, показанные выше, используют Фиксированную длину (Fixed length). Другие возможные значения: Сжатый (Compressed) и Упакованный (Packed).
table description
Список всех ключей в таблице. Для каждого ключа выводится некоторая информация низкого уровня:
Key
Код ключа.
Start
Где в записи начинается эта индексная часть.
Len
Длина индексной части. Для упакованных чисел это всегда полная длина столбца. Для строк это может быть короче, чем полная длина индексированного столбца потому, что Вы можете индексировать префикс строкового столбца.
Index
Уникальный (unique) или множественный (multip). Указывает, может ли одно значение существовать многократно в этом индексе.
Type
Какой тип данных имеет эта индексная часть. Это тип данных для ISAM с опциями packed, stripped или empty.
Root
Адрес корня индекса.
Blocksize
Размер каждого индексного блока. По умолчанию 1024, но значение может быть изменено во времени компиляции.
Rec/key
Это статистическое значение, используемое оптимизатором. Оно сообщает, сколько записей приходится на каждое значение ключа. Уникальный ключ всегда имеет значение 1. Это может модифицироваться после того, как таблица загружена (или значительно изменена) с помощью myisamchk -a. Если это не модифицируется вообще, значение по умолчанию 30.
В первом примере выше девятый ключ состоит из двух частей.
Keyblocks used
Какой процент от keyblocks используется. Так как таблица, использованная в примерах, была только что реорганизована с помощью myisamchk, значения очень высоки (очень близки к теоретическому максимуму).
Packed
MySQL пробует упаковывать ключи общим суффиксом. Это может использоваться только для ключей типов CHAR/ VARCHAR/DECIMAL. Для длинных строк это может существенно экономить место. В третьем примере выше четвертый ключ длиной 10 символов, и на нем достигнуто уменьшение в 60%.
Max levels
Размер структуры B-tree для этого ключа.
Records
Сколько строк находится в таблице.
M.recordlength
Средняя длина записи. Для таблиц с записями фиксированной длины это точная длина записи.
Packed
MySQL срезает пробелы с конца строк. Значение Packed указывает, сколько процентов на этом сэкономлено.
Recordspace used
Какой процент файла данных используется.
Empty space
Какой процент файла данных не используется.
Blocks/Record
Среднее число блоков, соответствующих записи (то есть, из скольких связей составлена фрагментированная запись). Всегда 1 для таблиц фиксированного формата. Это значение должно оставаться так близко к 1.0, как только возможно. Если оно становится слишком большим, Вы можете реорганизовать таблицу с помощью myisamchk. Подробности в разделе "4.4.6.10 Оптимизация таблиц".
Recordblocks
Сколько блоков (связей) используются. Для фиксированного формата это равно числу записей.
Deleteblocks
Сколько блоков (связей) сейчас удалено.
Recorddata
Сколько байтов в файле данных используются.
Deleted data
Сколько байтов в файле данных удалено.
Lost space
Если запись модифицируется к более короткой длине, сколько-то места будет потеряно. Это сумма всех таких потерь в байтах.
Linkdata
Когда используется динамический формат таблицы, фрагменты записей связаны с указателями (от 4 до 7 байт каждый). Linkdata равна сумме памяти, используемой всеми такими указателями.

Если таблица была сжата с помощью myisampack, то команда myisamchk -d выведет дополнительную информацию относительно каждого столбца таблицы. Подробности в разделе "4.7.4 Генератор сжатых таблиц MySQL только для чтения".




Партнёры:
PostgresPro
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2024 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру