67410

Администрирование MySQL

Конспект

Информатика, кибернетика и программирование

Этот курс посвящен рассмотрению различных аспектов администрирования MySQL. В нём представлена исчерпывающая информация обо всех основных вопросах которыми необходимо владеть для успешного выполнения задач по администрированию MySQL.

Русский

2014-09-10

805.5 KB

13 чел.

Администирование MySQL 62

Администрирование MySQL

Информация о курсе

Курс рассматривает вопросы администрирования MySql: от основных обязанностей администратора до аспектов резервирования и восстановления базы данных.

Этот курс посвящен рассмотрению различных аспектов администрирования MySQL. В нём представлена исчерпывающая информация обо всех основных вопросах, которыми необходимо владеть для успешного выполнения задач по администрированию MySQL. Рассмотрены все основные обязанности администратора и даны инструкции по их выполнению.

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

Предварительные знания

Для изучения курса необходимо иметь базовые навыки по работе с MySql на уровне пользователя.

Предварительные курсы

Основы SQL

Введение в СУБД MySQL

SQL и процедурно-ориентированные языки

Введение в модель данных SQL

Дополнительные курсы

SQL Server 2000

Основы СУБД Oracle, SQL и PL/SQL

Программирование в Microsoft SQL Server 2000

Описание лекций

1. Введение в администрирование MySQL  

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

2. Общее администрирование MySQL  

В лекции рассмотрены общие задачи администратора, инсталляция MySql, методы и опции запуска и завершения работы сервера MySql.

3. Управление пользовательскими учетными записями  

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

4. Проверка и восстановление таблиц  

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

5. Сценарии и утилиты MySQL  

Лекция рассматривает вспомогательные средства по работе с базами данных.

6. Виды таблиц и способ их хранения  

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

7. Каталог данных MySQL  

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

8. Файлы журналов MySQL 

В этой лекции рассматриваются вопросы аудита работы системы MySql.

9. Резервирование и копирование баз данных 

В этой лекции рассматриваются вопросы создания резервных копий баз данных и аспекты восстановления баз из резервных копий.

10. Оптимизация  

В лекции объясняется, когда и как нужно оптимизировать базы данных. Кроме того, затрагиваются проблемы перекомпиляции ядра MySql.

11. Безопасность 

В этой лекции основное внимание уделяется действиям администратора, направленным на поддержание безопасности и целостности инсталляции MySQL

12. Концепции распределенных баз данных 

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

13. Расширение возможностей 

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

Учебники к курсу

Полякова Л.Н. Основы SQL БИНОМ. Лаборатория знаний, Интернет-университет информационных технологий - intuit.ru, 2007


Лекция 1: Введение в администрирование MySQL


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

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

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

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

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

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

Обзор задач администрирования

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

  •  Сервер MySQL. Сервер mysqld выполняет все операции с базами данных и таблицами. Для запуска сервера, мониторинга его работы и перезапуска в случае сбоя применяется программа safe_mysqld (демон)
  •  Клиентские программы и утилиты MySQL. Для взаимодействия с сервером и выполнения ряда административных задач используются различные программы MySQL, наиболее важными среди которых являются следующие:
    •  mysql. Интерактивная программа, позволяющая отправлять SQL-запросы на сервер и просматривать результаты их выполнения.
      •  mysqladmin. Административная программа, позволяющая выполнять такие операции, как завершение работы сервера, создание и удаление баз данных. Эту же программу можно применять для проверки состояния сервера, если что-то в его работе не так.
      •  isamchk или myisamchk. Утилиты, предназначенные для анализа и оптимизации таблиц, а также восстановления их в случае повреждения.
      •  mysqldump. Средство резервирования баз данных или их копирования на другой сервер.
  •  SQL - язык сервера. Некоторые задачи администрирования можно выполнить только с помощью утилиты командной строки mysqladmin. Иногда гораздо эффективней справиться с задачей может администратор, который может "общаться" с сервером на его языке. Предположим, что необходимо проверить, почему привилегии пользователя работают вовсе не так, как ожидается. Напрямую "поговорить" с сервером на человеческом языке, к сожалению, нельзя. Зато можно воспользоваться программой-клиентом mysql и послать SQL-запрос для анализа таблиц разрешений. Если же используемая версия MySQL появилась раньше введения оператора grant, необходимо сначала применить команду mysql для настройки привилегий каждого пользователя.

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

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

Общее администрирование

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

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

Поддержка учетных записей пользователей. Администратор должен отчетливо понимать разницу между пользователями MySQL и пользователями UNIX и Windows, а также уметь настраивать учетные записи пользователей MySQL. Такая настройка зачастую заключается в определении, какие пользователи и с какого компьютера смогут подключаться к серверу. Новым пользователям необходимо, кроме всего прочего, сообщить свои параметры подключения, чтобы успешно настроить обмен данными с сервером. Настройка учетных записей — это задача администратора, а не пользователей!

  •  Поддержка регистрационных файлов. Администратор должен знать, регистрационные файлы каких типов необходимо проверять и поддерживать, а также каким образом и когда выполнять эти операции. Последовательный просмотр и замена регистрационных журналов позволит избежать переполнения ими используемых дисков.
  •  Резервирование и копирование баз данных. Резервирование баз данных — исключительно важная операция, позволяющая в случае необходимости восстановить работу системы после сбоя. Желательно, конечно, чтобы имелась возможность восстановить базы данных до того состояния, в котором они находились перед сбоем. В этом случае потеря данных будет минимальной. Заметьте, однако, что резервирование баз данных отличается от резервирования информации всей системы, выполняемой, например, с помощью UNIX-программы dump. В процессе активной работы сервера файлы таблиц базы данных, как правило, подвергаются изменениям. Восстановление файлов, зарезервированных в какой-то определенный момент времени, не позволит в полной мере восстановить базу данных, т.е. потеря определенной части данных неизбежна. Более полезными для восстановления базы данных являются файлы, сгенерированные программой mysqldump. С ее помощью можно выполнять резервирование без предварительного завершения работы сервера.

Иногда возникает необходимость в запуске СУБД на более быстродействующем главном компьютере или в создании копии базы данных. В этом случае все содержимое ее каталога можно скопировать на другой компьютер. Администратор должен уметь выполнять и эту процедуру. Файлы баз данных могут зависеть от конфигурации конкретной системы, поэтому простое копирование файлов не сможет дать достаточно удовлетворительных результатов.

  •  Настройка сервера. Пользователи всегда хотят, чтобы сервер работал максимально быстро. Самый быстрый и неэкономный метод повышения производительности сервера — приобретение и установка большего объема памяти и более быстрых дисков. Разбираться в работе сервера при применении подобных методов вовсе необязательно. Администратору необходимо знать, какие параметры применяются для настройки работы сервера и как их значения повлияют на ситуацию. Одни клиенты пользуются запросами в основном для выборки необходимой информации из баз данных, другие — в основном для добавления и обновления информации. Поэтому решение об изменении значений тех или иных параметров зачастую определяется типом запросов, отправляемых к узлу.
  •  Установка нескольких серверов. В некоторых случаях возникает необходимость в запуске сразу нескольких серверов. Она может определяться желанием протестировать работу новой версии MySQL перед полным переходом на нее или обеспечить более высокий уровень защиты для отдельных групп и пользователей. (Последний вариант, в частности, относится к провайдерам услуг Internet.) В таких ситуациях администратор должен знать, как установить и настроить несколько одновременно работающих инсталляций.
  •  Обновление MySQL. Новые версии MySQL появляются достаточно часто. Администратор должен знать, как обновить старую версию для устранения найденных ошибок программного кода и добавления новых возможностей. Следует также понимать, что иногда имеет смысл воздержаться от обновления, а также уметь выбирать между официальными и тестовыми версиями этого программного продукта.

Безопасность

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

  •  Защита файловой системы. В ОС UNIX могут поддерживаться несколько учетных записей пользователей, не обладающих полномочиями администратора MySQL. Необходимо проверить, чтобы эти пользователи не имели доступа к каталогам данных. Это позволит защититься от несанкционированного копирования или удаления таблиц баз данных либо чтения регистрационных файлов с критически важной информацией. Администратор должен знать, как настроить учетные записи пользователей UNIX для сервера MySQL, как сделать каталог данных доступным только для владельца и как определить привилегии пользователей сервера.
  •  Защита сервера. Знание принципов работы системы безопасности MySQL поможет правильно настроить пользовательские учетные записи и полномочия доступа. Подключающиеся через сеть к серверу пользователи должны обладать правами доступа только к самой необходимой информации. Полное игнорирование системы безопасности может обернуться предоставлением прав администратора анонимному пользователю.

Отладка и поддержка баз данных

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

  •  Восстановление после сбоя. Сбой может разрушить результаты даже самых успешных действий, поэтому администратор должен обязательно знать, как отладить и восстановить таблицы баз данных. Восстановление после сбоя — это процесс, выполняемый весьма редко. Однако он очень неприятен и, как правило, сопровождается пребыванием администратора в стрессовом состоянии (которое может еще более усугубляться постоянными звонками и стуком в дверь.) Для восстановления применяются средства проверки и восстановления - isamchk и myisamchk. Необходимо обязательно научиться восстанавливать информацию из резервных файлов и использовать записи регистрационных журналов для восстановления всех изменений, внесенных с момента последнего резервирования.
  •  Превентивная поддержка. Для снижения вероятности повреждения или разрушения баз данных следует постоянно применять программы превентивной поддержки. Необходимо также выполнять и резервирование, хотя, конечно, выполнение мер превентивной поддержки снижает возможность их использования.

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

Лекция 2: Общее администрирование MySQL


В лекции рассмотрены общие задачи администратора, инсталляция MySql, методы и опции запуска и завершения работы сервера MySql.

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

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

  •  Утилита mysqladmin позволяет выполнять всевозможные административные функции.
  •  Сценарии safemysqld и mysqlserver применяются для запуска MySQL-сервера mysqld.
  •  Программа mysqldump используется для резервирования и копирования баз данных.
  •  Утилиты myisamchk и isamchk применяются для проверки целостности данных таблиц и операций отладки.

Защита новой инсталляции MySQL

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

Сразу после первой инсталляции MySQL на компьютере привилегии в таблице разрешений базы данных mysql устанавливаются следующим образом.

  •  Зарегистрироваться в качестве основного пользователя root с локального компьютера можно без пароля. Пользователь root обладает всеми возможными правами (включая административные), может выполнять любые операции. (Кстати, совпадение имен суперпользователей MySQL и UNIX не является закономерностью Они никак друг на друга не влияют.)
  •  Права анонимного доступа предоставляются всем пользователям, подключающимся с локального компьютера к базе данных test или любой другой базе данных, имя которой начинается со слова test. Анонимные пользователи могут выполнять любые операции с такими таблицами, но не обладают привилегиями администратора. Для подключения к серверу с локального компьютера можно определить как имя главного компьютера localhost, так и его реальное имя. Например, если сервер размещается на компьютере pitviper.snake.net, клиент этого компьютера может подключиться без пароля к серверу для работы с базой данных test с помощью одной из двух следующих команд:

% mysql -h localhost test

 % mysql -h pit-viper.snake.net test

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

В версиях MySQL 3.22 и выше установить пароль можно с помощью команды mysqladmin. Для этого достаточно ввести следующую команду, заменив ее часть my password реальным паролем:

mysqladmin -u root password "my password"

Во всех остальных версиях MySQL для этих целей можно воспользоваться программой mysql и непосредственно обновить таблицу разрешений grant в базе данных mysql:

mysql> -u root mysql

mysql> UPDATE user SET Password=PASSWORD("my password") WHERE User="root";

  

Команда mysql и оператор update применяется в старых версиях MySQL, а также во всех бесплатно распространяемых версиях под Windows.

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

% mysqladmin -u root status

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

% mysqladmin -u root reload

После определения пароля пользователя root (и перезагрузки таблиц разрешений) самое время приступать к определению нового пароля для администратора.

Настройка процедур запуска и завершения работы сервера MySQL

Одна из основных обязанностей администратора MySQL — обеспечить согласованную и длительную работу сервера, что даст возможность пользователям получать доступ к нему в любое удобное время. Иногда, однако, возникает необходимость и во временной приостановке работы сервера. (Так, например, при перемещении базы данных необходимо убедиться, что сервер в это же время не обновляет ее таблицы.) Эти лекции не помогут разрешить компромисс между необходимостью постоянной работы сервера и желанием иногда временно приостановить его работу, поскольку зачастую решение этого компромисса зависит от конкретной ситуации. По крайней мере, читатели смогут узнать, как запустить и завершить работу сервера.

Все приведенные в этой лекции инструкции применимы только к операционным системам UNIX.

Запуск представленных команд

В большинстве примеров этой лекции такие программы, как mysqladmin, mysqldump и им подобные, для краткости представлены без опций -h, -u и . Предполагается, что пользователи будут правильно вызывать эти программы, используя в случае необходимости подключения к серверу соответствующие опции.

В версии MySQL 3.22.11 и выше перезагрузить таблицы можно с помощью команды mysqladmin flush-privileges и SQL-оператора FLUSH PRIVILEGES.

Запуск сервера MySQL непривилегированным пользователем

Прежде чем приступить к рассмотрению процедуры запуска сервера, давайте обсудим, какие пользователи могут выполнить подобный запуск. Сервер может запускаться вручную или автоматически. В первом случае сервер запускается в качестве пользователя, под именем которого зарегистрирован администратор, запускающий сервер (или другой сотрудник). Другими словами, если администратор зарегистрирован под именем пользователя paul и запускает сервер, то сервер будет работать с правами пользователя paul. Если затем администратор с помощью команды su зарегистрируется в качестве пользователя root и запустит сервер, сервер будет работать с правами пользователя root.

Однако всегда запускать сервер вручную весьма неудобно. Лучше всего настроить его на автоматический запуск во время загрузки системы. На UNIX-компьютерах процедура запуска выполняется системой с помощью UNIX-пользователя root, соответственно, все процессы, запускаемые во время этой процедуры, работают впоследствии с привилегиями пользователя root.

Администратору следует помнить о двух целях, преследуемых при настройке процедуры запуска сервера MySQL.

  •  Желательно, чтобы сервер не обладал привилегиями пользователя root. В большинстве случаев рекомендуется ограничивать возможности всех процессов, не требующих прав доступа root. He нужны эти права и демону mysqld.
  •  Желательно, чтобы сервер все время работал под именем одного и того же пользователя. Весьма нелогично запускать сервер в разное время под именами разных пользователей, поскольку в этом случае файлы и каталоги с данными будут создаваться разными владельцами. В определенный момент времени это может привести к невозможности получения доступа к базам данных или таблицам. Постоянный запуск сервера от имени одного пользователя позволит избежать этой проблемы.

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

  •  Выберите учетную запись, предназначенную для запуска сервера. Демон mysqld может работать от имени любого пользователя, однако лучше создать для него отдельную учетную запись. Можно также создать специальную группу для работы с MySQL. Предположим, что такой пользователь и группа уже созданы и имеют имена mysqladm и mysqlgrp. Пользователям, определившим другие имена для этих целей, придется подставлять их далее в примерах кода вместо mysqladm и mysqlgrp.

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

Если же система MySQL инсталлировалась под управлением ОС Linux Red Hat с помощью RPM-файла, в процессе установки автоматически создается учетная запись с именем mysql. Ее в последующих примерах этой лекции нужно применять вместо mysqladm.

  •  Создайте, если необходимо, учетную запись сервера, используя обычную процедуру создания учетных записей системы. Для этого необходимо сначала зарегистрироваться в качестве пользователя root.
  •  Завершите работу сервера, если он работает.
  •  Измените права владения каталогом данных, а также всех его подкаталогов и файлов, чтобы новым владельцем этих элементов оказался пользователь mysqladm. Например, чтобы сделать пользователя mysqladm владельцем каталога данных /usr/local/var, необходимо ввести следующие команды (зарегистрировавшись в качестве пользователя root).

# cd /usr/local/var. Переход в каталог данных.

# chown -R mysqladm.mysqlgrp.  

              Установка прав владения для всех каталогов и файлов.

  

  •  Измените полномочия доступа к каталогу данных и всем его подкаталогам и файлам, чтобы работать с ними мог только пользователь mysqladm. Запретите доступ к данным всем остальным пользователям — это самая эффективная мера предосторожности. Если каталог данных размешается в директории /usr/local/var, определить права владения на него для пользователя mysqladm можно с помощью следующих команд (зарегистрировавшись в качестве пользователя root).

# cd /usr/local/var. Переход в каталог данных.

# chmod -R go-rwx.

  Все элементы будут доступны только для пользователя mysqladm.

  

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

По завершении вышеприведенной процедуры следует убедиться в нормальном запуске сервера, предварительно зарегистрировавшись в качестве пользователя mysqladm или root. В последнем случае обязательно нужно определить опцию —user=mysqladm, чтобы пользователь мог переключить ID-номер своего компьютера на mysqladm (что также реализуется в процессе запуска системы).

Опция -user была добавлена в mysql в версии MySQL 3.22. В более ранних версиях для этих же целей можно использовать команду su, которая указывает системе, работающей под управлением пользователя root, что запускать сервер необходимо с учетной записью другого пользователя. Настоятельно рекомендуется заглянуть в справочное руководство и ознакомиться с синтаксисом команды su, поскольку он может видоизменяться для отдельных пользователей.

Методы запуска сервера

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

  •  Непосредственный вызов mysqld. Это, возможно, самый распространенный метод. Поэтому подробно рассматриваться далее он не будет. Достаточно лишь заметить, что в случае необходимости можно использовать команду mysqld —help для определения опций с другими методами запуска.
  •  Вызов сценария safemysqld. Этот сценарий в процессе своей работы пытается определить местоположение программы сервера и каталога данных, а затем запускает сервер с соответствующими опциями. Сценарий safemysqld записывает все сообщения об ошибках сервера в специальный файл ошибок, расположенный в каталоге данных. Кроме того, safemysqld следит за нормальной работой сервера и в случае сбоя перезагружает его. Этот сценарий наиболее часто применяется в BSD-версиях UNIX.
  •  Вызов сценария mysql.server. Этот сценарий запускает сервер посредством запуска сценария safemysqld. Сценарий mysql.server предназначен для использования на компьютерах с системой запуска/завершения работы System V. Данная система включает несколько каталогов со сценариями, вызываемыми при входе или выходе с определенного уровня работы. С помощью соответствующих аргументов start и stop можно определить, что делать дальше: запустить сервер или остановить его работу.

Сценарий safemysqld располагается в подкаталоге bin каталога инсталляции MySQL. Его же можно найти в каталоге scripts исходной дистрибуции MySQL. Сценарий mysql.server можно отыскать в подкаталоге share/mysql каталога инсталляции или каталоге support-files исходной дистрибуции MySQL. Для использования эти сценарии необходимо скопировать в соответствующие каталоги запуска.

В ОС BSD-UNIX довольно часто используются несколько специальных файлов, которые располагаются в каталоге /etc и инициируют службы во время запуска. Как правило, имена таких файлов начинаются с приставки "re". Файл rc.local (или имеющий подобное название), например, предназначен специально для запуска локальных служб. Для запуска сервера в подобных системах необходимо добавить в файл rc.local следующие строки (подставив правильный путь к сценарию safe_mysqld):

if [ -x /usr/local/bin/safe_mysqld ]; then /usr/local/bin/safe_mysqld & fi

  

В системах System V для инсталляции сценария mysql.server достаточно разместить его в подкаталоге /etc каталога запуска. Это наверняка уже сделано, если Linux или MySQL инсталлировались с помощью RPM-файла. Если нет, инсталлируйте сценарий в основной каталог сценариев запуска и установите связи с ним в каталогах уровней запуска. Можно также сделать так, чтобы сценарии запускались только пользователем root.

Структура каталогов с файлами запуска может изменяться от системы к системе, поэтому рекомендуется внимательно просмотреть, как эти файлы организованы в используемом компьютере. Например, в системе LinuxPC для запуска применяются каталоги /etc/rc.d/init.d и etc/rc.d/rc3.d. Соответственно, инсталляция сценария выполняется с помощью следующих команд:

# ср mysql.server /etc/rc.d/init.d

# cd /etc/init.d

# chmod 500 mysql.server

# cd /etc/rc.d/rc3.d

# ln -s ../init.d/mysql.server S99mysql

  

В ОС Solaris основной каталог сценариев — /etc/init.d, а каталог уровня запуска — /etc/rc2.d, поэтому набор команд инсталляции выглядит следующим образом:

# ср mysql.server /etc/init.d

# cd /etc/init.d

# chmod 500 mysql.server

# cd /etc/rc2.d

# ln -s ../init.d/mysql.server S99mysql

  

Эти команды обеспечивают автоматический запуск сценария S99mysql с аргументом start в процессе загрузки системы.

Если имеется возможность использования команды chkconfig (часто применяемой под управлением Linux), ее также можно применить для инсталляции сценария mysql.server. В этом случае от ручного ввода приведенных выше команд можно отказаться.

Определение опций запуска

Существует два способа определения дополнительных опций запуска, которые применяются при загрузке сервера. Во-первых, можно изменить используемый сценарий запуска (safemysqld или mysql.server) и задать опции непосредственно в строке вызова сервера. Во-вторых, можно определить опции собственно в конфигурационном файле. Профессионалы рекомендуют по возможности использовать для этих целей глобальные конфигурационные файлы. В системах UNIX и Windows этими файлами обычно являются /etc/my.cnf.

Однако есть информация, которую невозможно задать в конфигурационных файлах. Для ее определения необходимо изменить сценарий safemysqld. Так, например, если сервер неправильно считал установки временного пояса и возвращает значения времени в формате GMT (времени по Гринвичу), можно для подсказки установить переменную среды TZ. Если сервер запускается с помощью сценария safemysqld или mysql.server, установку временного пояса можно добавить в safemysqld. Отыщите строку запуска сервера и перед ней добавьте следующие команды:

TZ=US/Central

export TZ

  

Эти команды устанавливают часовой пояс центральной части Соединенных Штатов. Пользователям же необходимо аналогичным образом задать свой часовой пояс. Подобный синтаксис для переменной TZ применяется в системе Solaris. В других системах он может быть другим, например, таким:

TZ=CST6CDT

export TZ

  

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

Проверка таблиц во время запуска

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

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

Для самостоятельного завершения работы сервера применяется команда mysqladmin:

% mysqladmin shutdown

  

Автоматическое завершение работы сервера также не требует выполнения каких-либо специальных действий. В UNIX BSD работа служб обычно завершается посредством отправки процессам сигнала TERM. Службы либо соответствующим образом на него отвечают, либо просто закрываются. Сервер mysqld, например, на получение такого сигнала реагирует закрытием. В системах System V, запуск сервера в которых производится с помощью сценария mysql.server, процедура завершения работы реализуется посредством вызова этого же сценария, но с аргументом stop. При этом, конечно же, предполагается, что сценарий mysql.server инсталлирован.

Когда нельзя подключиться к серверу

В некоторых случаях возникает необходимость в самостоятельной перезагрузке сервера из-за невозможности подключения к нему. Честно говоря, получается какой-то парадокс, поскольку обычно для завершения работы сервера необходимо сначала подключиться к нему, а затем дать указание на закрытие. Как же может возникнуть подобная ситуация? Во-первых, администратор может не знать пароля MySQL-пользователя root. Иногда это случается, когда при изменении пароля случайно введен лишний символ или пароль просто забыт.

Во-вторых, подключение к компьютеру localhost обычно осуществляется через файл доменного разъема ОС UNIX, которым, как правило, является /tmp/mysql.sock. Удаление этого файла делает невозможным подключение клиентов. Такая ситуация, в свою очередь, может возникнуть после запуска процесса сron, который удаляет временные файлы из каталога /tmp.

Если подключиться нельзя из-за отсутствия файла разъема, проблему можно легко решить посредством простой перезагрузки сервера. В процессе запуска он воссоздаст этот файл. Проблема заключается в том, что использовать этот разъем для установления соединения с сервером нельзя. Для этого необходимо установить соединение TCP/IP. Например, если сервер запускается на компьютере с адресом viper.snake.net, подключиться к нему можно с помощью следующей команды:

% mysqladmin -p -u root -h pit-viper.snake.net shutdown

  

Если файл разъема удален в результате работы задания программы стоп, проблема может возникнуть снова. Чтобы избежать этого, настройте программу стоп на использование другого файла разъема. Это можно осуществить с помощью глобального конфигурационного файла. Так, например, если /usr/local/var — каталог данных, для перемещения в него файла разъема достаточно добавить следующие строки в файл /etc/my.cnf:

[mysqld]

socket=/usr/local/var/mysql.sock

[client]

socket=/usr/local/var/mysql.sock

  

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

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

  •  Завершите работу сервера. Зарегистрировавшись как пользователь root на компьютере с сервером, администратор может завершить работу сервера с помощью команды kill. Используя команду ps, можно отыскать ID-номер процесса сервера. С этой же целью можно просмотреть PlD-файл, который обычно располагается в каталоге данных.

Лучше сначала попытаться завершить работу сервера с помощью обычной команды kill, чем сразу посылать серверу сигнал term и проверять, правильно ли он отреагировал на нее, завершив работу. В этом случае все таблицы и журналы будут обработаны и закрыты корректно. Если в работе сервера не все нормально и он не отвечает на сигнал завершения работы, можно воспользоваться командой kill -9 для принудительного закрытия. Однако к ее помощи следует прибегать в самую последнюю очередь, поскольку в этом случае существует риск оставить таблицы поврежденными.

Если работа сервера все же была завершена с помощью команды kill -9, настоятельно рекомендуется перед следующим запуском сервера проверить таблицы с помощью команд myisamchk и isamchk.

  •  Перезапустите сервер с помощью опции —skip-grant-tables. Это укажет серверу не использовать таблицы разрешений для проверки соединений и позволит подключиться с полномочиями пользователя root без пароля. После удачного подключения измените пароль пользователя root без пароля.
  •  Используя команду mysqladmin flush-privileges, укажите серверу снова перезагрузиться, но с применением таблиц разрешений. Если используемая версия mysqladmin не поддерживает опцию flush-privileges, попробуйте воспользоваться командой reload.

Лекция 3: Управление пользовательскими учетными записями


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

В обязанности администратора MySQL входит также создание и настройка учетных записей пользователей MySQL. В процессе этой настройки необходимо определить, какие пользователи будут иметь возможность подключения к серверу, откуда они смогут подключиться и что смогут делать после подключения. Два появившихся в MySQL 3.22.11 оператора упрощают эту задачу. Оператор GRANT создает пользователей MySQL и позволяет настроить их привилегии. Оператор REVOKE удаляет привилегии. Эти два оператора являются своего рода внешним интерфейсом для таблиц разрешений базы данных mysql и обеспечивают альтернативу непосредственному редактированию содержимого этих таблиц. Операторы grant и revoke работают с четырьмя следующими таблицами.

Таблица разрешений

Содержимое

user 

Подключающиеся к серверу пользователи и все их глобальные привилегии

db 

Привилегии уровня базы данных

tables_priv

Привилегии уровня таблицы

columns_priv 

Привилегии уровня столбца

Существует еще одна, пятая таблица разрешений (host), однако операторы grant и revoke не в состоянии ее обрабатывать.

Если оператор GRANT запускается для определенного пользователя, в таблице user для него создается новая запись. Если оператор определяет для пользователя какие-либо глобальные привилегии (привилегии администратора или привилегии, применяемые сразу ко всем базам данных), они также записываются в таблицу user. Права обработки базы данных, таблицы или столбца записываются соответственно в таблицы db, tables_priv и column_priv.

Применять операторы grant и revoke проще, чем непосредственно редактировать таблицы разрешений. Однако для более полного понимания представленного здесь материала рекомендуется также прочитать лекцию 11, "Безопасность", в которой детально описываются таблицы разрешений.

Роль этих таблиц действительно велика, и администратор должен понимать, каким образом их обрабатывают операторы GRANT и REVOKE.

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

Некоторые пользователи захотят также познакомиться со сценариями mysqlaccess и mysql_setpermission, которые являются частью дистрибуции MySQL. Они представляют собой Perl-сценарии и обеспечивают альтернативу оператору grant, поскольку применяются для установки пользовательских учетных записей. Для использования сценария mysql_setpermission требуется инсталляция поддержки DBI.

Создание новых пользователей и предоставление привилегий

Оператор GRANT имеет следующий синтаксис:

GRANT privileges (columns)

ON what

TO user IDENTIFIED BY "password"

WITH GRANT OPTION

Для успешного его выполнения обязательно нужно правильно определить следующую информацию:

  •  privileges (привилегии). Привилегии присваиваются определенному пользователю.

Используемые в операторе grant спецификаторы привилегий описаны в представленной ниже таблице.

Спецификатор привилегий

Разрешенная операция

user 

Подключающиеся к серверу пользователи и все их глобальные привилегии

alter 

Изменение таблиц и индексов

create 

Создание баз данных и таблиц

delete

Удаление существующих записей из таблиц

drop 

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

index

Создание и удаление индексов

insert

Вставка новых записей в таблицы

references

He используется

select

Извлечение существующих записей из таблиц

update

Изменение существующих записей таблиц

file

Чтение и запись файлов сервера

process

Просмотр информации о внутренних потоках сервера и их удаление

reload

Перезагрузка таблиц разрешений или обновление журналов, кэша компьютера или кэша таблицы

shutdown

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

all

Все операции. Аналог — all privileges 

usage

Полное отсутствие привилегий

Спецификаторы привилегий, входящие в первую группу этой таблицы, применяются к базам данных, таблицам и столбцам. Спецификаторы второй группы определяют административные привилегии. Как правило, они применяются довольно редко, поскольку позволяют пользователю влиять на работу сервера. (Не каждому пользователю, например, необходима привилегия shutdown.) В третью группу входят два отдельных спецификатора: спецификатор ALL предоставляет "все привилегии", a USAGE означает "полное отсутствие привилегий". В последнем случае создается новый пользователь, не обладающий никакими правами;

  •  columns (столбцы). Столбцы, к которым применяются определенные привилегии. Этот параметр необязателен и используется только при установке привилегий для столбцов. Имена нескольких столбцов отделяются друг от друга запятыми;
  •  what (что). Уровень применения привилегий. Привилегии могут быть глобальными (применяемыми ко всем базам данных и их таблицам), уровня баз данных (применяемыми ко всем таблицам определенной базы данных) или уровня таблицы. Используя оператор columns, можно определить также привилегии уровня столбца;
  •  user (пользователь). Пользователь, которому присваиваются привилегии. В некоторых версиях MySQL необходимо указывать как имя пользователя, так и компьютер, с которого он сможет подключаться. Такой способ задания легко позволяет определить двух пользователей с одинаковым именем, но подключающихся с разных компьютеров. Возможности MySQL позволяют их различать и наделять различными правами.

Имя пользователя в некоторых версиях MySQL представляет собой имя, используемое при подключении к серверу. Оно вовсе не обязательно должно быть связано с именем регистрации в ОС UNIX или Windows. Если имя пользователя MySQL не определено явным образом, клиентские программы по умолчанию применяют его в качестве регистрационного имени, однако это необязательно. Не существует также каких-либо особых требований, чтобы суперпользователь MySQL, обладающий максимальными правами, имел имя root. По желанию его можно изменить в таблицах разрешений на nobody, если для выполнения определенных операций требуются его полномочия;

  •  password (пароль). Присвоенный пользователю пароль, который не является обязательным. Если для нового пользователя опустить выражение IDENTIFIED BY, пароль ему присвоен не будет (что не совсем разумно с точки зрения безопасности). Если же этот оператор задается для уже существующего пользователя, введенный пароль заменит используемый до настоящего момента. Старый пароль останется неизменным, если новый не будет определен. Строка пароля, задаваемая с помощью выражения IDENTIFIEDBY, должна представлять собой буквенную строку, которую при записи зашифрует оператор grant. Поэтому не следует применять функцию password(), применяемую с оператором SET password.

Оператор with grant option является необязательным. С его помощью можно предоставить пользователю все привилегии, определенные оператором GRANT для других пользователей. Этот оператор можно использовать для делегирования возможностей определенных категорий другим пользователям.

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

Чтобы определиться, какую же форму оператора GRANT применить, достаточно ответить на следующие вопросы.

  •  Кто и откуда может подключаться к серверу?
  •  Какой уровень привилегий должен иметь пользователь и на доступ к чему эти привилегии предоставляются?
  •  Необходимо ли пользователю предоставлять административные привилегии?

Давайте попробуем ответить на эти вопросы и рассмотрим примеры оператора grant для создания учетных записей пользователей MySQL.

Кто и откуда может подключаться к серверу

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

GRANT ALL ON samp_db.* TO boris@localhost IDENTIFIED BY "ruby"

GRANT ALL ON samp_db.* TO fred@ares.mars.net IDENTIFIED BY "quartz"

GRANT ALL ON samp_db.* TO max@% IDENTIFIED BY "diamond"

  

Символ "%" заменяет все возможные значения адресов и выполняет те же функции, что и при сравнении с помощью оператора LIKE. В предыдущем примере его можно условно заменить фразой "любой компьютер". Установка символа "%" аналогична простому опусканию части, задающей компьютер. Другими словами, эквивалентными в данном примере выступят записи max и mах@%. Это самый простой и, в то же время, самый незащищенный способ создать пользователя.

В случае необходимости можно также разрешить пользователю подключаться с ограниченного числа компьютеров. Так, чтобы пользователь mаrу мог подключаться с компьютеров домена snake.net, достаточно воспользоваться спецификатором %.snake.net: 

GRANT ALL ON samp_db.* TO mary@%.snake.net IDENTIFIED BY "topaz"

  

Для определения компьютера можно применять не только имена, но и IP-адреса. Эти адреса можно задавать явно либо с помощью вспомогательных символов. Кроме того, в версии MySQL 3.23 появилась возможность определять IP-адреса, задавая маску сети, устанавливая число разрядов в сетевом номере:

GRANT ALL ON samp_db.* TO joe@192.168.128.3 IDENTIFIED BY "water"

GRANT ALL ON samp_db.* TO ardis@192.168.128.% IDENTIFIED BY "snow"

GRANT ALL ON samp_db.* TO rex@192.168 .128.0/17 IDENTIFIED BY "ice"

  

Первый оператор определяет только один компьютер, с которого может подключиться пользователь joe. Второй определяет набор IP-адресов для подсети класса С 192.168.128. В третьем операторе часть 192.168.128.0/17 определяет 17-разрядный сетевой номер и соответствует любому компьютеру с адресом 192.168.128 в первых 17 разрядах IP-адреса.

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

GRANT ALL ON samp_db.president TO "my_friend"@"boa.snake.net"

  

Какой уровень привилегий должен иметь пользователь и на доступ

к чему эти привилегии предоставляются

Администратор может предоставлять пользователям привилегии разных уровней. Наиболее мощными являются глобальные привилегии, применяемые ко всем базам данных. Так, например, чтобы предоставить пользователю ethel права суперпользователя, который может делать все, в том числе и предоставлять привилегии другим пользователям, необходимо запустить следующий оператор:

GRANT ALL ON *.* ТО ethel@localhost IDENTIFIED BY "coffee"

     WITH GRANT OPTION

  

Спецификатор *. * в предложении ON можно условно заменить фразой "все базы данных и все таблицы". Для повышения безопасности подключаться пользователю ethel разрешено только с одного локального компьютера. Ограничение компьютеров, с которых может подключаться к серверу обладающий широкими правами пользователь, — весьма мудрое решение. В этом случае ограничивается и число компьютеров, с которых могут быть предприняты попытки взлома пароля.

Некоторые привилегии (FILE, process, reload и shutdown) являются административными и могут присваиваться только с помощью спецификатора глобальных привилегий ON *.*. В случае необходимости их можно присваивать без предоставления привилегий на уровне базы данных. Так, например, приведенный ниже оператор создает пользователя flush, который обладает возможностью только выполнять операторы FLUSH. Это может оказаться полезным в административных сценариях, когда необходимо выполнить обновление журналов:

GRANT RELOAD ON *.* ТО flush@localhost IDENTIFIED BY "flushpass"

  

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

Привилегии уровня базы данных применяются ко всем таблицам определенной базы. Такие привилегии присваиваются с помощью предложения ON db name:

GRANT ALL ON samp_db.* TO bill@racer.snake.net IDENTIFIED BY "rock"

GRANT SELECT ON menagerie.* TO ro_user@% IDENTIFIED BY "dirt"

  

Первый из указанных операторов предоставляет пользователю bill все права для работы со всеми таблицами базы данных sampdb. Второй оператор создает пользователя rouser с ограниченными правами (только чтение), который может получать доступ к любой таблице базы данных menagerie, однако только для чтения. Другими словами, этот пользователь имеет возможность запускать только оператор select.

Как определить имя локального компьютера в таблице разрешений

Довольно часто пользователи не могут подключиться к серверу с основного компьютера (на котором инсталлирован сервер) из-за того, что вместо имени localhost указывают имя сервера. Эта проблема возникает по причине использования разных способов определения имен, записанных в таблицах разрешений и выдаваемых программам. Если процедура сервера выдает неполное имя, например, pit-viper, а в таблицах разрешений записано полное имя pit-viper.snake.net (или наоборот), то подключение становится невозможным.

Чтобы определить, существует ли такая проблема на используемом компьютере, попытайтесь подключиться к локальному серверу с помощью опции -h, устанавливающей имя компьютера. Затем загляните в общий учетный файл сервера. Какое имя компьютера в нем записано, полное или неполное? Неважно, какая форма применяется. Важно использовать для определения имени компьютера в операторе grant именно это имя.

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

GRANT SELECT, INSERT, DELETE, UPDATE ON samp_db.* TO jennie@%

    IDENTIFIED BY "boron"

  

Для еще более детального управления доступом можно предоставлять привилегии отдельным таблицам или даже отдельным их столбцам. Привилегии столбцам оказываются особенно полезными, если определенную часть таблицы необходимо скрыть от пользователя либо предоставить возможность изменения только заданных столбцов. Предположим, что какая-то фирма нанимает на определенный период времени сотрудника, который будет выполнять роль секретаря. Администратор решает предоставить новому сотруднику права доступа только для чтения таблицы member, содержащей информацию о действующих членах общества, и привилегию update столбцу expiration (срок окончания членства) этой таблицы. При таком доступе новый секретарь вполне сможет изменять даты окончания членства организаций-участников, если они продолжают свое членство. Для создания такого пользователя MySQL можно использовать следующие операторы:

GRANT SELECT ON samp_db.member TO assistant@localhost IDENTIFIED

   BY "officehelp"

GRANT UPDATE (expiration) ON samp_db.member TO assistant@localhost

  

Первый оператор предоставляет права на чтение всей таблицы member и определяет пароль. Второй оператор добавляет привилегию update, но только для столбца expiration. Поскольку пароль устанавливается первым оператором, во втором его определять повторно вовсе не обязательно.

Если необходимо присвоить привилегии для работы с несколькими столбцами, их имена следует разделить запятыми. Так, например, чтобы добавить привилегии UPDATE для полей адресов таблицы member для пользователя assistant, используется приведенный ниже оператор. При его выполнении новые привилегии будут добавлены к уже существующим привилегиям пользователя:

GRANT UPDATE (street,city,state,zip) ON samp_db.member

  TO assistant@localhost

  

Как правило, пользователю не предоставляются большие привилегии, чем это нужно для работы. Иногда, тем не менее, возникает необходимость в предоставлении пользователям возможности создавать таблицы, чтобы заносить в них промежуточные результаты. Желательно, чтобы эти таблицы создавались не в рабочей базе данных, поскольку пользователи могут случайно изменить ее содержимое. Эту задачу можно решить посредством создания отдельной базы данных (назовем ее tmp) и предоставлению пользователям всех возможных привилегий для работы с ней.

Чтобы разрешить всем пользователям домена mars.net использовать базу данных tmp, достаточно ввести следующий оператор grant:

GRANT ALL ON tmp.* TO "@%.mars.net

  

После его выполнения пользователи смогут создавать и ссылаться на таблицы базы данных tmp с помощью имен типа tmp.tbl_name.

Нужны ли пользователю административные привилегии

Администратор может предоставить владельцу базы данных возможность управления доступом, предоставив ему все привилегии базы данных и определив опцию WITH GRANT OPTION. Например, чтобы разрешить пользователю alicia подключаться с любого компьютера домена bigcorp.com и предоставить ему административные привилегии для работы со всеми таблицами базы данных sales, необходимо использовать оператор grant следующего вида:

GRANT ALL ON sales.* TO alicia@%.big-corp.com IDENTIFIED BY "applejuice"

 WITH GRANT OPTION

  

Фактически, предложение with grant OPTION позволяет администратору делегировать права разрешения доступа другому пользователю. Однако следует проявлять осторожность, поскольку два пользователя с привилегиями grant могут предоставлять другим пользователям свои права. Если предоставить одному пользователю только привилегию SELECT, а второму, помимо select, привилегию GRANT, второй пользователь легко может сделать первого "более сильным".

Отмена привилегий и удаление пользователей

Для отмены привилегий пользователя применяется оператор revoke. Его синтаксис очень похож на синтаксис оператора GRANT с той лишь разницей, что предложение ТО заменено на предложение FROM, а предложения IDENTIFIED BY и WITH GRANT OPTION отсутствуют вовсе:

REVOKE privileges (columns) ON what FROM user

  

Часть user этого оператора должна соответствовать части user исходного оператора GRANT для пользователя, привилегии которого отменяются. Часть privileges необязательно должна соответствовать ранее определенным привилегиям. Пользуясь оператором revoke, можно отменить только некоторые из привилегий, предоставленные оператором GRANT.

Оператор revoke применяется для отмены привилегий, но не для удаления пользователей. В таблице user все равно остается запись для пользователя, даже если все привилегии для него сняты. Это означает, что пользователь все еще имеет возможность подключаться к серверу. Для полного удаления пользователя необходимо явным образом удалить его запись из таблицы user. Для этих целей применяется оператор DELETE:

% mysql -u root mysql

mysql> DELETE FROM user WHERE User = "user_name" and Host = "host_name";

mysql> FLUSH PRIVILEGES;

  

Оператор delete удаляет запись пользователя, а оператор FLUSH указывает серверу перезагрузить таблицы разрешений. (Таблицы перезагружаются автоматически при использовании операторов GRANT и revoke. Однако этого не происходит при непосредственном изменении таблиц разрешений.) Из описанной в следующем разделе ситуации вы узнаете, почему иногда лучше отказаться от удаления записей таблицы user.

Головоломка с привилегиями

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

GRANT ALL ON samp_db.* TO fred@%snake.net IDENTIFIED BY "cocoa"

  

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

Такая ситуация возникает, если в таблице разрешений содержатся записи, по умолчанию инсталлированные сценарием инициализации mysql_install_db. Причина ее возникновения в том, что при попытке подключения пользователя fred к серверу большим приоритетом перед записью этого пользователя обладает одна из записей анонимного пользователя. Согласно этой записи, для подключения пароль не нужен, однако пользователь fred пытается его ввести. Важно заметить, что для устранения этой проблемы достаточно удалить запись анонимного пользователя из таблицы user. Оператор revoke для этих целей не подходит, поскольку он отменяет только привилегии. Удаление записи выполняется с помощью следующих команд:

% mysql -u root mysql

mysql> DELETE FROM user where User="";  

mysql> FLUSH PRIVILEGES;

  

Сразу после удаления пользователь fred сможет успешно подключиться с локального компьютера.

Лекция 4: Проверка и восстановление таблиц


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

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

Проверка и восстановление таблиц

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

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

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

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

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

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

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

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

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

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

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

mysql>CHECK TABLE courses;

+--------------+-------+-----------+---------------------------------------------+

| Table        |  Op   | Msg_type  | Msg_text                                    |

+--------------+-------+-----------+---------------------------------------------+

| courses      | check | error     | Size of indexfile is: 1924 Should be: 2048  |

| test.courses | check | error     | corrupt                                     |

+--------------+-------+-----------+---------------------------------------------+

2 rows in set (0.00 sec)

                

mysql>REPAIR TABLE courses;

+--------------+--------+-----------+----------+

| Table        |  Op    | Msg_type  | Msg_text |

+--------------+--------+-----------+----------+

| test.courses | repair | status    | ok       |

+--------------+--------+-----------+----------+

1 row in set (0.08 sec)  

  

Листинг 4.1.

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

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

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

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

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

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

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

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

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

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

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

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

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

mysql>BACKUP TABLE dictionary TO '/tm/backup';

+-----------------+--------+-----------+------------------------+

| Table           |  Op    | Msg_type  | Msg_text               |

+-----------------+--------+-----------+------------------------+

| test.dictionary | backup | status    | ok                     |

+-----------------+--------+-----------+------------------------+

1 rows in set (0.27 sec)

  

Листинг 4.2.

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

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

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

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

mysql> RESTORE TABLE dictionary FROM '/tmp/backup';

+-----------------+---------+-----------+--------------------------+

| Table           |  Op     | Msg_type  | Msg_text                 |

+-----------------+---------+-----------+--------------------------+

| test.dictionary | restore | status    | ok                          |

+-----------------+---------+-----------+--------------------------+

1 rows in set (1 min 22.24 sec)

  

Листинг 4.3.

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

mysql> REPAIR TABLE dictionary;

+-----------------+---------+-----------+-----------------------------------------+

| Table           |  Op     | Msg_type  | Msg_text                                |

+-----------------+---------+-----------+-----------------------------------------+

| state           | repair  | warning   | number of rows changed from 0 to 104237 |

| test.state      | repair  | status    | ok                                      |

+-----------------+---------+-----------+-----------------------------------------+

2 rows in set (1 min 25.12 sec)

  

Листинг 4.4.

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

# mysqlhotcopy mysql /trap/hc

Locked 6 tables in 0 seconds.

Flushed tables(mysql.columns_priv, mysql.db, mysql.func, mysql.host,

 mysql.tables_priv, mysql.user) in 0 seconds.

Copying 18 files…

Copying indices for 0 files…

Unlocked tables.

Mysqlhotcopy copied 6 tables |(18 files) in 1 second (1 seconds overall).

# ls /tmp/hc/mysql

columns_priv.MYD db.MYD func.MYD host.MYD tables_priv.MYD user.MYD

columns_priv.MYI db.MYI func.MYI host.MYI tables_priv.MYI user.MYI

columns_priv.frm db.frm func.frm host.frm tables_priv.frm user.frm

  

Листинг 4.5.

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

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

Для создания sql-образа таблицы предназначена утилита mysqldump. Она записывает текст инструкций в поток stdout, поэтому нужно перенаправить результаты ее работы в файл. В листинге 4.6 показан созданный этой утилитой образ таблицы db из базы данных mysql. Утилита была запущена с опцией –opt которая включает режим оптимальных установок.

# MySQL dump   

#

# Host: localhost         Database: mysql

#

# Server version          4.12.25-log

#

# Table structure for table 'db'

#

DROP TABLE IF EXISTS db;

CREATE TABLE db (

 Host char(60) binary NOT NULL default '',

 Db char(64) binary NOT NULL default '',

 User char(16) binary NOT NULL default '',

 Select_priv enum('N', 'Y') NOT NULL default 'N',

 Insert_priv enum('N', 'Y') NOT NULL default 'N',

 Update_priv enum('N', 'Y') NOT NULL default 'N',

 Delete_priv enum('N', 'Y') NOT NULL default 'N',

 Create_priv enum('N', 'Y') NOT NULL default 'N',

 Drop_priv enum('N', 'Y') NOT NULL default 'N',

 Grant_priv enum('N', 'Y') NOT NULL default 'N',

 References_priv enum('N', 'Y') NOT NULL default 'N',

 Index_priv enum('N', 'Y') NOT NULL default 'N',

 Alter_priv enum('N', 'Y') NOT NULL default 'N',

 PRIMARY KEY (Host, Db, User),

 KEY User (User)

)TYPE=MyISAM COMMENT = 'Database privileges';

tt

# Dumping data for table 'db'

#

LOCK TABLES db WRITE;

INSERT INTO db VALUES

('%', 'test', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y'),

('%', 'test\\_%', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y'),

('Localhost', 'freetime', 'httpd', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'N');

UNLOCK TABLES;

Листинг 4.6.

He забудьте заблокировать все таблицы для чтения, прежде чем запускать утилиту mysqldump. В противном случае целостность результатов не гарантируется. Предположим, имеется приложение, которое хранит информацию о клиентах и их электронных адресах. Создание учетной записи нового клиента включает добавление записи в таблицу client и последующую вставку одной или нескольких записей в таблицу email_address. Если параллельно с этим создавать резервную копию базы данных, то может оказаться, что в промежутке между созданием образов таблиц client и email_address приложение попытается обновить обе эти таблицы. Доступ к первой таблице будет запрещен, а ко второй — нет. В результате в архиве появятся адреса, не соответствующие ни одной записи таблицы клиентов.

Чтобы восстановить данные из такого архива, достаточно выполнить SQL-сценарий в интерпретаторе mysql. Можно просто перенаправить сценарий на вход этой утилиты или же воспользоваться ее командой source. Интерпретатор выполнит все инструкции сценария так, как если бы они были введены в командной строке.

Утилита mysqldump имеет режим создания текстового образа таблицы. В этом режиме для каждой архивируемой таблицы создаются два файла. Один из них имеет расширение .sql и содержит соответствующую инструкцию CREATE TABLE. Второй файл имеет расширение .txt и содержит записи таблицы, причем для разделения полей применяются символы табуляции. В листинге 4.7 показана команда, создающая текстовый образ таблицы dictionary в каталоге /tmp.

[/tmp]# mysqldump –verbose –tab=/tmp test dictionary

# Connecting to localhost...

# Retrieving table structure for table dictionary...

# Sending SELECT query...

# Disconnecting from localhost...

  

Листинг 4.7.

Для восстановления данных из такого архива необходимо сначала создать таблицу, а затем выполнить инструкцию LOAD DATA INFILE, которая вставит записи в таблицу. Стандартный формат файла, создаваемого утилитой mysqldump, соответствует тому формату, который по умолчанию распознается инструкцией LOAD DATA INFILE. В листинге 4.8 демонстрируется загрузка данных в таблицу dictionary в среде mysql.

mysql> source /tmp/dictionary.sql

Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/dictionary.txt' INTO TABLE dictionary;

Query OK, 104237 rows affected (1 min 27.70 sec)

Records: 104237 Deleted: 0 Skipped: 0 Warnings: 0

Листинг 4.8.

Создать файл, понимаемый инструкцией LOAD DATA INFILE, позволяет также инструкция SELECT с предложением INTO (листинг 4.9). Схему таблицы необходимо получить другим путем, например с помощью инструкции SHOW CREATE TABLE.

mysql> SELECT * FROM dictionary INTO OUTFILE 'tmp/dictionary.txt';

Query OK, 104237 rows affected (6.42 sec)

  

Листинг 4.9.

Один из способов восстановления таблиц заключается в использовании двоичного журнала. Достаточно преобразовать его содержимое в SQL-инструкции и выполнить их. Предварительно необходимо заблокировать все таблицы для записи или отключить всех клиентов от сервера. Преобразование двоичного журнала осуществляется с помощью утилиты mysqlbinlog (листинг 4.10). Результаты ее работы нужно направить в файл или интерпретатору mysql. Обратите внимание: инструкция SET меняет метку текущего времени сеанса, чтобы дата создания таблицы осталась неизменной.

# mysqlbinlog –offset=1 –short-form red-bin.001

use freetime;

SET TIMESTAMP=991767105;

UPDATE session SET LastAction = now() WHERE ID='fNbbnOLBYYlqesqa';

use freetime;

SET TIMESTAMP=991767134;

UPDATE session SET LastAction = now() WHERE ID='fNbbnOLBYYlqesqa';

use freetime;

SET TIMESTAMP=991767134;

DELETE FROM project_view WHERE Project=2 AND User=2;

use freetime;

SET TIMESTAMP=991767135;

INSERT INTO project_view VALUES (2, 2, now());

  

Листинг 4.10.

Администрирование MySQL

Лекция 5: Сценарии и утилиты MySQL


Лекция рассматривает вспомогательные средства по работе с базами данных.

Обзор серверных сценариев и утилит

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

Заданные по умолчанию опции для всех стандартных программ можно переопределять при помощи файла опций.

В следующем списке приводятся краткие описания серверных программ MySQL.

  •  myisamchk

Утилита, применяемая для описания, проверки, оптимизации и восстановления таблиц MySQL. Поскольку утилита myisamchk имеет много функций, она описывается отдельно.

  •  make_binary_distribution

Создает бинарную поставку откомпилированного кода MySQL. Эту версию при помощи FTP можно передать на сайт support.mysql.com в директорию /pub/mysql/Incoming, чтобы ею могли воспользоваться и другие пользователи MySQL.

  •  mysqlbug

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

  •  mysqld 

Сам сервер MySQL, демон. Он должен работать постоянно.

  •  mysql_install_db

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

safe_mysqld, оболочка mysqld

В MySQL 4.0 safe_mysqld был переименован в mysqld_safe. 

safe_mysqld — это сценарий, с помощью которого рекомендуется запускать демон mysqld на Unix. safe_mysqld служит для реализации неких дополнительных функций безопасности для mysqld, таких, как возможность перезапуска сервера при обнаружении ошибки и записи в журнал информации о процессе работы mysqld.

Если не указаны опции -mysqld=# или -mysqld-version=#, safe_mysqld будет использовать исполняемый файл mysqld-max, если последний существует. Если mysqld-max не существует, safe_mysqld запустит mysqld. Это позволяет легко выяснить, что даст использование mysqld-max вместо mysqld: просто скопируйте mysqld-max поверх mysqld, и он будет работать.

Как правило, редактировать сценарий safe_mysqld не следует, однако можно указать опции для safe_mysqld, поместив их в раздел [safe_mysqld] файла my.cnf. safe_mysqld будет считывать все опции из разделов файлов опций [mysqld], [server] и [safe_mysqld].

Обратите внимание: все опции, которые вводятся в командной строке для safe_mysqld, передаются mysqld. Если требуется применить в safe_mysqld какие-либо опции, которые mysqld не поддерживает, эти опции нужно определить в файле опций.

Большинство опций safe_mysqld - те же, что и у mysqld.

Safe_mysqld поддерживает следующие опции:

-ledir=path

Путь к mysqld

-log-error=path

Записывать журнал ошибок в указанный файл

-no-defaults ,    

-open-files-limit=#

Максимальное количество файлов, которые могут быть открыты mysqld. Значение передается ulimit -n. Обратите внимание: чтобы эта опция работала корректно, необходимо запустить safe_mysqld от пользователя root!

-pid-file=path ,

-port=# ,

-socket=path ,

-timezone=#

Устанавливает переменную часового пояса (TZ) в значение, передаваемое в этом параметре

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

  •  Сервер и базы данных можно обнаружить относительно каталога, в котором вызывается safe_mysqld. safe_mysqld ищет в своей рабочей директории bin и data (для бинарных дистрибутивов) или libexec и var (для дистрибутивов с исходным кодом). Это условие должно выполняться, если safe_mysqld запускается из директории, в которую инсталлирован MySQL (например /usr/local/mysql для бинарного дистрибутива).
  •  Если сервер и базы данных не могут быть найдены относительно рабочей директории, safe_mysqld пытается найти их по абсолютным путям. Типичные местоположения — /usr/local/libexec и /usr/local/var. Действительные местоположения определяются при создании дистрибутива, из которого запускается safe_mysqld. Они должны быть корректными, если MySQL был инсталлирован в стандартное местоположение.

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

shell> cd mysql_installation_directory

shell> bin/safe_mysqld

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

Mysqld_multi, программа для управления множеством серверов MySQL

Программа mysqld_multi предназначена для управления несколькими процессами mysqld, работающих на различных сокетах Unix и портах TCP/IP.

Программа будет искать группу(группы) [mysqld#] в my.cnf (или заданных при помощи -config-file=... файлах), где # — любое положительное число, начиная с 1. Мы говорим про этот номер далее как про номер группы опций, или GNR. Номера групп различают группы опций одну от другой и используются как аргумент при запуске mysqld_multi чтобы указать, какие серверы вы хотите запустить, остановить или получить статус. Эти группы должны быть такими же, как и обычная группа [mysqld], но с такими портом, сокетом и т.д., которые требуются для каждого отдельного процесса mysqld.

mysqld_multi запускается в таком синтаксисе:

Использование: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]

или      mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]

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

shell> mysqld_multi -example

В качестве разделителей в списке GNR применяются запятые, комбинации создаются при помощи тире. Последнее означает, что будут задействованы все номера GNR из диапазона GNR1-GNR2. Если не задан аргумент GNR, то все группы будут либо запущены, либо остановлены, либо будет выведен отчет об этих группах. Обратите внимание, что в списке GNR не должно быть никаких пропусков (пробелов, символов табуляции или пустых строк). Любые данные после пропуска будут игнорироваться.

mysqld_multi поддерживает следующие опции:

-config-file=...

Альтернативный файл конфигурации (config file). Примечание: данный файл не влияет на собственные опции этой программы (группа [mysqld_multi]), а только на группы [mysqld#]. Без этой опции поиск всех данных будет осуществляться только в обычном файле my.cnf

-example

Представляет пример файла конфигурации

-log=...

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

-mysqladmin=...

Исполняемый файл mysqladmin, используемый для завершения работы сервера

-mysqld=...

Исполняемый файл mysqld, который будет использоваться. Обратите внимание: в этой опции можно также указывать safe_mysqld. Опции передаются mysqld. Необходимо только удостовериться, что в переменной окружения PATH имеется mysqld или что установлен safe_mysqld

-no-log

Вывод в stdout вместо журнала. По умолчанию журналы включены

-password=...

Пароль пользователя для доступа к mysqladmin

-tcp-ip

Подсоединение к серверу(ам) MySQL по TCP/IP вместо Unix-сокетов. Данная опция влияет на завершение работы сервера и создание отчетов. Если файл сокета отсутствует, сервер будет работать, но к нему можно будет обращаться только через порт TCP/IP. По умолчанию соединение осуществляется через сокет Unix

-user=...

Имя пользователя MySQL для mysqladmin

-version

Вывод номера версии и завершение работы

myisampack, MySQL-генератор сжатых таблиц (только для чтения)

Утилита myisampack используется для сжатия таблиц MyISAM, а утилита pack_isam — для сжатия таблиц ISAM. Поскольку таблицы ISAM являются устаревшими, здесь будет рассматриваться только myisampack.

myisampack сжимает каждый столбец в таблице по отдельности. Информация, необходимая для декомпрессии столбцов, считывается в память при открытии таблицы. В результате обеспечивается более высокая производительность при доступе к отдельным записям, поскольку нужно распаковывать только одну запись, а не значительно больший по размеру дисковый блок, как при использовании программы Stacker в MS DOS. В среднем myisampack сжимает файл данных на 40%-70%.

MySQL использует отображение в памяти (mmap()) для сжатых таблиц, а если mmap() не работает, возвращается назад к нормальному режиму чтения/записи.

Обратите внимание на следующее:

  •  После сжатия таблица доступна в режиме только для чтения. Это удобно, скажем, для записи на CD.
  •  myisampack может также сжимать столбцы c типами BLOB или TEXT.

Утилиту myisampack можно запустить следующим образом:

shell> myisampack [options] filename ...

Каждое имя файла (filename) должно быть именем индексного файла (.MYI). Если вы не находитесь в директории базы данных, необходимо указать полный путь к файлу. Допускается опускать расширение .MYI.

myisampack поддерживает следующие опции:

-b,

-backup

Создает резервную копию таблицы, присваивая ей имя tbl_name.OLD

-#,

 

-debug=debug_options

Выводить журнал отладки. Строка debug_options часто принимает значение d:t:o,filename 

-f,

-force

Сжатие таблицы происходит, даже если она увеличивается или если существует временный файл. Во время сжатия таблицы myisampack создает временный файл tbl_name.TMD. Если вы вручную прекратите выполнение myisampack, может оказаться, что файл tbl_name.TMD не будет удален. Обычно если myisampack обнаруживает существующий tbl_name.TMD, она прекращает работу и выдает ошибку. При указании опции -force myisampack сжимает таблицу в любом случае

-j big_tbl_name,

-join=big_tbl_name

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

-p #,

-packlength=#

Определяет разрядность поля, хранящего длину строки, в байтах. Может принимать значения 1, 2 или 3. (myisampack хранит все строки с указателями длины размером в 1, 2, или 3 байта. В большинстве случаев myisampack способна определить правильное значение длины перед началом сжатия файла, но во время сжатия она может обнаружить, что может быть использована более короткая длина. В этом случае myisampack выведет сообщение о том, что в следующий раз при сжатии данного файла можно использовать более короткую длину записи.)

-s,

-silent

Молчаливый режим. Сообщения выводятся только при возникновении ошибок

-t,

 

-test

Сжатие таблицы не выполняется, происходит только проверка процедуры сжатия

-T dir_name,

-tmp_dir=dir_name

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

-w,

-wait

Если таблица уже используется, подождать повторить попытку. Если сервер mysqld был вызван с опцией -skip-external-locking, то не самая лучшая идея — вызывать myisampack, если таблица может модифицироваться во время процесса сжатия

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

shell> ls -l station.*

-rw-rw-r--   1 monty    my         994128 Apr 17 19:00 station.MYD

-rw-rw-r--   1 monty    my          53248 Apr 17 19:00 station.MYI

-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station

MyISAM file:     station

Isam-version:  2

Creation time: 1996-03-13 10:08:58

Recover time:  1997-02-02  3:06:43

Data records:              1192  Deleted blocks:              0

Datafile: Parts:           1192  Deleted data:                0

Datafile pointer (bytes):     2  Keyfile pointer (bytes):     2

Max datafile length:   54657023  Max keyfile length:   33554431

Recordlength:               834

Record format: Fixed length

table description:

Key Start Len Index   Type                       Root  Blocksize    Rec/key

1   2     4   unique  unsigned long              1024       1024          1

2   32    30  multip. text                      10240       1024          1

Field Start Length Type

1     1     1

2     2     4

... ... ... ...

... ... ... ...

56    827   4

57    831   4

shell> myisampack station.MYI

Compressing station.MYI: (1192 records)

- Calculating statistics

normal:     20  empty-space:      16  empty-zero:        12  empty-fill:  11

pre-space:   0  end-space:        12  table-lookups:      5  zero:         7

Original trees:  57  After join: 17

- Compressing file

87.14%

shell> ls -l station.*

-rw-rw-r--   1 monty    my         127874 Apr 17 19:00 station.MYD

-rw-rw-r--   1 monty    my          55296 Apr 17 19:04 station.MYI

-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station

MyISAM file:     station

Isam-version:  2

Creation time: 1996-03-13 10:08:58

Recover time:  1997-04-17 19:04:26

Data records:              1192  Deleted blocks:              0

Datafile: Parts:           1192  Deleted data:                0

Datafilepointer (bytes):      3  Keyfile pointer (bytes):     1

Max datafile length:   16777215  Max keyfile length:     131071

Recordlength:               834

Record format: Compressed

table description:

Key Start Len Index   Type                       Root  Blocksize    Rec/key

1   2     4   unique  unsigned long             10240       1024          1

2   32    30  multip. text                      54272       1024          1

Field Start Length Type                         Huff tree  Bits

1     1     1      constant                             1     0

2     2     4      zerofill(1)                          2     9

3     6     4      no zeros, zerofill(1)                2     9

4     10    1                                           3     9

5     11    20     table-lookup                         4     0

6     31    1                                           3     9

7     32    30     no endspace, not_always              5     9

... ... ... ...

... ... ... ...

53    805   1                                          17     1

54    806   1                                           3     9

55    807   20     no empty                             3     9

56    827   4      no zeros, zerofill(2)                2     9

57    831   4      no zeros, zerofill(1)                2     9

Листинг 5.1.

Ниже приведено описание вывода myisampack:

normal

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

empty-space

Количество столбцов, содержащих пустые значения; онизанимают по 1 биту

empty-zero

Количество целочисленных столбцов, в которых содержатся только двоичные нули (ascii 0); каждый из них будет занимать 1 бит

empty-fill

Количество целочисленных столбцов, значения которых не полностью занимают отведенную для них разрядность в байтах; тип этих столбцов изменяется на тип с меньшей разрядностью (например, столбец INTEGER может быть изменен на MEDIUMINT)

pre-space

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

end-space

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

table-lookup

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

zero

Количество столбцов, все значения которых являются нулями

Original trees

Начальное количество деревьев Хаффмана

After join

Количество различных деревьев Хаффмана, оставленных после соединения деревьев для сохранения небольшого пространства в заголовках

После сжатия таблицы myisamchk -dvv выводит дополнительную информацию по каждому полю.

  •  Type

Тип поля может содержать следующие дескрипторы:

constant

Все строки содержат одинаковое значение

no endspace

Не сохраняются замыкающие пробелы

no endspace,

not_always

Не сохраняются замыкающие пробелы и не производится сжатие за счет замыкающих пробелов для всех значений

no endspace,

no empty

Не сохраняются замыкающие пробелы. Не сохраняются пустые значения

table-lookup

Столбец был преобразован к ENUM

zerofill(n)

В значении n главных байтов всегда являются 0 и не сохранены.

no zeros

Не сохраняются нули.

always zero

Значения 0 хранятся в 1 бите.

  •  Huff tree

Дерево Хаффмана, связанное с полем.

  •  Bits

Количество битов, используемых в дереве Хаффмана.

После запуска pack_isam/myisampack нужно запустить isamchk/myisamchk для повторного создания индекса. В это время можно также отсортировать индексные блоки и создать статистику, необходимую для более эффективной работы оптимизатора MySQL:

myisamchk -rq -analyze -sort-index table_name.MYI

isamchk -rq -analyze -sort-index table_name.ISM

После установки сжатой таблицы в директорию базы данных MySQL нужно проделать операцию mysqladmin flush-tables, чтобы сервер mysqld начал использовать новую таблицу.

Для распаковки сжатой таблицы можно использовать опцию -unpack isamchk или myisamchk.

Обзор клиентских сценариев и утилит

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

Имя

Описание

MYSQL_UNIX_PORT

Сокет, используемый по умолчанию для локальных подсоединений

MYSQL_TCP_PORT

Устанавливаемый по умолчанию порт TCP/IP

MYSQL_PWD

Устанавливаемый по умолчанию пароль

MYSQL_DEBUG

Опции пошаговой отладки программ

TMPDIR

Каталог для создания временных таблиц/файлов

Клиент mysql использует файл, указанный в переменной окружения MYSQL_HISTFILE, для хранения истории командной строки. Значение по умолчанию для этого файла истории — $HOME/.mysql_history, где $HOME — значение переменной окружения HOME.

Все программы MySQL принимают множество различных опций.

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

В приведенном ниже списке дано краткое описание клиентских программ MySQL.

msql2mysql

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

mysqlaccess

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

mysqladmin

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

mysqldump

Выводит содержимое базы данных MySQL в виде файла с SQL-операторами или в виде текстовых файлов с символом табуляции в качестве разделителя

mysqlimport

Импортирует текстовые файлы в соответствующие таблицы, используя команду LOAD DATA INFILE

>mysqlshow

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

replace

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

Утилита командной строки mysql

Утилита командной строки mysql является простой SQL-оболочкой (с возможностями библиотеки readline проекта GNU). Она поддерживает интерактивный и неинтерактивный режимы. В интерактивном режиме результаты запроса представляются в ASCII-формате. При использовании в неинтерактивном режиме (например, в качестве фильтра) результат представляется в текстовом формате с символом табуляции в качестве разделителя (выходной формат можно изменить при помощи параметров командной строки). Сценарии можно запускать, как показано ниже:

shell> mysql database < script.sql > output.tab

Если возникают проблемы из-за недостатка памяти на данном клиенте, применяйте параметр -quick! Это заставит mysql использовать функцию mysql_use_result() вместо функции mysql_store_result() для получения результирующей выборки данных.

Использовать mysql очень легко. Запустите mysql database или mysql -user=user_name -password=your_password database. Наберите SQL-команду прямо в командной строке, завершив ее одним из символов: ';', '\g' или '\G', и нажмите клавишу "Ввод".

Утилита командной строки mysql поддерживает следующие параметры.

-?, -help           

Вывод справочной информации об использовании программы и выход из нее

-A, -no-auto-rehash

Отключает автоматическое рехеширование. Rehash следует использовать для получения хеша таблиц и полей. Это обеспечивает более быстрый старт mysql

-prompt=...

Устанавливает приглашение на ввод команд в заданном формате

-b, -no-beep   

Выключает звуковой сигнал об ошибке

-B, -batch    

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

-character-sets-dir=...

Директория, где находятся наборы символов

-C, -compress

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

-#, -debug[=...]

Журнал отладки. Значение по умолчанию — 'd:t:o,/tmp/mysql.trace'

-D, -database=...

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

-default-character-set=...

Установить набор символов по умолчанию

-e, -execute=...  

Выполнить команду и завершить программу (вывод результата как и для -batch)

-E, -vertical   

Вывести результаты запроса (строки) по вертикали. Можно произвести вывод подобным образом и без данного параметра, завершая команды символами \G 

-f, -force

Продолжать обработку даже при обнаружении ошибки SQL

-g, -no-named-commands

Выключает именованные команды. Следует использовать только команды вида \* либо применять именованные команды только в начале строки, заканчивающейся символом ';' 

-i, -ignore-space

Игнорировать пробел после имен функций

-h, -host=...

Подсоединиться к базе данных на указанном хосте

-H, -html

Вывести выходные данные в виде HTML

-X, -xml

Вывести выходные данные в виде XML

-L, -skip-line-numbers

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

-no-pager

Блокирует пейджер (программа постраничного вывода) и выводит результат в стандартный вывод stdout (в Unix)

-no-tee

Блокирует выходной файл. Смотрите также команду \h (интерактивная помощь)

-n, -unbuffered

Очищать буфер после каждого запроса

-N, -skip-column-names

Не указывать имена столбцов в результатах

-o, -one-database

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

-pager[=...]

Устанавливает тип данных вывода. По умолчанию это переменная окружения PAGER. Ее возможные значения - less, more, cat [> имя файла], и т.д.

-p[password], -

password[=...]

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

-P порт, -port=порт

Номер порта TCP/IP, используемый для подсоединения

-q, -quick

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

-s, -silent

Режим молчания. Выводить только сообщения об ошибках

-S -socket=...

Файл сокета, используемый для подсоединения

-t -table

Выводить результат в табличном формате. Установлено по умолчанию для непакетного режима

-tee=...

Присоединить что-либо к выходному файлу. Смотрите также команду \h (интерактивная помощь). Этот параметр не работает в пакетном режиме

-u, -user=#

Имя пользователя MySQL, если этот пользователь не является активным в данное время

mysqladmin, администрирование MySQL-сервера.

Утилита для выполнения административных операций. Ее синтаксис:

shell> mysqladmin [ПАРАМЕТРЫ] command [command-option] command ...

Список опций, поддерживаемых вашей конкретной версией mysqladmin, можно получить, выполнив команду mysqladmin -help.

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

  •  create databasename

Создать новую базу данных.

  •  drop databasename 

Удалить базу данных и все ее таблицы.

  •  extended-status

Выдает расширенный отчет о состоянии сервера (более полный, чем при команде status ).

  •  flush-hosts

Сбросить и перезагрузить хосты.

  •  flush-logs

Сбросить на диск и переоткрыть все журналы.

  •  flush-tables

Закрыть все открытые таблицы.

  •  flush-privileges

Перечитать таблицы привилегий.

  •  kill id,id,... 

Завершить потоки mysql с указанными thread-id.

  •  password

Установить новый пароль для сервера баз данных. Изменить старый пароль на новый.

  •  ping

Проверить, работает ли сервер mysqld.

  •  processlist

Показать список активных потоков на сервере.

  •  reload

Перезагрузить таблицы привилегий.

  •  refresh

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

  •  shutdown

Завершить работу сервера баз данных.

  •  slave-start

Запустить подчиненный дублирующий поток.

  •  slave-stop

Остановить подчиненный дублирующий поток.

  •  status

Выдает краткий отчет о состоянии сервера.

  •  variables

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

  •  version

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

Все команды могут сокращаться до их уникальных префиксов. Например:

shell> mysqladmin proc stat

+----+-------+-----------+----+-------------+------+-------+------+

| Id | User  | Host      | db | Command     | Time | State | Info |

+----+-------+-----------+----+-------------+------+-------+------+

| 6  | monty | localhost |    | Processlist | 0    |       |      |

+----+-------+-----------+----+-------------+------+-------+------+

Uptime: 10077  Threads: 1  Questions:   9  Slow queries: 0

Opens: 6 Flush tables:  1  Open tables: 2

Memory in use: 1092K       Max memory used: 1116K

Результат команды mysqladmin status выводится в виде следующих столбцов:

Столбец

Описание

Uptime

Количество секунд с момента запуска MySQL-сервера

Threads

Количество активных потоков (клиентов)

Questions

Количество вопросов от клиентов с момента запуска программы mysqld

Slow queries

Количество запросов, потребовавших большее количество секунд, чем установлено в конфигурации ключом long_query_time 

Opens

Количество таблиц, открытых программой mysqld

Flush table

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

Open tables

Количество таблиц, открытых в данное время

Memory in use

Память, используемая непосредственно программой mysqld (доступно только в случае компиляции MySQL с установленным значением -with-debug=full)

Max memory used

Максимальный объем памяти, использованный непосредственно программой mysqld (доступно только в случае компиляции MySQL с установленным значением -with-debug=full)

При выполнении mysqladmin shutdown через сокет (другими словами, через компьютер с запущенным mysqld) mysqladmin будет ожидать, пока на сервере MySQL не будет удален файл pid-file (в котором содержится идентификатор процесса pid запущенного сервера), чтобы убедиться, что сервер остановлен должным образом.

Использование mysqlcheck для сопровождения и аварийного восстановления таблиц

Начиная с версии MySQL 3.23.38, можно применять новый инструмент для проверки и восстановления MyISAM-таблиц. Отличие mysqlcheck от myisamchk состоит в том, что утилита mysqlcheck должна использоваться при работающем сервере mysqld, в то время как myisamchk — при остановленном. Преимущество же заключается в том, что теперь не нужно останавливать сервер для проверки или восстановления таблиц.

Утилита mysqlcheck использует соответствующие команды MySQL-сервера CHECK, REPAIR, ANALYZE и OPTIMIZE удобным для пользователя образом.

Существует три альтернативных способа запуска mysqlcheck:

shell> mysqlcheck [OPTIONS] database [tables]

shell> mysqlcheck [OPTIONS] -databases DB1 [DB2 DB3...]

shell> mysqlcheck [OPTIONS] -all-databases

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

В сравнении с другими клиентами утилита mysqlcheck имеет следующую отличительную особенность: установка поведения по умолчанию (проверка таблиц, ) может быть изменена путем переименования исполняемого файла утилиты. Итак, чтобы получить инструмент, восстанавливающий таблицы по умолчанию, просто скопируйте mysqlcheck с новым именем, mysqlrepair, или, наоборот, сделайте символьную ссылку на mysqlrepair и обозначьте ее как mysqlrepair. Если теперь запустить mysqlrepair, то утилита по умолчанию будет восстанавливать таблицы.

mysqldump, получение дампов данных и структуры таблицы

Данная утилита позволяет получить дамп ("моментальный снимок") содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

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

shell> mysqldump [OPTIONS] database [tables]

или  mysqldump [OPTIONS] -databases [OPTIONS] DB1 [DB2 DB3...]

или  mysqldump [OPTIONS] -all-databases [OPTIONS]

Если не указывать имена таблиц или использовать параметры -databases или -all-databases, то будет получен дамп базы данных в целом (соответственно — всех баз данных).

Перечень опций, поддерживаемых вашей конкретной версией утилиты mysqldump, можно получить, выполнив команду mysqldump -help.

Следует иметь в виду, что утилита mysqldump, используемая без опций -quick или -opt, перед тем, как сделать дамп результата выборки информации, загрузит весь результат в память. Это может создать проблемы при получении дампа большой базы данных.

Учтите, что не следует применять параметры -opt или -e, если вы собираетесь использовать для получения дампа новую копию программы mysqldump, а затем воспроизводить его на очень старом MySQL-сервере.

Утилита mysqldump поддерживает следующие опции:

  •  -add-locks

Добавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).

  •  -add-drop-table

Добавить команду DROP TABLE перед каждой командой CREATE TABLE.

  •  -A, -all-databases

Произвести дамп всех баз данных. Аналогично опции -databases с указанием всех баз данных.

  •  -a, -all

Включить все опции создания объектов, специфичные для MySQL.

  •  -allow-keywords

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

  •  -c, -complete-insert

Использовать полные команды INSERT (с именами столбцов).

  •  -C, -compress

Использовать сжатие всей информации между клиентом и сервером, если они оба поддерживают сжатие.

  •  -B, -databases

Выполнить дамп нескольких баз данных. Обратите внимание на разницу в применении: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных. Оператор USE db_name; включается в вывод перед каждой новой базой данных.

  •  -delayed

Использовать команду INSERT DELAYED при вставке строк.

  •  -e, -extended-insert

Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).

  •  -#, -debug[=option_string]

Отслеживать прохождение программы (для отладки).

  •  -help

Вывести справочную информацию и выйти из программы.

  •  -fields-terminated-by=... , -fields-enclosed-by=... , -fields-optionally-enclosed-by=... ,
  •     -fields-escaped-by=... , -lines-terminated-by=...

Эти опции используются совместно с параметром -T и имеют то же самое значение, что и соответствующие операторы для LOAD DATA INFILE.

  •  -F, -flush-logs

Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа.

  •  -f, -force,

Продолжать даже при получении ошибки SQL при выполнении дампа таблицы.

  •  -h, -host=..

Выполнить дамп данных MySQL сервера на указанном хосте. Значение хоста по умолчанию — localhost.

  •  -l, -lock-tables.

Заблокировать все таблицы перед началом выполнения дампа. Таблицы блокируются оператором READ LOCAL, чтобы разрешить параллельные записи для MyISAM-таблиц. Следует отметить, что при выполнении дампа совокупности баз данных опция -lock-tables блокирует таблицы каждой базы по отдельности. Таким образом, использование этого параметра не гарантирует, что таблицы будут логически непротиворечивы в пределах этих баз данных. В различных базах данных при выполнении дампа таблицы могут находиться в совершенно разных состояниях.

  •  -K, -disable-keys

Добавляет выражение /*!40000 ALTER TABLE tb_name DISABLE KEYS */; и /*!40000 ALTER TABLE tb_name ENABLE KEYS */; в выводе результата. Это ускорит загрузку данных на сервер MySQL 4.0, так как индексы создаются после внесения всех данных.

  •  -n, -no-create-db

В выводе результата выражение CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; будет отсутствовать. Данная строка будет добавлена в любом случае при использовании опций -databases или -all-databases.

  •  -t, -no-create-info

Не записывать информацию о создании таблицы (команда CREATE TABLE).

  •  -d, -no-data

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

  •  -opt

То же, что и -quick -add-drop-table -add-locks -extended-insert -lock-tables. Должно дать наиболее быстрый дамп для чтения на MySQL-сервере.

  •  -pyour_pass, -password[=your_pass]

Используемый пароль при подключении к серверу. Если аргумент =your_pass не введен, mysqldump предложит ввести пароль.

  •  -P port_num, -port=port_num

Номер порта TCP/IP, используемого для подключения к хосту.

  •  -protocol=(TCP | SOCKET | PIPE | MEMORY)

Для указания протокола соединения, который надлежит использовать. Новшество в MySQL 4.1.0.

  •  -q, -quick

Выводить дамп непосредственно на стандартный вывод stdout без буферизации запроса. Для этого используется функция mysql_use_result().

  •  -Q, -quote-names

Взять в кавычки имена таблиц и столбцов без символов '`'.

  •  -r, -result-file=...

Прямой вывод указанного файла. Этот опцию следует использовать в MS DOS, так как она предотвращает преобразование символа новой строки '\n' в последовательность '\n\r' (новая строка + возврат каретки).

  •  -single-transaction

Данная опция выдает SQL-команду BEGIN перед выполнением дампа данных с сервера. Наиболее часто используется с InnoDB-таблицамии и уровнем изоляции транзакций READ_COMMITTED, так как именно в этом режиме можно получить дамп с непротиворечивым состоянием базы данных после выполнения команды BEGIN без блокирования каких-либо приложений. Используя эту опцию, необходимо помнить, что при выполнении дампа только транзакционные таблицы будут находиться в непротиворечивом состоянии, т.е. некоторые MyISAM- или HEAP-таблицы при использовании данной опции могут все же изменить свое состояние.

Опция -single-transaction добавлена в версии 4.0.2. Она является взаимоисключающей по отношению к опции -lock-tables, так как команда LOCK TABLES уже принимает открытую транзакцию.

  •  -S /path/to/socket, -socket=/path/to/socket

Файл сокета для подсоединения к localhost (значение хоста по умолчанию).

  •  -tables

Перекрывает параметр -databases (-B).

  •  -T, -tab=path-to-some-directory

Для каждой заданной таблицы создает файл table_name.sql, содержащий SQL CREATE команды для создания таблицы, и файл table_name.txt с данными таблицы. Файл .txt имеет формат в соответствии с параметрами -fields-xxx и -lines-xxx. Примечание: этот параметр работает только при условии, что утилита mysqldump запущена на том же компьютере, что и демон mysqld, причем пользователь/группа, запустившие данный поток mysqld (обычно это пользователь mysql и группа mysql), должны иметь право создавать/записывать файл по указанному адресу.

  •  -u user_name, -user=user_name

Имя пользователя MySQL-сервера, используемое при подключении к серверу. Значением по умолчанию является имя пользователя Unix.

  •  -O var=option, -set-variable var=option

Установить значения переменных. Доступные для использования переменные перечислены ниже. В MySQL 4.0 просто используйте -var=option.

  •  -v, -verbose

Расширенный режим вывода. Вывод более детальной информации о работе программы.

  •  -V, -version

Вывести информацию о версии и выйти из программы.

  •  -w, -where='where-condition'

Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны.

"-where=user='jimf'" "-wuserid>1" "-wuserid<1"

  •  -X, -xml

Представляет дамп базы данных в виде XML.

  •  -x, -first-slave

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

  •  -O net_buffer_length=#, where # < 16M

При создании многострочных операторов ввода (как и в случаях применения параметров -extended-insert или -opt) утилита mysqldump будет создавать строки длиной вплоть до указанной в net_buffer_length. При увеличении значения этой переменной необходимо также убедиться в том, что в MySQL-сервере для переменной max_allowed_packet указано значение, большее, чем величина net_buffer_length.

Чаще всего утилита mysqldump используется для получения резервной копии всех баз данных.

mysqldump -opt database > backup-file.sql

Можно, наоборот, прочитать этот файл на MySQL-сервере посредством команды:

mysql database < backup-file.sql

или

mysql -e "source /patch-to-backup/backup-file.sql" database

Данная утилита достаточно часто используется и для переноса информации из базы данных на другой MySQL-сервер:

mysqldump -opt database | mysql -host=remote-host -C database

Вполне возможно получить дамп нескольких баз данных с помощью одной команды:

mysqldump -databases database1 [database2 ...] > my_databases.sql

Если необходим дамп всех баз данных, можно использовать:

mysqldump -all-databases > all_databases.sql

mysqlhotcopy, копирование баз данных и таблиц MySQL

Утилита mysqlhotcopy представляет собой Perl-сценарий, использующий SQL-команды LOCK TABLES, FLUSH TABLES и Unix-утилиты cp или scp для быстрого получения резервной копии базы данных. Пожалуй, это наиболее быстрый способ копирования баз данных или таблиц, но он может работать только на том же компьютере, где расположены каталоги копируемой базы данных.

mysqlhotcopy db_name [/path/to/new_directory]

mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

mysqlhotcopy db_name./regex/

Утилита mysqlhotcopy поддерживает следующие опции:

  •  -u, -user=#

Имя пользователя для входа в базу данных.

  •  -p, -password=#

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

  •  -P, -port=#

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

  •  -S, -socket=#

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

  •  -keepold

Не удалять предыдущий результат (только что переименованный) после выполнения команды.

  •  -q, -quiet

Выводить только сообщения об ошибках.

  •  -debug

Разрешить отладку.

  •  -n, -dryrun

Сообщать о действиях без их выполнения.

  •  -regexp=#

Копировать все базы данных с именами, встречающимися в функции regexp.

  •  -checkpoint=#

Внести проверочную запись в предусмотренную таблицу базы данных.

  •  -flushlog

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

  •  -tmpdir=#

Временная директория (вместо /tmp).

А также некоторые другие. Более полное описание данного сценария можно посмотреть в документации по языку программирования Perl.

Сценарий mysqlhotcopy берет информацию для групп [client] и [mysqlhotcopy] из файлов опций.

Для выполнения программы mysqlhotcopy необходимы доступ для записи в директорию, куда будет помещена копия, и привилегия выполнения команды SELECT для копируемых таблиц и команды RELOAD для MySQL-сервера (чтобы выполнить FLUSH TABLES).

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

Утилита mysqlimport обеспечивает интерфейс командной строки для SQL-оператора LOAD DATA INFILE. Большинство параметров mysqlimport полностью соответствует аналогичным параметрам для оператора LOAD DATA INFILE.

Утилита mysqlimport вызывается следующим образом:

shell> mysqlimport [параметры] database textfile1 [textfile2 ...]

Для каждого текстового файла, указанного в командной строке, mysqlimport удаляет расширение в каждом имени файла и использует его, чтобы определить, в какую таблицу занести содержимое. Например, файлы с именами patient.txt, patient.text и patient должны быть все занесены в таблицу с именем patient.

Утилита mysqlimport поддерживает следующие опции:

  •  -c, -columns=...

Эта опция принимает в качестве аргумента список разделенных запятыми имен полей. Данный список полей используется для создания соответствующей команды LOAD DATA INFILE, которая затем посылается в MySQL.

  •  -C, -compress

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

  •  -#, -debug[=option_string]

Отслеживать прохождение программы (для отладки).

  •  -d, -delete

Удалить данные из таблицы перед импортированием текстового файла.

  •  -f, -force

Игнорировать ошибки. Например, если таблица для текстового файла не существует, продолжать обработку остающихся файлов. Без параметра -force утилита mysqlimport прекращает работу при отсутствии таблицы.

  •  -h host_name, -host=host_name

Импортировать данные в MySQL-сервер на указанном хосте. Значение хоста по умолчанию — localhost.

  •  -l, -lock-tables

Заблокировать все таблицы для записи перед обработкой любых текстовых файлов. Это обеспечивает синхронизацию всех таблиц на сервере.

  •  -L, -local

Читать входящие файлы из клиента. По умолчанию предполагается, что текстовые файлы расположены на сервере при подсоединении к localhost (значение хоста по умолчанию).

  •  -pyour_pass, -password[=your_pass]

Используемый пароль при подключении к серверу. Если аргумент =your_pass не введен, mysqlimport предложит ввести пароль.

  •  -P port_num, -port=port_num

Номер порта TCP/IP, используемого для подсоединения к хосту.

  •  -r, -replace

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

  •  -s, -silent

Режим молчания. Выводить только сообщения об ошибках.

  •  -u user_name, -user=user_name

Имя пользователя MySQL-сервера, используемое при подсоединении к серверу. Значением по умолчанию является имя для входа в Unix.

mysqlshow, просмотр баз данных, таблиц и столбцов

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

Аналогичную информацию можно получить с помощью программы mysql, используя команду SHOW.

Утилита mysqlshow вызывается следующим образом:

shell> mysqlshow [ПАРАМЕТРЫ] [database [table [column]]]

  •  Если имя базы данных не указано, то выдается список всех существующих баз данных.
  •  Если не указана таблица — показываются все таблицы, найденные в этой базе данных.
  •  Если не задан столбец — показываются все найденные в таблице столбцы и представленные в виде столбцов данные.

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

Если последний аргумент содержит в себе шаблонные символы (*, ?, % или _) процессора или SQL, то будут представлены только данные, совпадающие с шаблоном. Если имя базы данных содержит подчеркивание, то оно должно быть экранировано обратным слешем (некоторые оболочки в Unix востребуют два обратных слеша) для того, чтобы получить корректные имена. '*' конвертируются в '%' и '?' - в '_'.

Это может вызвать путаницу при попытке просмотреть столбцы таблицы с символом _, так как в таком случае mysqlshow покажет только имена таблиц, совпадающие с шаблоном. Ситуацию можно легко исправить добавлением дополнительного символа % в конец командной строки (как отдельного аргумента).

perror, разъяснение кодов ошибок

Для большинства системных ошибок, помимо внутреннего текстового сообщения MySQL, можно также выводить номер кода системной ошибки в одном из следующих стилей: message ... (errno: #) или message ... (Errcode: #).

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

perror выводит описание кода системной ошибки или код ошибки обработчика таблиц MyISAM/ISAM.

perror вызывается следующим образом:

shell> perror [ПАРАМЕТРЫ] [ERRORCODE [ERRORCODE...]]

Пример:

shell> perror 13 64

Error code 13: Доступ запрещен

Error code 64: Компьютер не находится в сети

Следует учитывать, что сообщения об ошибках в большинстве своем являются системно-зависимыми!

Запуск SQL-команд из текстового файла

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

shell> mysql database

Однако вполне можно поместить SQL команды в текстовый файл и указать mysql считывать входные данные из этого файла. Для этого необходимо создать текстовый файл text_file, содержащий команды, которые предстоит выполнить. Затем запускаем mysql как показано ниже:

shell> mysql database < text_file

Можно также запустить текстовый файл с командой USE db_name. В этом случае указывать имя базы данных в командной строке не обязательно:

shell> mysql < text_file

Если программа mysql уже работает, можно запустить файл с SQL-сценарием, используя команду source: 

mysql> source filename;

Лекция 6: Виды таблиц и способ их хранения

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

Способ хранения таблиц и баз данных

В MySQL таблице соответствует несколько файлов. Их имена совпадают с именем таблицы, а расширение определяет назначение файла. К примеру, файл с расширением .frm содержит описание структуры таблицы. Что касается баз данных, то они являются подкаталогами основного каталога данных (по умолчанию это /usr/local/var). Имя подкаталога соответствует имени базы данных. Это означает, что имена баз данных и таблиц отвечают тем же требованиям, которые предъявляются к именам файлов в данной системе. Скажем, файловая система ext2 в Linux чувствительна к регистру символов, a FAT32 в Windows — нет.

Операционные системы налагают свои ограничения на максимальный размер файла. Обычно он составляет от 2 до 4 Гбайт. Для таблиц типа MyISAM, описываемого ниже, все данные сохраняются в одном файле, следовательно, максимальный размер файла одновременно является максимальным размером таблицы.

MySQL позволяет разбивать табличные данные на несколько файлов при наличии опции RAID_TYPE в инструкции CREATE TABLE. Тогда максимальный размер таблицы возрастет во столько раз, сколько файлов для нее создается. Можно даже разместить эти файлы на разных физических дисках, чтобы повысить производительность базы данных.

Выделенные разделы

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

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

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

Типы таблиц

Начиная с версии 3.23.37 в В MySQL поддерживаются семь типов таблиц. Три из них – Berkley DB, Gemini и Inno DB ориентированы на транзакции, а четыре — Heap, ISAM, Merge и MyIsam — нет. Транзакции являются относительно новым понятием в MySQL, но соответствующие функции для таблиц Berkeley DB и InnoDB существуют уже достаточно давно, что позволило включить их в стандартные бинарные дистрибутивы.

Стандартным типом таблиц в MySQL является тип MyISAM. Он возник на основе более старого типа ISAM, который все еще существует, хотя использовать его не рекомендуется. Переопределить установку по умолчанию позволяет опция TYPE инструкций CREATE TABLE и ALTER TABLE 

Berkeley DB

Проект Berkeley DB начался в Калифорнийском университете в Беркли. Впоследствии его авторы сформировали компанию Sleepycat Software (www.sleepycat.com) и занялись распространением коммерческой версии СУБД. Многие утилиты до сих пор работают со старыми версиями BDB (1.85 и 1.86), в то время как компания Sleepycat Software предлагает уже семейство версий 3.x и 4.x. Эта СУБД свободно распространяется с исходными кодами, и ею можно пользоваться бесплатно, за исключением случаев, когда на ее основе планируется создавать приложения, не распространяемые на условиях открытой лицензии.

BDB — это простая файловая СУБД, поддерживающая транзакции, но не располагающая каким-либо языком запросов. В MySQL эта СУБД нужна для того, чтобы можно было работать с таблицами в режиме транзакций.

С Web-узла MySQL можно загрузить скомпилированную версию программы, в которую встроена поддержка BDB. Если впоследствии потребуется отключить эту поддержку, достаточно будет запустить демон mysql с опцией –skip-bdb. На этапе компиляции MySQL поддержка BDB включается с помощью опции –with-berkeley-db. Для MySQL нужна исправленная версия BDB, которая входит в исходный дистрибутив MySQL.

Разработчики MySQL тесно сотрудничают с программистами компании Sleepycat, чтобы гарантировать максимальную эффективность использования библиотеки BDB.

Вообще-то, поддержка BDB в MySQL появилась не так давно (в версии 3.23.24), но, учитывая высокую стабильность обоих продуктов, их интеграция не привела к возникновению каких-либо трудностей для пользователей. Разработчики MySQL планируют и дальше улучшать поддержку BDB.

С функциональной точки зрения таблицы BDB ведут себя аналогично таблицам MyISAM. Нет никаких ограничений на число столбцов или индексов, как в случае резидентных таблиц. Единственное условие: для таблиц BDB обязательно наличие первичного ключа. Если он не задан, MySQL самостоятельно создаст внутренний первичный ключ, охватывающий первые пять байтов каждой записи. К таблицам BDB можно применять инструкцию LOCK TABLES, но при частой работе с ними лучше пользоваться преимуществами транзакций.

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

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

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

СУБД BDB не ведет подсчет записей в таблицах, но MySQL хранит собственный счетчик. Он используется модулем оптимизации объединений при выборе индексов. Значение счетчика может быть неточным, если произошел сбой базы данных, но счетчик можно сбросить с помощью инструкции ANALYZE TABLE или OPTIMIZE TABLE. Ведение счетчика записей немного замедляет работу с таблицами BDB.

Табличные данные хранятся в виде двоичного дерева. Это более медленный метод, чем тот, который применяется для таблиц MyISAM. СУБД BDB оставляет в дереве пустые позиции, чтобы операции вставки выполнялись быстрее. В результате размер файла становится большим, чем нужно.

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

Gemini

Функции работы с таблицами Gemini реализовали программисты компании NuSphere (www.nusphere.com). Эта компания обеспечивает поддержку и обучение пользователей MySQL. В этих таблицах отсутствуют столбцы типа BLOB и TEXT. Количество пользователей, которые могут одновременно работать с таблицами, по умолчанию равно 100. Данную установку можно изменить с помощью серверной переменной gemini_connection_limit.

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

Как и в случае таблиц MyISAM, доступ к файлу данных таблицы Gemini не требуется, если в запросе участвуют столбцы одного индекса. Для этих таблиц также ведется счетчик записей, используемый модулем оптимизации объединений.

Heap

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

Резидентные таблицы не располагают многими возможностями обычных таблиц. Они не могут иметь столбцы типа BLOB или TEXT. Нельзя использовать флаг AUTO_INCREMENT. Можно создавать индексы, но нельзя индексировать столбцы, допускающие значения NULL. Индексы используются только в операциях = и <=> .

Записи резидентных таблиц имеют фиксированную длину. Для столбцов типа VARCHAR сразу выделяется максимальное число байтов. Поскольку память — ограниченный ресурс, можно задать предельное количество записей в резидентной таблице.

Для этого предназначена опция max_rows инструкции CREATE TABLE. Серверная переменная max_heap_table_size задает максимальный объем памяти, занимаемой всеми резидентными таблицами.

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

InnoDB

СУБД InnoDB была разработана Хейкки Туури (Heikki Tuuri) из компании Innobase — финского производителя программного обеспечения, специализирующегося на технологии реляционных баз данных. InnoDB представляет собой результат исследований, проводимых Хейкки в университете Хельсинки. Поддержка InnoDB появилась в MySQL версии 3.23. Сама СУБД доступна на условиях открытой лицензии.

На Web-узле InnoDB можно найти массу информации о деталях работы ядра этой СУБД. Ядро не существует само по себе, а является дополнением к MySQL. С Web-узла MySQL можно загрузить скомпилированную версию программы, в которую встроена поддержка InnoDB. Если впоследствии потребуется отключить эту поддержку, достаточно будет запустить демон mysql с опцией –skip-innodb. На этапе компиляции MySQL поддержка InnoDB включается с помощью опции –with-innodb. Исходные коды InnoDB входят в исходный дистрибутив MySQL.

В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB хранятся в больших совместно используемых файлах. Можно создать произвольное число файлов данных, но их нельзя будет удалить. Размер файлов определяется в конфигурационном файле. Если нужно уменьшить объем дискового пространства, занимаемого таблицами InnoDB, создайте резервные копии таблиц, после чего удалите все файлы InnoDB и позвольте программе MySQL восстановить их в соответствии с новыми установками конфигурационного файла. Журнальные файлы InnoDB можно безопасно удалить после остановки сервера. При повторном запуске сервера программа MySQL создаст журнальные файлы заново.

В листинге 6.1 приведен пример опций, которые необходимо добавить в конфигурационный файл в группу [mysql] чтобы активизировать таблицы InnoDB. Размер файлов здесь задан относительно небольшим, что вполне подходит для целей эксперимента. На практике используются файлы гораздо большего размера.

innodb_data_home_dir = /usr/local/var/innodb/

innodb_data_file_path = ibdata1/ibdata1:100M; ibdata2/ibdata2:100M

set-variable = innodb_mirrored_log_groups = 1

innodb_log_group_home_dir = /disk2/innodb/log

set-variable = innodb_log_files_in_group = 3

set-variable = innodb_log_file_size = 16M

set-variable = innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_log_arch_dir = /disk2/innodb/log

innodb_log_archive = 0

set_variable = innodb_buffer_pool_size = 25M

set_variable = innodb_additional_mem_pool_size = 5M

set_variable = innodb_file_io_threads = 4

set_variable = innodb_lock_wait_timeout = 50

Листинг 6.1.

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

Таблицы InnoDB блокируются на уровне записей. Это происходит без участия пользователей по мере выполнения инструкций в рамках транзакций. Инструкция LOCK TABLE может конфликтовать с блокировками InnoDB. В отличие от таблиц MyISAM, блокировки таблиц InnoDB способны приводить к возникновению тупиков, т.е. взаимоблокировок. В подобной ситуации одна или несколько транзакций отменяется. Это следует учитывать при написании приложений. Инструкция, которая приводит к автоматической отмене транзакции, возвращает сообщение об ошибке. Транзакции отменяются также в случае нехватки места в файловой системе.

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

На момент создания этого курса существовало несколько ограничений таблиц InnoDB. Самое существенное из них заключалось в способе отслеживания таблиц. В InnoDB ведётся каталог таблиц, который не поддерживается инструкцией DROP TABLE, поэтому каждую таблицу приходится удалять отдельно. Не разрешается индексировать префикс столбца, а также индексировать столбцы типа BLOB и TEXT. Максимальное количество столбцов в таблице — 1000. Флаг DELAYED в инструкции INSERT не поддерживается.

ISAM

До версии 3.23 стандартным типом таблиц в MySQL был тип ISAM. Он не обладает такими возможностями, как более новый тип MyISAM, поэтому в современных версиях MySQL использовать его не рекомендуется.

Merge

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

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

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

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

MyISAM

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

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

MySQL хранит счетчик подключений к таблице MylSAM. Когда таблица закрывается, счетчик сбрасывается в нуль. Если сервер неожиданно завершает работу, счетчик остается положительным числом. В таком случае в процессе перезапуска сервер обнаружит проблему. Это не означает, что таблица непременно повреждена, но подобная возможность существует. Следует немедленно выполнить инструкцию CHECK TABLE или вызвать утилиту myisamchk. Можно также запустить демон mysql с опцией –myisam-recover, чтобы заставить его восстанавливать все таблицы MyISAM с ненулевым значением счетчика.

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

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

Индексные файлы имеют расширение .MYI. Файлы с расширением .MYD содержат данные, а с расширением .frm – схему таблицы. Если индексный файл по какой-то причине теряется, программа перестраивает индексы, используя информацию из frm-файла.

По умолчанию в каждой таблице может быть не более тридцати двух индексов, но это значение можно повысить до шестидесяти четырех. Индексы создаются в виде двоичных деревьев. Разрешается индексировать столбцы типа BLOB и TEXT, а также столбцы, допускающие значения NULL.

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

Таблица будет иметь записи фиксированной длины, если в ней нет столбцов типа VARCHAR, BLOB или TEXT. Одинаковая длина записей имеет свои преимущества. Утилите myisampack будет проще восстанавливать поврежденные записи, если она знает их точную длину. Такие записи никогда не приходится разбивать на части при наличии в таблице пустых промежутков, что ускоряет операции чтения. Правда, записи фиксированной длины обычно занимают больше места на диске.

Все записи таблицы будут динамическими, если в ней есть столбцы типа VARCHAR, BLOB или TEXT. Возможно также приведение столбцов типа CHAR к типу VARCHAR, если их длина больше четырех символов. Длина каждой записи отслеживается по специальному заголовку. В нем указана длина текущего сегмента записи. Поскольку при повреждении таблицы связи между фрагментами могут теряться, корректное восстановление записи не всегда возможно.

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

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

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

Столбцы

В табл. 6.1 указаны размерности стандартных типов данных MySQL. Значения некоторых типов всегда занимают фиксированный объем памяти. Например, размерность столбцов типа INTEGER всегда составляет 4 байта. Столбцы типа CHAR могут иметь размерность от 0 до 255, но в момент создания таблицы под них отводится фиксированный объем памяти. Существуют также столбцы переменной размерности. Например, столбцы типа VARCHAR и BLOB интерпретируются в соответствии с их содержимым.

Таблица 6.1. Размерности стандартных типов данных.

Тип

Размерность

BIGINT

8 байтов

BLOB, TEXT

длина содержимого + 2 байта

CHAR

указанное число байтов

DATE

3 байта

DATETIME

8 байтов

DECIMAL (длина, точность)

длина + 1 байт, если точность равна 0; в противном случае — длина + 2 байта

DOUBLE

8 байтов

DOUBLE PRECISION

8 байтов

ENUM 

1 байт, если в перечислении менее 255 элементов; в противном случае — 2 байта

FLOAT 

4 байта

FLOAT (длина)

4 байта, если длина <= 24; в противном случае — 8 байтов

INT 

4 байта

INTEGER 

4 байта

LONGBLOB, LONGTEXT

длина + 2 байта

MEDIUMBLOB, MEDIUMTEXT 

длина + 2 байта

MEDIUMINT

3 байта

NUMERIC (длина, точность)

длина + 1 байт, если точность равна 0; в противном случае – длина + 2 байта

REAL 

8 байтов

SET 

1, 2, 3, 4 или 8 байтов, в зависимости от количества элементов множества

SMALLINT 

2 байта

TIME 

3 байта

TIMESTAMP 

4 байта

TINYBLOB, TINYTEXT 

длина + 2 байта

TINYINT 

1 байт

VARCHAR (длина)

длина содержимого + 1 байт

YEAR

1 байт

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

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

Блокировки таблиц

В MySQL разрешается явно блокировать таблицы с помощью инструкции LOCK TABLES. Тем не менее, не рекомендуется делать это для таблиц тех типов, которые поддерживают транзакции. Блокировки и транзакции — это два разных способа решения проблемы одновременного доступа к таблице, поэтому нужно сделать выбор в пользу одного из них.

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

Можно заблокировать таблицу таким образом, чтобы разрешить другим потокам обращаться к ней для чтения. Это называется блокировкой чтения. Блокировка записи гарантирует текущему потоку монопольный доступ к таблице. Запросы на чтение откладываются до тех пор, пока не будут сняты все блокировки записи. Эту установку можно изменить с помощью флагов инструкций либо путем задания специальных серверных переменных. Для SQL-инструкций создаются две очереди. Чтобы программа MySQL начала извлекать инструкции из очереди на чтение, очередь на запись должна быть пуста. При наличии флага LOW_PRIORITY инструкции DELETE, INSERT и UPDATE помещаются в очередь на чтение, т.е. они получают такой же приоритет, что и инструкции SELECT. Флаг HIGH_PRIORITY переводит инструкцию SELECT в очередь на запись.

Индексы

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

Определения индексов хранятся в frm-файле а сами индексируемые значения — в файле с расширением .MYI. Если индексный файл отсутствует на момент запуска сервера, он будет автоматически воссоздан. Таким образом, при создании резервных копий можно не заботиться об индексах в целях экономии места. Позднее, в процессе восстановления базы данных, программа MySQL создаст индексы заново на основании схемы таблицы.

Индексы способны повысить производительность инструкций, связанных с поиском записей. Они ускоряют процесс сравнения столбцов при выполнении операций объединения. Кроме того, они помогают находить минимальное и максимальное значения столбца и ускоряют выполнение инструкций SELECT с предложением ORDER BY.

Чтобы индекс был задействован, он должен быть указан во всех частях предложения WHERE. Если используется лишь часть индекса, то должен соблюдаться порядок обращения к индексируемым столбцам: слева направо. Для примера рассмотрим таблицу, определение которой приведено в листинге 6.2.

CREATE TABLE car (

Make CHAR(32) NOT NULL,

Model CHAR(32) NOT NULL,

Introduced YEAR,

PRIMARY KEY (MAKE,Model)

);

Листинг 6.2.

У таблицы car имеется составной первичный ключ. В запросе, который показан в листинге 6.3, индекс будет использован, так как столбец Make является самым левым компонентом индекса.

SELECT * FROM car WHERE Make='Ford';

Листинг 6.3.

А вот в следующем запросе (листинг 6.4) этого не произойдет, поскольку правило очередности столбцов не соблюдается.

SELECT * FROM car WHERE Model='Pinto';

Листинг 6.4.

В листинге 6.5 индекс также не используется, из-за того, что самый левый компонент индекса нельзя применить к каждой записи. Если бы в предложении WHERE стоял оператор AND, а не OR, все было бы наоборот.

SELECT * FROM car WHERE Make='Ford' OR Model='Impala';

Листинг 6.5.

Следующий запрос (листинг 6.6) является правильным с точки зрения использования индекса. В данном случае просмотр значений столбца осуществляется слева направо.

SELECT * FROM car WHERE Make LIKE 'F%';

Листинг 6.6.

В листинге 6.7 индекс не используется, потому что просмотр значений столбца осуществляется справа налево (метасимвол % стоит вначале).

SELECT * FROM car WHERE Make LIKE '%d';

Листинг 6.7.

Дескрипторы файлов

Сервер MySQL представляет собой один процесс со множеством потоков. Для каждого сеанса подключения к серверу создается свой поток. Каждому потоку требуется один или несколько дескрипторов файлов, чтобы он мог осуществлять чтение и запись таблиц. Операционная система ограничивает количество файловых дескрипторов, доступных процессу. Это число может быть самым разным. Например, в AIX оно равно 2000 по умолчанию, а в Solaris — всего лишь 64. В Linux лимит по умолчанию составляет 1024 дескриптора. В Windows NT и 2000 видимый предел отсутствует.

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

Размер кэша дескрипторов можно задать другим, но не забывайте об ограничении, которое накладывается операционной системой. Правда, ее собственный лимит тоже можно изменить. Для этого существует, например, команда unlimit. Еще один способ — перекомпиляция ядра.

Чтобы получить доступ к таблице, поток должен иметь в своем распоряжении дескриптор ее файла данных. Все потоки совместно владеют дескриптором индексного файла. Таким образом, когда три потока одновременно извлекают данные из таблицы, используются четыре дескриптора. Если таблица дважды указана в предложении FROM, например в случае операции самообъединения, программа MySQL вынуждена открывать отдельный дескриптор для каждой ссылки на таблицу.

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

Системная память

В MySQL специальные буферы и кэши применяются для самых разных целей. Их размеры можно задавать в конфигурационном файле или в командной строке запуска сервера. У каждого потока есть свой стек, буфер приема входных данных от клиента и буфер результатов запроса. Размер стека задается серверной переменной thread_stack, а размеры обоих буферов — переменной net_buffer_length. Последняя определяет начальные размеры буферов, так как они могут увеличиваться в случае необходимости, например, при обработке столбцов типа BLOB или TEXT.

Все потоки совместно используют индексный буфер. Его размер определяется переменной key_buffer_size. В операциях объединения, проходящих без участия индексных столбцов, используется отдельный буфер (переменная join_buffer_size), как и в операциях сканирования таблиц (переменная record_buffer).

Если для выполнения операции объединения требуется временная таблица, она создается как резидентная (тип Heap). Максимальный размер таких таблиц определяется переменной tmp_table_size. После превышения этого предела таблица преобразуется в формат MyISAM. В любом случае временные таблицы удаляются по окончании операции.

Лекция 7: Каталог данных MySQL


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

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

По умолчанию вся информация, управляемая сервером mysqld, содержится в так называемом каталоге данных MySQL (MySQL data directory). В нем хранятся все базы данных и файлы состояния с информацией о функционировании сервера. Пользователь, выполняющий функции администратора MySQL, должен знать структуру этого каталога и уметь использовать его в своей повседневной работе.

В этой лекции даются исчерпывающие ответы на следующие вопросы.

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

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

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

Размещение каталога данных

По умолчанию местоположение для каталога данных устанавливается при компиляции сервера. Обычно при инсталляции с исходной дистрибуции устанавливается каталог /usr/local/var, при инсталляции из двоичной дистрибуции — /usr/local/mysql/data, а при инсталляции из файла RPM -- /var/lib/mysql.

Размещение каталога данных можно задать и явным образом при запуске сервера. Для этих целей применяется опция --datadir= /path/to/dir. Она оказывается весьма кстати, если каталог данных необходимо разместить в месте, отличном от того, которое указывается по умолчанию.

Администратор MySQL обязательно должен знать, где находится каталог данных. При запуске нескольких серверов следует записать местоположение всех каталогов данных. Если же размещение каталога неизвестно (например, из-за того, что предыдущий администратор плохо вел свои записи), его можно определить несколькими методами.

  •  Воспользоваться командой mysqladmin variables для получения пути к каталогу данных непосредственно с сервера. На компьютере, работающем под управлением ОС UNIX, результат ее ввода будет выглядеть примерно так:
  •  % mysqladmin variables
  •  +-------------------+-----------------+
  •  | Variable name     | Value           |
  •  +-------------------+-----------------+
  •  | back log          | 5               |
  •  | connect timeout   | 5               |
  •  | basedir           | /var/local/     |
  •  | datadir           | /usr/local/var/ |
  •  ...

Из приведенных выше результатов видно, что каталог данных размещается в каталоге /usr/local/var/ сервера.

На компьютере, работающем под управлением ОС Windows, результаты ввода этой же команды будут выглядеть следующим образом.

% mysqladmin variables

+-------------------+-----------------+

| Variable name     | Value           |

+-------------------+-----------------+

| back log          | 5               |

| connect timeout   | 5               |

| basedir           | c: \mysql\      |

| datadir           | c: \mysql\data\ |

...

Если на компьютере запущено несколько серверов, каждый из них использует свой порт TCP/IP и разъем. Чтобы получить информацию о каталоге данных от каждого сервера, достаточно подключиться с помощью опций --port и --socket к соответствующему порту и разъему.

% mysqladmin --port=port_num variables

% mysqladmin --socket=/path/to/socket 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 BSD—UNIX
  •  % ps -ef | grep mysql     ps системы System V

Команда ps особенно полезна при запуске на одном компьютере нескольких серверов, поскольку позволяет узнать месторасположение сразу всех каталогов данных. Недостаток этого метода заключается в том, что команду ps обязательно нужно запускать на главном компьютере. Кроме того, она будет бесполезна, если переменная --datadir не описана явным образом в командной строке mysql.

  •  Если MySQL инсталлировалась из исходной дистрибуции, месторасположение каталога данных можно получить из информации о конфигурации. Так, например, месторасположение каталога указывается в элементе верхнего уровня Makefile. Однако будьте осторожны, поскольку позиция каталога является в Makefile значением переменной localstatedir, а не datadir, как ожидают многие. Кроме того, если дистрибуция размещается на смонтированной сетевой файловой системе NFS и используется для установки MySQL на несколько компьютеров, в информации конфигурации отражаются данные только для компьютера, на котором система устанавливалась последней. Вполне возможно, что им окажется не тот компьютер, для которого необходимы данные.
  •  Если все предыдущие методы вам не подходят, можно воспользоваться командой find для поиска файлов базы данных. Приведенная ниже команда ищет все файлы .frm (описания), являющиеся частью инсталляций MySQL:
  •  % find / -name "*.frm" -print

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

Структура каталога данных

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

  •  Каждой базе данных соответствует подкаталог, расположенный внутри каталога данных.
  •  Таблицам базы данных соответствуют файлы, размещенные внутри каталога базы данных.

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

Как обеспечивается доступ к данным сервера MySQL

Каждый элемент внутри каталога данных находится под управлением MySQL-сервера mysqld. Клиентские программы никогда не обращаются к данным напрямую. Точку взаимодействия, с помощью которой осуществляется доступ к базам данным, обеспечивает сервер, работающий в качестве промежуточного звена между клиентскими программами и данными (рис. 7.1).


Рис. 7.1.  Сервер является промежуточным звеном между клиентскими программами и данными

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

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

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

Запущены утилиты isamchk и myisamchk. Эти утилиты используются для поддержки таблиц, устранения проблем и отладки баз данных. Поскольку они обладают возможностью изменения содержимого таблиц, то могут получать доступ к данным одновременно с сервером. Это может стать источником повреждений данных. Администратор должен понимать, как ограничить подобный совместный доступ, чтобы снизить вероятность разрушения таблиц.

Представление баз данных

Каждая управляемая сервером MySQL база данных имеет свой собственный каталог. Он представлен в виде подкаталога каталога данных и имеет такое же название, как и собственно база. Так, например, базе данных my_db будет соответствовать каталог базы данных DATADIR/my_db.

Такое представление значительно упрощает понимание предназначения и принципов работы некоторых операторов обработки баз данных. Так, оператор create database db_name создает пустой подкаталог db_name в каталоге данных, устанавливая права владения и режим, которые обеспечивают доступ только для пользователя сервера MySQL (UNIX-пользователя, работающего на сервере). Аналогичных результатов создания базы данных пользователь сервера может добиться и вручную, введя следующие команды.

% mkdir DATADIR/db_name    Создает каталог базы данных

% chmod 700 DATADIR/db_name   Делает его доступным только для пользователя сервера MySQL

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

Также легко можно реализовать и команду DROP database. Команда drop database db_name удаляет из каталога данных подкаталог db_name вместе со всеми расположенными в нем файлами. Тех же результатов можно достичь с помощью команды:

% rm -rf DATADIR/db_name

(Разница между этими двумя командами заключается в том, что сервер удалит лишь файлы, расширение которых отвечает табличным файлам. Если же в каталоге базы данных были созданы также какие-либо другие файлы, они останутся нетронутыми и каталог удален не будет.)

Команда show databases на самом деле выводит список названий подкаталогов каталога данных. Некоторые системы управления базами данных поддерживают специальную таблицу со списком всех баз данных. В MySQL такой таблицы нет. Благодаря простоте структуры список баз является списком подкаталогов каталога данных. Следовательно, и необходимость в подобной таблице отсутствует.

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

Каждая таблица представлена в каталоге базы данных в виде трех файлов: файла формы (описания), файла данных и файла индексов. Основное имя файла соответствует названию таблицы, а его расширение отражает тип файла. Краткое описание расширений представлено в табл. 7.1. По расширениям файлов данных и индексов можно определить, используется ли в таблице старый формат ISAM или новый MyISAM.

Таблица 7.1. Типы файлов MySQL

Тип файла

Расширение имение файла

Содержимое файла

Файл формы

frm

Описывает структуру таблицы (столбцы, типы столбцов, индексы и т.п.)

Файл данных

ISD (ISAM) или MYD (MyISAM)

Содержит данные таблицы, т.е. его строки

Файл индексов

ISM (ISAM) или MYI (MyISAM)

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

При выполнении оператора CREATE TABLE tblname, определяющего структуру таблицы, сервер создает файл tblname.frm с внутренней кодировкой структуры. Кроме того, создаются также файлы данных и индексов с информацией об отсутствии записей и индексов. (Если оператор create table включает спецификации индексов, в файле индексов они отражаются соответствующим образом.) Параметры владельца и режима файлов таблицы устанавливаются такими, чтобы обеспечить доступ только пользователю сервера MySQL.

При исполнении оператора alter table расшифровывает файл tbl_name.frm и изменяет файлы данных и индексов с учетом определенных оператором структурных изменений. Такие же операции имеют место и при выполнении операторов create index и drop index, поскольку они рассматриваются сервером как эквивалентные оператору ALTER table. В процессе выполнения оператора drop table из каталога базы данных удаляются все три представляющих таблицу файла.

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

% rm -f DATADIR/my_db/my_tbl. *

Вывод оператора SHOW tables mydb представляет собой простой список имен (без расширений) FRM-файлов каталога базы данных my_db. Как уже отмечалось ранее, некоторые СУБД поддерживают специальный реестр со списком всех таблиц баз данных. В MySQL такой реестр не нужен, поскольку список таблиц легко определяется благодаря структуре каталога данных.

Ограничения операционной системы на имена баз данных и таблиц

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

  •  Имена могут включать буквы и цифры текущего набора символов, а также символы подчеркивания и доллара ("_" и "$").
  •  Длина имен не может превышать 64 символа.

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

Во-первых, в именах баз данных и таблиц можно использовать только разрешенные для имен файлов символы. Так, например, символ "$" разрешается правилами MySQL, однако в некоторых операционных системах его нельзя применять в именах файлов. Такого рода ограничения отсутствует в ОС UNIX и Windows. Наиболее значительные проблемы могут возникнуть в момент ссылки на имена баз данных при выполнении задач администрирования непосредственно из оболочки. Предположим, например, что база данных имеет имя $my_db. В этом случае всякая ссылка на имя базы может интерпретироваться как ссылка на переменную:

% Is $my_db

my_db: Undefined variable.

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

% Is my_db

% Is '$my_db'

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

Во-вторых, несмотря на то, что MySQL разрешает задавать для баз данных и таблиц имена длиной до 64 символов, на самом деле их длина ограничивается максимально возможной длиной имен файлов. В большинстве случаев эта проблема как таковая отсутствует, хотя в некоторых UNIX-системах System V все еще существует старое ограничение в 14 символов. В таком случае имя таблицы должно содержать не более 10 символов, поскольку четыре остальных позиции отводится под точку и трехсимвольное расширение.

В-третьих, на присвоение имен базам данных и таблицам оказывает влияние также чувствительность используемой файловой системы к регистру символов. Если буквы нижнего и верхнего регистров операционной системой воспринимаются по-разному (как, например, в ОС UNIX), имена my_tbl и my_tbl будут указывать на разные таблицы. Если же регистр не играет никакой роли (как, например, в Windows), my_tbl и mytbl окажутся разными названиями одной и той же таблицы. Об этом следует помнить при разработке базы данных, которую впоследствии планируется перенести на другую платформу.

Влияние структуры каталога данных на производительность системы

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

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

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

Так, например, каталог базы данных, включающей 10000 таблиц, содержит 30000 файлов. При открытии большого количества таблиц замедление выполнения операций открытия становится достаточно заметным. (Особенно это относится к файловым системам Linux ext2 и Solaris.) Если же эта проблема приобретает действительно угрожающие масштабы, возможно, имеет смысл пересмотреть структуру своих таблиц в соответствии со спецификой работы приложений и соответствующим образом их реорганизовать. Тщательно подумайте, действительно ли необходимо такое большое число таблиц. Иногда приложения генерируют их безо всякой на то необходимости. Много таблиц с аналогичными структурами могут генерироваться приложениями, которые создают отдельную таблицу для каждого пользователя. Чтобы объединить такие таблицы в одну, достаточно добавить столбец, который идентифицирует пользователя владельца строки. Если в результате таких действий число таблиц значительно уменьшится, производительность приложения возрастет.

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

  •  Ограничение дискового пространства. Объединение таблиц приводит к уменьшению их числа (уменьшая, в свою очередь, время открытия), но сопровождается добавлением дополнительных столбцов (занимая дополнительное дисковое пространство). В результате складывается достаточно распространенный компромисс между временем обработки и свободным пространством, и администратор должен решить, какой фактор более важен. Если на первом плане стоит скорость, возможно, придется пожертвовать дополнительным дисковым пространством. Если же объемы дисков сильно ограничены, придется использовать большее количество таблиц и немного дольше ждать при открытии.
  •  Вопросы безопасности. Эта причина также может в значительной степени ограничить возможности и желание объединить таблицы. Основная цель использования отдельных таблиц для каждого пользователя — открытие доступа к данным таблицы только пользователям, обладающим соответствующими привилегиями. На самом деле права пользователей определяются полномочиями на уровне таблицы. После объединения данные всех пользователей окажутся в одной таблице.

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

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

В последнее время намечается тенденция к ослаблению ограничений на размеры таблиц. Так, например, если в ОС IBM AIX 4.1 существовало ограничение на размер файла 2 Гбайта, то в ОС IBM AIX 4.2 оно увеличилось до приблизительно 64 Гбайт. Внутренний лимит на размер таблицы в MySQL в новых версиях также увеличивается. Так, если во всех предшествующих версии 3.23 системах он составляет 4 Гбайта, то в 3.23 был поднят до приблизительно 9 миллионов терабайт. Данные табл. 7.2 позволяют оценить, как внутренний лимит на размер таблицы в MySQL сопоставляется с ограничением файловой системы AIX. Подобные сопоставления можно применять и для других операционных систем.

Таблица 7.2. Сопоставление ограничений MySQL и операционной системы

Версия MySQL

Версия AIX

Максимальный размер таблицы

Ограничивающий фактор

MySQL 3 22 22

AIX 4 1

2 Гбайт

Максимальный размер файла AIX 2 Гбайта

MySQL 3 22 22

AIX 4 2

4 Гбайт

Максимальный размер таблицы MySQL — 4 Гбайта

MySQL 3 23

AIX 4.1

2 Гбайт

Максимальный размер файла AIX 2 Гбайта

MySQL 3 23 11

AIX 4 2

64 Гбайт

Максимальный размер таблицы MySQL — 64 Гбайта

Файлы состояния MySQL

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

Сервер записывает ID-номер своего процесса (Process ID — PID) в PID- файл при запуске и удаляет этот файл при завершении работы. Именно с помощью PID-файла сервер позволяет находить себя работающим процессам. Так, например, если во время завершения работы системы запустить сценарий mysql.server для завершения работы и сервера MySQL, данный сценарий обратится к PID-файлу. Это обращение позволит определить, какому процессу отправить команду на завершение работы.

Таблица 7.3.

Тип файла

Имя по умолчанию

Содержимое файла

ID-номер процесса

HOSTNAME, pid

ID-номер процесса сервера

Журнал ошибок

HOSTNAME, err

События запуска и завершения работы, а также записи об ошибках

Общий журнал

HOSTNAME, log

События подключения /отключения и информация о запросах

Журнал обновлений

HOSTNAME, nnn

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

Журнал ошибок создается сценарием safe_mysqld. Впоследствии именно в этот журнал перенаправляются все стандартные сообщения об ошибках сервера. Другими словами, журнал содержит все сообщения, записываемые сервером в stderr, и существует, только если сервер запускается с помощью вызова сценария safe_mysqld. (Это более предпочтительный метод запуска сервера, поскольку safe_mysqld перезапускает сервер при сбое в работе из-за ошибки.)

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

Общий журнал предоставляет информацию о функционировании сервера: кто и с какого компьютера подключился и какие запросы присылает. Журнал обновлений также содержит информацию о запросах, однако только о тех из них, которые связаны с изменением содержимого баз данных. Содержимое же журнала обновлений представляется в виде операторов SQL. Впоследствии их можно выполнить, представив в виде ввода для клиента mysql. Журналы обновлений оказываются особенно полезными в случае сбоя, когда необходимо отследить все изменения, внесенные с момента последнего резервирования базы данных. Их использование позволяет восстановить базы данных до состояния, в котором они находились перед самым сбоем.

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

990509  7:34:09     492 Connect   paul@localhost on test

              492 Query    show databases

                   492 Query    show tables

                   492 Field List   tbl_l

                   492 Field List   tbl 2

990509  7:34:22  492 Query   CREATE TABLE my_tbl (val INT)

990509  7:34:34  492 Query   INSERT INTO my_tbl VALUE(A)

990509  7:34:38  492 Query   DROP TABLE my tbl

990509  7:34:40  492 Quit

Отдельные столбцы общего журнала отражают дату и время события, ID-номер сервера, тип события и относящуюся к нему специальную информацию Тот же сеанс в журнале обновлений отобразится следующим образом:

use test;

CREATE TABLE my_tbl (val INT);

INSERT INTO my_tbl VALUE(A);

DROP TABLE my_tbl;

Для получения расширенной формы журнала обновлений используется опция --log-long-format. В расширенном журнале предоставляется информация также об отправителе и времени поступления запроса. Эти данные занимают, конечно, больше места на диске, однако позволяют узнать, кто и что пытался сделать. Сопоставлять информацию о событиях в общем журнале и журнале обновлений для этого не нужно. Для уже описанной выше сессии расширенный журнал обновлений будет выглядеть таким образом:

# Time: 9905097:43:42

# User@Host: paul [paul] @ localhost []

use test;

CREATE TABLE my_tbl (val INT);

# User@Host: paul [paul] @ localhost {]

INSERT INTO my_tbl VALUE(A);

# Time: 9905097:43:43

# User@Host: paul [paul] 0 localhost П

DROP TABLE my_tbl;

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

990509 7:47:24 4 Query UPDATE user SET

Password=PASSWORD("secret")

WHERE user="root"

Детально о проверке и настройке полномочий на доступ к каталогу данных рассказывается в лекции 11, "Безопасность". Для защиты каталога данных можно воспользоваться простой командой:

% chmod 700 DATADIR

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

Файлы состояния размещаются на верхнем уровне каталога данных вместе с каталогами баз данных. Поэтому иногда пользователи беспокоятся, что имена файлов состояния могут конфликтовать с именами баз данных (например, при выполнении сервером оператора SHOW databases). Этого бояться не стоит. Информация о событиях и состояниях хранится в файлах, а базы данных записаны в каталогах, что позволяет исполняемым программам легко отличить их, вызвав команду stat(). (Именно таким образом их различает сервер.) Просматривая каталог данных с помощью опции ls -1, пользователь может отличить файлы состояния от каталогов баз данных, определив первую букву данных в режиме: ' - ' или ' d':

% ls –l DATADIR

total 31

drwxrwx-- 1 mysqladm mysqlgrp  1024 May  8 13:22 blgdb

drwxrwx-- 2 mysqladm mysqlgrp  1024 Dec 15 22:34 mysql

-rw-rw--- 1 mysqladm mysqlgrp    64 May  9 20:11 pit-vlper. 001

-rw-rw-r- 1 mysqladm mysqlgrp 24168 May  9 20:11 pit-vlper. err

-rw-rw--- 1 mysqladm mysqlgrp  4376 May  9 20:11 pit-vlper. log

-rw-rw-r- 1 mysqladm mysqlgrp     5 May  9 20:11 pit-vlper. pld

drwxrwx-- 7 mysqladm mysqlgrp   512 Sep 10  1998 sql-bench

drwxrwx-- 2 mysqladm mysqlgrp   512 May  9 07:34 test

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

Более детально о поддержке файлов регистрации и способах работы с ними рассказывалось в лекции 2 "Общее администрирование MySQL".

Перемещение содержимого каталога данных

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

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

  •  Каталог данных можно разместить на диске большего размера, чем используется в настоящий момент.
  •  Если каталог данных располагается на часто используемом диске, перемещение его на другой диск позволит уровнять загрузку среди физических дисков. В этом случае можно разместить файлы баз данных и журналов на отдельном диске или распределить их по нескольким дискам сразу.
  •  Каждый из одновременно запущенных серверов можно разместить в своем каталоге данных. Такой подход является одним из способов обойти ограничения на файловые дескрипторы, особенно если эти ограничения нельзя устранить посредством настройки ядра системы.
  •  Некоторые операционные системы хранят PID-файлы сервера в отдельном каталоге, например /var/run. Возможно, для большей согласованности работы системы администратор пожелает разместить в этой папке и PID-файлы MySQL.

Методы перемещения

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

  •  Определение опции загрузки сервера с помощью командной строки или в группе [mysqld] конфигурационного файла.
  •  Перемещение элементов и создание в исходном каталоге символической связи (symbolic link), указывающей на новое местоположение.

Ни один из приведенных методов не является универсальным для переноса информации. В табл. 7.4 отмечается, какие компоненты каталога данных можно перемещать и какой метод следует для этого использовать. Если применяется первый метод, можно задать опции в глобальном конфигурационном файле /etc/my.cnf (C:\my.cnf на компьютерах, работающих под управлением ОС Windows). В последних версиях ОС Windows этот файл может располагаться в системной папке (С:\Windows).

Таблица 7.4. Обзор методов перемещения

Перемещаемый компонент

Применяемый метод перемещения

Целый каталог данных

Опция запуска или символическая связь

Каталоги отдельных баз данных

Символическая связь

Отдельные таблицы баз данных

Символическая связь

PID-файл

Опция запуска

Файл общего журнала

Опция запуска

Файл журнала обновлений

Опция запуска

Для перемещения можно также применить файл my.cnf, расположенный в каталоге данных по умолчанию, однако делать это не рекомендуется. Если ваша цель — переместить весь каталог данных, необходимо оставить этот каталог нетронутым на старой позиции, чтобы разместить в нем конфигурационный файл со ссылкой на "реальный" каталог данных. Это может привести к путанице. Для определения опций сервера лучше воспользоваться конфигурационным файлом /etc/my.cnf.

Определение эффекта перемещения

Прежде чем приступать к перемещению каких-либо компонентов, настоятельно рекомендуется убедиться, что эта операция приведет к желаемому эффекту. Для получения информации о пространстве диска некоторые пользователи предпочитают использовать команды du, df и Is -I, хотя этот выбор, в первую очередь, определяется правильным пониманием структуры используемой файловой системы.

В приведенном ниже примере существует едва заметная ловушка, в которую можно попасться при перемещении каталога данных. Предположим, что каталог данных /usr/local/var планируется переместить в каталог /var/mysql, поскольку согласно выводу команды df файловая система /var содержит больше свободного пространства.

% df /usr/var

Filesystem   1k-blocks  Used     Avail   Capacity   Mounted on

/dev/wd0s3e  396895     292126   73018   80%        /usr

/dev/wd0s3f  1189359    1111924  162287  15%        /var

Сколько же пространства освободится в файловой системе /usr в результате перемещения каталога данных? Чтобы вычислить этот объем, воспользуемся командой du -s и посмотрим, сколько этот каталог занимает:

% cd /usr/local/var

% du -s

133426

Как видно, этот каталог занимает чуть более 130 Мбайт, которые можно освободить в /usr. Однако можно ли этот прием реализовать на самом деле? Запустите команду df в каталоге данных:

% df /usr/local/var

Filesystem   1k-blocks  Used     Avail   Capacity   Mounted on

/dev/wd0s3f  1189359    1111924  162287  15%        /var

Что же получается? При запросе объема свободного пространства в файловой системе, содержащей каталог /usr/local/var, команда df отображает свободный объем в /var. Почему так? Ответ на этот вопрос дает команда Is -1:

% is -l /usr/local

lrwxrwxr 1 root wheel 10 Dec 11 23:46 var -> /var/mysql

Из результатов выполнения этой команды видно, что /usr/local/var является символической связью с /var/mysql. Другими словами, каталог данных уже перемещен в файловую систему /var и включает указывающую на нее символическую связь. Соответственно, никакой выгоды перемещение каталога данных из /usr в /var не принесет.

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

Перемещение каталога данных

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

Таблица 7.5. Синтаксис перемещения каталога данных

Метод

Синтаксис

Командная строка

--datadir=/path/to/dir 

Конфигурационный файл опций

[mysqld] datadir=/path/to/dir

Перемещение баз данных

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

Так, например, перемещение базы данных bigdb на другое место выполняется с помощью следующих команд.

% mysqladmin -u root -p shutdown

Enter password: ********

% cd DATADIR

% tar cf - bigdb | (cd /var/db; tar xf -)

% mv bigdb bigdb.orig

% ln -s /var/db/bigdb .

% safe_mysqld &

Меры предосторожности при перемещении

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

Для выполнения всех этих команд необходимо зарегистрироваться в качестве владельца каталога данных. Как видите, исходный каталог данных переименовывается для безопасности в bigdb.orig. После проверки правильности работы сервера с перемещенной базой данных его можно удалить:

% rm -rf bigdb.orig

Перемещение таблиц баз данных

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

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

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

Перемещение файлов состояния

Перемещение PlD-файла, общего журнала и журнала обновлений осуществляется с помощью символических связей. Как уже отмечалось ранее, журнал ошибок создается сценарием safemysqld и поэтому не может перемещаться куда-либо (если, конечно, для этого не прибегнуть к редактированию safemysqld).

Для записи файла состояния в новую позицию завершите работу сервера и перезапустите его, точно определив посредством соответствующей опции новое местоположение. Синтаксис командной строки и файла опций для каждого файла состояния представлен в табл. 7.6.

Таблица 7.6. Синтаксис перемещения файлов состояния

Метод

Синтаксис

Командная строка

-pid-file=pidfile

-log=logfile

-log-update=updatefile

Файл опций

[mysqld]

pid-file=pidfilee

log=logfile

log-update=updatefile

Удаление перемещенной базы данных

Удалить базу данных можно с помощью оператора drop database, хотя в старых версиях MySQL с удалением перемещенной базы данных могут возникнуть проблемы. Таблицы такой базы данных будут удалены правильно. Ошибка возникает при попытке сервера удалить каталог базы данных поскольку он является лишь символической связью, а не реальным каталогом. Администратор MySQL должен самостоятельно удалить каталог базы данных и указывающую на него связь. Эта проблема устранена в MySQL версии 3 23 и выше.

Если определить имя файла состояния, указав полный путь, то файл будет создан в определенной этим путем позиции. Во всех остальных случаях файл создается в каталоге данных. Так, например, при определении опции --pid-file=/var/run/mysqld.pid PID-файл mysqld.pid будет создан в каталоге /var/run. Если же определена опция --pid-file=mysqld.pid, этим файлом окажется файл DATADIR/mysqld.pid.

При определении имени журнала обновлений без расширения MySQL будет создавать последовательные имена каждый раз при открытии этого журнала. Эти имена будут дополняться расширением nnn, где nnn — следующий не используемый существующим файлом журнала обновлений номер (например, update.001, update.002 и тп). Чтобы избежать создания подобных имен сервером, достаточно определить имя с явным расширением.


 

А также другие работы, которые могут Вас заинтересовать

5073. Рыночные структуры в условиях несовершенной конкуренции: монополия, олигополия, монополистическая конкуренция 391 KB
  Конкуренция, которая в той или иной степени связана с заметным ограничением свободного предпринимательства, называется несовершенной. Для этого вида конкуренции характерно незначительное количество фирм в каждой сфере предпринимательской де...
5074. Проектирование приспособления для контроля межцентрового расстояния 90.5 KB
  Проектирование приспособления для контроля межцентрового расстояния Для контроля межцентровых расстояний проектируется специальное контрольное приспособление, оснащенное индикатором часового типа. В базовом техпроцессе измерение межцентрового...
5075. Анализ монополий 157 KB
  Любой рынок, независимо от его конкретного вида, базируется на трех основных элементах: цене, спросе и предложении, конкуренции. Известно, что наиболее эффективно рыночный механизм действует в условиях свободной, или совершенной конкуренции...
5076. Вологодская область 430.5 KB
  Внешняя торговля Истоки внешней торговли вологжан затеряны в глубине веков. Однако подлинными воротами в Поморье Вологда стала после появления в ней англичан из экспедиции Ричарда Ченслера. Следовавшие в Москву с Белого моря британцы вполне оценил...
5077. Расчет барабанной сушильной установки 155.5 KB
  Удаление влаги из твердых и пастообразных материалов удешевляет их транспортировку и придает им определенные свойства, а также уменьшению коррозии аппаратуры. Влагу можно удалять механическим способом: отжим, центрифугирование, отстаивание....
5078. Особенности построения спутниковой системы подвижной связи 254.5 KB
  Определить мощность ТВ радиопередатчика Р, обеспечивающего требуемое значение напряженности электромагнитного поля в пределах заданной площади, имеющей форму круга, находящегося в пределах зоны прямой видимости при условии, что ТВ вещание...
5079. Бортовые радио-электронные системы. Конспект лекций 307 KB
  Классификация радиоэлектронного оборудования. Определение места и скорость ВС, предупреждение столкновений с другими ВС и наземными припятствиями, обнаружение опасных гидро-метеообразований, обеспечение внутренней связи на ВС и обеспечение решения з...
5080. Музей-заповедник Книжи 413 KB
  Архитектура - (лат. architectura, от греч. architekthon строитель) (зодчество), искусство проектировать и строить здания и др. сооружения (также их комплексы), создающие материально организованную среду, необходимую людям для их жизни и дея...
5081. Образ былинного богатыря отечественного искусства 19 в 257.5 KB
  История мировой живописи знает немного картин, созданию которых художник посвящал бы значительную часть своей творческой жизни. В числе таких картин Богатыри Виктора Михайловича Васнецова. Много лет работал замечательный художник над этим знаменит...