Учебно-методические материалы для студентов кафедры АСОИУ

Учебные программы » Сетевые технологии » Лабораторный практикум

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

Цель работы

Формирование навыков управления клиент-серверными СУБД, в т.ч установки, начальная настройки и основ администрирования, на примере СУБД 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 можно найти в официальном руководстве.

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

Анатольев А.Г., 12.11.2013

Постоянный адрес этой страницы:

↑ В начало страницы