ПРОВЕРКА И ВОССТАНОВЛЕНИЕ ТАБЛИЦ БАЗЫ ДАННЫХ

Повреждения в таблицах MyISAM происходят вследствие событий, которые невозможно избежать. Различные аппаратные сбои могут оказать самое непредсказуемое влияние на базу данных. Например, если жесткий диск выйдет из строя, данные окажутся полностью потерянными. Неожиданное выключение системы из-за сбоя питания может привести к тому, что изменения в таблицу будут внесены не полностью. Даже если уничтожить серверный процесс по команде kill, у него не будет возможности корректно завершить свою работу.

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

Существуют два способа проверки и восстановления таблиц. Первый — с помощью специальных инструкций, второй — с помощью утилиты myisamchk. Соответствующие инструкции называются CHECK TABLE, REPAIR TABLE и OPTIMIZE TABLE. Они достаточно удобны, поскольку выполняются в рамках серверного процесса. В этом смысле они ничем не отличаются, к примеру, от инструкции SELECT. Утилита myisamchk обладает рядом дополнительных возможностей, которые в ряде ситуаций оказываются весьма удобными.

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

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

Возможно, имеет смысл изменить сценарий safe_MySQL таким образом, чтобы при запуске сервера выполнялись инструкции проверки таблиц. Файл, содержащий такие инструкции, задается с помощью опции –init-file. Если повреждения произошли из-за того, что сервер внезапно прекратил работу, они будут немедленно исправлены.

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

Таблицы снабжены флагом, указывающим, изменилось ли содержимое таблицы с момента последней проверки. Инструкция CHECK TABLE пропустит неизмененные таблицы при наличии ключевого слова CHANGED. В утилите myisamchkсоответствующий режим включается с помощью опции –check-only-changed. Особым образом помечаются также неправильно закрытые таблицы. Чтобы проверить только их, укажите флаг FAST (инструкция CHECK TABLE) или опцию –fast(утилита myisamchk).

По умолчанию утилита myisamchk ищет повреждения только в индексных файлах. В инструкции CHECK TABLE этот режим включается с помощью флага QUICK. Сама инструкция CHECK TABLE по умолчанию проверяет не только индексы, но и неправильные ссылки на удаленные записи. В утилите myisamchk этот режим включается с помощью опции –medium-check. Расширенный режим проверки задается флагом EXTEND и опцией –extended-check. В этом случае будут проверяться все индексируемые значения.

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

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

mysql>CHECK TABLE courses;
+--------------+-------+-----------+---------------------------------------------+
| Table        |  Op   | Msg_type  | Msg_text                                    | 
+--------------+-------+-----------+---------------------------------------------+
| courses      | check | error     | Size of indexfile is: 1924 Should be: 2048  |
| test.courses | check | error     | corrupt                                     |
+--------------+-------+-----------+---------------------------------------------+
2 rows in set (0.00 sec)
                 
mysql>REPAIR TABLE courses;
+--------------+--------+-----------+----------+
| Table        |  Op    | Msg_type  | Msg_text |
+--------------+--------+-----------+----------+
| test.courses | repair | status    | ok       |
+--------------+--------+-----------+----------+
1 row in set (0.08 sec)  
   

Таблицы можно проверять, когда сервер запущен. Программа MySQL не будет пытаться их восстановить. Но если обнаруживается поврежденная таблица, программа запрещает потокам обращаться к ней до тех пор, пока таблица не будет восстановлена. Для восстановления требуется получить монопольный доступ к таблице. В этой ситуации служебными инструкциями пользоваться удобнее, чем утилитой myisamchk, так как MySQL сможет заблокировать другие потоки на время восстановления таблицы. Утилита myisamchk может работать таким образом, только если операционная система поддерживает блокировку файлов. В Linux соответствующих функций нет, поэтому перед восстановлением таблиц нужно останавливать сервер.

Инструкция REPAIR TABLE устраняет повреждения в таблице. То же самое делает утилита myisamchk, при наличии опции –recover. Программа MySQL поддерживает три типа процедур восстановления: быструю, обычную и безопасную. В первом случае устраняются лишь проблемы с индексами. Во втором случае исправляется также большинство ошибок в табличном файле. В безопасном режиме таблица проверяется строка за строкой, а индексный файл создается заново. Это наиболее длительная процедура.

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

Таблицы с записями переменной длины неизбежно оказываются фрагментированными. Это происходит, когда обновляемая запись не помещается в отведенном для нее пространстве. В результате снижается производительность операций выборки, поскольку программа вынуждена искать запись в двух и более точках файла. Инструкция OPTIMIZE TABLE удаляет из таблицы пустые участки и осуществляет пересортировку записей. Аналогичные действия выполняет утилита myisamchk при наличии опции –analyze. Инструкция OPTIMIZE TABLE также сортирует индексы (соответствующая опция утилиты myisamchk называется –sort-index).

Резервное копирование и восстановление

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

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

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

Помните общие правила обращения с резервными копиями. Если они хранятся в той же файловой системе, что и сама база данных, то данные не защищены от сбоев файловой системы. Отсюда правило: копии должны находиться на отдельном носителе. Храните их на перезаписываемом компакт-диске, магнитной ленте или другом жестком диске. Резервные копии могут храниться дома у начальника или администратора компании. Их можно также пересылать по сети в другую систему. С помощью Internet это делать не сложно.

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

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

Какой бы метод ни был выбран, не забудьте защитить таблицы от изменений на время резервного копирования. Если копируются табличные файлы, следует остановить сервер. В остальных случаях достаточно поставить блокировки чтения с помощью инструкции LOCK TABLES и выполнить инструкцию FLUSH TABLES. Последняя необходима для того, чтобы все изменения индексов были записаны в таблицы. Наличие блокировок чтения позволит другим потокам параллельно обращаться к таблицам с запросами на выборку.

Инструкции BACKUP TABLE и RESTORE TABLE копируют табличные файлы в указанный каталог. Естественно, серверный процесс должен иметь право записи в этот каталог. Программа MySQL копирует туда файлы с расширениями .frm и .MUD. Индексный файл (.MYI) можно воссоздать на основании первых двух, что позволит сэкономить место в архиве. В листинге 4.2 показан пример архивирования таблицы.

mysql>BACKUP TABLE dictionary TO '/tm/backup';
+-----------------+--------+-----------+------------------------+
| Table           |  Op    | Msg_type  | Msg_text               |
+-----------------+--------+-----------+------------------------+
| test.dictionary | backup | status    | ok                     |
+-----------------+--------+-----------+------------------------+
1 rows in set (0.27 sec)
   

Функции копирования файлов предоставляются операционной системой, поэтому данный способ создания резервных копий является самым быстрым. Таблица dictionary, скопированная в листинге 4.2, содержит более 100000 записей, а файл данных занимает почти 3 Мбайт. Как видите, процедура архивирования такой таблицы заняла менее секунды.

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

Инструкция RESTORE TABLE копирует архивные файлы в каталог базы данных и перестраивает индексы. Таблица не должна существовать на момент восстановления. В случае необходимости можно удалить ее с помощью инструкции DROP TABLEили же вручную удалить табличные файлы.

В листинге 4.3 показаны результаты восстановления таблицы dictionary, резервная копия которой была создана в листинге 4.2. Обратите внимание на то, что процесс восстановления длился гораздо дольше, чем архивирование. Причина в том, что на перестройку индексов уходит много времени.

mysql> RESTORE TABLE dictionary FROM '/tmp/backup';
+-----------------+---------+-----------+--------------------------+
| Table           |  Op     | Msg_type  | Msg_text                 |
+-----------------+---------+-----------+--------------------------+
| test.dictionary | restore | status    | ok                          |
+-----------------+---------+-----------+--------------------------+
1 rows in set (1 min 22.24 sec)
   

Если резервные копии создаются вручную, то в архив можно также включить индексный файл. В этом случае в процессе восстановления таблицы индексный файл будет просто скопирован в каталог базы данных. Тем не менее его всегда можно воссоздать с помощью инструкции REPAIR TABLE. Предположим, таблица dictionary была полностью утеряна. Процесс ее восстановления начнем с копирования frm-файла обратно в каталог базы данных. Создать пустые файлы данных и индексов можно с помощью инструкции TRUNCATE TABLE. Затем необходимо скопировать старый файл данных поверх нового. После этого вводится инструкция REPAIR TABLE. В листинге 4.4 показано, как программа MySQLобнаруживает расхождение в количестве записей и перестраивает индексы.

mysql> REPAIR TABLE dictionary;
+-----------------+---------+-----------+-----------------------------------------+
| Table           |  Op     | Msg_type  | Msg_text                                |
+-----------------+---------+-----------+-----------------------------------------+
| state           | repair  | warning   | number of rows changed from 0 to 104237 |
| test.state      | repair  | status    | ok                                      |
+-----------------+---------+-----------+-----------------------------------------+
2 rows in set (1 min 25.12 sec)
   

Для безопасного создания резервных копий лучше пользоваться специальной программой, чем делать все вручную. С этой целью в дистрибутив MySQL входит Perl-сценарий mysqlhotcopy. В листинге 4.5 показано, как с его помощью создаются копии таблиц привилегий. Команда ls позволяет убедиться, что все файлы, в том числе индексные, на месте.

# mysqlhotcopy MySQL /trap/hc
Locked 6 tables in 0 seconds.
Flushed tables(MySQL.columns_priv, MySQL.db, MySQL.func, MySQL.host,
  MySQL.tables_priv, MySQL.user) in 0 seconds.
Copying 18 files…
Copying indices for 0 files…
Unlocked tables.
Mysqlhotcopy copied 6 tables |(18 files) in 1 second (1 seconds overall).
# ls /tmp/hc/mysql
columns_priv.MYD db.MYD func.MYD host.MYD tables_priv.MYD user.MYD
columns_priv.MYI db.MYI func.MYI host.MYI tables_priv.MYI user.MYI
columns_priv.frm db.frm func.frm host.frm tables_priv.frm user.frm
   

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

Формат табличных файлов понятен только программе MySQL. Если же создать SQL-образы таблиц, то их можно будет перенести в другие СУБД. Кроме того, в некоторых ситуациях полезно просматривать такие SQL-инструкции. Предположим, к примеру, что потеря данных оставалась незамеченной на протяжении нескольких месяцев. Возможно, пользователи удалили какие-то записи и лишь позднее обнаружили, что это было сделано неправильно. Нужно восстановить только удаленные записи, но не известно, когда точно они были удалены. Если резервные копии хранятся в формате SQL, можно просмотреть архивы и поискать, когда последний раз встречались требуемые записи. Недостатком такого способа резервного копирования является то, что процедура восстановления занимает много времени, поскольку программа MySQL вынуждена выполнять каждую инструкцию из архива.

Для создания sql-образа таблицы предназначена утилита mysqldump. Она записывает текст инструкций в поток stdout, поэтому нужно перенаправить результаты ее работы в файл. 

Друзья! Приглашаем вас к обсуждению. Если у вас есть своё мнение, напишите нам в комментарии.