MySQL — это популярный сервер баз данных, используемый в различных областях. Аббревиатура SQL означает Structured Query Language (язык структурированных запросов), именно его использует MySQL для общения с другими программами. Более того, в MySQL есть собственные расширенные функции SQL, дающие дополнительные возможности пользователям. В этом руководстве рассматривается процесс начальной установки MySQL, настройка баз данных и таблиц, заведение новых пользователей. Итак, начнем с установки.

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

(просмотр имеющихся флагов USE)
# emerge --pretend --verbose mysql
(установка MySQL)
# emerge mysql

По завершении установки вы увидите такое сообщение:

You might want to run:
"emerge --config =dev-db/mysql-[version]"
if this is a new install.

Рекомендуется запустить команду
"emerge --config =dev-db/mysql-[версия]"
после первоначальной установки MySQL.

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

# ebuild /var/db/pkg/dev-db/mysql-[version]/mysql-[version].ebuild config
 * MySQL DATADIR is /var/lib/mysql
 * Press ENTER to create the mysql database and set proper
 * permissions on it, or Control-C to abort now...

   Preparing db table
   Preparing host table
   Preparing user table
   Preparing func table
   Preparing tables_priv table
   Preparing columns_priv table
   Installing all prepared tables

   To start mysqld at boot time you have to copy support-files/mysql.server
   to the right place for your system

   PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
   To do so, issue the following commands to start the server
   and change the applicable passwords:
(обратите внимание на следующие 3 строки)
   /etc/init.d/mysql start
   /usr/bin/mysqladmin -u root -h pegasos password 'new-password'
   /usr/bin/mysqladmin -u root password 'new-password'
   Depending on your configuration, a -p option may be needed
   in the last command. See the manual for more details.

(различные сообщения, не относящиеся к установке, опущены для краткости)

   * For security reasons you should set your MySQL root
   * password as soon as possible.

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

# /etc/init.d/mysql start
 * Re-caching dependency info (mtimes differ)...
 * Starting mysqld (/etc/mysql/my.cnf) ...        [ ok ]
(вместо 'новый-пароль' укажите ваш пароль)
# /usr/bin/mysqladmin -u root -h localhost password 'новый-пароль'

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

$ mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.25

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

mysql>

Параметр -u указывает пользователя, который будет входить в систему. Параметр -h указывает узел сети. Обычно это localhost, если вы настраиваете не удаленный сервер. Наконец, -p сообщает программе-клиенту mysql, что для доступа к базе данных будет вводится пароль. Обратите внимание на приглашение mysql>. Именно здесь вы будете вводить все свои команды. Теперь, находясь в среде mysql в качестве корневого пользователя, мы можем начать настройку базы данных.

Мы вошли, и на экране — приглашение mysql. Сначала взглянем на список уже имеющихся баз данных. Для этого введем команду SHOW DATABASES.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.09 sec)

Несмотря на то, что база данных test уже создана, создадим свою собственную. Базы данных создаются командой CREATE DATABASE. Мы назовем свою gentoo.

mysql> CREATE DATABASE gentoo;
Query OK, 1 row affected (0.08 sec)

Ответ дает понять, что команда выполнена без каких-либо ошибок. В данном случае, изменилась одна строка. Это относится к главной базе данных mysql, в которой содержится список всех баз данных. Но вам не нужно слишком беспокоиться о второстепенных подробностях. Последнее число означает время выполнения запроса. Убедиться, что база данных создана, мы можем, снова запустив команду SHOW DATABASES.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| gentoo   |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

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

mysql> USE gentoo;
Database changed

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

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

mysql> SHOW TABLES;
Empty set (0.00 sec)

Значит, нужно создать несколько таблиц. Чтобы это сделать, используем команду CREATE TABLE. Однако, эта команда довольно сильно отличается от простой команды CREATE DATABASE. Ей передается список аргументов следующего вида:

CREATE TABLE [имя_таблицы] ([имя_поля] [тип_данных_поля]([размер]));

имя_таблицы — имя создаваемой таблицы. В данном случае, давайте создадим таблицу с названием developers (разработчики). В таблице будут находится имена разработчиков, адреса электронной почты и род занятий. имя_поля будет содержать имена полей. Нам нужны три имени: name (имя), email (почта) и job (род занятий). В параметре тип_данных_поля укажем тип заносимой информации. Перечень возможных форматов находится на странице описания типов столбцов MySQL (англ.). Здесь мы для всех полей укажем тип VARCHAR. VARCHAR — один из простейших типов данных, предназначенный для работы со строками. размер указывает, сколько данных можно сохранить в одном поле. Укажем 128. Это значит, что поле сможет содержать 128 байт данных типа VARCHAR. Cейчас можно упрощенно считать, что это 128 знаков текста, хотя на вышеупомянутом сайте представлено более точное описание. Теперь, зная, какую мы собираемся создать таблицу, сделаем это.

mysql> CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));
Query OK, 0 rows affected (0.11 sec)

Похоже, таблица создалась нормально. Давайте проверим это командой SHOW TABLES:

mysql> SHOW TABLES;
+------------------+
| Tables_in_gentoo |
+------------------+
| developers       |
+------------------+
1 row in set (0.00 sec)

Да, это она! Но не показано никакой информации о введенных типах полей. Для этого используем команду DESCRIBE (или ее краткий вариант, DESC), параметром которой указывается имя таблицы. Посмотрим, что она выдаст для таблицы developers.

mysql> DESCRIBE developers;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(128) | YES  |     | NULL    |       |
| email | varchar(128) | YES  |     | NULL    |       |
| job   | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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

Таблица заполняется (данные добавляются) командой INSERT. Как и у команды CREATE TABLE, у нее есть определенный формат:

INSERT INTO имя_таблицы (столбец1, столбец2, ...) VALUES('знач1', 'знач2', ...);

Эту команду используют для вставки записей в таблицу. Сначала указывается имя таблицы, в которую нужно добавить информацию. Затем может идти список столбцов, в которые добавляются данные, а в VALUES указываются значения, которые добавляются в таблицу. Можно опустить список полей, если значения добавляются в каждое в том же порядке, в каком определены поля в таблице. Сейчас мы добавим данные в теблицу developers. Вставьте записи как в примере:

mysql> INSERT INTO developers VALUES('Joe Smith', 'joesmith@gentoo.org', 'toolchain');
Query OK, 1 row affected (0.06 sec)
(Если вы не знаете порядок полей в таблице или хотите добавить неполную запись)
mysql> NSERT INTO developers (job, name) VALUES('outsourced', 'Jane Doe');
Query OK, 1 row affected (0.01 sec)

Согласно полученному ответу, запись, похоже, добавилась правильно. Но что если требуется ввести более одной записи? Именно здесь пригодиться команда LOAD DATA. Она загружает записи из файла, разделенного знаками табуляции. Попробуем создать файл с записями в своем домашнем каталоге. Назовем его records.txt. Вот пример:

John Doe	johndoe@gentoo.org	portage
Chris White	chriswhite@gentoo.org	documentation
Sam Smith	samsmith@gentoo.org	amd64

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

LOAD DATA LOCAL INFILE '/путь/к/файлу' INTO TABLE имя_таблицы;

Здесь тоже все просто. Укажите путь к файлу и имя таблицы. В нашем случае — это файл ~/records.txt и таблица developers.

mysql> LOAD DATA LOCAL INFILE '~/records.txt' INTO TABLE developers;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Сработало. Но эта команда просто добавляет записи и не дает проконтролировать работу MySQL. Множество веб-приложений используют сценарии sql для быстрой и легкой настройки базы данных MySQL. Если вы хотите использовать сценарий sql, вам понадобится запустить mysql в пакетном режиме (в качестве ввода используется файл со сценарием). Вот пример запуска mysql в пакетном режиме:

$ mysql -u root -h localhost -p < sqlfile

Как и в случае с LOAD DATA, убедитесь, что знаете, что именно выполняет файл sqlfile. Не делая этого, вы можете подвергнуть вашу базу данных серьезному риску утечки! Другой путь заключается в использовании команды source. Эта команда запускает команды mysql из файла, когда mysql находится в интерактивном режиме. Вот как взять команды sql из файла:

mysql> source sqlfile;

Если вы столкнетесь с веб-приложением, которому нужно, чтобы вы запустили файл с командами sql, можно использовать одну из двух команд, показанных выше. Итак, наша таблица сформирована, но как же работать с полями? Это делается поиском в таблице при помощи запросов.

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

(Выборка всех записей в таблице)
SELECT * FROM имя_таблицы;
(Выборка конкретных записей в таблице)
SELECT * FROM имя_таблицы WHERE поле=значение;
(Выборка конкретных полей)
SELECT поле1,поле2,поле3 FROM имя_таблицы [WHERE поле=значение];

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

mysql> SELECT * FROM developers;
+-------------+-----------------------+----------------+
| name        | email                 | job            |
+-------------+-----------------------+----------------+
| Joe Smith   | joesmith@gentoo.org   | toolchain      |
| John Doe    | johndoe@gentoo.org    | portage        |
| Chris White | chriswhite@gentoo.org | documentation  |
| Sam Smith   | samsmith@gentoo.org   | amd64          |
| Jane Doe    | NULL                  | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)

Видно данные, не только вставленные путем INSERT, но и вставленные командой LOAD DATA. Теперь, к примеру, нужно посмотреть запись для Chris White. Это можно сделать, используя вторую форму выборки.

mysql> SELECT * FROM developers WHERE name = 'Chris White';
+-------------+-----------------------+---------------+
| name        | email                 | job           |
+-------------+-----------------------+---------------+
| Chris White | chriswhite@gentoo.org | documentation |
+-------------+-----------------------+---------------+
1 row in set (0.08 sec)

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

mysql> SELECT email,job FROM developers WHERE name = 'Chris White';
+-----------------------+---------------+
| email                 | job           |
+-----------------------+---------------+
| chriswhite@gentoo.org | documentation |
+-----------------------+---------------+
1 row in set (0.04 sec)

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

Привилегиями определяются возможности доступа пользователей к базам данных, таблицам... почти ко всему. Сейчас только суперпользователь root из MySQL может обращаться к базе данных gentoo, согласно данным разрешениям. Давайте создадим двух самых обычных пользователей, например, guest (гостя) и admin (администратора), которые будут обращаться к базе данных gentoo и работать с информацией из нее. Пусть пользователь guest будет ограничен в правах, и все, что он сможет — это получать информацию из базы данных. admin получит те же права доступа, что и root, но только к базе данных gentoo (не к основным базам mysql). Но прежде, чем начать, давайте взглянем на несколько упрощенный формат команды GRANT.

GRANT [привилегии] ON база_данных.* TO '[пользователь]'@'[узел]' IDENTIFIED BY '[пароль]';

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

Для нашего пользователя admin подойдет ALL. А для пользователя guest привилегии SELECT будет достаточно для доступа на чтение. В качестве базы данных укажем gentoo, на нее будут установлены разрешения. .* означает «все таблицы». Если потребуется, можно установить права доступа к отдельным таблицам. Дальше идет имя пользователя и имя узла, с которого будет работать пользователь. В большинстве случаев это будет узел localhost. Наконец, задается пароль пользователя. Исходя из этого, давайте создадим пользователей.

(admin)
mysql> GRANT ALL ON gentoo.* TO 'admin'@'localhost' IDENTIFIED BY 'пароль';
(guest)
mysql> GRANT SELECT ON gentoo.* TO 'guest'@'localhost' IDENTIFIED BY 'пароль';

Итак, пользователи созданы; теперь протестируем их. Сначала выйдем из mysql, написав quit в строке приглашения:

mysql> quit

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

Попробуем зайти как пользователь guest. В данный момент у пользователя guest есть только право выборки (SELECT). В основном, это сводится к возможности поиска и ничему другому. Зайдем как пользователь guest.

$ mysql -u guest -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.25

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

mysql>

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

mysql> USE gentoo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Теперь попытаемся сделать что-нибудь недозволенное. Попробуем создать таблицу.

mysql> CREATE TABLE test (test VARCHAR(20), foobar VARCHAR(2));
ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

Как видно, это не удалось, из-за того, что у пользователя недостаточно прав. Но ему дано право на использование SELECT. Давайте проверим:

mysql> SELECT * FROM developers;
+-------------+-----------------------+----------------+
| name        | email                 | job            |
+-------------+-----------------------+----------------+
| Joe Smith   | joesmith@gentoo.org   | toolchain      |
| John Doe    | johndoe@gentoo.org    | portage        |
| Chris White | chriswhite@gentoo.org | documentation  |
| Sam Smith   | samsmith@gentoo.org   | amd64          |
| Jane Doe    | NULL                  | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)

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

mysql> quit
Bye
$ mysql -u admin -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.25

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

mysql>

Для начала, попробуем создать новую базу данных как пользователь admin. У пользователя admin схожие права с учетной записью root в MySQL, и он может вносить любые изменения, выбрав базу данных gentoo. Мы проверим права его доступа к главной базе данных MySQL. Вспомним, что ранее мы дали ему права только для доступа к конкретной базе данных.

mysql> CREATE DATABASE gentoo2;
ERROR 1044: Access denied for user: 'admin@localhost' to database 'gentoo2'

Разумеется, пользователь admin не может создавать базы данных в основной базе MySQL, несмотря на все права на базу данных gentoo. Но admin все еще может изменять базу данных gentoo, как показывает вставка данных в следующем примере.

mysql> USE gentoo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO developers VALUES('Bob Simmons', 'bobsimmons@gentoo.org', 'python');
Query OK, 1 row affected (0.08 sec)

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

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

REVOKE [привилегии] ON база_данных.* FROM '[пользователь]'@'[узел]';

Параметры объясняются в разделе команды GRANT. А сейчас мы запретим пользователю любой вид доступа. Скажем, мы выяснили, что учетная запись guest вызывает проблемы с безопасностью. Мы решаем отозвать все права. Заходим как root делаем необходимое.

mysql> REVOKE ALL ON gentoo.* FROM 'guest'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Давайте выйдем и попробуем зайти как пользователь guest.

$ mysql -u guest -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.25

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

mysql>

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

mysql> USE gentoo;
ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

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

Таблица пользователей MySQL — это список пользователей и информации о них. Убедитесь, что вы зашли как root. Используйте основную базу данных MySQL.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

Посмотрим, какие в базе mysql существуют таблицы:

mysql> SHOW TABLES;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.00 sec)

Таблица user — та, что нужна. В ней 30 различных полей, и ее сложно читать. Для облегчения чтения используем третью форму команды SELECT. Искомые поля — Host (узел) и User (пользователь).

mysql> SELECT Host,User FROM user WHERE User = 'guest';
+-----------+-------+
| Host      | User  |
+-----------+-------+
| localhost | guest |
+-----------+-------+
1 row in set (0.00 sec)

Теперь, получив информацию, мы можем избавиться от пользователя guest. Это делается командой DELETE; вот ее синтаксис:

DELETE FROM имя_таблицы WHERE поле='значение';

Вы могли заметить, что формат DELETE чем-то схож с форматом SELECT. Укажем поле User и значение guest. Это удалит запись из таблицы user, где пользователь — guest, удаляя нашу гостевую учетную запись. Сделаем так:

mysql> DELETE FROM user WHERE User='guest';
Query OK, 1 row affected (0.07 sec)
(Команда FLUSH PRIVILEGES нужна, чтобы обновить разрешения)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Похоже, сработало. Проверим: выйдите и попробуйте зайти как пользователь guest.

mysql> quit
Bye
$ mysql -u guest -h localhost -p
Enter password:
ERROR 1045: Access denied for user: 'guest@localhost' (Using password: YES)

Все, пользователь успешно удален!