MySQL - (официально произносится /mɑɪ ɛs kjuː ɛl/, сленг. «майсиквел», сленг. «мускул»)[1] — свободная система управления базами данных (СУБД). MySQL является собственностью компании Sun Microsystems, осуществляющей разработку и поддержку приложения. Распространяется под GNU General Public License и под собственной коммерческой лицензией, на выбор. Помимо этого компания MySQL AB разрабатывает функциональность по заказу лицензионных пользователей, именно благодаря такому заказу почти в самых ранних версиях появился механизм репликации.

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

Для чего нужен MySQL?

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

Итак, Вы установили MySQL. Что теперь?

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

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

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

Файл конфигурации MySQL: my.cnf

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

Интересно, что область действия этого файла зависит от его местоположения. Параметры настройки будут глобальными для всех серверов MySQL, если они сохранены в /etc/my.cnf. Этот файл будет глобальным для определенного сервера MySQL, если расположен в том каталоге, где находятся его базы данных(/usr/local/mysql/data для бинарной инсталляции, или /usr/local/var для инсталляции с исходными текстами). Наконец, его область действия может быть ограничена пользователем, если файл конфигурации расположен в домашнем каталоге пользователя MySQL (~/.my.cnf). Имейте в виду, что, даже если файл my.cnf находится в /etc/my.cnf (глобальном для всех серверов MySQL на этой машине), сервер продолжит поиск своего файла, а затем пользовательского файла конфигурации. Окончательные настроечные параметры являются результатом последовательного применения настроек из /etc/my.cnf, mysql-data-dir/my.cnf и ~/.my.cnf.

Чтобы помочь администратору правильно сформировать этот файл, разработчики MySQL включили в дистрибутив четыре простых my.cnf файла. Их имена - my-huge.cnf.sh, my-large.cnf.sh, my-medium.cnf.sh и my-small.cnf.sh. Каждый из них включает рекомендованные настройки конфигурации в завтсимости от доступности тех или иных системных ресурсов.

Таблицы привилегий MySQL!

Перед тем, как начать разбор примеров, кратко остановимся на одном из наиболее важных (и наиболее часто неправильно понимаемых!) аспектов сервера MySQL - механизме обеспечения целостности и безопасности данных: таблицах привилегий MySQL. Таблицы привилегий нужны для аутентификации пользователя при обращении к серверу MySQL и последующего связывания его прав доступа с набором привилегий. Этот набор привилегий определяет то, что может предпринимать пользователь, присоединенный к серверу MySQL, управляя действиями пользователя на уровне всего сервера, базы данных, уровне отдельных таблиц и даже колонок. Например, администратор может предоставить пользователю привилегии, достаточные для соединения с одной определенной базой данных MySQL и ограничить доступ ко всем другим. Кроме того, этому пользователю можно предоставить лишь некоторые права при работе с базой данных, например, права выбора, вставки и модификации данных. Любая попытка удаления данных пользователем, обладающим только этими тремя правами, будет отклонена, поскольку у пользователя нет прав на удаление.

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

Соединение с сервером MySQL в первый раз!!!

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

[spoiler]%>mysql [options] [database][/spoiler]

Здесь [options] это одна или несколько опций программы mysql, а [database] - имя используемой базы данных. Поскольку читатель, судя по всему, впервые использует монитор MySQL, потратим несколько минут на обзор всех возможных опций, выполнив следующую команду:

[spoiler]%>mysql --help[/spoiler]

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

[spoiler]%>mysql -u root[/spoiler]

На экране должно появиться:

[spoiler]Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 3.23.28-gamma-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql>[/spoiler]

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

[spoiler]mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secret_password');[/spoiler]

Имя пользователя 'root' и имя хоста 'localhost' вместе определяют уникального пользователя в MySQL. Для читателей, незнакомых с сетевой терминологией, поясним:'localhost' - имя локального сервера, т. е. сервера, на котором установлен MySQL. Поэтому, ссылаясь на 'root'@'localhost', эта команда указывает серверу MySQL установить пароль для пользователя 'root', который будет устанавливать соединение с локального сервера (т.е. с 'localhost'). Более точно: эта команда изменит пароль путем внесения изменений в таблицы привилегий MySQL. Эти таблицы, хранящиеся в базе данных mysql, содержат информацию о подключениях и возможностях всех пользователей сервера баз данных MySQL. Более точно: эта команда изменит пользовательскую таблицу, модифицируя поле password строки, в которой значением поля user является 'root'. В поле password будет записано шифрованное значение строки пароля, вычисленное с помощью функции Password().

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

[spoiler]%>mysqladmin -u root password 'secret_password'[/spoiler]

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

[spoiler]mysql>\q[/spoiler]

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

[spoiler]%>mysql -u root -p[/spoiler]

Вы увидите приглашение для ввода пароля пользователя root:

Enter password:

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

Осторожно с паролем!

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

[spoiler]%>mysql -u root -psecret_password[/spoiler]

Не делайте этого! Мало того, что это очень опасный способ ввода пароля, но это не приведет к ожидаемым результатам! Это небезопасно не только потому, что позволяет любому видеть пароль в открытом виде, но также и потому, что любой пользователь может с помощью команду 'ps' в Unix видеть вводимые Вами команды и видеть Ваш пароль в обычном текстовом формате.

Хорошо бы хранить пароль в Вашем файле конфигурации ~/.my.cnf. Если Вы не знаете, что это за файл, пожалуйста прочитайте раздел "Файл конфигурации MySQL: my.cnf".

Обеспечение безопасности базы данных!

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

Существует два способа модификации таблиц привилегий. Первый сводится к использованию обычных SQL-предложений, таких как INSERT, UPDATE и DELETE. Однако, этот способ не получил широкого распространения наряду со вторым, который предполагает использование специальных команд GRANT и REVOKE. Т.о., только второй способ будет обсуждаться дальше.
Команда Grant

Назначение команды GRANT - и создавать новых пользователей, и присваивать им привилегии. Её синтаксис:

[spoiler]mysql>GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION][/spoiler]

Понимание того, как работает GRANT, лучше всего приходит через разбор примеров. В первом примере GRANT используется для создания нового пользователя базы данных. Этот пользователь будет нужен для доступа к базе данных widgets:

[spoiler]mysql>GRANT usage ON *.* TO widgetAdmin@localhost
->IDENTIFIED BY 'ilovewidgets';[/spoiler]

Будет создан новый пользователь с именем widgetAdmin, котрорый сможет соединиться с сервером баз данных MySQL через хост localhost с паролем ilovewidgets. Имейте в виду, что этому пользователю предоставлены права лишь на соединение. Больше ничего с сервером MySQL он сделать не сможет! Переключитесь на работу с базой данных mysql и выполните запрос:

[spoiler]mysql>SELECT * FROM user;[/spoiler]

Обратите внимание на то, что в строке соответствующей пользователю widgetAdmin, содержится N значений для всех возможных привилегий. Вообще, таблица user содержит глобальные установки привилегий пользователей. Поясним: если значение какой-либо привилегии пользователя установить в Y, то он будет пользоваться этой привилегией в любой базе данных MySQL. Поэтому почти всегда лучше все привилегии в этой таблице устанавливать в N.

Как же тогда устанавливаются привилегии для каждой отдельной базы данных? Это делается абсолютно так же, как была установлена привилегия usage в предыдущем примере. Например, предположим, что администратор захотел предоставить пользователю widgetAdmin привилегии SELECT, INSERT, UPDATE и DELETE для базы данных widget. Это можно сделать с помощью следующей команды GRANT:

[spoiler]mysql>GRANT SELECT, INSERT, UPDATE, DELETE
->ON widgets.* TO widgetAdmin@localhost;[/spoiler]

После её выполнения пользователь widgetAdmin может немедленно начать пользоваться этими привилегиями.

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

Таблица 1-1: Привилегии, указываемые в командах GRANT и REVOKE
ALL PRIVILEGES FILE RELOAD
ALTER INDEX SELECT
CREATE INSERT SHUTDOWN
DELETE PROCESS UPDATE
DROP REFERENCES USAGE

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

[spoiler]mysql>SELECT * FROM db;[/spoiler]

Обратите внимание, что в таблицу db была добавлена строка для пользователя widgetAdmin, в которой значения полей SELECT, INSERT, UPDATE и DELETE установлены в Y.

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

[spoiler]mysql>GRANT SELECT, INSERT, UPDATE, DELETE
->ON widgets.* TO widgetAdmin@localhost
->IDENTIFIED BY 'ilovewidgets';[/spoiler]

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

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

Команда REVOKE используется для отмены привилегий пользователя, выданных ему ранее. Ее синтаксис:

[spoiler]REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...][/spoiler]

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

[spoiler]mysql>REVOKE DELETE ON widgets.*
->FROM widgetAdmin@localhost;[/spoiler]

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

Необходимо только помнить, что несмотря на то, что REVOKE может отменить все привилегии пользователя (включая привилегию соединения), она не удаляет этого пользователя из таблиц привилегий. В качестве иллюстрации рассмотрим команду:

[spoiler]mysql>REVOKE ALL PRIVILEGES ON widgets.*
->FROM widgetAdmin@localhost;[/spoiler]

Несмотря на то, что все привилегии пользователя widgetAdmin отменяются, эта команда не удаляет соответствующие ему строки из таблиц привилегий! Если мы намереваемся полностью удалить пользователя из базы данных, эти строки должны быть удалены командой DELETE:

[spoiler]mysql>DELETE FROM user WHERE user = 'widgetAdmin';
Query OK, 1 row affected (0.00 sec)
mysql>flush privileges;[/spoiler]

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

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

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

Утилита mysqldump предоставляет довольно удобный способ дампирования содержимого и структур таблиц. Заметим, что хотя mysqldump не самый эффективный способ создания резервных копий (как mysqlhotcopy, описанный далее), это удобный метод копирования структур и содержимого таблиц, которые затем необходимо использовать для загрузки в базу данных другого SQL-сервера, причем не обязательно MySQL.

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

Использование mysqldump для создания резервной копии одной базы данных:

[spoiler]%>mysqldump [options] db_name[/spoiler]

Использование mysqldump для резервирования несколько таблиц в пределах базы данных:

[spoiler]%>mysqldump [options] db_name table1 table2 . . . tableN[/spoiler]

Использование mysqldump для резервирования нескольких баз данных:

[spoiler]%>mysqldump [options] --databases [options] db_name1 db_name2 . . . db_nameN[/spoiler]

Использование mysqldump для резервирования всех баз данных:

[spoiler]%>mysqldump [options] --all-databases [options][/spoiler]

Список опций утилиты mysqldump можно сформировать по команде:

[spoiler]%>mysqldump --help[/spoiler]

Примеры:

Резервирование структуры базы данных widgets и хранящихся в ней данных может быть произведено так:

[spoiler]%>mysqldump -u root -p --opt widgets[/spoiler]

Возможна ситуация, когда требуется резервировать только данные. Это можно сделать, включив опцию --no-create-info, которая запрещает сохранение информации о структуре таблиц:

[spoiler]%>mysqldump -u root -p --no-create-info widgets[/spoiler]

Еще один вариант - сохранить только табличную структуру. Это делается включением опции --no-data, запрещающей резервирование хранящихся в таблицах данных:

[spoiler]%>mysqldump -u root -p --no-data widgets[/spoiler]

Если Вы планируете использовать mysqldump для переноса данных в другой сервер MySQL, рекомендуется применять опцию '--opt'. Так Вы получите оптимизированный дамп, который в результате, при загрузке его в другой сервер MySQL, будет считываться быстрее.

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

Утилита mysqlhotcopy - это скрипт на perl, использующий основные системные и SQL-команды для создания резервной копии базы данных. Более конкретно, эта утилита заблокирует таблицы, сохранит в них несохраненные данные, сделает их копию и разблокирует. Хотя это самый быстрый способ создания резервных копий баз данных MySQL, он работает только для баз данных, находящихся на том компьютере, на котором выполняется скрипт.

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

Использование mysqlhotcopy для резервирования одной базы данных:

[spoiler]%>mysqlhotcopy [options] db_name /path/to/new_directory[/spoiler]

Использование mysqlhotcopy для резервирования несколько баз данных:

[spoiler]%>mysqlhotcopy [options] db_name_1 ... db_name_n /path/to/new_directory[/spoiler]

Использование mysqlhotcopy для резервирования таблиц в пределах данной базы данных, чиь имена соответствуют регулярному выражению:

[spoiler]%>mysqlhotcopy [options] db_name./regex[/spoiler]/

Список опций можно получить, выполнив команду:

[spoiler]%>mysqlhotcopy --help[/spoiler]

Примеры:
Проэкспериментируйте с mysqlhotcopy, создав резервную копию базы данных widgets в директории "/usr/mysql/backups/". Выполните команду:

[spoiler]%>mysqlhotcopy -u root -p widgets /usr/mysql/backups[/spoiler]

Пусть, например, база данных widgets содержит таблицы: "products2000", "products2001", "clientele2000" и "clientele2001", где последние четыре цифры каждого имени обозначают год. Администратор хочет создать резервную копию только тех таблиц, которые содержат данные за 2000 год:

[spoiler]%>mysqlhotcopy -u root -p widgets./^.+('2000')$/ /usr/mysql/backups[/spoiler]

В этом примере регулярное выражение /^.+('2000')$/ сообщает утилите mysqlhotcopy о необходимости создания резервных копий только тех таблиц, имена которых оканчиваются строкой "2000".