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

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

2-е издание, исправленное

Национальный Открытый Университет “ИНТУИТ”
2016

2

Администрирование MySQL/ - М.: Национальный Открытый Университет “ИНТУИТ”, 2016

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

(c) ООО “ИНТУИТ.РУ”, 2007-2016
(c) 2007-2016

3

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

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

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

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

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

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

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

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

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

Сервер MySQL. Сервер mysqld выполняет все операции с базами данных и

4

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

mysql. Интерактивная программа, позволяющая отправлять SQL-запросы на
сервер и просматривать результаты их выполнения.
mysqladmin. Административная программа, позволяющая выполнять такие
операции, как завершение работы сервера, создание и удаление баз данных.
Эту же программу можно применять для проверки состояния сервера, если
что-то в его работе не так.
isamchk или myisamchk. Утилиты, предназначенные для анализа и
оптимизации таблиц, а также восстановления их в случае повреждения.
mysqldump. Средство резервирования баз данных или их копирования на
другой сервер.

SQL - язык сервера. Некоторые задачи администрирования можно выполнить
только с помощью утилиты командной строки mysqladmin. Иногда гораздо
эффективней справиться с задачей может администратор, который может
“общаться” с сервером на его языке. Предположим, что необходимо проверить,
почему привилегии пользователя работают вовсе не так, как ожидается. Напрямую
“поговорить” с сервером на человеческом языке, к сожалению, нельзя. Зато можно
воспользоваться программой-клиентом mysql и послать SQL-запрос для анализа
таблиц разрешений.

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

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

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

Общее администрирование включает в себя в основном работу с демоном mysqld,

5

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

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

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

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

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

mysqldump. С ее помощью можно выполнять резервирование без предварительного
завершения работы сервера.

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

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

6

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

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

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

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

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

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

7

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

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

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

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

8

Общее администрирование 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 обладает всеми возможными правами
(включая административные), может выполнять любые операции. (Кстати,

9

совпадение имен суперпользователей 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 без пароля,
укажите ему перезагрузить таблицы, введя следующую команду:

10