Начиная работать с MySQL

Перевод статьи: © Олег Иманский, poxuxol@rambler.ru
(ссылка на оригинал статьи: http://dev.mysql.com/tech-resources/articles/mysql_intro.html)

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

Предположения

Предполагается, что читатель успешно установил сервер баз данных MySQL. Если MySQL еще не установлен, пожалуйста потратьте некоторое время на просмотр информации, приведенной в разделе документации MySQL, посвященном установке. Предполагается также, что база данных mysql была создана (с использованием mysql_install_db), и сервер баз данных MySQL был запущен при помощи скрипта safe_mysqld. Если это не так, потратьте несколько минут на чтение раздела "Post-Installation Setup and Testing" документации MySQL.

Также предполагается, что читатель, по крайней мере, понимает синтаксис SQL (Structured Query Language). Для новичков в мире SQL, ниже приведены ссылки на некоторые весьма полезные руководства:

• Philip Greenspun. "SQL for Web Nerds"
• Mike Chapple's Introduction to SQL
• James Hoffman's Introduction to SQL

Оглавление

• Итак, Вы установили MySQL. Что теперь?
• Файл конфигурации MySQL: my.cnf
• Таблицы привилегий MySQL
• Соединение с сервером MySQL в первый раз
  • Выход и повторное соединение с монитором MySQL
    • Осторожно с паролем!
• Выбор базы данных
• mysqladmin
• Обеспечение безопасности базы данных
  • Команда GRANT
  • Команда REVOKE
• Резервные копии базы данных
  • mysqldump
  • mysqlhotcopy
• Заключение

Итак, Вы установили 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. Каждый из них включает рекомендованные настройки конфигурации в завтсимости от доступности тех или иных системных ресурсов.

Дальнейшее чтение

• Option Files

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

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

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

Дальнейшее чтение

• How the Privilege System Works
• How to Make MySQL Secure Against Crackers
• Adding New User Privileges to MySQL

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

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

%>mysql [options] [database]

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

%>mysql --help

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

%>mysql -u root

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

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>

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

mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secret_password');

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

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

Существует альтернативный способ смены пароля:

%>mysqladmin -u root  password 'secret_password'

Эта команда даст тот же самый результат, что и предыдущая.

Выход и повторное соединение с монитором MySQL

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

mysql>\q

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

%>mysql -u root -p

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

Enter password:

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

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

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

%>mysql -u root -psecret_password

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

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

Выбор базы данных

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

• Указать имя базы данных в строке команды mysql. Например, команда для соединнения с сервером MySQL и выбора базы данных mysql:

%>mysql -u root -p mysql

Некоторых это может ввести в заблуждение, поскольку кажется, что mysql это пароль. Это не так. Потратьте минуту на обзор синтаксиса по справке, выдаваемой mysql --help, и Вам станет очевидно, что -u root -p фактически соответствуют компоненту [options] синтаксиса, а mysql соответствует компоненту [database].

• После установления связи с сервером выберите базу данных, используя команду use:

  mysql>use mydatabase
  

После выполнения этой команды все запросы без явного указания имени базы данных будут направлены к гипотетической базе данных mydatabase.

mysqladmin

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

%>mysqladmin [options] command(s)

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

%>mysqladmin --help

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

%>mysqladmin -u root -p create widgets
Enter Password:

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

Дальнейшее чтение

• Administering a MySQL Server

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

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

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

Команда Grant

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

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]

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

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

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

mysql>SELECT * FROM user;

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

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

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

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

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

Таблица 1-1: Привилегии, указываемые в командах GRANT и REVOKE

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

mysql>SELECT * FROM db;

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

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

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

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

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

Команда Revoke

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

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

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

mysql>REVOKE DELETE ON widgets.*
    ->FROM widgetAdmin@localhost;

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

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

mysql>REVOKE ALL PRIVILEGES ON widgets.*
    ->FROM widgetAdmin@localhost;

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

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

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

Дальнейшее чтение

• Grant and Revoke Syntax

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

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

mysqldump

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

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

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

%>mysqldump [options] db_name

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

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

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

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

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

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

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

%>mysqldump --help

Примеры:

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

%>mysqldump -u root -p --opt widgets

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

%>mysqldump -u root -p --no-create-info widgets

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

%>mysqldump -u root -p --no-data widgets

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

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

mysqlhotcopy

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

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

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

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

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

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

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

%>mysqlhotcopy [options] db_name./regex/

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

%>mysqlhotcopy --help

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

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

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

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

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

Дальнейшее Чтение

• Dumping MySQL Database and Table Structures and Data
• Replication in MySQL
• Copying MySQL Databases and Tables

Заключение

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

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