Бегун.Рекомендую - рекламодателю
Здесь может быть ваша реклама
|

 Глава №4. MySQL
Глава №4. MySQL

MySQL является, возможно, самым ярким программным проектом после выхода Linux. В то время как mSQL заслуживает уважения за то, что столкнула дело управления базами данных с мертвой точки, MySQL умело воспользовалась моментом. Сейчас она не больше и не меньше, как серьезный конкурент большим СУБД в области разработки баз данных малого и среднего масштаба. В самом начале MySQL просто стала заменой устаревающему ядру mSQL версии 1. Как отмечалось в Главе 1, признаки старения mSQL проявились в виде проблем со стабильностью и неспособностью удовлетворить растущие требования, которые обрушились на нее благодаря успеху. MySQL использовала основы проекта mSQL и теперь превосходит ее по набору характеристик и одновременно по производительности.

Проект

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

Сделав MySQL многопоточной, ТсХ дала пользователям много выгод. Каждое входящее соединение обрабатывается отдельным потоком, при этом еще один всегда выполняющийся поток управляет соединениями, поэтому клиентам не приходится ждать завершения выполнения запросов других клиентов. Одновременно может выполняться любое количество запросов. Пока какой-либо поток записывает данные в таблицу, все другие запросы, требующие доступа к этой таблице, просто ждут, пока она освободится. Клиент может выполнять все допустимые операции, не обращая внимания на другие одновременные соединения. Управляющий поток предотвращает одновременную запись какими-либо двумя потоками в одну и ту же таблицу.

Такая архитектура, конечно, более сложна, чем однопоточная архитектура mSQL. Однако выигрыш в скорости благодаря одновременному выполнению нескольких запросов значительно превосходит потери скорости, вызванные увеличением сложности.

Другое преимущество многопоточной обработки присуще всем многопоточным приложениям. Несмотря на то что потоки совместно используют память процесса, они выполняются раздельно. Благодаря этому разделению выполнение потоков на многопроцессорных машинах может быть распределено по нескольким ЦП. На рис. 4-1 показана эта многопоточная природа сервера MySQL.

Рис. 4-1. Клиент-серверная архитектура MySQL

Помимо выигрыша в производительности, полученного благодаря многопоточности, MySQL поддерживает большее подмножество SQL, чем mSQL. MySQL поддерживает более десятка типов данных, а также функции SQL. Ваше приложение может получить доступ к этим функциям через команды ANSI SQL.

MySQL фактически расширяет ANSI SQL несколькими новыми возможностями. В их числе новые функции (ENCRYPT, WEEKDAY, IF и другие), возможность инкрементирования полей (AUTO_INCREMENT и LAST_INSERT_ID), а также возможность различать верхний и нижний регистры.

ТсХ намеренно опустила некоторые возможности SQL, встречающиеся в больших базах данных. Наиболее заметно отсутствие транзакций и встроенных процедур. Как и Дэвид Хьюз в mSQL, ТсХ решила, что реализация этих возможностей нанесет слишком сильный удар по производительности. Однако ТсХ продолжает работу в этом направлении, но так, чтобы от потери производительности страдали только те пользователи, которым такие возможности действительно необходимы.

С 1996 года ТсХ использует MySQL в среде, где имеется более 40 баз данных, содержащих 10 000 таблиц. Из этих 10 000 более 500 таблиц имеют, в свою очередь, более 7 миллионов записей - около 100 Гбайт данных.

Установка MySQL

Прежде чем использовать MySQL, ее необходимо установить. MySQL работает почти на любой известной Unix-платформе, а также под управлением Windows 9x, Windows NT и OS/2. Для Windows 9x и Windows NT требуется приобрести лицензию. Если вы хотите лишь протестировать работу этой СУБД, имеется более старая бесплатная версия.

Дистрибутив MySQL существует как в двоичном виде, так и в виде исходного текста. Если вы не прочь внести свой вклад в проект MySQL, добавить к нему свои усовершенствования или просто не можете найти двоичного дистрибутива для своей платформы, то необходимо взять дистрибутив с исходным кодом. Большинству пользователей, впрочем, следует брать двоичный дистрибутив. Самые свежие дистрибутивы - двоичные и в виде исходных текстов - можно найти на http://www.mysql.com

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

Запуск MySQL

Как правило, сервер баз данных работает постоянно. В конце концов, какой смысл иметь базу данных, если нельзя получить данные. ТсХ создавала MySQL, имея это в виду, поэтому MySQL работает под Unix как демон, и как служба — под Windows NT. В Windows 95 есть только грубый аналог, реализуемый помещением ярлыка исполняемого приложения в папку Автозагрузка (StartUp). Важной особенностью работы под Windows 95 является то, что при создании каждого потока происходит утечка примерно 200 байт оперативной памяти. Поэтому под Windows 95 не следует надолго оставлять MySQL работающей. К Windows 98 и Windows NT это не относится.

Запуск MySQL осуществляется с помощью сценария safe_mysqld. Под Unix этот файл устанавливается по умолчанию как /usr/local/bin/ safe_mysqld. Это сценарий для командного процессора Борна, и вы можете редактировать его, чтобы изменять принятые по умолчанию параметры. Все параметры, которые вы зададите в safe_mysqld, будут переданы непосредственно демону MySQL.

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

/usr/local/mysql/bin/safe_mysqld &

Причина, по которой вы должны запускать safe_mysqld в фоновом режиме (и по которой нужно запускать именно safe_mysqld, а не mysqld) в том, что сценарий safe_mysqld запускает mysqld, а затем непрерывно проверяет, выполняется ли mysqld. Если выполнение MySQL неожиданно прекратится, safe_mysqld ее перезапустит.

Теперь, когда вы знаете, как запускать MySQL, нужно сделать так, чтобы MySQL стартовала и заканчивала работу вместе с компьютером, на котором она выполняется. Под Windows NT, конечно, достаточно установить MySQL как службу. Под Windows 9x нужно поместить ярлык сценария запуска MySQL в папку Startup. Под Unix, как и почти всегда бывает в этом мире, заставить MySQL стартовать и завершаться вместе с системой несколько сложнее. Unix-системы обычно ищут сценарии начального запуска где-то в каталоге /etc - в /etc/rc.d или /etc/ init.d. Вы должны узнать у системного администратора или в документации, куда именно нужно помещать сценарии запуска/завершения. Дистрибутив MySQL содержит в каталоге support_j'lies файл mysql.ser-ver. Этот сценарий и будет служить сценарием запуска/завершения.

Администрирование базы данных

Теперь, когда у вас есть свежеустановленная и запущенная MySQL, нужно первым делом поменять пароль суперпользователя сервера, выполнив из каталога, в который установлена MySQL, команду:

./bin/mysqladmin -u root password 'mynewpasswd'

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

Утилита mysqladmin

Главным инструментом администрирования баз данных в MySQL служит утилита mysqladmin. С ее помощью вы можете создавать, уничтожать и контролировать свой сервер и поддерживаемые им базы данных.

Создание баз данных

Ваш сервер бесполезен, пока нет баз данных, которые он обслуживает. С помощью mysqladmin можно создавать новые базы данных:

mysqladmin -p create DATABASENAME

Параметр указывает, что вы хотите, чтобы было выдано приглашение для ввода пароля суперпользователя, который вы задали раньше. Если вы введете правильный пароль, то mysqladmin создаст новую пустую базу данных с именем, которое вы указали. Поскольку в MySQL база данных - это каталог с группой файлов, команда mysqladmin create создает новый каталог, который будет содержать файлы базы данных. Например, если вы создали базу данных с именем «mydata», в каталоге data, содержащемся в директории, в которую установлена MySQL, будет создан каталог mydata.

Поскольку базы данных и таблицы MySQL хранятся как файлы файловой системы, вы столкнетесь с неприятными различиями -в поведении реализаций для Unix и Win32. Именно, все файловые системы для Win32 нечувствительны к регистру, в то время как файловые системы Unix различают регистр. В результате имена баз данных и таблиц различаются по регистру в Unix и не различаются в Win32.

Удаление базы данных

В процессе разработки приложения вам, вероятно, потребуется создать несколько баз данных для поддержки процесса разработки. Например, обычной практикой в разработке приложений баз данных является создание отдельных баз данных для разработки, тестирования и работы. По завершении разработки следует избавиться от этих промежуточных баз данных. Утилита mysqladmin позволяет удалить базу данных с помощью параметра «drop»:

mysqladmin -p drop DATABASENAME

Как и в команде mysqladmin create, DATABASENAME является именем базы данных, которую нужно уничтожить. MySQL не позволит вам случайно удалить базу данных. После ввода этой команды она предупредит вас, что удаление базы данных потенциально очень опасно и попросит вас подтвердить свое намерение. После удаления базы данных вы можете убедиться в том, что в каталоге data больше нет каталога, служившего ранее этой базой данных.

Переименование и копирование баз данных

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

Состояние сервера

В утилите mysqladmin очень много команд, позволяющих контролировать состояние сервера MySQL. Ввод команды mysqladmin status обеспечивает выдачу состояния сервера в одной строке, которая выглядит следующим образом:

Uptime: 395 Threads: 1 Questions: 14 Slow queries: 0

Opens: 10 Flush tables: 1 Open tables: 6

Выводимые величины означают следующее: Uptime

Число секунд, в течение которых сервер запущен и работает. Threads

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

Questions

Число запросов, переданных базе данных с момента запуска.

Slow queries

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

Opens

Число таблиц, открытых с момента запуска сервера.

Flush tables

Количество команд flush, refresh и reload.

Open tables

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

Если компиляция MySQL производилась с параметром --with-debug , то mysqladmin status показывает также текущую и максимальную величину используемой памяти.

Если вас интересует более общая статическая информация, то выполните команду mysqladmin version. Она выдает на экран следующее:

bin/mysqladmin Ver 7.8 Distrib 3.22.17, for sun-solaris2.6 on spare TCX Datakonsult AB, by Monty

Server version 3.22.17

Protocol version 10

Connection Localhost via Unix socket

Unix socket /tmp/;ny3ql. sock

Uptime: 23 mm 58 sec

Threads: 1 Questions: 15 Slow queries: 0 Opens: 10 Flush tables: 1 Open tables: 6

Последняя строка совпадает, конечно, с теми данными, которые показывает mysqladmin status. Остальные данные совершенно другие.

Server version

Версия запущенного сервера MySQL. Protocol version

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

Connection

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

Unix socket

Имя файла сокета, который вы используете для обмена данными с сервером. Если вы связываетесь с MySQL через TCP/IP, вместо этого пункта будет указан пункт TCP port с номером порта MySQL.

Uptime

Суммарное время работы сервера.

Две другие команды, mysqladmin variables и mysqladmin extended-status, предлагают дополнительную информацию.

Поскольку MySQL многопоточна, отследить активность процесса с помощью команды Unix ps не просто. Несмотря на то что выполняется несколько потоков, в списке процессов будет указан только один процесс. MySQL позволяет справиться с этим с помощью команды mysqladmin processlist, которая перечисляет все активные потоки в виде чудесно представленной таблички:

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

Id

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

User

Пользователь, подключенный к серверу через этот поток.

Host

Имя узла, с которого подключился пользователь.

db

База данных, к которой подключен пользователь.

Command

Тип команды, выполняемой потоком. Команда может иметь один из следующих типов:

Sleep

Поток ждет ввода пользователя. Большинство процессов должно находиться в этом состоянии.

Quit

Поток в процессе завершения.

Init DB

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

Query

Поток выполняет реальный запрос. Хотя наибольшая часть взаимодействия с базой данных происходит в виде запросов, эти команды производятся очень быстро и редко появляются в выдаче.

Field list

Поток создает список полей в таблице.

Create DB

Поток создает новую базу данных.

Drop DB

Поток удаляет базу данных.

Reload

Поток перезагружает таблицы доступа MySQL. После перезагрузки все новые потоки будут использовать обновленные таблицы доступа.

Shutdown

Поток находится в процессе завершения всех других потоков и закрытия сервера.

Statistics

Поток генерирует статистику.

Processes

Этот поток анализирует другие потоки. С этим значением будет показан поток, выполняющий данную команду.

Connect

Поток в процессе установления входящего соединения с клиентом.

Kill

Этот поток завершает другой поток.

Refresh

Поток очищает все буферы и сбрасывает журнальные файлы.

Файлы журналов MySQL дают еще один способ получения полезной информации для администрирования сервера. MySQL создает главный журнал, если mysqld запускается с параметром --log. Этот журнал ведется в файле /usr/local/var/HOSTNAME.log, где HOSTNAME - имя машины, на которой запущен MySQL. В этот журнал записываются подключения к серверу и команды, посылаемые ему клиентами.

Задав параметр -debug для mysqld (или safe_mysqld), вы заставите MySQL посылать в журнал дополнительную информацию. Пакет отладки, используемый MySQL, имеет десятки параметров, большинство из которых вы никогда не будете использовать. Наиболее часто используется установка -d:t:o,FILENAME, где FILENAME - имя журнала отладки, который вы хотите использовать. При задании этого параметра в журнал будут заноситься практически все действия сервера, шаг за шагом.

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

Завершение работы сервера

Следующая команда производит корректное завершение работы сервера MySQL:

mysqladmin -p shutdown

Эта команда - самый правильный способ завершения работы сервера. Если вы запустили MySQL с помощью safe_mysqld и пытаетесь закрыть сервер каким-либо другим способом, safe_mysqld просто запустит еще один экземпляр сервера. Можно также безопасно закрыть сервер традиционной Unix-командой kill, но никогда не пользуйтесь kill-9.

Параметры командной строки для mysqladmin

Утилита mysqladmin - очень богатый инструмент со множеством параметров командной строки. Общий ее формат

mysqladmin OPTIONS COMMAND1 COMMAND2 . . . COMMANDn

Иными словами, можно одновременно задавать несколько команд. Будет выполнена даже такая последовательность команд как, скажем,

mysqladmin -p create silly drop silly

Эта команда одним махом создаст и уничтожит базу данных «silly». Вот перечень команд, которые можно передать mysqladmin:

create DATABASENAME

Создает новую базу данных с указанным именем.

drop DATABASENAME

Удаляет базу данных с указанным именем.

extended-status

Выдает расширенное сообщение о статусе сервера.

flush-hosts

Немедленно записывает все буферизованные изменения на удаленных компьютерах.

flush-logs

Немедленно записывает все буферизованные изменения в журналы.

flush-tables

Немедленно записывает все буферизованные изменения в таблицы.

flush-privileges

То же, что reload.

killID1,ID2.....IDn

Завершает потоки с заданными IDs.

password NEWPASSWORD

Заменяет пароль на новое значение.

ping

Проверяет, работает ли еще mysqld.

processlist

Выдает список активных потоков.

reload

Заново загружает все таблицы доступа.

refresh

Записывает буферизованные изменения во все таблицы и закрывает и открывает все журналы.

shutdown

Завершает работу сервера.

status

Выдает краткое сообщение о состоянии сервера.

variables

Выдает значения имеющихся переменных.

version

Выдает данные о версии сервера.

Кроме команд поддерживаются также следующие параметры:

-# LOG

Выдача отладочной информации в журнал. Часто это 'd:t:o,FILENAME'.

-f

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

-? или --help

Выдача подсказки по использованию утилиты msqladmin.


Использовать сжатие в протоколе клиент/сервер.

-Н HOST

Подключиться к указанному компьютеру.

-р [PASSWORD]

Использовать указанный пароль для проверки прав пользователя.

Если пароль не указан, пользователю будет выдано приглашение для ввода пароля.

-Р PORT

Использовать для подключения указанный порт.

-i SECONDS

Повторно выполнять команды через заданный промежуток времени.

-s

Выйти без сообщений, если соединение с сервером невозможно установить.

-S SOCKET

Файл для использования в качестве сокета Unix.

-t TIMEOUT

Тайм-аут для соединения.

-u USER

Имя для регистрации пользователя, если оно отлично от текущего.

-V

Выдать информацию о версии и завершить работу.

-w COUNT

Ждать и повторить попытку заданное число раз, если сервер в данный момент не готов.

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

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

В главе 5 «mSQL» приводится подробное изложение роли команды msqldump при резервировании данных mSQL. MySQL поддерживает почти идентичную функциональность в виде команды mysqldump. Мы рекомендуем изучить этот пункт, чтобы понять роль mysqldump при полном резервировании баз данных. В данном параграфе мы остановимся на следующем наиболее важном виде резервирования - инкре-ментном резервировании.

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

При использовании MySQL инкрементное резервирование можно производить, используя такую возможность сервера баз данных, как «update log» - журнал изменений MySQL. Если сервер баз данных mysqld запущен с параметром --log-update, то все изменения в базе данных будут сохраняться в файле в виде команд SQL. Изменения будут сохраняться в порядке их производства. В результате получается файл, который, будучи обработан монитором mysql, воспроизведет все действия, произведенные над базой данных. Если журнал хранится с самого образования базы данных, то будет восстановлен весь жизненный цикл базы данных, который приведет ее в текущее состояние.

С большей пользой журнал, ведущийся с некоторого определенного момента, например, от последнего резервирования базы данных, можно использовать для приведения резервной копии в текущее состояние. Такова технология инкрементного резервирования. Производите регулярное (скажем, раз в неделю) полное резервирование базы данных. Затем каждый день копируйте журнал изменений на магнитную ленту или выделенную область жесткого диска. Сохраняйте копии всех ежедневных журналов изменений, начиная с даты последнего полного резервирования. Это позволяет восстановить базу данных в случае аварии, а также все данные, утраченные с момента последнего резервирования. Поскольку журнал изменений является текстовым файлом, можно просмотреть команды SQL для поиска конкретных данных.

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

Система безопасности

Вам не только нужно иметь надежный доступ к своим данным, но и быть уверенным, что у других нет никакого доступа к ним. MySQL использует собственный сервер баз данных для обеспечения безопасности. При первоначальной установке MySQL создается база данных под названием «mysql». В этой базе есть пять таблиц: db, host, user, tab-les_priv, и columns_priv . Более новые версии MySQL создают также базу данных с названием func, но она не имеет отношения к безопасности. MySQL использует эти таблицы для определения того, кому что позволено делать. Таблица user содержит данные по безопасности, относящиеся к серверу в целом. Таблица host содержит права доступа к серверу для удаленных компьютеров. И наконец, db, tables_priv и со-lumns_priv управляют доступом к отдельным базам данных, таблицам и колонкам.

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

Таблица user

Таблица user имеет вид, показанный в Таблице 4-1:

Таблица 4-1. Таблица user

        
 

Поле

Тип

Null

Ключ

Значение

Примеч.

 
 





по умолчанию


 
 

Host

char(60)


PRI



 
 

User

char(16)


PRI



 
        
        
 

Поле

Тип

Null

Ключ

Значение

Примеч.

 
 





по умолчанию


 
 

Password

char(16)





 
 

Select_priv

enum('N','Y')



N


 
 Insert priv enum('N','Y')   N  
 

Update_priv

enum('N','Y')



N


 
 

Delete_priv

enum('N','Y')



N


 
 

Create_priv

enum('N','Y')



N


 
 

Drop priv

enum('N','Y')



N


 
 

Reload priv

enum('N','Y')



N


 
 

Shutdown_priv

enum('N','Y')



N


 
 

Process_priv

enum('N','Y')



N


 
 

File_priv

enum('N','Y')



N


 
 

Grant_priv

enum('N','Y')



N


 
 

References_priv

enum('N','Y')



N


 
 
MKPortal©2003-2008 mkportal.it
MultiBoard ©2007-2009 RusMKPortal