Книги, научные публикации
Pages: | 1 | ... | 3 | 4 | 5 | 6 | 7 | ... | 11 | мтт. ...
-- [ Страница 5 ] --Часть II. Программные интерфейсы СУБД MySQL Для того чтобы проверить этот сценарий в действии, его можно загруить самостоятельно (приложение А, "Получение и инсталляция проаммного обеспечения") или создать его с помощью обычного текстового фактора, сделав затем исполнимым. Конечно, может понадобится изменить по крайней мере один из параметров подключения (имя узла, имя базы данных, имя пользователя, пароль). Это справедливо и для других сценариев, приведенных в этой главе. По умолчанию привилегии всех сценариев присвоены таким образом, что право на их чтение предоставлено только вам. Их надо сохранить, даже если вы ввели свое собственное имя пользователя и пароль для СУБД MySQL. Позже в разделе "Определение параметров соединения" будет показано, как можно получить парчметпьт из файла вместо того, чтобы вводить их прямо в сценарий. Теперь рассмотрим сценарий построчно. Первая строка содержит стандартный индикатор "местоположение Perl":
#! /usr/bin/perl Эта строка присутствует в каждом сценарии, представленном в этой главе. Уместно будет включить в сценарий короткое описание его назначения, поэтому следующая строка содержит намек на то, чем "занимается" данный сценарий: # dump_members - список членов "Исторической Лиги" Строки, начинающиеся с символов "#", считаются комментариями. Общепринятой практикой является дополнение сценариев комментариями с разъяснениями работы сценариев. Далее следуют такие строки: use DBI;
use struct;
Строка use DBI сообщает интерпретатору Perl, что ему необходимы ресурсы модуля DBI. Без этой строки при попытке сделать в сценарии что-либо, связанное с интерфейсом, происходит ошибка. При этом нет надобности указывать, какой модуль уровня DBD нужен. Интерфейс DBI инициирует нужный драйвер DBD в момент подключения к базе данных. Строка use struct сообщает интерпретатору Perl, что перед использованием переменных их требуется декларировать. Эту строку в сценарии можно опустить, но такая возможность очень полезна при диагностировании ошибок, так что я настоятельно рекомендую всегда ее включать в исходный текст. Например, если переменная $my_var была объявлена при включенном режиме struct mode, но потом было ошибочное обращение к переменной $mv_var, во время выполнения сценария будет получено следующее сообщение:
Global symbol "$mv_var" requires explicit package name at line n Для глобального символа "$mv_var" требуется явное объявление имени в строке n Глава 7. Программный интерфейс Perl API Когда вы увидите это сообщение, возникнет мысль, что вы никогда не использовали переменную с подобным именем! А когда вы посмотрите в своем сценарии на строку с номером п, вы, конечно, увидите, в че|л заключается проблема и исправите ошибку. Не работая в режиме strucjt mode, интерпретатор Perl не будет сообщать об ошибке, допущенной _ с переменной $mv_var;
а просто создаст новую переменную с таким именем, со значением undef (неопределенный), "тихо" использует ее, и вы будете сильно удивлены тем, что ваш сценарий не работает. При работе в режиме struct mode придется объявить все переменные, которые используются сценарием: my ($dsn) = "DBI:mysql:samp_db:localhost";
# наименование источника # данных my (Suser_name) = "paul";
# имя пользователя my ($password) = "secret";
# пароль my (Sdbh, $ s t h ) ;
# дескрипторы базы данных и операторов my ( @ a r y ) ;
# массив строк, возвращаемых запросом Теперь мы готовы к подключению к базе данных:
# подключение к базе данных $dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 ));
Вызов connect () производится как DBI->connect () потому, что этот метод присущ классу DBI. Вам совсем не обязательно знать, что это значит, это просто такой объектно-ориентированный жаргон, который призван сбить вас с толку. (Если вы хотите знать, то это значит, что метод connect О "принадлежит" интерфейсу DBI.) Метод connect () принимает несколько аргументов. Х Источник данных. (Обычно это называется "data source name" (имя источника данных) или DSN.) Форматы источника данных определены требованиями к используемому модулю DBD. Форматы драйвера СУБД MySQL могут включать что-нибудь подобное:
"DBI:mysql:db_name" "DBI:mysql:db_name:host_name" В первом случае имя узла по умолчанию принимается равным localhost. (В действительности существуют и другие допустимые исходные форматы данных, которые будут изложены позднее в разделе "Определение параметров соединения".) Представление "DBI" дается в верхнем регистре, но "mysql" должно быть представлено в нижнем регистре. Х Ваше имя пользователя и пароль. Х Дополнительный аргумент, отражающий дополнительные атрибуты соединения. Он управляет поведением DBI при обработке ошибок интерфейсом, и загадочная конструкция, указанная здесь, активизирует атрибут RaiseError. Это вынуждает DBI проверять возникающие ошибки, которые связаны с базами данных, выдавать диагностические сообщения и выходить при обнаружении таковых. 338 Часть II. Программные интерфейсы СУБД MySQL (Вот почему полностью отсутствует программный код проверок на ошибки в сценарии dump_members;
интерфейс DBI берет все это на себя.) В разделе "Обработка ошибок" изложены и другие способы реакции на ошибки. В случае успешного завершения работы метода connect () переменной $dbh возвращается дескриптор базы данных. (Если работа метода connect () завершается ошибкой, он возвращает undef. Однако с атрибутом RaiseError в сценарии метод connect () не возвращает управление, а интерфейс DBI выдаст сообщение об ошибке и завершает свое выполнение в случае ошибки.) После подключения к базе данных сценарий dump_members генерирует запрос SELECT для выборки списка членов, а затем выполняет цикл по обработке возвращаемых запросом строк. Эти строки и составляют результирующий набор. Для выполнения запроса сначала его нужно создать, а затем выполнить:
# создать запрос $sth = $dbh->prepare ("SELECT last_name, first_name, suffix, email,". "street, city, state, zip, phone FROM member ORDER BY last_name");
S sth->execute();
Метод prepare () вызывается с использованием дескриптора базы данных. Он передает оператор SQL драйверу для предварительной обработки перед выполнением. Одни драйверы действительно производят какие-то операции на этой фазе. Другие просто запоминают эту информацию до момента вызова метода execute () для выполнения оператора. Значение, возвращаемое методом prepare (), является дескриптором оператора $sth в случае успешного завершения или undef в случае ошибочного. Дескриптор оператора используется для дальнейшей обработки, имеющей отношение к данному оператору. Прошу обратить внимание, что запрос не завершается символом "точка с запятой". В процессе длительного использования программы mysql у вас, без сомнения, выработалась привычка завершать операторы языка SQL символом ";
". Однако эту привычку надо забыть, так как интерфейс DBI расценивает такой синтаксис как ошибочный. Это справедливо и при добавлении к запросам последовательности "\д". При запуске метода без передачи ему каких-либо параметров можно опустить скобки. Вот два абсолютно аналогичных вызова:
$sth->execute (};
$sth->execute;
Я предпочитаю указывать скобки потому, что это позволяет отличать метод от переменной. Вы можете иметь другие предпочтения. После вызова execute () обработаем полученный список. В сценарии dump_members цикл выборки строк просто распечатывает содержимое каждой строки: Глава 7. Программный интерфейс Perl API # прочесть результаты, возвращаемые запросами, и очистить память while (@ary = Ssth->fetchrow_array()) { print join ( " \ t ", @ a r y ), " \ n " ;
} $sth->finish() ;
Метод f etchrowarray () возвращает массив, содержащий значения столбцов текущей строки и пустую строку, когда таких строк нет. Таким образом, цикл последовательно выбирает строки, возвращаемые оператором SELECT, и распечатывает каждый, разделяя значения столбцов символами табуляции. Пустые значения в базе данных возвращаются в сценарий Perl как значения undef, а не слово "NULL". Обратите внимание, что символы перевода на новую строку ("\t" и "\п") заключены в двойные кавычки. Управляющие последовательности в Perl интерпретируются только тогда, когда они заключены в двойные кавычки. При наличии одинарных кавычек выводимые строки будут заполнены литеральными образцами строк "\t" и "\п". После завершения цикла выборки вызов метода f i n i s h () "дает знать" интерфейсу DBI, что дескриптор оператора больше не нужен, и все временные ресурсы, отданные под него, могут быть освобождены. Не обязательно вызывать метод f i n i s h (), если выбрана только часть результирующего набора (неважно Ч намеренно или по причине сбоя). Однако указание метода f i n i s h (} безопасно после цикла fetch и я нахожу, что значительно проще его вызвать и завершить операцию, чем специально писать программу, необходимую для анализа ситуации, когда вызов метода f i n i s h () необходим и когда он не нужен. После того как список членов будет распечатан, можно отключиться от сервера и завершить работу сценария: $dbh->disconnect();
exit(0);
Сценарий dump_members иллюстрирует несколько самых характерных для большинства программ, работающих с интерфейсом DBI, концепций. Этого вполне достаточно для того, чтобы начать писать свои программы, работающие с интерфейсом DBI. Например, чтобы распечатать содержимое другой таблицы, все, что необходимо сделать, это изменить текст оператора SELECT, который будет передан методу prepare (). Конечно, если есть насущная необходимость посмотреть, каким образом работает этот метод, можно прямо перейти к изучению раздела "Подготовка интерфейса к работе". В нем проиллюстрировано создание списка членов для программы ежегодного банкета "Исторической Лиги" и списка членов лиги. Однако интерфейс DB1 обладает и многими другими полезными возможностями. Их детальное изложение можно найти в последующих разделах. Там и можно посмотреть, каким образом в Perl можно выполнять более сложные операции, чем простой оператор SELECT. 340 Часть II. Программные интерфейсы СУБД MySQL Обработка ошибок Сценарий dump_members включает атрибут обработки ошибок RaiseError при инициализации метода connect О. Таким образом, возникновение ошибки автоматически прервет выполнение сценария и выдаст диагностическое сообщение. Ошибки можно обрабатывать и другим способом. Например, обработку ошибочных ситуаций можно запрограммировать, не полагаясь на возможности интерфейса DBI. Для того чтобы подробнее ознакомиться с поведением интерфейса DBI при обработке ошибочных ситуаций, обратим внимание на последний аргумент метода connect (). Этими двумя атрибутами будут RaiseError и PrintError. Х Атрибут RaiseError активизирован (установлено ненулевое значение). В случае возникновения ошибки интерфейс DBI для вывода сообщения и завершения выполнения сценария вызывает метод die (). Х Атрибут PrintError активизирован. В случае возникновения ошибки интерфейс DBI вызывает метод warn (), но сценарий продолжает выполнение. По умолчанию параметр RaiseError заблокирован, а параметр PrintError активизирован. В этом случае, если при выполнении метода connect () возникает ошибка, интерфейс DBI выдает сообщение, но выполнение сценария не прерывается. Таким образом, при обработке ошибок по умолчанию, которой можно добиться, не передавая методу аргументов, ошибки можно обрабатывать следующим образом:
$dbh = DBI->connect or exit (1);
($dsn, $user_name, Spassword) При возникновении ошибки метод connect () возвращает значение undef и инициирует вызов метода exit (). В этом случае не нужно печатать сообщение об ошибке потому, что интерфейс DBI уже выдал такое сообщение. При явном определении атрибутов проверки ошибок вызов метода connect () выглядит следующим образом: $dbh = DBI->connect ($dsn, $user_name, $password, { RaiseEiror => 0, PrintError => 1}) or exit (1);
Это потребует больших затрат времени, но даст больше информации о поведении программного кода случайному пользователю. Если есть намерение писать собственноручно программный код, обрабатывающий ошибочные ситуации, необходимо заблокировать параметры RaiseError И PrintError:
$dbh = DBI->connect ($dsn, $user_name, Spassword, { RaiseError => 0, PrintError => 0} ) or die "Невозможно подключиться к серверу: $DBI::err ($DBI: : errstr)\n";
Глава 7. Программный интерфейс Perl API Переменные $DBI::err и $DBI: : errstr, которые используются в только что показанном вызове метода die (), нужны для создания диагностических сообщений. Они содержат коды и тексты ошибок СУБД MySQL, совсем как функции интерфейса С API mysql_errno () и mysql_error(). Если есть намерение возложить обработку всех ошибочных ситуаций на интерфейс DBI, чтобы не проверять их самому, достаточно разблокировать параметр RaiseError:
$dbh = DBI->connect (Sdsn, $user_name, Spassword, ( RaiseError => 1 });
Этот подход значительно проще и он применен в сценарии dump_members. При необходимости проводить некие операции очистки памяти при выходе по ошибке актуализация параметра RaiseError подходит не совсем, но и в этом случае остается возможность сделать все, что требуется. Для этого достаточно переопределить дескриптор $SIG{_DIE_}. Кроме того, желание избежать разблокировки параметра RaiseError может диктоваться тем, что интерфейс DBI распечатывает в своих диагностических сообщениях только диагностическую информацию: disconnect(DBI::db=HASH(Oxl97aae4)) invalidates 1 active statement. Either destroy statement handles or call finish on them before disconnecting. Отключение(DBI::db=HASH(Oxl97aae4)) делает недействительным 1 активный оператор. Удалите дескриптор оператора или завершите его работу перед отключением. Эта информация понятна программисту, но такую информацию вряд ли стоит показывать обычному пользователю. В этом случае лучше сделать проверку на ошибки самому и формировать более понятные сообщения для пользователей данного сценария. Кроме того, в качестве альтернативного метода здесь можно рассмотреть возможность переопределения дескриптора $SIG{_DIE_}. Это может упростить программирование обработки ошибочных ситуаций потому, что позволит воспользоваться возможностями параметра RaiseError, заменив при этом обычные сообщения интерфейса DBI своими собственными сообщениями. Для того чтобы создать свой собственный дескриптор _DIE_, до вызова DBI сделайте что-то наподобие следующего: $SIG{_DIE_} = sub { die "Извините, обнаружена ошибкаХп";
} ;
Подпрограмму можно объявить обычным образом и установить значение дескриптора с помощью обращения к подпрограмме:
sub die_handler { die " Извините, обнаружена ошибкаХп";
} $SIG{_DIE_} = \&die_handler;
Часть II. Программные интерфейсы СУБД MySQL В качестве альтернативы передаче атрибутов обработки ошибок методу connect () литеральным образом можно определить их с помощью хэша и передавать уже только ссылку на хэш. Есть мнение, что такие установки атрибутов делают сценарии более читаемыми и редактируемыми, но функционально оба подхода идентичны. Вот пример использования хэша атрибутов. %attr = ( PrintError => 0, RaiseError => 0 );
$dbh = DBI->connect ($dsn, $user_name, $password, \ % a t t r ) or die " Невозможно подключиться к серверу: $DBI::err ($DBI::errstr)\n";
Вот сценарий dump_members2, иллюстрирующий возможность создания сценария, в котором запрограммирована обработка ошибок, и печати своих собственных сообщений. Сценарий dump_members2 обрабатывает тот же запрос, что и сценарий dump_members, но явным образом блокирует работу параметров PrintError и RaiseError, а затем блокирует результат каждого обращения к интерфейсу DBI. При возникновении ошибок сценарий перед выходом вызывает подпрограмму bail_out (), которая выводит сообщение и содержимое переменных $DBI: :err и $DBI: ::
# ' /usr/bin/perl # dump_members2 - список членов "Исторической Лиги" use DBI;
use strict;
my ($dsn) = "DBI:mysql:samp_db:localhost";
# наименование источника данных my my my my my ( ) ($user_name) = "paul";
($password) = "secret";
($dbh, $ s t h ) ;
(@ary);
(%attr) = PrintError => О, RaiseError => О # # # # # имя пользователя пароль дескрипторы базы данных и операторов массив строк, возвращаемых запросом атрибуты обработки ошибок # подключение к базе данных $dbh = DBI->connect ($dsn, $user_name, $password, \%attr) or bail_out ("Невозможно подключиться к базе данных ");
# создать запрос $sth = $dbh->prepare ("SELECT last_name, first_name, suffix, email,". "street, city, state, zip, phone FROM member ORDER BY last_name") or bail_out ("Невозможно создать запрос");
$sth->execute () Глава 7. Программный интерфейс Perl API or bail_out ("Невозможно выполнить запрос");
# прочесть результаты запросов и очистить while (gary = Ssth->fetchrow_array ()) { print join ("\t", @ary), "\n";
} $DBI: :err == 0 or bail_out ("Во время выборки произошла ошибка");
# очистить память $sth->finish () or bail_out ("Невозможно завершить выполнение запроса");
Sdbh->disconnect () or bail_out ("Невозможно отключиться от базы данных ");
exit (0);
# подпрограмма bail_out()- распечатать код и сообщение об ошибке, а затем завершить выполнение sub bail out ( ~ моцп г.
my ($message) tHiUMtjlft;
, J i;
HC (,i.,. die " $ m e g \ n E r r o r $DBI::err ($DBI : : errstr) \n";
Подпрограмма,bail_out ( ) аналогачна функции print_error ( ), которая использовалась в главе 6, "Программный интерфейс MySQL С API", за одним исключением: подпрограмма bail_out() завершает выполнение сценария, а не возвращает управление вызывающей программе. Подпрограмма bail_out ( ) освобождает от необходимости работы с переменными $DBI: :err и $DBI: :errstr всякий раз, когда есть необходимость напечатать диагностическое сообщение. Обработка диагностических сообщений в одной подпрограмме позволяет при необходимости менять формат диагностического сообщения во всем сценарии простым изменением в одном месте, в подпрограмме. Сценарий dump_merabers2 делает проверку сразу же после цикла выборки строк, чего нет в сценарии durap_members. Поскольку сценарий dump_members2 не автоматически завершает работу при возникновении ошибки в fetchrow_array ( ), он в праве определить, завершил ли свою работу цикл из-за того, что результирующий набор был выбран полностью (нормальное завершение) или из-за возникновения ошибки. Работа цикла завершается в любом случае, но при возникновении ошибки вывод будет сделан в усеченном виде. В случае отсутствия проверки на ошибку пользователь не будет знать того, что что-то было не так! Если сценарий программируется таким образом, что проверка ошибочных ситуаций производится "вручную", обязательно проверяйте результаты выборки.
Часть II. Программные интерфейсы СУБД MySQL Обработка запросов, не возвращающих результирующего набора данных Операторы типа DELETE, INSERT, REPLACE и UPDATE, не возвращающие строк, по сравнению с операторами класса SELECT, DESCRIBE, EXPLAIN и SHOW, возвращающими строки, относительно просто обрабатывать. Для обработки операторов, не принадлежащих классу SELECT, их следует передать методу do () с использованием дескриптора. Метод do ( ) подготавливает и выполняет запрос за один прием. Например, для того, чтобы завести запись для нового члена по имени Marcia Brown с датой истечения срока членства 3 июня 2002, необходимо сделать следующее: $rows = $dbh->do ( " I N S E R T member (last_name, first_name, e x p i r a t i o n ) 1". " VALUES! ' B r o w n ', 'Marcia, ' 2 0 0 2 - 6 - 3 ' ) " ) ;
Метод do ( ) возвращает количество обработанных строк или undef в случае возникновения ошибочной ситуации. Ошибка может произойти по нескольким причинам. (Например, запрос сам может содержать ошибку или у вас нет разрешения на доступ к таблице.) В случае возврата значения, отличного от undef, обратите внимание на случай, когда нет обработанных строк. Когда такое происходит, метод do ( ) возвращает не значение 0, а строку "ОЕО" (форма записи 0, характерная для языка написания сценариев Perl). Значение "ОЕО" в цифровом контексте равно 0, а в условных операциях дает значение "истина", которая может быть отлична от значения undef. Если бы метод do ( ) возвращал 0, было бы значительно сложнее сделать различие между ошибочной ситуацией (undef) и ситуацией "строки не обработаны". Сделать проверку на ошибку можно с помощью одного из тестов: if ( I d e f i n e d ( $ r o w s ) ) { # e r r o r } if ( ! $ r o w s ) { # error } В цифровом контексте "ОЕО" оценивается как 0. Нижеследующий программный код правильно печатает число строк, дающих значение переменной $rows, отличное от undef: if ( ! $ r o w s ) { print "ошибка\п";
} else { Srows += 0;
tt преобразовать в число, если значение равно "ОЕО" print "Обработано $rows строкХп";
} Вывести значение $rows также можно с помощью формата "%d" оператора printf ( ) для преобразования в числовой формат: if ( ! $ r o w s ) { print " ошибка \ n " ;
Глава 7. Программный интерфейс Perl API else { } printf "%d строк обработано \ n ", $rows;
Метод d o ( ) аналогичен последовательности методов prepare () и execute () Предшествующий оператор INSERT можно запрограммировать, не вызывая метода do (): $sth = $dbh->prepare ("INSERT member (last_name,first_name,expiration)" 1 1 " VALUES('Brown,'Marcia,'2002-6-3')");
$rows = $sth->execute ();
Обработка запросов, возвращающих результирующий набор данных В этом разделе излагается информация о возможностях выполнения циклов выборки строк запросов типа SELECT (или других SELECT-подобных запросов, возвращающих строки, такие как DESCRIBE, EXPLAIN и SHOW) Кроме того, затрагивается тема определения количества результирующих строк, обработки результирующих наборов строк, для которых цикл не нужен. Вы также узнаете, как произвести единовременную выборку. Как писать циклы выборки строк Сценарий dumpjnembers производит выборку данных с помощью стандартной последовательности методов интерфейса DBI: метода prepare О, осуществляющего предварительную обработку запроса драйвером, и метода execute (), запускающего собственно выполнение запроса, метода f etchrow_array (), собственно для выборки строк результирующего запроса, и метода f i n i s h (), необходимого для освобождения всех ресурсов, задействованных при выполнении запроса. Методы prepare (), execute () и f i n i s h () являются достаточно традиционными частями процедуры обработки запросов, которые возвращают строки Однако для операции выборки строк метод f etchrow_array () действительно является единственно возможным методом ( табл. 7.3) Таблица 7.3. Методы выборки строк для интерфейса DBI Название метода f etchrow_array () fetchrow_arrayref () fetch () f etchrow_hashref () Возвращаемое значение Массив со значениями строк Ссылка на массив со значениями строк Аналогично методу f etchrow_arrayref () Ссылка на хэш со значениями строк с ключевыми столбцами Часть II. Программные интерфейсы СУБД MySQL Следующие примеры демонстрируют работу методов выборки строк. В этих примерах в цикле просматривается результирующий набор и построчно распечатываются значения столбцов, разделенные запятыми. В некоторых случаях существуют и более эффективные способы создания программ, но эти примеры написаны таким образом, чтобы лучше проиллюстрировать синтаксис, дающий доступ к значениям в каждом отдельном столбце. Метод fetchrow_array () работает следующим образом:
while (Oary = $sth->fetchrow_array ()) {77 $delim = "";
for ($1 = 0;
$1 < @ary;
$i++) { print $delim. $ary[$i];
$delim = ",";
} print "\n";
} Каждый вызов метода fetchrow_array О возвращает массив, содержащий значения строк, или пустой массив, когда строк больше нет Существует альтернативный метод выборки значений столбцов не в массивы, а в скалярные переменные. Такая необходимость может возникнуть при желании работать с переменными, имена которых будут понятнее, чем $агу[0], $агу[1] и т.д. Предположим, что надо произвести выборку имени и адреса электронной почты. С помощью метода f etchrow_array () производится выборка строк набора данных:
Ssth = Sdbh->prepare ("SELECT last_name, first_name, suffix, email" " FROM member ORDER BY last_name");
$sth->execute ();
while (($last_name, $first_name, ?suffix, $email) = $sth->fetchrow_array ()) { # произвести определенные действия над переменными } Конечно, при такой работе со списком переменных у программиста всегда есть уверенность в том, что столбцы выбираются в нужном порядке. Интерфейс DBI не "знает" порядка следования столбцов в операторе SELECT, таким образом задачей программиста является соблюдение правильного порядка следования столбцов Кроме того, можно задать автоматическое присвоение значений столбцов отдельным переменным, пользуясь так называемым методом привязки параметров. Этот метод будет изложен позднее в разделе "Маркеры и привязка параметров". Метод fetchrow_arrayref () аналогичен методу fetchrow_array (). Но вместо возвращения массива, содержащего значения столбцов текущих строк, он возвращает ссылку на массив или значение undef, когда строк больше не осталось. Этот метод можно запустить следующим образом:
Глава 7. Программный интерфейс Perl API while ($ary_ref = $sth->fetchrow_arrayref ()) { $delim = "";
for ($1 = 0;
$1 < @($ary_ref};
$i++) { print $delim. $ary_ref->[$i];
Sdelim = ",";
} print "\n";
} Доступ к элементам массива осуществляется с помощью ссылки на массив $ary_ref. Для использования механизма указателя здесь применяется нотация $ary_ref-> [$i], а не $ a r y [ $ i ]. Для обращения к массиву можно воспользоваться конструкцией @ { $ a r y _ r e f }. Метод fetchrow_arrayref () не подходит для выборки переменных в список. Так, например, такой цикл работать не будет:
while (($last_name, $first_name, $suffix, $email) = 8{$sth->fetchrow_arrayref ()}) { # произвести определенные действия над переменными } Пока метод fetchrow_arrayref О действительно производит выборку строк, этот цикл функционирует нормально. Но как только строки завершаются, fetchrow_arrayref () возвращает значение undef, а выражение @ { u n d e f } становится ошибочным. (Чтобы было понятно Ч это аналогично ссылке на пустой указатель в С-программе.) Еще один метод выборки строк fetchrow_hashref () работает следующим образом:
while ($hashref = $sth->fetchrow_nashref ()) { $delim = "";
foreach $key (keys (%($hashref}) { print $delim. $hashref->{$key};
$delim = ",";
} print "\n";
} Каждый вызов fetchrow_hashref О возвращает ссылку на хэш строковых значений или, если строк больше не осталось, значение undef. В этом случае чередование значений столбцов не имеет какоголибо определенного порядка, элементы хэша в языке написания сценариев Perl не следуют в каком-то определенном порядке. Однако элементы хэша имеют ключевые ссылки на имена столбцов, таким образом $hashref дает одну переменную, с помощью которой можно получить доступ к каждому столбцу по имени. Это позволяет получать значения (или любое их подмножество) в нужном порядке, и совсем не обязательно знать порядок, в котором были выбраны столбцы в запросе 348 Часть II. Программные интерфейсы СУБД MySQL SELECT. Например, для получения имени и электронного адреса это можно сделать следующим образом:
while ($hashref = $sth->fetchrow_hashref ()) Sdelim = "";
foreach $key ("last_name", "first_name", "suffix", "email") print $delim. $hashref->{$key);
$delim = ",";
print "\n";
Процедура f etchrow_hashref () очень удобна при передаче функции последовательности значений, не зависящих от порядка, в котором столбцы были перечислены в операторе SELECT. В этом случае для выборки строк можно вызвать fetchrow_hashref () и создать функцию, которая получает доступ к хэшу с помощью имен столбцов. Необходимо быть очень внимательным при использовании процедуры fetchrow_hashref() Х При необходимости экономить каждую секунду метод f etchrow_hashref () не может считаться лучшим решением, так как он не настолько эффективен, как методы f etchrow_array () ИЛИ fetchrow_arrayref () Х Имена столбцов, которые здесь используются в качестве ключевых значений в хэше, имеют тот же регистр, что и в операторе SELECT. В СУБД MySQL имена столбцов не реагируют на регистр, таким образом, запрос будет работать независимо от того, в каком регистре написаны имена столбцов. Но ключевые имена хэш-памяти языка написания сценариев Perl чувствительны к регистру. Это несоответствие может вызвать определенные затруднения в работе. Для того чтобы избежать вероятных проблем несоответствия регистра, можно непосредственно указать fetchrow_hashref О обрабатывать имена в определенном регистре, передав методу атрибуты NAME_lc или NAME_uc:
$hash_ref # имена в $hash_ref # имена в = $sth->fetchrow_hashref нижнем регистре = $sth->fetchrow_hashref верхнем регистре ('NAME_lc');
('NAME_uc');
Х Сама структура хэша такова, что один элемент содержит одно уникальное имя столбца. Выборка же может содержать объединение нескольких таблиц, имена столбцов которых могут совпадать. В этом случае значение таких столбцов получить невозможно. Например, fetchrow_hashref () вернет только один элемент при запросе типа: SELECT a.name, b.name FROM a, b WHERE a.name = b.name Глава 7. Программный интерфейс Perl API Определение количества строк, возвращаемых запросами Каким образом можно определить количество строк, возвращаемых запросом SELECT или подобным запросом? Конечно, одним способом является подсчет возвращаемых столбцов по мере их выборки. И действительно, это единственный универсальный способ узнать количество строк, возвращаемых запросом SELECT. Пользуясь драйвером СУБД MySQL, после вызова execute () можно вызвать метод rows (} с помощью дескриптора оператора, но такой прием неприменим для других баз данных. Более того, даже для СУБД MySQL метод rows () при установленном атрибуте mysqluse_result не возвращает правильный результат до полной выборки всех строк. (Детальная информация изложена в приложении Ж, "Программный интерфейс DBI языка написания сценариев Perl".) Так что можно просто подсчитать количество строк по мере их выборки.
Выборка результатов, содержащих одну строку Совсем не обязательно создавать цикл для того, чтобы произвести выборку, заведомо содержащую одну строку. Предположим, что вы собираетесь написать сценарий count_members, который подсчитывает количество членов "Исторической Лиги" на данный момент времени. Вот программный код, выполняющий такой запрос: # создать запрос $sth = $dbh->prepare ("SELECT COUNT(*) FROM member");
$sth->execute ( ) ;
# прочесть результаты запроса, а затем очистить память Scount = $ s t h - > f e t c h r o w _ a r r a y ( ) ;
$count = " т р у д н о с у д и т ь " if print " $ c o u n t \ n " ;
$sth->finish () ;
[defined ( $ c o u n t ) ;
Оператор SELECT возвращает только одну строку, поэтому здесь нет необходимости в цикле, вызов метода f e t c h r o w _ a r r a y ( ) производится только один раз. Кроме того, так как производится выборка только одного столбца, нет необходимости присваивать возвращаемое значение массиву. При вызове fetchrow_array () в скалярном контексте (когда ожидается возвращение одного, а не нескольких значений), она возвращает первый столбец строки или значение undef, если строк больше нет. Еще одним типом запроса, для которого ожидается как максимум одна запись, является запрос, содержащий конструкцию LIMIT 1, ограничивающую количество возвращаемых строк одной строкой. Наиболее традиционным применением запросов такого типа является возвращение строки, содержащей максимальное или минимальное значение определенного столбца. Например, вот запрос, который возвращает имя и дату рождения самого молодого президента:
Часть II. Программные интерфейсы СУБД MySQL $query = "SELECT last_narae, first_name, birth" " FROM president ORDER BY birth DESC LIMIT I";
$sth = $dbh->prepare ($query);
$sth->execute ();
# получить результаты запроса и очистить память ($last_name, $first_name, $birth) = $sth->fetchrow_array ();
$sth->finish ();
if ((defined ($lastname! ) { print "Запрос не дал результатов\п";
) else { print "Самый молодой президент: $first_name $last_name (Sbirth)\n";
} Другими типами запросов, которым не требуется цикл выборки, являются запросы, использующие агрегирующие функции МАХ () или MIN () для выборки единственного значения. Но во всех этих случаях существует еще один способ получить результат, состоящий из одной строки, Ч воспользоваться методом selectrow_array(), объединяющим возможности методов prepare (), execute О и выборки строк в одном вызове. Этот метод возвращает массив (а не ссылку на массив) или пустой массив в случае возникновения ошибки. Предыдущий пример можно переписать с помощью метода select row_array () следующим образом:
$query = "SELECT last_name, first_name, birth" " FROM president ORDER BY birth DESC LIMIT I";
($last_name, $first_name, Sbirth) = Sdbh->selectrow_array ($query);
if (!defined ($last_name)) { print " Запрос не дал результатов \п";
} else < print " Самый молодой президент: $first_name $last_name ($birth)\n";
} Выборка полных результирующих наборов данных Интерфейс DBI не позволяет производить выборку в произвольном порядке и обрабатывать возвращаемые строки в порядке, отличном от того, в котором они поступают. Кроме того, после выборки строки предыдущая выбранная строка будет потеряна, если до этого не будет предпринято шагов для сохранения ее результатов в памяти. Такое положение вещей не всегда удобно по ряду причин. Х Может потребоваться обработка строк в непоследовательном порядке. Предположим, вы хотите провести викторину, основываясь на имеющейся у вас информации о президентах США. Каждый раз вопросы необходимо формулировать в произвольном порядке. Глава 7. Программный интерфейс Perl API Для этого необходимо производить выборку всех строк из таблицы, содержащей информацию о президентах. После этого уже можно варьировать порядок появления вопросов о президентах. Для произвольной выборки строки необходимо иметь доступ ко всем строкам одновременно. Х Возможно, понадобится только подмножество возвращаемых строк, собранных в произвольном порядке. Например, для того, чтобы сконструировать вопрос о месте рождения президентов и несколько возможных вариантов ответа на него, необходимо сначала в случайном порядке выбрать президента (и правильный ответ), а затем еще несколько строк для формирования нескольких ложных ответов. Х Полный результирующий набор может потребоваться, даже если его надо обрабатывать в последовательном порядке. Это необходимо для повторных просмотров строк. Например, при статистических вычислениях сначала необходимо получить общие цифровые свойства данных, а затем просмотреть строки повторно для получения более детального анализа. Получить доступ к результирующему набору как к одному целому можно разными способами. Это можно сделать с помощью обычного цикла выборки, сохраняя каждую строку после выборки. Можно воспользоваться методом, который одновременно возвращает весь результирующий набор. Но в любом случае будет получена матрица, содержащая результирующий набор данных. Элементы матрицы можно обрабатывать в произвольном порядке столько раз, сколько это необходимо. Ниже сравниваются оба эти подхода. Задействовать цикл выборки с целью получить результирующий набор данных можно с помощью метода f etchrowarray (), сохраняя при этом ссылки на строки. Вот исходный текст программы, который производит цикл выборки и печати, аналогичный тому, что представлен в процедуре dump_members, за исключением того, что он сначала сохраняет все выбранные строки, а затем для того, чтобы продемонстрировать определение количества строк и столбцов в матрице и осуществление доступа к отдельным ее элементам, распечатывает всю матрицу.
my (Smatrix) = ();
I массив ссылок на массивы while (my @ary = $sth->fetchrow_array ()) # построчная выборка { push (@matrix, [ @ary ]);
# сохранить ссылку на только что tt выбранную строку } Ssth->finish ();
# определить размеры матрицы my ($rows) = scalar (@matrix);
ту ($cols) = ($rows == 0 ? О : scalar (@{$matrix[0]}));
for (my $i = 0;
$i < $rows;
$i++) { # Построчная печать Часть II. Программные интерфейсы СУБД MySQL my ($delim) = "";
for (my Sj = 0;
$j < $cols;
$j++) { print $delim. Smatrix[$i][$j];
$delira = ",";
} print "\n";
} При определении размеров матрицы сначала определяется количество столбцов в матрице, потому что число столбцов матрицы обусловлено тем, пуста эта матрица или нет. Если значение переменной $rows равно О, матрица будет пустой и $cols будет тоже равно 0. В противном случае количество столбцов можно вычислить равным количеству элементов в массиве строк, пользуясь синтаксисом @ { $ m a t r i x [ $ i ] }, позволяющим получить доступ к строке $1. В предыдущем примере мы сначала производили выборку каждой строки, а затем запоминали ссылку на нее. Можно предположить, что гораздо эффективнее сделать вызов f etchrow_arrayref (), а не делать непосредственный поиск ссылок на строки: my ( A matrix) = ();
# массив ссылок на массив while (my Sary_ref = $sth->fetchrow_arrayref ()) { # это не будет работать! push (Smatrix, $ a r y _ r e f ) ;
# запомнить ссылку на только # что выбранную строку } $sth->finish ();
Такая конструкция не будет работать потому, что f etchrow_arrayref () повторно использует массив, на который ссылается (указывает) ссылка. Полученная матрица будет представлять собой массив ссылок, каждая из которых будет указывать на одну и ту же самую последнюю найденную строку. Поэтому, если необходима построчная выборка, лучше воспользоваться методом fetchrow_array(), а не fetchrow_arrayref(). В качестве альтернативы использованию цикла выборки можно воспользоваться одним из методов интерфейса DBI, возвращающим весь результирующий набор. Например, метод fetchall_arrayref () возвращает массив ссылок, каждый из которых ссылается на содержимое одной строки результирующего набора. Действительно, возвращаемое значение является ссылкой на матрицу. Перед использованием метода fetchall_arrayref () вызовите последовательно prepare () и execute О, а затем можно выбирать результат следующим образом:
my ($matrix_ref);
# ссылка на массив ссылок ();
?О : #выбрать все строки scalar $matrix_ref = $sth->fetchall_arrayref # определить размерность матрицы my ($rows) = ((defined ($matrix_ref) (@{$matrix_ref}));
Глава 7. Программный интерфейс Perl API 12- my ($cols) = ($rows == 0 ? 0 :
scalar (@{$matrix_ref->[0]}));
for (ray $1 = 0;
$1 < $rows;
$!++)# построчная печать { my (Sdelim) = "";
for { (my $] = 0;
S] < $cols;
$]++) } } print "\n";
print $delim. $matnx_ref-> [$i] [$3 ] ;
$delim = ",";
Метод f e t c h a l l _ a r r a y r e f () возвращает ссылку на пустой массив, если результирующий набор пуст. Метод возвращает значение undef в случае ошибки, поэтому тогда, когда параметр RaiseError не включен, необходимо проверять возвращаемое значение перед его использованием. Количество строк и столбцов определяется заполнением матрицы. Для доступа ко всей строке $i матрицы как к массиву можно воспользоваться синтаксической конструкцией @{$matnx_ref-> [$i] }. Конечно, намного проще для выборки результирующего набора пользоваться методом f etchall_arrayref (), чем писать специальный цикл выборки строк, хотя доступ к элементам массива будет несколько усложнен. Существует метод, во многом аналогичный функциональным возможностям метода fetchall_arrayref () в случайном порядке, однако более функциональный. Это метод selectall_arrayref (). Он выполняет полную последовательность операций prepare О, execute (), цикл выборки, f i n i s h ( ). Для того чтобы работать с методом selectall_arrayref (), необходимо передать запрос непосредственно этому методу с помощью дескриптора базы данных. my ($matrix_ref ) ;
# ссылка на массив ссылок $matrix_ref = $dbh->selectall_arrayref ("SELECT last_name, first_name, s u f f i x, email, "street, city, state, zip, phone FROM member ORDER BY last_name");
^ # определить размерность матрицы ray ($rows) = ('defined ($matrix_ref ) ? 0 : scalar (@{$matnx_ref } ) ) ;
my ($cols) = ($rows == 0 ? 0 : scalar (@{$matrix_ref - > [ 0 ] } ) ) ;
for (my $1 = 0;
$1 < $rows;
$!++)# построчная печать { my (Sdelim) = "";
( for (my $] = 0;
$] < $cols;
$j++) { print Sdelim. $matrix_ref ->[$i] [$]];
Sdelim = ",";
} print "\n";
Часть \\. Программные интерфейсы СУБД MySQL Проверка пустых значений При выборке данных из базы необходимо различать значения "NULL'' и нулевые значения столбцов или пустые строки Это достаточно просто потому, что интерфейс DBI возвращает пустые значения столбцов как undef. Однако при этом необходимо делать соответствующую проверку. Вот фрагмент программного кода, который всегда распечатывает " f a l s e ' ":
$col_val = undef;
$col_val =0;
if $col_val = "";
if if ('$col_val) { print "false'\n";
('$col_val) { print "false'\n";
} ('$col_val) { print "false!\n";
} } Более того, следующий фрагмент печатает " f a l s e ' " для двух проверок:
$col_val = undef;
$col_val = "";
if ($col_val eq "") if ($col_val eq "") { print "false'\n";
{ print "false'Xn";
} } Вот фрагмент, который делает то же самое:
$col_val = " " ;
if ($col_val eq "") if ($col_val == 0) ( print "false1\n";
{ print "false'W;
} } Для определения различия между столбцами со значением "NULL" и столбцами, имеющими другие значения, воспользуйтесь методом defined (). После того как вы убедились в том, что значение не является пустым, можно производить анализ других значений. Например:
if ('defined ($col_val)) { elsif ($col_val eq "") { elsif (Scol_val == 0) { else { print "другое\п";
print print print } "NULL\n";
} "пустая строка \п";
"ноль\п";
} } Здесь очень важно производить проверки в правильной очередности. Это важно потому, что если $col_val является пустой строкой, вторая и третья операции сравнения дают результат "True". Если изменить порядок следования этих операторов, пустые строки ошибочно будут идентифицированы как строки, имеющие значение ноль Проблема кавычек До сих пор мы конструировали запросы самым простейшим образом с использованием простых кавычек. Проблемы на лексическом уровне языка написания сценариев Perl возникают в случае, когда строки, заключенные в кавычки, сами содержат величины в кавычках. Затруднения могут возникнуть и на уровне SQL, когда необходимо добавить или выбрать значения, которые содержат кавычки, обратную косую черту или двоичные данные При определении запроса как строки, заключенной в кавычки, нужно всячески избегать любого появления символа "кавычки" внутри строки запроса:
Squery = 'INSERT absence VALUES(14,\'1999-9-16X')';
$query = "INSERT absence VALUES(14,\"1999-9-16\")";
Глава 7. Программный интерфейс Perl API 12 Синтаксис Perl и MySQL позволяет заключать строки как в одинарные, так и в двойные кавычки. Таким образом, можно избегать неоднозначности:
$query = 'INSERT absence VALUES(14,"1999-9-16")';
Squery = "INSERT absence VALUES(14,'1999-9-16')";
Однако эти два типа кавычек в Perl не эквивалентны. Ссылки на переменные интерпретируются только внутри двойных кавычек. Поэтому одинарные кавычки при создании запросов не помогут, когда в строке запроса имеется ссылка на переменные. Например, для $var, равного 14, следующие две строки не будут идентичны:
"SELECT * FROM member WHERE id = $var" 'SELECT * FROM member WHERE id = $var' Строки интерпретируются следующим образом и очевидно, что первая строка серверу MySQL будет более понятна:
"SELECT * FROM member WHERE id = 14" 'SELECT * FROM member WHERE id = $var' В качестве альтернативы двойным кавычкам можно назвать конструкцию q q { }, которая позволяет интерпретатору Perl рассматривать все, что находится между "qq{" и "}" как строку, заключенную в двойные кавычки. В названии этой конструкции qq можно рассматривать как аббревиатуру "double-quote" (двойная кавычка). Например, эти две строки эквивалентны: $date = "1999-9-16";
Sdate = qq{1999-9-16};
Конструкция q q { } позволяет совсем не беспокоиться о проблеме двойной кавычки и свободно пользоваться в запросах кавычками (одинарными или двойными). Кроме того, безошибочно интерпретируются ссылки на переменные. Эти обе возможности конструкции q q f } проиллюстрированы следующим оператором INSERT' $id = 14;
$date = "1999-9-16";
Squery = qq{INSERT absence VALUES($id,"$date")};
В качестве ограничителей конструкции qq можно пользоваться как "{" и "}", так и q q ( ) и qq//. Автор предпочитает форму qq{}, потому что вероятность того, что символ "}" появится в тексте SQL-запроса значительно ниже, чем вероятность появления там символов ")" или "/". Например, символ ")" появляется внутри оператора INSERT, показанного только что. Таким образом, qq () не будет безошибочно интерпретирован при квотировании строки запроса. Конструкция q q { } позволяет выделить запрос отдельной строкой, если есть необходимость и желание выделить SQL-запрос из программного кода Perl: $id = 14;
$date = "1999-9-16";
356 Часть II. Программные интерфейсы СУБД MySQL $query = qq{ INSERT absence VALUES($id,"$date") };
Это также будет полезно и с точки зрения придания большей читабельности запросу. Например, оператор SELECT, представленный в сценарии dump_members, выглядит следующим образом:
$sth = $dbh->prepare ("SELECT last_name, first_name, suffix, email," "street, city, state, zip, phone FROM member ORDER BY last_name");
Применив конструкцию q q { }, придадим оператору более читабельный вид:
Ssth = $dbh->prepare (qq{ SELECT last_name, first_name, suffix, email, street, city, state, zip, phone FROM member ORDER BY last_name });
Довольно часты случаи, когда строки, заключенные в двойные кавычки, тоже могут занимать несколько строк. Но все равно автор при создании многострочных строк отдает предпочтение конструкции qq {}. Я поймал себя на мысли, что когда я вижу непарную кавычку в строке, то думаю, не ошибка ли это? После чего я машинально трачу время зря на поиски парной кавычки Конструкция qq{} берет на себя все проблемы заключения в кавычки на лексическом уровне интерпретатора Perl. Это позволяет совершенно не заботиться об этой проблеме Однако необходимо принимать во внимание синтаксис уровня SQL. Рассмотрим такую попытку добавления записи в таблицу member:
Slast = "O'Malley";
$first = "Brian";
$expiration = "2002-9-1";
$rows = Sdbh->do (qqf INSERT member (last_name,first_name,expiration) VALUES!'$last','$first','Sexpiration1) ;
Строка, которую метод do () здесь посылает серверу MySQL, будет выглядеть следующим образом:
INSERT member (last_name, first_name, expiration) VALUES!'O'Malley1,'Brian','2002-9-1') Этот SQL-запрос содержит ошибку потому, что одинарная кавычка попадает внутрь строчки, заключенной в кавычки. В главе 6, "Программный интерфейс MySQL С API", мы уже сталкивались с аналогичной проблемой. Тогда она была вызвана использованием функции mysql_escape_string (). Интерфейс DBI имеет подобный механизм Ч каждое значение, взятое в кавычки, которое затем будет использоваться явным образом в операторе, выГлава 7. Программный интерфейс Perl API зывает метод quote О и в дальнейшем используется уже только возвращенное значение. Предыдущий пример лучше переписать следующим образом:
Slast = $dbh->quote ( "O'Malley");
Sfirst = $dbh->quote ("Brian");
$expiration = $dbh->quote ("2002-9-1");
$rows = $dbh->do (qq{ INSERT member (last_name,first_name, expiration) VALUES($last, Sfirst, Sexpiration) });
Теперь строка, которую метод do () посылает серверу MySQL, приобретает следующий вид. INSERT member (last_name, f i r s t name, expiration) VALUES) ' O V M a l l e y 1, ' B r i a n ', ' 2 0 0 2 - 9 - 1 ' ) Обратите внимание, что при ссылке на $last и $ f i r s t в строке запроса нет "окружающих" кавычек, их присваивает метод quote () При добавлении кавычек в запросе будет слишком много кавычек:
$value = "paul";
$quoted_value = $dbh->quote ($value);
print "... WHERE name = $quoted_value\n";
print "... WHERE name = '$quoted_value' \n";
Это операторы дают следующий результат:
... WHERE name =... WHERE name = 'paul' ' 'paul' ' Маркеры и привязка параметров В предыдущих разделах мы создавали запросы, указывая добавляемые значения и критерии выборки прямо в строке запроса, что совсем не обязательно. Интерфейс DBI позволяет размещать в строке запроса специальные маркеры, а затем во время выполнения запросов подставлять на место этих маркеров реальные значения. Это преследует одну основную цель Ч увеличение производительности. Проиллюстрируем, каким образом работают маркеры. Предположим, что в школе начинается новый семестр и необходимо очистить таблицу student, а затем проинициализировать ее, введя туда новых учащихся, воспользовавшись списком имен учащихся, хранящимся в файле. Без маркеров удалить существующие в таблице данные и загрузить новые можно следующим образом:
$dbh->do (qq{ DELETE FROM student });
# удалить существующие строки while (о) # добавить новые строки < chomp;
$_ = $dbh->quote ($_) ;
$dbh->do (qq{ INSERT student SET name = $_ });
Часть II. Программные интерфейсы СУБД MySQL Такая процедура работает неэффективно потому, что основной запрос INSERT будет статичным, а метод do () вызывает в цикле методы prepare О и execute (). Гораздо более эффективно будет работать процедура, в которой для установки оператора INSERT метод prepare () вызывается всего один раз перед началом цикла, а в цикле вызывается один только метод execute (). Это позволяет избежать всех вызовов метода prepare (), кроме одного. Интерфейс DBI осуществляет это следующим образом: Sdbh->do (qq( DELETE FROM student } );
# удалить существующие строки $sth = $dbh->prepare (qq{ INSERT student SET name = ? });
while (<>) # добавить новые строки { chomp;
Ssth->execute ( $ _ ) ;
} $sth->finish();
Обратите внимание на знак вопроса в запросе INSERT. Это и есть маркер. При выполнении метода execute () реальное значение замещает маркер. В таком виде запрос передается на сервер В целом, если метод do () находится внутри цикла, гораздо лучше запустить метод prepare () до начала цикла, а внутри цикла оставить только execute (). Вот несколько замечаний относительно маркеров. Х Не заключайте символ маркера в кавычки внутри строки запроса. Если это сделать, то данный символ не будет распознан как таковой. Х При определении значений маркеров не пользуйтесь методом quote () В противном случае во вставляемых значениях появляются дополнительные кавычки. Х Можно задавать несколько маркеров в строке запроса, но при этом методу execute () обязательно нужно передавать столько значений, сколько было задано маркеров. Х Каждый маркер должен определять одно, а не список значений. Например, такой оператор будет неверным$sth = $dbh->prepare ( q q { { INSERT member last_name, first_name VALUES (?) ;
Ssth->execute ("Adams,Bill,2003-09-19");
Чтобы избежать ошибки, необходимо поступить следующим образом | г' I*, ' ));
$sth = $dbh->prepare (qq{{ INSERT member lastname, Ssth->execute first_name VALUES (?,?,?) ("Adams","Bill","2003-09-19");
;
"р Х Для определения пустого значения в качестве маркера используйте значение undef.
Глава 7. Программный интерфейс Perl API Х Не пытайтесь использовать шаблон для ключевых слов. Это не даст результата, так как значения маркеров автоматически обрабатываются методом quote (). В таком случае ключевое слово в запросе будет взято в кавычки, и запрос не сработает из-за синтаксической ошибки. Иногда от использования маркеров можно получить еще один выигрыш. В некоторых базах данных по мере планирования эффективного выполнения запросов кэшируют уже готовые к выполнению готовые запросы. Таким образом, если такой же запрос будет получен повторно, он может быть использован снова, минуя этап генерации плана выполнения. Кэширование запросов очень эффективно при обработке сложных запросов SELECT, так как генерация хорошего плана выполнения таких запросов может занимать много времени. Маркеры дают больше шансов для создания универсальных запросов и, соответственно, для попадания их в кэш-память. В случае с нашей СУБД MySQL шаблоны не дают такого выигрыша производительности, прежде всего потому, что СУБД MySQL не кэширует запросы. Тем не менее, не надо бояться использования маркеров. Ведь может случиться так, что ваш сценарий, реализующий интерфейс DBI, будет работать с другой СУБД, которая как раз и использует механизм кэширования запросов. И ваш сценарий с маркерами будет работать эффективней. Маркеры позволяют подставлять значения в запросы непосредственно во время выполнения запроса. Другими словами, они позволяют параметризировать запрос. Интерфейс DBI также позволяет осуществлять соответствующую операцию вывода, которая называется "связыванием параметров". Эта операция позволяет параметризировать "вывод" и автоматически присваивать выбранные значения столбцов прямо переменным, минуя этап принудительного присвоения. Предположим, что у нас есть запрос на выборку имен членов из таблицы member. Можно задать интерфейсу DBI режим, при котором будут назначаться значения выбранных столбцов переменным Perl. При выборке строки переменные будут автоматически инициализированы соответствующими значениями столбцов. Вот пример, показывающий, каким образом можно связать столбцы с переменным, а затем получить доступ к ним в цикле выборки:
$sth = $dbh->prepare (qq{ SELECT last_name, first_name, suffix FROM member ORDER BY last_name, first_name });
Ssth->execute ();
$sth->bind_col (I, \Slast_name);
$sth->bind_col (2, \$first_name);
$sth->bind_col (3, \$suffix);
print "$last_name, $first_name, $suffix\n" while $sth->fetch;
Часть II. Программные интерфейсы СУБД MySQL Каждый вызов метода bind_col () призван определить номер столбца и ссылку на переменную, которая ассоциируется с этим столбцом. Нумерация столбцов начинается с 1. Метод bind_col() должен вызываться после отработки метода execute (). Существует и другой способ, при котором используется только один вызов метода b i n d _ c o l ( ). В этом случае методу передаются ссылки на переменные:
$sth = $dbh->prepare (qq{ SELECT last_name, first_name, suffix FROM member ORDER BY last_name, first_name $sth->execute {) ;
$sth->bind_columns (\$last_name, \$first_name, \$suffix);
print "$last_name, $first_name, $suffix\n" while $sth->fetch;
));
Определение параметров соединения Наиболее простой способ установки параметров соединения Ч определение всех параметров в момент выполнения метода connect (): $data_source = "DBI: mysql :db_name:host_name";
$dbh->connect ($data_source, user_name, password);
При установке параметров соединения интерфейс DB1 делает следующее. Переменная установки среды DBI_DSN используется в случае, если источник данных не определен или его значение не является пустой строкой. Переменные DBi_OSER и DBI_PASS используются, если не определены имя пользователя и пароль (но в этом случае они не могут быть представлены пустой строкой). Работая под ОС Windows, в случае неопределенности имени пользователя используется переменная USER. Х По умолчанию принимается имя узла localhost. Х Если имя пользователя имеет значение undef или "пустая строка", по умолчанию используется регистрационное имя UNIX. Под ОС Windows имя пользователя имеет значение ODBC. Х Если пароль имеет значение undef или "пустая строка", пароль не передается. Опции можно указывать, добавив их в начало выражения и предварив двоеточием. Например, для определения имени конфигурационного файла можно указать опцию mysql_read_default_f ile:
$data_source = "DBI:mysql:samp_db;
mysql_read_default_file=/u/paul/.my.cnf";
Х При выполнении сценария открывается файл, содержащий параметры соединения. Предположим, что файл / u / p a u l /. m y. c n f содержит: Глава 7. Программный интерфейс Perl API [client] host=pit-viper.snake.net user=paul password=secret После этого метод connect!) попытается подключиться к серверу MySQL, расположенному на узле p i t - v i p e r. s n a k e. n e t. При успешном соединении будет подключен пользователь paul с паролем secret. Чтобы дать возможность пользоваться вашим сценарием'любому пользователю, можно задать источник данных следующим образом:
$data_source = "DBI:mysql:samp_db;
mysql_read_default_file=$ENV{HOME}/.my.cnf";
Здесь переменная $ENV{HOME} содержит путь к корневому каталогу пользователя, запустившего сценарий. Таким образом, имена узла и пользователя и его пароль, используемые сценарием, будут взяты из конфигурационного файла каждого пользователя. Для сценария, написанного таким образом, необходимость хранить в своем теле параметры соединения отпадает. Для определения групп можно воспользоваться опцией mysql_read_default_group. Она автоматизирует чтение конфигурационного файла. m y. c n f данного пользователя и позволяет производить чтение дополнительной группы в дополнение к группе [client]. Например, если у вас имеются опции, которые присущи только для ваших сценариев, работающих с интерфейсом DBI, их можно перечислить в группе [ dbi ] и указать еще один источник данных: $data_source = "DBI:mysql:sarap_db;
mysql_read_default_group=dbi";
Работа с опциями mysql_read_default_file и mysql_read_default_ group возможна только для версии MySQL 3.22.Ю или выше и драйверов DBD::mysql 1.21.06 или выше. Более подробную информацию на эту тему можно найти в приложении Ж, "Программный интерфейс DBI языка написания сценариев Perl". Детальную информацию на предмет формата конфигурационных файлов MySQL можно найти в приложении Д, "Программы MySQL". Использование конфигурационных файлов совсем не освобождает вас от необходимости объявления параметров конфигурации при вызове метода connect () (например, в сценарии, подключающем определенного пользователя). Любые имена узлов, пользователей и паролей, указанные при вызове метода connect () явным образом, имеют более высокий приоритет, чем параметры, указанные в конфигурационном файле. Например, необходимо, чтобы сценарий анализировал опции - -host, - user и - -password, указанные в командной строке, и отдавал приоритет этим значениям перед значениями, указанными в конфигурационном файле. Это достаточно традиционное поведение стандартного пользователя СУБД MySQL. Таким образом, создаваемые вами сценарии DBI должны учитывать такое положение вещей. 362 Часть II. Программные интерфейсы СУБД MySQL В остальных сценариях обработки командной строки, которые приводятся в этой главе, будут использоваться стандартная процедура установки и разрыва соединений. Я только один раз покажу, как это делается, чтобы потом полностью сконцентрироваться на том, для чего был создан каждый конкретный сценарий в отдельности: #! /usr/bin/perl use DBI;
use strict;
# Синтаксический анализ параметров соединения из командной строки, если таковые присутствуют use Getopt::Long;
$Getopt::Long::ignorecase =0;
# опции чувствительны к регистру # параметры по умолчанию Ч все пропускается my ($host_name, $user_name, $password) = (undef, undef, undef);
# GetOptions не позволяет применять форму -uuser_name, а только -и user_name? GetOptions( # =s означает, что необходим строковый аргумент после опции "host|h=s" => \$host_name "user|u=s" => \$user_name # :s означает, что строковый аргумент после опции можно не указывать,'password p:s" => \Spassword ) or exit (1);
# запрос пароля, если опция определена, но значение отсутствует if (defined ($password) && !$password) { # отключить эхо open (TTY, "/dev/tty") or die "Терминал не открывается\п";
system ("stty -echo < /dev/tty");
print STDERR "Ввести пароль:";
chomp ($password =
system ("stty echo < /dev/tty");
close (TTY);
print STDERR "\n";
# создание источника данных my (Sdsn) = "DBI:mysql:samp_db";
$dsn.= ":hostname=$host_name" if $host_name;
$dsn.= ";
mysql_read_default_file=$ENV{HOME}/.my.cnf";
# подключение к серверу my (%attr) = ( RaiseError => 1 );
my ($dbh) = DBI->connect ($dsn, $user_name, $password, \%attr);
Глава 7. Программный интерфейс Perl API Этот код инициализирует интерфейс DBI, принимает параметры соединения из командной строки, а затем с помощью параметров, полученных из командной строки или из конфигурационного файла ~ /. m y. c n f пользователя, запустившего сценарий, осуществляет соединение с сервером MySQL. Это значит, что если в корневом каталоге такого пользователя имеется конфигурационный файл. m y. c n f, то дополнительно вводить параметры соединения нет необходимости. (Только нужно не забывать установить такой режим, который обеспечит защиту этого файла от чтения. Инструкции по этому поводу можно найти в приложении Д, "Программы MySQL".) Финальная часть нашего сценария будет очень похожа на окончания многих других сценариев. Она просто завершает сеанс связи и работу сценария:
$dbh->disconnect exit (8);
О;
Когда мы перейдем к разделу, повествующему о программировании в Web, "Работа с интерфейсом DBI в Web-приложениях", код установки соединения будет немного видоизменен, но основная идея останется прежней.
Отладка При отладке сценария DBI, работающего неправильно, прибегают к двум методам, применяя их каждый отдельно или оба сразу. Во-первых, можно разместить в различных местах сценария операторы печати. Это позволяет создавать удобную отладочную диагностику, но вводить эти операторы приходится вручную. Во-вторых, можно использовать встроенные средства трассировки интерфейса DBI. Это более универсальная возможность, но и более систематическая, запускаемая автоматически при ее включении. Трассировка интерфейса DBI отображает информацию о работе самого драйвера, которую невозможно получить иным путем.
Отладка с использованием операторов печати В вопросах, поступающих в список рассылки СУБД MySQL, одним из самых часто встречающихся является: "У меня есть запрос, который отлично работает при запуске из mysql, но совсем не работает из сценария DBI. Что мне делать?" Очень часто оказывается, что сценарий DBI генерирует запрос, совершенно отличный от того, чего ожидает создавший его программист. И, если запрос распечатать до его отсылки на сервер, будет очень интересно увидеть, что собственно посылается на сервер. Предположим, что при запуске запроса из mysql, который выглядит следующим образом (без двоеточия в конце):
INSERT member (last_name, first_name, expiration) VALUES("Brown","Marcia","2002-6-3") Попробуем сделать то же самое из сценария DBI: 364 Часть II. Программные интерфейсы СУБД MySQL $last = "Brown";
$first = "Marcia";
$expiration = "2002-6-3";
$query = qq{ };
$rows = $dbh->do ( $ q u e r y ) ;
INSERT member (last_name,first_name,expiration) V A L U E S ( S l a s t, $ f i r s t, $expiration) Несмотря на то, что это аналогичный запрос, такая конструкция не работает. В чем дело? Попробуем распечатать получившийся запрос: print "$query\n";
Вот результат: INSERT member (last_name, first_name, expiration) VALUES(Brown,Marcia,2002-6-3) Здесь отчетливо видно, что мы забыли заключить в кавычки значения колонок в списке VALUES (). Правильно будет так:
$last = $dbh->quote ("Brown");
$first = $dbh->quote ("Marcia");
$expiration = $dbh->quote ("2002-6-3");
$query = qqf };
INSERT member (last_name, first_name, VALUES(Slast, $first, $expiration) expiration) Есть другой метод Ч создать запрос с использованием маркеров и передавать значения с помощью аргументов метода do ():
$last = "Brown";
Sfirst = "Marcia";
$expiration = "2002-6-3";
$query = qq{ INSERT member (last_name, first_name, expiration) VALUES)?,?,?) ($query, undef, $last, $ first, Sexpiration);
};
$rows = $dbh->do К сожалению, в этом случае с помощью оператора print нельзя увидеть, как собственно выглядит полный запрос, потому что маркеры нельзя оценить до запуска метода do (). При использовании маркеров в качестве метода отладки больше подойдет трассировка. Отладка с применением трассировки Для определения, почему интерфейс DBI работает неверно, существует режим генерирования трассировочной (отладочной) информации. Есть десять уровней трассировки от 0 (выключено) до 9 (максимальное количество информации). Наиболее удобными являются уровни 1 и 2. На втором уровне отображаются выполняемые запросы (включая запросы, Глава 7. Программный интерфейс Perl API полученные после подстановок), результаты обращений к методу quote () и т.д. Эту помощь трудно переоценить в решении проблем, возникающих в процессе отладки. Трассировкой можно управлять из отдельного сценария с помощью метода trace {) или переменной управления DBIJTRACE для установки в режим отладки всех сценариев DBI, которые вами запускаются. Для того чтобы воспользоваться вызовом trace (), необходимо передать аргумент уровня и имя файла. Если имя файла не указано, весь вывод трассировки идет на устройство STDERR;
если указано Ч в указанный файл. Вот некоторые примеры:
DBI->trace (1) уровень 1, трассировка производится на устройство STDERR DBI->trace (2,"trace.out") уровень 2, трассировка производится в файл "trace.out" DBI->trace (0) трассировка выключена При запуске DBI->trace() все операции с интерфейсом DBI трассируются. Более тонкий подход заключается в возможности трассировки на уровне отдельного дескриптора. Такой подход применим, когда известно, в каком месте сценария возникла проблема и вам совсем не нужна регистрация того, что происходит до этой точки. Например, если возникла проблема с запросом SELECT, можно трассировать дескриптор оператора, связанного только с этим запросом:
$sth = $dbh->prepare (qq{ SELECT... $sth->trace (1);
$sth->execute ();
# Создать дескриптор # оператора # разрешить трассировку оператора }};
Если при вызове метода trace () указано имя файла, вся диагностика будет записываться в этот файл. Для того чтобы включить трассировку для всех запущенных вами сценариев DBI, установите переменную среды DBIJTRACE из оболочки. Синтаксис полностью зависит от используемой оболочки:
% setenv DBI_TRACE значение для оболочек csh, tcsh S ВВ1_ТКАСЕ=значение для оболочек sh, ksh, bash $ export DBIJTRACE C:\> set ВВ1_ТКАСЕ=зна^ениедля ОС Windows Формат значение для всех оболочек аналогичен: число п для включения трассировки уровня п на устройство STDERR;
имя файла для включения трассировки второго уровня в поименованный файл или n=flle_name. Вот несколько примеров с использованием синтаксиса оболочки csh:
% setenv DBI_TRACE 1 STDERR % setenv DBI_TRACE l=trace.out % setenv DBI_TRACE trace.out трассировка уровня 1 на устройство трассировка уровня 1 в "trace.out" трассировка уровня 1 в "trace.out" Но, включив режим трассировки, не забудьте отключить его после решения проблемы. Отладочная информация будет добавляться в трасси366 Часть II. Программные интерфейсы СУБД MySQL Х SQL Ч язык сервера. Некоторые задачи администрирования можно выполнить только с помощью утилиты командной строки mysqladmin. Иногда гораздо эффективней справиться с задачей может администратор, который может "общаться" с сервером на его языке. Предположим, например, что необходимо проверить, почему привилегии пользователя работают вовсе не так, как ожидается. Напрямую "поговорить" с сервером на человеческом языке, к сожалению, нельзя. Зато можно воспользоваться программой-клиентом mysql и послать SQL-запрос для анализа таблиц разрешений. Если же используемая версия MySQL появилась раньше введения оператора GRANT, необходимо сначала использовать команду mysql для настройки привилегий каждого пользователя. Незнакомым с операторами SQL пользователям следует разобраться по крайней мере в базовых принципах их работы. Незнание SQL Ч достаточно серьезное препятствие, и время, затраченное на изучение этого языка, окупится очень быстро. Конечно, чтобы стать настоящим "знатоком SQL", придется напряженно учиться и достаточно долго практиковаться. Познакомиться же с основами этого языка можно за весьма короткий промежуток времени. Более детально о языке SQL и использовании клиентской программы, работающей с командной строкой, рассказывается в главе 1, "Знакомство с СУБД MySQL и SQL". Х Каталог данных MySQL. Каталог данных используется сервером для хранения баз данных и файлов состояния. Важно понимать структуру и содержимое каталога данных, чтобы знать, как сервер представляет свои базы данных и таблицы в файловой системе, где хранятся различные файлы (например, регистрационные), и что в них содержится. Необходимо также уметь управлять распределением дискового пространства, чтобы избежать переполнения раздела с каталогом данных.
Общее администрирование Общее администрирование включает в себя в основном работу с mysqld, сервером MySQL и обеспечение доступа пользователей. К наиболее важным задачам общего администрирования относятся следующие. Х Запуск и останов сервера. Администратор должен уметь запускать и останавливать сервер вручную из командной строки и настраивать автоматический запуск и завершение работы. Важно также знать, как восстановить работу сервера в случае сбоя или некорректного функционирования. Х Поддержка учетных записей пользователей. Администратор должен отчетливо понимать разницу между пользователями MySQL и Часть III, Администрирование MySQL пользователями UNIX и Windows и уметь настраивать учетные записи пользователей MySQL. Такая настройка зачастую заключается в определении, какие пользователи и с какого компьютера смогут подключаться к серверу. Новым пользователям необходимо, кроме всего прочего, сообщить свои параметры подключения, чтобы успешно настроить обмен данными с сервером. Настройка учетных записей Ч это задача администратора, а не пользователей! Х Поддержка регистрационных файлов. Администратор должен знать, регистрационные файлы каких типов необходимо проверять и поддерживать, а также, каким образом и когда выполнять эти операции. Последовательный просмотр и замена регистрационных журналов позволит избежать переполнения ими используемых дисков. Х Резервирование и копирование баз данных. Резервирование баз данных Ч исключительно важная операция, позволяющая в случае необходимости восстановить работу системы после сбоя. Желательно, конечно, чтобы имелась возможность восстановить базы данных до того состояния, в котором они находились перед сбоем. В этом случае потеря данных будет минимальной. Заметьте, однако, что резервирование баз данных отличается от резервирования информации всей системы, выполняемой, например, с помощью UNIX-программы dump. В процессе активной работы сервера файлы таблиц базы данных, как правило, подвергаются изменениям. Восстановление файлов, зарезервированных в какой-то определенный момент времени, не позволит в полной мере восстановить базу данных, т.е. потеря определенной части данных неизбежна. Более полезными для восстановления базы данных являются файлы, сгенерированные программой mysqldump. С ее помощью можно выполнять резервирование без предварительного завершения работы сервера. Иногда возникает необходимость в запуске СУБД на более быстродействующем главном компьютере или создании копии базы данных. В этом случае все содержимое ее каталога можно скопировать на другой компьютер. Администратор должен уметь выполнять и эту процедуру. Файлы баз данных могут зависеть от конфигурации конкретной системы, поэтому простое копирование файлов не сможет дать достаточно удовлетворительных результатов. Х Настройка сервера. Пользователи всегда хотят, чтобы сервер работал максимально быстро. Самый быстрый и неэкономный метод повышения производительности сервера Ч приобретение и установка большего объема памяти и более быстрых дисков. Разбираться в работе сервера при применении подобных методов вовсе необязательно. Администратору необходимо знать, какие параметры применяются для настройки работы сервера и как их значения повлияют на ситуацию. Одни клиенты пользуются запросами в Глава 9. Введение в администрирование MySQL основном для выборки необходимой информации из баз данных. Другие в основном для добавления и обновления информации. Поэтому решение об изменении значений тех или иных параметров зачастую определяется типом запросов, отправляемых к узлу. Установка нескольких серверов. В некоторых случаях возникает необходимость в запуске сразу нескольких серверов. Она может определяться желанием протестировать работу новой версии MySQL перед полным переходом на нее или обеспечить более высокий уровень защиты для отдельных групп и пользователей. (Последний вариант, в частности, относится к провайдерам услуг Internet.) В таких ситуациях администратор должен знать, как установить и настроить несколько одновременно работающих инсталляций. Обновление MySQL. Новые версии MySQL появляются достаточно часто. Администратор должен знать, как обновить старую версию для устранения найденных ошибок программного кода и добавления новых возможностей. Следует также понимать, что иногда имеет смысл воздержаться от обновления, а также уметь выбирать между официальными и тестовыми версиями этого программного продукта.
Безопасность В процессе инсталляции MySQL важно также обеспечить надежную защиту хранимых данных. Администратор MySQL полностью отвечает за предоставление доступа к каталогам данных и серверу и должен разбираться в следующих вопросах. Х Защита файловой системы. В ОС UNIX могут поддерживаться несколько учетных записей пользователей, не обладающих полномочиями администратора MySQL. Необходимо проверить, чтобы эти пользователи не имели доступа к каталогам данных. Это позволит защититься от несанкционированного копирования или удаления таблиц баз данных, либо чтения регистрационных файлов с критически важной информацией. Администратор должен знать, как настроить учетные записи пользователей UNIX для сервера MySQL, как сделать каталог данных доступным только для владельца и как определить привилегии пользователей сервера. Х Защита сервера. Знание принципов работы системы безопасности MySQL поможет правильно настроить пользовательские учетные записи и полномочия доступа. Подключающиеся через сеть к серверу пользователи должны обладать правами доступа только к самой необходимой информации. Полное игнорирование системы безопасности может обернуться предоставлением прав администратора анонимному пользователю.
Часть III. Администрирование MySQL Отладка и поддержка баз данных В душе каждый администратор MySQL надеется избежать повреждения или полного разрушения таблиц баз данных. Однако одних надежд мало. Выполнение определенных процедур позволит минимизировать риск сбоя и оказаться более подготовленным к негативному развитию событий. Х Восстановление после сбоя. Сбой может разрушить результаты даже самых успешных действий, поэтому администратор должен обязательно знать, как отладить и восстановить таблицы баз данных. Восстановление после сбоя Ч это процесс, выполняемый весьма редко. Однако он очень неприятен и, как правило, сопровождается пребыванием администратора в стрессовом состоянии (которое может еще более усугубляться постоянными звонками и стуком в дверь.) Для восстановления применяются средства проверки и восстановления программ isamchk и myisamchk. Необходимо обязательно научиться восстанавливать информацию из резервных файлов и использовать записи регистрационных журналов для восстановления всех изменений, внесенных с момента последнего резервирования. Х Превентивная поддержка. Для снижения вероятности повреждения или разрушения баз данных следует постоянно применять программы превентивной поддержки. Необходимо также выполнять и резервирование, хотя, конечно, выполнение мер превентивной поддержки снижает возможность их использования. Эта глава посвящена краткому описанию всех основных обязанностей администратора MySQL. В следующих главах эти задачи рассматриваются более детально, а также описываются процедуры эффективного их выполнения. Однако сначала рассматривается каталог данных MySQL. Ведь администратору необходимо четко понимать структуру и содержимое ресурса, на который направлены все операции поддержки. Ниже будут рассмотрены все общие административные задачи, система безопасности MySQL, поддержка работы СУБД и методы устранения всевозможных проблем.
Глава 9. Введение в администрирование MySQL 1 Каталог данных MySQL Концептуальные принципы построения большинства систем управления реляционными базами данных одинаковы: все они состоят из набора баз данных, каждая из которых, в свою очередь, включает набор таблиц. Однако каждая система по-своему организует управляемые данные. Не является исключением в этом отношении и MySQL. По умолчанию вся информация, управляемая сервером mysqld, содержится в так называемом каталоге данных MySQL (MySQL data directory). В нем хранятся все базы данных и файлы состояния с информацией о функционировании сервера. Пользователь, выполняющий функции администратора MySQL, должен знать структуру этого каталога и уметь использовать его в своей повседневной работе. В этой главе даются исчерпывающие ответы на следующие вопросы. Х Как определить месторасположение каталога данных. Это необходимо для эффективного управления его содержимым. Х Как организуется и обеспечивается доступ к базам данных и таблицам сервера. Эта информация необходима для создания расписания операций превентивной поддержки и восстановления поврежденных таблиц после сбоя.
Часть III. Администрирование MySQL Х Где размещаются сгенерированные сервером файлы состояния и что они содержат. Эти файлы содержат информацию о работе сервера, которая может быть очень полезна для устранения всевозможных проблем. Х Как изменить месторасположение каталога данных по умолчанию или отдельных баз данных. Знание этих вопросов важно для управления размещением дискового пространства системы. Это может быть необходимо для распределения данных по физическим дискам или перемещения данных в файловых системах с целью освобождения пространства на одном из дисков. Данная информация пригодится и при планировании размещения новых баз данных. Значительную пользу от чтения этой главы получат даже те пользователи, которые не занимаются администрированием MySQL. Дополнительные знания о принципах работы сервера никогда не будут лишними.
Размещение каталога данных По умолчанию местоположение для каталога данных устанавливается при компиляции сервера. Обычно при инсталляции с исходной дистрибуции устанавливается каталог / u s r / l o c a l / v a r, при инсталляции из двоичной дистрибуции Ч / u s r / l o c a l / m y s q l / d a t a, а при инсталляции из файла RPM Ч / v a r / l i b / m y s q l. Размещение каталога данных можно задать и явным образом при запуске сервера. Для этих целей применяется опция Чdatadir=/path/to/dir. Она оказывается весьма кстати, если каталог данных необходимо разместить в месте, отличном от того, которое указывается по умолчанию. Администратор MySQL обязательно должен знать, где находится каталог данных. При запуске нескольких серверов следует записать местоположение всех каталогов данных. Если же размещение каталога неизвестно (например, из-за того, что предыдущий администратор плохо вел свои записи), его можно определить несколькими методами. Х Воспользоваться командой mysqladmin variables для получения пути к каталогу данных непосредственно с сервера. На компьютере UNIX результат ее ввода будет выглядеть примерно так.
% mysqladmin variables Variable name back log connect timeout basedir datadir ! Value !5 ! ! /var/local/ ! /usr/local/var/ Из приведенных выше результатов видно, что каталог данных размещается в каталоге /usr/local/var/ сервера. Глава 10. Каталог данных MySQL На компьютере, работающем под управлением ОС Windows, результаты ввода этой же команды будут выглядеть следующим образом.
% mysqladmin variables Variable_name back_log connect timeout basedir datadir | Value |5 !5 c:\mysql\ c:\mysql\data\ I \ | Если на компьютере запущено несколько серверов, каждый из них использует свой порт TCP/IP и разъем. Чтобы получить информацию о каталоге данных от каждого сервера, достаточно подключиться с помощью опций Чport и Чsocket к соответствующему порту и разъему.
% mysqladmin --port=port_num variables % mysqladmin Чsocket=/path/to/soaket variables Команду mysqladmin можно запускать на любом компьютере, который подключен к серверу. Для подключения к серверу с удаленного компьютера применяется опция --host=host_name. % mysqladmin --host=host_name variables С компьютера, работающего под ОС Windows, можно подключиться к работающему через именованный канал серверу Windows NT с помощью опции Чpipe, активизирующей соединение по именованному каналу, и опции Чsocket=pipe_name, определяющей имя канала. С:\> mysqladmin Чpipe --socket=pipe_name variables Воспользоваться командой ps для вывода командной строки исполняемого процесса mysql. Попробуйте одну из указанных ниже команд (в зависимости от версии ps, поддерживаемой системой) и поищите переменную --datadir в выводимых результатах.
% ps axww I grep mysql % ps -ef | grep mysql ps BSDЧUNIX ps системы System V Команда ps особенно полезна при запуске на одном компьютере нескольких серверов, поскольку позволяет узнать месторасположение сразу всех каталогов данных. Недостаток этого метода заключается в том, что команду ps обязательно нужно запускать на главном компьютере. Кроме того, она будет бесполезна, если переменная Чdatadir не описана явным образом в командной строке mysql. Если MySQL инсталлировалась из исходной дистрибуции, месторасположение каталога данных можно получить из информации о конфигурации. Так, например, месторасположение каталога указывается в элементе верхнего уровня Makefile. Однако будьте осторожны, поскольку позиция каталога является в M a k e f i l e значением Часть III. Администрирование MySQL переменной localstatedir, а не datadir, как ожидают многие. Кроме того, если дистрибуция размещается на смонтированной сетевой файловой системе NFS и используется для установки MySQL на несколько компьютеров, в информации конфигурации отражаются данные только для компьютера, на котором система устанавливалась последней. Вполне возможно, что им окажется не тот компьютер, для которого необходимы данные. Х Если все предыдущие методы вам не подходят, можно воспользоваться командой find для поиска файлов базы данных. Приведенная ниже команда ищет все файлы. f rm (описания), являющиеся частью инсталляций MySQL: % find / -name "*.frm" -print Во всех примерах этой главы в качестве каталога данных MySQL определен каталог DATADIR. Вполне возможно, что на других компьютерах для этих целей может применяться другой каталог.
Структура каталога данных Каталог данных MySQL содержит все управляемые сервером базы данных и таблицы. Они организованы в структуру простого дерева, что позволяет, в свою очередь, воспользоваться преимуществами иерархической структуры файловых систем ОС UNIX и Windows. Х Каждой базе данных соответствует подкаталог, расположенный внутри каталога данных. Х Таблицам базы данных соответствуют файлы, размещенные внутри каталога базы данных. Каталог данных содержит также создаваемые сервером файлы состояния, такие, например, как журналы. Они обеспечивают важную информацию о функционировании сервера и просто незаменимы для администратора, особенно когда сервер начинает сбоить и нужно найти источник проблемы. Если неправильно сформированный запрос приводит к сбою в работе сервера, с помощью журналов можно вычислить "виновника".
Как обеспечивается доступ к данным сервера MySQL Каждый элемент внутри каталога данных находится под управлением MySQL-сервера mysqld. Клиентские программы никогда не обращаются к данным напрямую. Точку взаимодействия, с помощью которой осуществляется доступ к базам данным, обеспечивает сервер, работающий в качестве промежуточного звена между клиентскими программами и данными (рис. 10.1). Глава 10. Каталог данных MySQL Клиент Клиент Клиент Доменный разъем UNIX (UNIX) Порт TCP/IP (UNIX, NT) | Именованный канап I (NT) Сетевой интерфейс Таблица! Таблица2 Таблица3 Таблица! Таблица2 Таблица3 Таблица! Таблица2 Таблица3 Рис. 10.1. Управление доступом к каталогу данных сервера MySQL В процессе запуска сервер открывает регистрационные файлы (если таковые запрашиваются) и предоставляет сетевой интерфейс к каталогу данных, прослушивая сетевые соединения. Для получения доступа к данным клиентская программа устанавливает соединение с сервером и посылает SQL-запрос на выполнение определенных операций (например, создание таблицы, извлечение или обновление записей). Сервер выполняет все операции и результаты отправляет обратно клиенту. Сервер представляет собой мультипотоковый механизм, поэтому может обслуживать несколько клиентских соединений одновременно. Однако, так как две и более операции обновления не могут выполняться в один момент, на практике происходит разделение запросов по наборам, чтобы исключить одновременную попытку двух клиентов изменить одну запись. В обычных условиях использование сервера как единственной точки доступа к базе данных обеспечивает защиту от всякого рода недоразумений, связанных с одновременным доступом нескольких процессов к таблицам базы данных. Администраторы, однако, должны знать, что в некоторых случаях серверы не обладают эксклюзивным контролем над каталогом данных. Х В одном каталоге данных запущено несколько серверов. Как правило, для управления всеми базами данных используется один сервер, хотя можно запустить сразу несколько серверов. Если это делается для обеспечения доступа к нескольким отдельным катало. гам данных, то проблем с взаимодействием различных серверов не Часть III. Администрирование MySQL возникает. Можно задать нескольким серверам один каталог данных (хотя удачным такое решение назвать сложно). В таком случае необходимо обязательно убедиться, что системы обеспечивают надежную блокировку файлов друг от друга. Иначе серверы начнут взаимодействовать некорректно. Кроме того, при одновременном запуске нескольких серверов в одном каталоге регистрационные файлы будут содержать беспорядочные данные, а не ценную информацию. Запущены утилиты isamchk и myisamchk. Эти утилиты используются для поддержки таблиц, устранения проблем и отладки баз данных. Поскольку они обладают возможностью изменения LOдержимого таблиц, то могут получать доступ к данным одновременно с сервером. Это может стать источником повреждений данных. Администратор должен понимать, как ограничить подобный совместный доступ, чтобы снизить вероятность разрушения таблиц. Более детально об использовании этих утилит рассказывается в главе 13, "Поддержка и восстановление баз данных". Представление баз данных Каждая управляемая сервером MySQL база данных имеет свой собственный каталог. Он представлен в виде подкаталога каталога данных и имеет такое же название, как и собственно база. Так, например, базе данных my_db будет соответствовать каталог базы данных DATADIR/my_db. Такое представление значительно упрощает понимание предназначения и принципов работы некоторых операторов обработки баз данных. Так, оператор CREATE DATABASE dbjiame создает пустой подкаталог db_name в каталоге данных, устанавливая права владения и режим, которые обеспечивают доступ только для пользователя сервера MySQL (UNIX-пользователя, работающего на сервере). Аналогичных результатов создания базы данных пользователь сервера может добиться и вручную, введя следующие команды.
% mkdir DATADIR/db_name % chmod 700 DATADIR/db_name пользователя сервера MySQL Создает каталог базы данных Делает его доступным только для Такой подход, заключающийся в создании новой базы данных посредством создания пустого каталога, противоречит принципам, принятым в некоторых других СУБД, которые создают большое количество управляющих и системных файлов даже для "пустой" базы данных. Также легко можно реализовать и команду DROP DATABASE. Команда DROP DATABASE dbjwme удаляет из каталога данных подкаталог db_name вместе со всеми расположенными в нем файлами. Тех же результатов можно достичь с помощью команды: % rm -rf DATADIR/db_naiae Глава 10. Каталог данных MySQL (Разница между этими двумя командами заключается в том, что сервер удалит лишь файлы, расширение которых отвечает табличным файлам. Если же в каталоге базы данных были созданы также какие-либо другие файлы, они останутся нетронутыми и каталог удален не будет.) Команда SHOW DATABASES на самом деле выводит список названий подкаталогов каталога данных. Некоторые системы управления базами данных поддерживают специальную таблицу со списком всех баз данных. В MySQL такой таблицы нет. Благодаря простоте структуры список баз является списком подкаталогов каталога данных. Следовательно, и необходимость в подобной таблице отсутствует.
Представление таблиц баз данных Каждая таблица представлена в каталоге базы данных в виде трех файлов: файла формы (описания), файла данных и файла индексов. Основное имя файла соответствует названию таблицы, а его разрешение отражает тип файла. Краткое описание расширений представлено в табл. 10.1. По расширениям файлов данных и индексов можно определить, используется ли в таблице старый формат ISAM или новый MylSAM. Таблица 10.1. Типы файлов MySQL Тип файла Расширение имение файла Содержимое файла Описывает структуру таблицы (столбцы, типы столбцов, индексы и т.п.) Содержит данные таблицы, т.е. его строки Содержит дерево индексов для каждого индекса файла данных. Этот файл существует независимо от того, имеются в таблице индексы или нет Файл формы. f rm Файл данных ISO (ISAM) или MYD (MylSAM) Файл индек- ISM (ISAM) или сов MYI (MylSAM) При выполнении оператора CREATE TABLE tbl_name, определяющего структуру таблицы, сервер создает файл tbl_name. f rm с внутренней кодировкой структуры. Кроме того, создаются также файлы данных и индексов с информацией об отсутствии записей и индексов. (Если оператор CREATE TABLE включает спецификации индексов, в файле индексов они отражаются соответствующим образом.) Параметры владельца и режима файлов таблицы устанавливаются такими, чтобы обеспечить доступ только пользователю сервера MySQL. При исполнении оператора ALTER TABLE расшифровывает файл tbl_name. f rm и изменяет файлы данных и индексов с учетом определенных оператором структурных изменений. Такие же операции имеют место и при выполнении операторов CREATE INDEX и DROP INDEX, no Часть III. Администрирование MySQL скольку они рассматриваются сервером как эквивалентные оператору ALTER TABLE. В процессе выполнения оператора DROP TABLE из каталога базы данных удаляются все три представляющих таблицу файла. Пользователь не может вручную создать или изменить таблицу, хотя имеется возможность удалить ее. Для чего достаточно удалить три соответствующих файла. Так, например, эквивалентом оператора DROP TABLE my_tbl для текущей базы данных my_tbl может быть команда: % rm -f DATADIR/my_db/my_tbl. * Вывод оператора SHOW TABLES my_db представляет собой простой список имен (без расширений) FRM-файлов каталога базы данных my_db. Как уже отмечалось ранее, некоторые СУБД поддерживают специальный реестр со списком всех таблиц баз данных. В MySQL такой реестр не нужен, поскольку список таблиц легко определяется благодаря структуре каталога данных. Ограничения операционной системы на имена баз данных и таблиц В MySQL устанавливается несколько основных правил присвоения имен базам данных и таблицам. Х Имена могут включать буквы и цифры текущего набора символов, а также символы подчеркивания и доллара ("_" и "$"). Длина имен не может превышать 64 символа. Однако так как именам баз данных и таблиц соответствуют названия каталогов и файлов, операционная система, под управлением которой работает сервер, может накладывать дополнительные ограничения. Во-первых, в именах баз данных и таблиц можно использовать только разрешенные для имен файлов символы. Так, например, символ "$" разрешается правилами MySQL, однако в некоторых операционных системах его нельзя применять в именах файлов. Такого рода ограничения отсутствует в ОС UNIX и Windows. Наиболее значительные проблемы могут возникнуть в момент ссылки на имена баз данных при выполнении задач администрирования непосредственно из оболочки. Предположим, например, что база данных имеет имя $my_db. В этом случае всякая ссылка на имя базы может интерпретироваться как ссылка на переменную:
% Is $my_db my_db: Undefined variable.
Чтобы избежать возможных недоразумений, необходимо либо избегать использования символа "$", либо использовать кавычки, подтверждая его специальное значение: % Is my_db % Is '$my_db' Глава 10. Каталог данных MySQL Необходимо использовать одинарные кавычки, поскольку двойные кавычки не отражают специального значения символов имени. Во-вторых, несмотря на то, что MySQL разрешает задавать для баз данных и таблиц имена длиной до 64 символов, на самом деле их длина ограничивается максимально возможной длиной имен файлов. В большинстве случаев эта проблема как таковая отсутствует, хотя в некоторых UNIX-системах System V все еще существует старое ограничение в 14 символов. В таком случае имя таблицы должно содержать не более 10 символов, поскольку четыре остальных позиции отводится под точку и трехсимвольное расширение. В-третьих, на присвоение имен базам данных и таблицам оказывает влияние также чувствительность используемой файловой системы к регистру символов. Если буквы нижнего и верхнего регистров операционной системой воспринимаются по-разному (как, например, в ОС UNIX), имена my_tbl и MYJTBL будут указывать на разные таблицы. Если же регистр не играет никакой роли (как, например, в Windows), my_tbl и MY_TBL окажутся разными названиями одной и той же таблицы. Об этом следует помнить при разработке базы данных, которую впоследствии планируется перенести на другую платформу. Влияние структуры каталога данных на производительность системы Построение структуры каталогов данных на основании иерархической структуры файловой системы делает структуру каталога данных понятной для пользователей. В то же время, эта структура оказывает определенное влияние на производительность системы. Особенно это справедливо в отношении операций открытия файлов таблиц баз данных. Поскольку в структуре каталога данных таблица представляется несколькими файлами, для открытия каждой таблицы требуется не один, а сразу несколько дескрипторов файлов. Сервер весьма эффективно кэширует дескрипторы, однако загруженному серверу для обслуживания множества одновременных клиентских соединений и обработки сложных запросов к нескольким таблицам потребуется большое количество дескрипторов. Дескрипторы файлов Ч весьма ограниченный ресурс во многих системах, особенно в тех из них, в которых по умолчанию установлен низкий лимит. В главе 11, "Общее администрирование MySQL", рассказывается, как рассчитывать необходимое число дескрипторов и настроить конфигурацию сервера или операционной системы. Еще один негативный момент, связанный с представлением одной таблицы в виде нескольких файлов, заключается в том, что с увеличением таблиц увеличивается и время их открытия. Операции открытия таблиц целиком и полностью базируются на системных операциях открытия файлов, а следовательно, зависят от эффективности работы механизмов поиска файлов операционной системы. Как правило, эта проблема несу468 Часть III. Администрирование MySQL шественна, хотя о ней все же стоит помнить при создании базы данных с большим количеством таблиц. Так, например, каталог базы данных, включающей 10000 таблиц, содержит 30000 файлов. При открытии большого количества таблиц замедление выполнения операций открытия становится достаточно заметным. (Особенно это относится к файловым системам Linux ext2 и Solaris.) Если же эта проблема приобретает действительно угрожающие масштабы, возможно, имеет смысл пересмотреть структуру своих таблиц в соответствии со спецификой работы приложений и соответствующим образом их реорганизовать. Тщательно подумайте, действительно ли необходимо такое большое число таблиц. Иногда приложения генерируют их безо всякой на то необходимости. Много таблиц с аналогичными структурами могут генерироваться приложениями, которые создают отдельную таблицу для каждого пользователя. Чтобы объединить такие таблицы в одну, достаточно добавить столбец, который идентифицирует пользователявладельца строки. Если в результате таких действий число таблиц значительно уменьшится, производительность приложения возрастет. Всегда при проектировании базы данных следует анализировать, подходит ли для данного приложения та или иная стратегия. Однако существуют также причины, не позволяющие объединять таблицы описанным выше способом. Основными среди них являются следующие. Х Ограничение дискового пространства. Объединение таблиц приводит к уменьшению их числа (уменьшая, в свою очередь, время открытия), но сопровождается добавлением дополнительных столбцов (занимая дополнительное дисковое пространство). В результате имеет место достаточно распространенный компромисс между временем обработки и свободным пространством, и администратор должен решить, какой фактор более важен. Если на первом плане стоит скорость, возможно, придется пожертвовать дополнительным дисковым пространством. Если же объемы дисков сильно ограничены, придется использовать большее количество таблиц и немного дольше ждать при открытии. Вопросы безопасности. Эта причина также может в значительной степени ограничить возможности и желание объединить таблицы. Основная цель использования отдельных таблиц для каждого пользователя Ч открытие доступа к данным таблицы только пользователям, обладающим соответствующими привилегиями. На самом деле права пользователей определяются полномочиями на уровне таблицы. После объединения данные всех пользователей окажутся в одной таблице. Возможности MySQL не позволяют ограничить для определенного пользователя доступ к определенным строкам таблиц. Соответственно, нельзя объединить таблицы, не потеряв контроля над доступом. Однако если доступ к данным контролируется приложением (пользователи не подключаются непосредственно к базе данных), Глава 10. Каталог данных MySQL можно спокойно объединить таблицы и для определения прав доступа воспользоваться соответствующими средствами приложения. MySQL накладывает внутреннее ограничение на размеры таблицы, однако поскольку таблицы представляются в виде файлов, учитывать следует также и максимальный размер файла, разрешаемый операционной системой. Другими словами, максимально возможный размер таблицы определяется наиболее жестким ограничением среди ограничений MySQL и операционной системы. В последнее время намечается тенденция к ослаблению ограничений на размеры таблиц. Так, например, если в ОС IBM AIX 4.1 имело место ограничение на размер файла 2 Гбайта, то в ОС IBM AIX 4.2 оно увеличилось до приблизительно 64 Гбайт. Внутренний лимит на размер таблицы в MySQL в новых версиях также увеличивается. Так, если во всех предшествующих версии 3.23 системах он составляет 4 Гбайта, то в 3.23 был поднят до приблизительно 9 миллионов терабайт. Данные табл. 10.2 позволяют оценить, как внутренний лимит на размер таблицы в MySQL сопоставляется с ограничением файловой системы AIX. Подобные сопоставления можно применять и для других операционных систем. Таблица 10.2. Сопоставление ограничений MySQL и операционной системы Версия MySQL Версия Ш Максимальный Ограничивающий фактор размер таблицы 2 Гбайт Максимальный размер файла AIX 2 Гбайта Максимальный размер таблицы MySQL Ч 4 Гбайта Максимальный размер файла AIX Х 2 Гбайта Максимальный размер таблицы MySQL Ч 64 Гбайта MySQL 322 22 AIX MySQL 322 22 AIX 4 2 4 Гбайт MySQL 323 О AIX 4.1 2 Гбайт MySQL 3.23 О AIX 4 2 64 Гбайт Файлы состояния MySQL Помимо подкаталогов баз данных, каталог данных MySQL содержит множество файлов состояния. Краткий обзор этих файлов представлен в табл. 10.3, а детальное их описание следует далее. По умолчанию основная часть имени этих файлов содержит имя главного компьютера. В таблице это имя заменено словом HOSTNAME. Сервер записывает ID-номер своего процесса (Process ID Ч PID) в PIDфайл при запуске и удаляет этот файл при завершении работы. Именно с помощью PID-файла сервер позволяет находить себя работающим процессам. Так, например, если во время завершения работы системы запустить Часть III. Администрирование MySQL сценарий mysql. server для завершения работы и сервера MySQL, данный сценарий обратится к PID-файлу. Это обращение позволит определить, какому процессу отправить команду на завершение работы. Таблица 10.3. Файлы состояния MySQL Тип файла ID-номер процесса Журнал ошибок Общий журнал Имя по умолчанию HOSTNAME, pid HOSTNAME, err HOSTNAME.log Содержимое файла ID-номер процесса сервера События запуска и завершения работы, а также записи об ошибках События подключения/отключения и информация о запросах Текст всех запросов, изменяющих содержимое или структуру таблицы Журнал обновлений HOSTNAME.nnn Журнал ошибок создается сценарием safejnysqld. Впоследствии именно в этот журнал перенаправляются все стандартные сообщения об ошибках сервера Другими словами, журнал содержит все сообщения, записываемые сервером в stderr, и существует, только если сервер запускается с помощью вызова сценария safe_mysqld. (Это более предпочтительный метод запуска сервера, поскольку safe_mysqld перезапускает сервер при сбое в работе из-за ошибки.) Общий журнал и журнал обновления являются необязательными. Используя опции --log и Чlog-update, можно задать регистрацию только необходимой информации. Общий журнал предоставляет информацию о функционировании сервера: кто и с какого компьютера подключился и какие запросы присылает. Журнал обновлений также содержит информацию о запросах, однако только о тех из них, которые связаны с изменением содержимого баз данных. Содержимое же журнала обновлений представляется в виде операторов SQL. Впоследствии их можно выполнить, представив в виде ввода для клиента mysql. Журналы обновлений оказываются особенно полезными в случае сбоя, когда необходимо отследить все изменения, внесенные с момента последнего резервирования базы данных. Их использование позволяет восстановить базы данных до состояния, в котором они находились перед самым сбоем. Ниже представлены данные, которые заносятся в общий журнал в течение короткой клиентской сессии. На протяжении работы клиент создает таблицу в базе данных test, вставляет в нее строку и затем удаляет всю таблицу.
990509 7:34:09 492 492 492 492 492 Connect Query Query Field List Field List paul@localhost on test show databases show tables tbl_l tbl Глава 10. Каталог данных MySQL 990509 990509 990509 7:34:22 7:34:34 7:34:38 7:34: 492 492 492 Query Query Query Quit CREATE TABLE my_tbl (val INT) INSERT INTO my_tbl VALUES(1) DROP TABLE my tbl Отдельные столбцы общего журнала отражают дату и время события, ID-номер сервера, тип события и относящуюся к нему специальную информацию Тот же сеанс в журнале обновлений отобразится следующим образом:
use test;
CREATE TABLE my_tbl (val INT);
INSERT INTO my_tbl VALUES(1);
DROP TABLE my_tbl;
Для получения расширенной формы журнала обновлений используется опция Чlog-long-format. В расширенном журнале предоставляется информация также об отправителе и времени поступления запроса. Эти данные занимают, конечно, больше места на диске, однако позволяют узнать, кто и что пытался сделать. Сопоставлять информацию о событиях в общем журнале и журнале обновлений для этого не нужно. Для уже описанной выше сессии расширенный журнал обновлений будет выглядеть таким образом. # Time: 9905097:43: # User@Host: paul [paul] @ localhost [] use test;
CREATE TABLE my_tbl (val INT);
# Qser@Host: paul [paul] @ localhost [} INSERT INTO my_tbl VALUES(1);
# Time: 9905097:43:43 # User@Host: paul [paul] @ localhost [] DROP TABLE my_tbl;
Администратору настоятельно рекомендуется убедиться, что файлы журналов защищены от просмотра случайными пользователями. Как общий журнал, так и журнал обновлений могут содержать такую критически важную информацию, как пароли. Ведь они включают текст отправляемых запросов Ниже представлен пример одной записи журнала, которую вряд ли даже начинающий администратор захочет показать другому пользователю, поскольку она содержит пароль пользователя.
990509 7:47:24 4 Query Password=PASSWORD("secret") UPDATE user SET WHERE user="root" Детально о проверке и настройке полномочий на доступ к каталогу данных рассказывается в главе 12, "Безопасность". Для защиты каталога данных можно воспользоваться простой командой: % chmod 700 DATADIR Запустите эту команду, зарегистрировавшись в качестве пользователя-владельца каталога данных. Не забудьте также зарегистрироваться под именем этого пользователя на сервере, иначе команда не только 472 Часть III. Администрирование MySQL запретит другим пользователям доступ к каталогу данных (что и требуется), но также закроет базы данных для сервера (чего допустить ни в коем случае нельзя!). Файлы состояния размещаются на верхнем уровне каталога данных вместе с каталогами баз данных. Поэтому иногда пользователи беспокоятся, что имена файлов состояния могут конфликтовать с именами баз данных (например, при выполнении сервером оператора SHOW DATABASES). Этого бояться не стоит Информация о событиях и состояниях хранится в файлах, а базы данных записаны в каталогах, что позволяет исполняемым программам легко отличить их, вызвав команду s t a t ( ). (Именно таким образом их различает сервер.) Просматривая каталог данных с помощью опции Is -1, пользователь может отличить файлы состояния от каталогов баз данных, определив первую букву данных в режиме: ' - ' или ' d':
% Is -1 DATADIR total 31 1 mysqladm drwxrwx 2 mysqladm drwxrwx -rw-rw 1 mysqladm -rw-rw-r-- 1 mysqladm -rw-rw 1 mysqladm -rw-rw-r-- 1 mysqladm 7 mysqladm drwxrwx drwxrwx 2 mysqladm mysqlgrp 1024 May 8 mysqlgrp 1024 Dec 15 mysqlgrp 64 May 9 mysqlgrp 24168 May 9 mysqlgrp 4376 May 9 mysqlgrp 5 May 9 mysqlgrp 512 Sep 10 mysqlgrp 512 May 13 :22 bigdb 22 :34 mysql 20: 11 pit-viper..001 : 20 : 11 pit-viper.err 20: 11 pit-viper..log : 20: 11 pit-viper. pid : sql-bench 1998 07: :34 test Можно также просто просмотреть список имен, ведь имена всех файлов состояния включают точку, в именах баз данных она не используется (более того, запрещена). Более детально о поддержке файлов регистрации и способах работы с ними рассказывается в главе 11, "Общее администрирование MySQL". Перемещение содержимого каталога данных В предыдущем разделе описывалась структура каталога данных, создаваемая сервером по умолчанию. Этот каталог содержит все рабочие базы данных и файлы состояния. Иногда возникает необходимость в определении специального места для хранения содержимого каталога данных. В этом разделе рассказывается, зачем может потребоваться перемещать отдельные части каталога данных (и даже сам каталог), какие его компоненты можно перемещать и как такое перемещение осуществить. Возможности MySQL позволяют администратору перемещать каталог данных или его внутренние элементы на другое место. Необходимость в этом может быть вызвана следующими причинами.
Глава 10. Каталог данных MySQL Каталог данных можно разместить на диске большего размера, чем используется в настоящий момент. Если каталог данных располагается на часто используемом диске, перемещение его на другой диск позволит уровнять загрузку среди физических дисков. В этом случае можно разместить файлы баз данных и журналов на отдельном диске или распределить их по нескольким дискам сразу. Каждый из одновременно запущенных серверов можно разместить в своем каталоге данных. Такой подход является одним из способов обойти ограничения на файловые дескрипторы, особенно если эти ограничения нельзя устранить посредством настройки ядра системы. Некоторые операционные системы хранят PID-файлы сервера в отдельном каталоге, например / v a r / r u n. Возможно, для большей согласованности работы системы администратор пожелает разместить в этой папке и PID-файлы MySQL. Методы перемещения Существует два способа перемещения компонентов каталога данных. ш Х Определение опции загрузки сервера с помощью командной строки или в группе [mysqld] конфигурационного файла.
Перемещение элементов и создание в исходном каталоге символической связи (symbolic link), указывающей на новое местоположение. Ни один из приведенных методов не является универсальным для переноса информации. В табл. 10.4 отмечается, какие компоненты каталога данных можно перемещать и какой метод следует для этого использовать. Если применяется первый метод, можно задать опции в глобальном конфигурационном файле / e t c / m y. c n f (C:\my.cnf на компьютерах, работающих под управлением ОС Windows). В последних версиях ОС Windows этот файл может располагаться в системной папке (С: \windows или С: \NT). Таблица 10.4. Обзор методов перемещения Перемещаемый компонент Целый каталог данных Каталоги отдельных баз данных Отдельные таблицы баз данных PID-файл Файл общего журнала Файл журнала обновлений Применяемый метод перемещения Опция запуска или символическая связь Символическая связь Символическая связь Опция запуска Опция запуска Опция запуска Часть III. Администрирование MySQL Для перемещения можно также использовать файл m y. c n f, расположенный в каталоге данных по умолчанию, однако делать это не рекомендуется. Если ваша цель Ч переместить весь каталог данных, необходимо оставить этот каталог нетронутым на старой позиции, чтобы разместить в нем конфигурационный файл со ссылкой на "реальный" каталог данных. Это может привести к путанице. Для определения опций сервера лучше воспользоваться конфигурационным файлом /etc/my, cnf. Определение эффекта перемещения Прежде чем приступать к перемещению каких-либо компонентов, настоятельно рекомендуется убедиться, что эта операция приведет к желаемому эффекту. Для получения информации о пространстве диска некоторые пользователи предпочитают использовать команды du, df и Is -I, хотя этот выбор, в первую очередь, определяется правильным пониманием структуры используемой файловой системы. В приведенном ниже примере существует едва заметная ловушка, в которую можно попасться при перемещении каталога данных. Предположим, что каталог данных / u s r / l o c a l / v a r планируется переместить в каталог / v a r / m y s q l, поскольку согласно выводу команды df файловая система /var содержит больше свободного пространства:
% df /usr /var Filesystem IK-blocks /dev/wdOs3e 396895 /dev/wdOs3f Used 292126 Avail Capacity 73018 80% 162287 15% Mounted on /usr /var Сколько же пространства освободится в файловой системе /usr в результате перемещения каталога данных? Чтобы вычислить этот объем, воспользуемся командой du -s и посмотрим, сколько этот каталог занимает:
% cd /usr/local/var % du -s Как видно, этот каталог занимает чуть более 130 Мбайт, которые можно освободить в /usr. Однако можно ли этот прием реализовать на самом деле? Запустите команду df в каталоге данных: % df /usr/local/var Filesystem IK-blocks Used Avail Capacity Mounted on /dev/wdOs3f 1189359 1111924 162287 15% /var Что же получается? При запросе объема свободного пространства в файловой системе, содержащей каталог / u s r / l o c a l / v a r, команда df отображает свободный объем в /var. Почему так? Ответ на этот вопрос дает команда Is -1: % Is -I /usr/local Irwxrwxr 1 root wheel 10 Dec 11 23:46 var -> /var/mysql Глава 10. Каталог данных MySQL Из результатов выполнения этой команды видно, что / u s r / l o c a l / v a r является символической связью с /var/mysql. Другими словами, каталог данных уже перемещен в файловую систему /var и включает указывающую на нее символическую связь. Соответственно, никакой выгоды перемещение каталога данных из /usr в /var не принесет. Суть этого примера состоит в том, что несколько действий по определению эффекта перемещения могут показать нецелесообразность подобного перемещения. Такая предосторожность позволяет вовремя остановиться и не тратить уйму времени на перемещение только для того, чтобы затем понять, что достичь нужной цели невозможно. Перемещение каталога данных Для перемещения каталога данных необходимо завершить работу сервера и только после этого перенести каталог на новую позицию. Затем необходимо удалить данные исходного каталога и заменить его символической связью, указывающей на новую позицию, либо перезапустить сервер с опцией, определяющей новое местоположение. Синтаксис командной строки и конфигурационного файла представлен в табл. 10.5. Таблица 10.5. Синтаксис перемещения каталога данных Метод Синтаксис Командная строка Конфигурационный файл опций Чdatadir=/pa№/fo/cf/r [mysqld] datadir=/paf/7/(o/d/r Перемещение баз данных Базы данных можно перемещать только с помощью метода символической связи. Для этого необходимо завершить работу сервера, перенести каталог базы данных, затем удалить этот каталог и заменить его символической связью, указывающей на новую позицию. После этого можно перезапустить сервер. Так, например, перемещение базы данных bigdb на другое место выполняется с помощью следующих команд.
% mysqladmin -u root -p shutdown Enter password: ******** % cd DATADIR % tar cf - bigdb | (cd /var/db;
tar xf -) % mv bigdb bigdb.orig % In -s /var/db/bigdb. % safe_mysqld & Часть III. Администрирование MySQL Меры предосторожности при перемещении Необходимо завершить работу сервера перед выполнением операции перемещения и запустить его снова впоследствии При перемещении некоторых компонентов (например, каталога базы данных) можно, хотя и не рекомендуется, оставить сервер в рабочем состоянии В этом случае следует убедиться, что сервер не обращается к перемещаемой базе данных. Не забудьте также выполнить оператор FLUSH TABLES перед перемещением базы данных, чтобы сервер закрыл все открытые файлы таблиц. Игнорирование этих моментов может привести к повреждению таблиц. Для выполнения всех этих команд необходимо зарегистрироваться в качестве владельца каталога данных. Как видите, исходный каталог данных переименовывается для безопасности в bigdb.orig. После проверки правильности работы сервера с перемещенной базой данных его можно удалить: % пп -rf bigdb.orig Перемещение таблиц баз данных Перемещение отдельных таблиц баз данных Ч далеко не самая лучшая идея. В случае необходимости эту операцию можно реализовать посредством переноса файлов таблиц на новую позицию и установки символических связей в исходном каталоге данных, указывающих на новое местоположение. Однако если впоследствии выполнить операторы ALTER TABLE или OPTIMIZE TABLE, изменения внесены не будут. В процессе выполнения каждого такого оператора в каталоге базы данных сначала создается временная таблица, которая и поддается изменению или оптимизации. Сразу после этого исходная таблица удаляется, а ее имя присваивается временной таблице. В результате этой процедуры символические связи будут удалены, а новая измененная таблица окажется записанной в том же каталоге данных, откуда ранее была перемещена исходная таблица. В конечном итоге, старые перемещенные ранее файлы таблиц оказываются на новой позиции. В большинстве случаев пользователи о них забывают, что способствует неэффективному использованию дискового пространства. Кроме того, в процессе изменения удаляются символические связи, из-за чего впоследствии оказывается довольно трудно вспомнить, куда были перемещены файлы таблиц. Поскольку практически невозможно гарантировать, что обладающие соответствующими полномочиями пользователи не будут пытаться изменить или оптимизировать таблицы (таким образом, сводя на нет все усилия по их перемещению), лучше оставить файлы таблиц размещенными в каталоге базы данных.
Глава 10. Каталог данных MySQL Перемещение файлов состояния Перемещение PlD-файла, общего журнала и журнала обновлений осуществляется с помощью символических связей. Как уже отмечалось ранее, журнал ошибок создается сценарием safejmysqld, и поэтому не может перемещаться куда-либо (если, конечно, для этого не прибегнуть к редактированию safe_mysqld) Для записи файла состояния в новую позицию завершите работу сервера и перезапустите его, точно определив посредством соответствующей опции новое местоположение. Синтаксис командной строки и файла опций для каждого файла состояния представлен в табл. 10 6. Таблица 10.6. Синтаксис перемещения файлов состояния Метод Командная строка Синтаксис Чpid-f ile=p/dfi/e Ч log=/ogf//e Ч log-update=updaff/fe [mysqld] pid-file=p/d///e log=/ogf//e log-update=updafef//e Файл опций ;
| ( ;
Удаление перемещенной базы данных Удалить базу данных можно с помощью оператора DROP DATABASE, хотя в старых версиях MySQL с удалением перемещенной базы данных могут возникнуть проблемы Таблицы такой базы данных будут удалены правильно Ошибка возникает при попытке сервера удалить каталог базы данных поскольку он является лишь символической связью, а не реальным каталогом Администратор MySQL должен самостоятельно удалить каталог базы данных и указывающую на него связь Эта проблема устранена в MySQL версии 3 23 и выше Если определить имя файла состояния, указав полный путь, то файл будет создан в определенной этим путем позиции. Во всех остальных случаях файл создается в каталоге данных. Так, например, при определении опции Чpid-file=/var/run/mysqld.pid PID-файл mysqld.pid будет создан в каталоге /var/run. Если же определена опция Чpid-file=mysqld.pid этим файлом окажется файл Zi47/4Z)/^/mysqld.pid. При определении имени журнала обновлений без расширения MySQL будет создавать последовательные имена каждый раз при открытии этого журнала. Эти имена будут дополняться расширением ппп, где ппп Ч следующий неиспользуемый существующим файлом журнала обновлений номер (например, update. 001, update. 002 и тп). Чтобы избежать создания подобных имен сервером, достаточно определить имя с явным расширением. 478 Часть III. Администрирование MySQL и Общее администрирование MySQL Эта глава посвящена рассмотрению задач администратора MySQL, точное выполнение которых позволит обеспечить согласованную и эффективную работу сервера MySQL. К задачам подобного рода относится проверка работоспособности сервера, достижение максимально возможной производительности, настройка пользовательских учетных записей для обеспечения клиентского доступа к серверу, поддержка журналов и резервирование баз данных. В некоторых случаях, когда на одном компьютере запускается несколько серверов, администратору для достижения максимальной производительности работы приходится также изменять операционные параметры работы сервера. Стремительное развитие возможностей MySQL вынуждает администратора пристально следить за новинками и вовремя обновлять свою систему MySQL посредством инсталляции новых версий. Кроме того, существуют и другие задачи администрирования, однако о них речь пойдет в главах 12, "Безопасность", и 13, "Поддержка и восстановление баз данных". В этой и следующих главах описывается несколько программ, исключительно полезных для выполнения задач администрирования MySQL. Глава 11. Общее администрирование MySQL Утилита mysqladmin позволяет выполнять всевозможные административные функции. Х Сценарии safe_mysqld и mysql. server применяются для запуска MySQL-сервера mysqld. Х Программа mysqldump используется для резервирования и копирования баз данных. Х Утилиты myisamchk и isamchk применяются для проверки целостности данных таблиц и операций отладки. Дополнительную информацию об этих программах можно найти в приложении Д, "Программы MySQL". Защита новой инсталляции MySQL Многие пользователи наверняка приступят к чтению этой главы сразу после выполнения инсталляции MySQL (в соответствии с инструкциями приложения А, "Получение и инсталляция программного обеспечения"). На этом этапе необходимо обязательно установить пароль для MySQLпользователя root, поскольку сразу после установки права сервера не защищены. Предполагается, что каталог данных и база данных mysql с таблицей разрешений уже инициализированы. На компьютерах с UNIX для их инициализации достаточно запустить сценарий mysql_install_db. На компьютерах, работающих под управлением Windows, каталог данных и база данных mysql инициализируются посредством запуска программы Setup в дистрибуции сервера. Итак, каталог и основная база данных про инициализированы, и сервер запущен. Сразу после первой инсталляции MySQL на компьютере привилегии в таблице разрешений базы данных mysql устанавливаются следующим образом. Х Зарегистрироваться в качестве основного пользователя root с ло кального компьютера можно без пароля. Пользователь root обла дает всеми возможными правами (включая административные) \ может выполнять любые операции. (Кстати, совпадение имен су перпользователей MySQL и UNIX не является закономерностью Они никак друг на друга не влияют.) Х Права анонимного доступа предоставляются всем пользователям, подключающимся с локального компьютера к базе данных test или любой другой базе данных, имя которой начинается со слова test_. Анонимные пользователи могут выполнять любые операции с такими таблицами, но не обладают привилегиями администратора. Для подключения к серверу с локального компьютера можно определить как имя главного компьютера localhost, так и его реаль480 Часть III. Администрирование MySQL а ное имя. Например, если сервер размещается на компьютере pitviper. snake. net, клиент этого компьютера может подключиться без пароля к серверу для работы с базой данных test с помощью одной из двух следующих команд:
% mysql -h localhost test % mysql -h pit-viper.snake.net test На полное отсутствие защиты исходной инсталляции указывает тот факт, что подключиться к серверу MySQL можно в качестве пользователя root абсолютно без пароля. Именно поэтому одна из первых задач администратора MySQL заключается в установке пароля для пользователя t root. Затем, в зависимости от метода установки пароля, возможно, еще [придется указать серверу перезагрузить таблицы разрешений, чтобы загрузить в память все сделанные изменения. (В процессе запуска сервер i загружает таблицы в память и может не заметить внесенные впоследст|вии изменения. В таком случае следует явным образом указать ему на |необходимость повторного считывания таблиц.) В версиях MySQL 3.22 и выше установить пароль можно с помощью 1 команды mysqladmin. Для этого достаточно ввести следующую команду, заменив ее часть my password реальным паролем:
~- mysqladmin -u root password "my password" Во всех остальных версиях MySQL для этих целей можно воспользоваться программой mysql и непосредственно обновить таблицу разрешений grant в базе данных mysql:
t mysql -u root mysql nysql> UPDATE user SET Password=PASSWORD("my password") -> WHERE User="root";
Команда mysql и оператор UPDATE применяется в старых версиях MySQL, а также во всех бесплатно распространяемых версиях под Windows. После установки пароля необходимо определиться, следует ли задавать серверу перезагрузку таблицы разрешений. Для этого запустите следующую команду:
% mysqladmin -u root status Если сервер все еще позволяет подключаться в качестве пользователя root без пароля, укажите ему перезагрузить таблицы, введя следующую команду:
% mysqladmin -u root reload После определения пароля пользователя root (и перезагрузки таблиц разрешений), самое время приступать к определению нового пароля для администратора.
Глава 11. Общее администрирование MySQL 4-16- Настройка процедур запуска и завершения работы сервера MySQL Одна из основных обязанностей администратора MySQL Ч обеспечить согласованную и длительную работу сервера, что даст возможность пользователям получать доступ к нему в любое удобное время. Иногда, однако, возникает необходимость и во временной приостановке работы сервера. (Так, например, при перемещении базы данных необходимо убедиться, что сервер в это же время не обновляет ее таблицы.) Эта книга не поможет разрешить компромисс между необходимостью постоянной работы сервера и желанием иногда временно приостановить его работу, поскольку зачастую решение этого компромисса зависит от конкретной ситуации. По крайней мере читатели смогут узнать, как запустить и завершить работу сервера. Все приведенные в этой главе инструкции применимы только к операционным системам UNIX. Пользователям компьютеров Windows эту главу можно пропустить, поскольку в приложении А, "Получение и инсталляция программного обеспечения", представлены все необходимые для запуска и завершения работы сервера команды., "4 '"'J ft | Х1 II Запуск представленных в этой главе команд В большинстве примеров этой главы такие программы, как mysqladmin, mysqidump и им подобные для краткости представлены без опций -h, -u и р. Предполагается, что пользователи будут правильно вызывать эти программы, используя в случае необходимости подключения к серверу соответствующие опции.
В версии MySQL 3.22.11 и выше перезагрузить таблицы можно с помощью команды mysqladmin flush-privileges и SQL-оператора FLUSH PRIVILEGES.
Запуск сервера MySQL непривилегированным пользователем Прежде чем приступить к рассмотрению процедуры запуска сервера, давайте обсудим, какие пользователи могут выполнить подобный запуск. Сервер может запускаться вручную или автоматически. В первом случае сервер запускается в качестве пользователя, под именем которого зарегистрирован администратор, запускающий сервер (или другой сотрудник) Другими словами, если администратор зарегистрирован под именем пользователя paul и запускает сервер, сервер будет работать с правами пользователя paul. Если затем администратор с помощью команды su зарегистрируется в качестве пользователя root и запустит сервер, сервер будет работать с правами пользователя root. 482 Часть III, Администрирование MySQL Однако всегда запускать сервер вручную весьма неудобно. Лучше всего настроить сервер на автоматический запуск во время загрузки системы. На UNIX-компьютерах процедура запуска выполняется системой с помощью UNIX-пользователя root, соответственно все процессы, запускаемые во время этой процедуры, работают впоследствии с привилегиями пользователя root. Администратору следует помнить о двух целях, преследуемых при настройке процедуры запуска сервера MySQL. Х Желательно, чтобы сервер не обладал привилегиями пользователя root. В большинстве случаев рекомендуется ограничивать возможности всех процессов, не требующих прав доступа root. He нужны эти права и серверу mysqld. Х Желательно, чтобы сервер все время работал под именем одного и того же пользователя. Весьма нелогично запускать сервер в разное время под именами разных пользователей, поскольку в этом случае файлы и каталоги с данными будут создаваться разными владельцами. В определенный момент времени это может привести к невозможности получения доступа к базам данных или таблицам. Постоянный запуск сервера от имени одного пользователя позволит избежать этой проблемы. Для запуска сервера под обычным пользователем, не обладающим широкими правами, выполните следующие действия. 1. Выберите учетную запись, предназначенную для запуска сервера. Сервер mysqld может работать от имени любого пользователя, однако лучше создать для него отдельную учетную запись. Можно также создать специальную группу для работы с MySQL. Предположим, что такой пользователь и группа уже созданы и имеют имена mysqladm и mysqlgrp. Пользователям, определившим другие имена для этих целей, придется подставлять их далее в примерах кода вместо mysqladm и mysqlgrp. Если же MySQL был установлен под каким-либо пользователем, не обладающим специальными правами администратора, сервер наверняка будет запускаться под собственным именем этого пользователя. В этом случае необходимо заменить mysqladm и mysqlgrp соответствующим регистрационным именем и именем группы. Если же система MySQL инсталлировалась под управлением ОС Linux Red Hat с помощью RPM-файла, в процессе установки автоматически создается учетная запись с именем mysql. Ее в последующих примерах этой книги нужно применять вместо mysqladm. 2. Создайте, если необходимо, учетную запись сервера, используя обычную процедуру создания учетных записей системы. Для этого необходимо сначала зарегистрироваться в качестве пользователя root. Глава 11. Общее администрирование MySQL 3. Завершите работу сервера, если он работает. 4. Измените права владения каталогом данных, а также всех его подкаталогов и файлов, чтобы новым владельцем этих элементов оказался пользователь mysqladm. Например, чтобы сделать пользователя mysqladm владельцем каталога данных / u s r / l o c a l / v a r, необходимо ввести следующие команды (зарегистрировавшись в качестве пользователя root).
# cd /usr/local/var. Переход в каталог данных. # ohown -R mysqladm.mysqlgrp. Установка прав владения для всех каталогов и файлов.
5. Измените полномочия доступа к каталогу данных и всем его подкаталогам и файлам, чтобы работать с ними мог только пользователь mysqladm. Запретите доступ к данным всем остальным пользователям Ч это самая эффективная мера предосторожности. Если каталог данных размешается в директории / u s r / l o c a l / v a r, определить права владения на него для пользователя mysqladm можно с помощью следующих команд (зарегистрировавшись в качестве пользователя root).
# cd /usr/local/var. # chmod -R go-rwx. Переход в каталог данных. Все элементы будут доступны только для пользователя mysqladm.
Pages: | 1 | ... | 3 | 4 | 5 | 6 | 7 | ... | 11 | Книги, научные публикации