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

Цель работы

Формирование навыков управления клиент-серверными СУБД, в т.ч установки, начальная настройки и основ администрирования, на примере СУБД MySQL.

Задания к работе

Использование mysql-клиента

1. Ознакомиться с описанием клиентского приложения mysql (см.: man mysql, mysql --help), особое внимание обратить на опции подключения к БД (логин, пароль, адрес сервера, база данных).
2. Работа с удаленной БД
• Подключиться в интерактивном режиме к серверной БД asoiu (хост: edu.asoiu; порт: по умолчанию; логин: student, пароль: student).
• Просмотреть список таблиц в этой БД и структуру таблицы students.
• Добавить в таблицу students информацию о себе (ном. зач.книжки, ФИО, группа, подгруппа).
• Выбрать все записи из таблицы students.
3. Закрыть клиента mysql.
4. Составить пакетный файл my.sql (название может быть любым), содержащий перечисленные ниже запросы:
• выбрать из таблицы students (БД asoiu_remote) записи о всех студентах, отсортированные по фамилии и группе;
• выбрать записи о студентах вашей подгруппы;
• подсчитать количество студентов в каждой подгруппе;
5. Запустить mysql-клиента в пакетном режиме с созданным вами файлом запросов. Результаты вывести в формате xml или html и сохранить в файл.
6. Выгрузить дамп БД asoiu в локальный файл asoiu.sql (см. mysqldump).

Установка и настройка сервера MySQL

1. Установить на локальном хосте MySQL-сервер в дефолтной конфигурации.
2. Настроить учетную запись суперпользователя (задать пароль и запомнить этот пароль!).
3. Добавить учетную запись для пользователя user с привилегиями создания БД и управления таблицами с удаленных хостов.
4. Запустить mysqld.
5. С помощью клиента mysql в пакетном режиме из файла asoiu.sql создать локальную копию базы данных asoiu со всеми таблицами.
6. Подключиться к созданной вами БД с одного из компьютеров в классе и выбрать все записи из таблицы students.
7. При необходимости внести изменения в настройки (привилегии, кодировки, доступ и т.п.) установленного вами сервера MySQL и повторно проверить правильность его работы.

Указания к лабораторной работе

Эта лабораторная работа состоит из двух частей: в первой — практика управления удаленной БД MySQL из клиентского приложения, во второй — установка и базовая настройка сервера MySQL. Для выполнения заданий необходимы навыки работы в консольном режиме и знания принципов управления правами доступа в Unix-системах.

1. Работа с удаленной базой

Подключение к серверу MySQL

Для подключения к БД MySQL нужно знать: адрес или доменное имя сервера, порт (если MySQL работает на порту, отличном от дефолтного значения 3306, см. сетевые сервисы), логин и пароль. Эти значения передаются как параметры клиента mysql, например так:

aag@stilo:> mysql -u student -p -h 194.188.210.254 // порт по умолчанию

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

При удачном подключении клиент mysql перейдет в интерактивный режим и вы можете выполнять SQL-запросы. Для получения справочной информации по работе с клиентом нужно выполнить команду "HELP;", которая выведет список доступных интерактивных команд. Для получения справки о серверных возможностях нужно выполнить команду "HELP CONTENTS;".

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

Выбрать конкретную базу данных MySQL можно по крайней мере 3-мя способами:

• На этапе подключения, через опции командной строки:

  aag@stilo:> mysql -D db_name [прочие опции] 

• В интерактивном режиме, специальным запросом:

  mysql> USE db_name;

• Непосредственно в SQL-запросе, используя стандартную точечную нотацию, например:

  mysql> SELECT * FROM db_name.table_name;

Управление данными

Работа с данными сводится к выполнению SQL-запросов (SELECT, INSERT, UPDATE, SET, DELETE и проч.) с учетом поддержки стандартов языка SQL в используемой версии сервера MySQL. Примеры запросов:

mysql> SELECT group, fullname FROM students ORDER BY 1,2;

mysql> INSERT INTO students (regnum, fullname, groupnum, subgroup)
VALUES ('AS-003', 'Яковлев Яков Яковлевич', 'AS-449', 2);

mysql> DELETE FROM students WHERE fullname LIKE 'и%';

Получение информации об объектах данных, таких как БД, таблицы, столбцы или индексы возможно с помощью команды SHOW. Несколько примеров использования SHOW:

mysql> SHOW DATABASES; // выводит список всех БД на сервере

mysql> SHOW TABLES; // выводит список таблиц в текущей БД

mysql> SHOW TABLES FROM db_name; // выводит список таблиц в базе db_name

mysql> SHOW CREATE TABLE some_table; // выводит структуру таблицы в виде SQL-запроса

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

mysql> DESCRIBE students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| regnum | varchar(10) | NO | PRI | NULL | |
| fullname | varchar(40) | YES | | NULL | |
| groupnum | varchar(8) | YES | | NULL | |
| subgroup | smallint(6) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

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

Разорвать установленное интерактивное соединение можно в любой момент, набрав в командной строке mysql команду QUIT:

mysql> QUIT;
Bye
aag@stilo:> // выход в оболочку

Отсоединиться от сервера MySQL можно и при помощи сочетания клавиш Control-D.

2. Установка и настройка сервера MySQL

Установка MySQL

aag@stilo:> zypper install mysql // Установка в OpenSuSE
Чтение установленных пакетов...
Будет установлен следующий НОВЫЙ пакет:
mysql
Полный размер загрузки: 7,8 M. После этой операции будет использовано дополнительно 20,9 M.
Продолжить? [да/нет]: y
Загружается пакет mysql-5.0.51a-27.1.i586 (1/1), 7,8 M (20,9 M нераспакованный)
Устанавливается: mysql-5.0.51a-27.1 [готово]
aag@stilo:>

Проверить какие пакеты были установлены и/или доступны для установки можно следующим образом (символ i — installed):

aag@stilo:> zypper search mysql
Чтение установленных пакетов...
С | Имя | Сводка | Тип
--+----------------------+-----------------------------------------------------+------
| bytefx-data-mysql | Database connectivity for Mono | пакет
i | libgda-3_0-mysql | mySQL Provider for GNU Data Access (GDA) | пакет
i | libmysqlclient15 | MySQL Shared Libraries | пакет
| libmysqlclient_r15 | MySQL Shared Libraries | пакет
| libqt4-sql-mysql | Qt 4 MySQL support | пакет
i | mysql | A True Multiuser, Multithreaded SQL Database Server | пакет
i | mysql-client | MySQL Client | пакет
| mysql-connector-java | Official JDBC Driver for MySQL | пакет
| perl-DBD-mysql | Interface to the MySQL database | пакет
| php5-mysql | PHP5 Extension Module | пакет
| qt3-mysql | MySQL Plug-In for Qt | пакет
| ruby-mysql | MySQL bindings for Ruby | пакет

Как видно из таблицы, были установлены сам сервер MySQL, клиент и несколько библиотек. Дополнительные компоненты устанавливаются аналогичным образом. Например:

aag@stilo:> zypper install php5-mysql // Модуль поддержки MySQL для PHP5

Подробней см.: Управлении пакетами в дистрибутивах Linux.

Управление пользователями

MySQL, как типичная клиент-серверная СУБД, рассчитана на одновременную работу большого количества пользователей, подключающихся как локально (например, через веб-интерфейс), так и с удаленных клиентов через TCP/IP.

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

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

aag@stilo:> mysql -u root mysql

2. Выполняем SQL-запрос вида:

mysql>UPDATE user SET Password=PASSWORD('rootpassword') WHERE user='root';

Этот запрос обновит учетную запись в системной таблице user, добавив хэшированный пароль пользователю root.

3. Заставляем сервер принять изменения:

mysql>FLUSH PRIVILEGES;

Если все было выполнено правильно, то при попытке входа суперпользователя без пароля будет сгенерировано сообщение об ошибке:

aag@stilo:> mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

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

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, DROP ON *.* TO 'username'@'localhost' IDENTIFIED BY 'userpassword'
WITH GRANT OPTION;

Такой пользователь сможет использовать все основные SQL-команды для данных в таблицах, а так же создавать и удалять базы данных. Однако он не сможет управлять поведением сервера MySQL (выключать, перезапускать, просматривать список процессов и т.п.), а так же не сможет подключаться к серверу баз данных с удаленных хостов.

Запрос GRANT позволяет гибко управлять привилегиями и возможностями доступа к БД для различных пользователей. Так, например, SQL-запрос вида

GRANT ALL ON user1db.* TO 'user1'@'%';

добавит запись о пользователе user1, имеющем полный доступ без пароля (что не есть хорошо) с любого удаленного хоста (@'%') ко всем объектам (.*) базы данных user1db.

После добавления пользователя требуется обновить привилегии (FLUSH).

Подробное описание команд управления пользователями смотри в mysql>HELP ACCOUNT MANAGEMENT;

Конфигурационный файл my.cfg

Основным файлом, где MySQL хранит все параметры настройки является /etc/my.cfg (в MS Windows — это файл windows-system-directory\my.ini)

Структура файла my.cnf может включать следующие элементы:

#comment

Строка комментариев начинаются с символа `#' или `;'. Пустые строки игнорируются.

[group]

Группа — указывает на программу (mysql, mysqld, mysqladmin, mysqldump и т.п.) или группу, для которой необходимо задать параметры.

option

Эквивалент --option в командной строке.

option=value

Эквивалент --option=value в командной строке.

set-variable = variable=value

Эквивалент --set-variable variable=value в командной строке. Данный синтаксис необходимо использовать для задания переменных сервера MySQL (mysqld).

Рассмотрим использование my.cnf для локализации сервера MySQL. По умолчанию, кодировка сервера и баз данных установлены в latin1, а клиентские кодировки — в UTF-8:

mysql> SHOW VARIABLES LIKE 'char%'; // показать системные переменные
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Этот набор параметров локализации вызовет проблемы при сохранении строковых данных, содержащих русские буквы:

mysql> INSERT INTO t2 (c1, c2) VALUES (1, 'Hello, world!'), (2,'Привет, мир!');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t2;
+------+---------------+
| c1 | c2 |
+------+---------------+
| 1 | Hello, world! |
| 2 | ??????, ???! |
+------+---------------+
2 rows in set (0.00 sec)

Чтобы заставить сервер MySQL корректно работать с символами русского алфавита нужно изменить его настройки в my.cnf. Загрузим в текстовый редактор этот файл (sudo mcedit /etc/my.cnf) и добавим следующие строки в группу [mysqld]:

[mysqld]
# прочие параметры MySQL
skip-character-set-client-handshake #не согласовывать кодировки м/у клиентом и сервером
default-character-set=utf8 #установить UTF-8 по умолчанию для всех объектов данных
init-connect='SET NAMES utf8' #кодировка при подключении
default-collation=utf8_general_ci #сопоставление кодовых таблиц
# прочие параметры MySQL

После сохранения файла нужно перезагрузить сервер MySQL:

aag@stilo:> sudo service mysql restart

Если все сделано правильно, то настройки будут применены:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Теперь все вновь создаваемые объекты данных будут использовать UTF-8 и символы кириллицы будут отображаться правильно:

mysql> CREATE DATABASE db2;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE db2;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

Ранее созданные объекты данных (как базы, так и таблицы в них) не изменятся. Для их перекодировки потребуется выполнить дополнительные действия.

Полную информацию об администрировании сервера MySQL можно найти в официальном руководстве.

Контрольные вопросы

CC-BY-CA Анатольев А.Г., 12.11.2013