Регламентное обслуживание базы данных MySQL

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

Классифицируем средства администрирования MySQLИтак, какие же средства имеет MySQL для этих целей:

• механизм администрирования с помощью специальных SQL-запросов;
• входящие в состав поставки SQL-сервера утилиты для обслуживания базы;
• встроенный механизм отлавливания ошибок и некорректных операций.

Административные функции встроенного языка запросов MySQLДля того чтобы восстанавливать таблицы базы данных при их «падении», выполнять процессы оптимизации и обслуживания, в MySQL существует несколько команд. Перечислим их с необходимыми пояснениями.

• CHECK TABLES «параметр»

  Выполняет проверку физической целостности таблицы. «Параметр» представляет собой одно из значений (в порядке уменьшения эффективности применения):

  • EXTENDED — более медленная, но и более эффективная проверка таблиц;
  • MEDIUM — проверка физических ошибок и контрольных сумм таблиц. Считается по умолчанию, если не задан никакой параметр вообще;
  • CHANGED — проверить таблицы, изменившиеся после последней проверки (или некорректно закрытые);
  • FAST — быстрая проверка таблиц, которые закрыты нормально;
  • QUICK — проверка индексов.

• OPTIMIZE TABLE table_name (OPTIMIZE TABLES table_1 ... table_n)

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

• REPAIR TABLE table_name (REPAIR TABLES table_1 ... table_n) «параметр»

  Восстановление таблиц данных. При некорректном выключении сервера в файлы таблиц может записаться неправильная информация. Команда предназначена для восстановления таблиц базы при аппаратных сбоях. «Параметр» может быть:

  • EXTENDED — восстанавливает данные и индексы таблиц;
  • QUICK — восстанавливает только индексы;
  • USE_FRM — при этом таблицы данных будут восстановлены исходя из файлов форматов таблиц базы данных. Помогает восстановить частично поврежденные таблицы, насколько это возможно.

• BACKUP TABLE (BACKUP TABLES table_1 ... table_n) TO директория

  Запрос выполняет копирование файлов структуры и данных указанных таблиц (индексы при этом не копируются) в указанный каталог на сервере. Особенность запроса — он выполняется только для таблиц MyISAM. Запрос равносилен физическому копированию таблиц на серверном узле. Таблицы блокируются по мере копирования. В случае если необходимо сделать «снимок» состояния базы, необходимо заблокировать таблицы до начала выполнения запроса командой LOCK TABLES, а после него разблокировать командой UNLOCK TABLES. Параметр «директория» указывает на каталог, доступный для записи процессу — владельцу базы данных.

• RESTORE TABLE (RESTORE TABLES table_1 ... table_n) FROM директория

  Загрузка данных из копии, предварительно сделанной командой BACKUP. Параметр «директория» указывает на каталог, доступный процессу — владельцу базы данных.

Запросы CHECK, REPAIR возвращают строки данных с результатами восстановления, проверки или оптимизации таблиц базы данных.

Пример реализации на PHP процедуры проверкиОптимальным способом тестирования вашей базы данных является регулярная проверка таблиц. Причем здесь возможны варианты. Можно давать команды CHECK по всем таблицам базы последовательно. Если в результате этого запроса будет содержаться негативный результат, то запускается операция восстановления базы (REPAIR). После того как выполнена проверка таблицы, проводится ее оптимизация.

 
<? 
#!/usr/bin/php -q 
$database="test"; 
mysql_connect("localhost", "root", "$database"); 
$res=mysql_query("SHOW TABLES IN $database;"); 
while ( $o=mysql_fetch_row($res) ) 
{ 
 $res_ch=mysql_query("CHECK TABLE $database.$o[0] EXTENDED;"); 
 $o_ch=mysql_fetch_row($res_ch); 
 $o_rp[3]="No"; 
 if ( $o_ch[3]!="OK" ) 
 { 
 $res_rp=mysql_query("REPAIR TABLE $database.$o[0] EXTENDED;"); 
 $o_rp=mysql_fetch_row($res_rp); 
 } 
 $res_op=mysql_query("OPTIMIZE TABLE $database.$o[0] EXTENDED;"); 
 $o_op=mysql_fetch_row($res_op); 
 echo "$o[0] CHECK:$o_ch[3] REPAIR: $o_rp[3], OPTIMIZE: $o_op[3]\n"; 
} 
?> 

Приведенный скрипт можно разместить в вашей домашней директории, прописать его в планировщике, после чего быть уверенным, что ваш сайт не перестанет работать из-за сбоев на сервере.

Встроенная утилита для обслуживания базы MySQL

Все функции проверки и восстановления базы данных MySQL сосредоточены в функциональности утилиты mysqlcheck. Она имеет большое количество ключей, при запуске с которыми способна оптимизировать, проверять и восстанавливать базы данных MySQL-сервера. Утилита поддерживает аутентификацию на удаленных хостах и стандартные параметры для работы с протоколом SSL^*. Часть параметров утилиты стандартна для утилит комплекта MySQL-сервера. Речь идет о настройках параметров соединения. Немаловажная особенность — версии утилиты есть также и для Windows-платформы.

Параметры запуска mysqlcheckВсе параметры командной строки утилиты mysqlcheck можно разбить на четыре группы.

• Общие параметры, которые возможно использовать с приведенными ниже группами параметров:

  • --help — справка по ключам и режимам запуска программы;
  • --host= (-h) — хост, на котором расположена база данных (локально следует указывать localhost или 127.0.0.1);
  • --user= (-u) — имя пользователя;
  • --verbose (-v) — выводить дополнительную информацию про ход выполнения программы;
  • --silent (-s) — не выводить ничего, кроме ошибок (полезно для записи вывода команды в лог-файл);
  • --force (-f) — при возникновении ошибки выполнение утилиты прекращается (если указать это параметр, то выполнение пройдет до конца);
  • --all-in-1 (-1) — выполнять все запросы в одной группе запросов (полезно для проверки одновременно всех);
  • --extended (-e) — выполняется полная проверка или восстановление таблиц базы данных;
  • --databases (-B) — проверить несколько таблиц.

• Параметры проверки таблиц:

  • --check (-c) — проверить таблицу на наличие ошибок;
  • --check-only-changed (-C) — проверить только измененные таблицы (аналогично параметру CHANGED в запросе CHECK TABLE[S]);
  • --medium-check (-m) — аналогично параметру MEDIUM в запросе CHECK.

• Параметры для анализа таблиц:

  • --analyse — выполнить анализ таблицы с помощью запроса ANALYZE TABLE[S].

• Параметры для восстановления таблиц:

  • --auto-repair — автоматическое восстановление поврежденных таблиц (действия, аналогичные тем, которые выполняет уже приведенный в этой статье скрипт PHP для обслуживания таблиц);
  • --repair (-r) — выполнить исправление таблиц указанных баз.

Пример использования утилиты mysqlcheck.

• mysqlcheck.exe -h localhost -u root -1 -r -e test price_firms

  Восстановление (с наибольшими возможностями) таблицы price_firms из базы данных test (если даже таблицы не повреждены, этот запрос не повредит ваши данные).

• mysqlcheck.exe -h localhost -u root -1 -c -e test

  Проверка всех таблиц базы данных test.

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

По умолчанию механизм фиксирования длительных запросов не включен. Для того чтобы его включить, следует в строке инициализации демона MySQL указать параметр --log-slow-queries либо же в конфигурационном файле my.ini (my.cnf) указать в разделе [mysqld] параметр log-slow-queries.

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

Ссылки по теме

• MySQL — система управления базами данных
• Справка по mysqlcheck