BatSchool · Создание базы данных и пользователя

В прошлом уроке мы подняли сервер и вошли в него под root. Это пользователь, которому можно всё: удалить любую базу, завести кого угодно, поменять любой пароль. Работать под ним каждый день всё равно что раздать всем сотрудникам кинотеатра ключ от сейфа. Сегодня заводим отдельную базу и пользователя, которому разрешено ровно то, что нужно.

Здание, кабинеты и пропуска

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

На входе в здание стоит охрана. У каждого сотрудника пропуск, в пропуске написано, кто он и через какую проходную может входить. А ещё список кабинетов, куда его пускают. В MySQL пропуск это пользователь, проходная это хост, а список кабинетов это права.

Своя база

Создадим тестовую базу кинотеатра:

CREATE DATABASE kino_test;

Query OK, 1 row affected (0.00 sec)

Повторим ту же команду:

CREATE DATABASE kino_test;

ERROR 1007 (HY000): Can't create database 'kino_test'; database exists

База уже есть, и сервер отказал. В скриптах, которые запускают много раз, пишут IF NOT EXISTS: «создай, если её ещё нет».

CREATE DATABASE IF NOT EXISTS kino_test;

Query OK, 1 row affected, 1 warning (0.00 sec)

Ошибки нет, но появилось 1 warning. Сервер выполнил команду и хочет что-то сказать. Что именно, покажет SHOW WARNINGS:

SHOW WARNINGS;

+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Note  | 1007 | Can't create database 'kino_test'; database exists |
+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

Та же ошибка 1007, только понижена до заметки (Note): база уже есть, делать нечего. Привыкай смотреть SHOW WARNINGS, как только видишь в ответе слово warning. MySQL часто говорит о проблеме именно так, вполголоса.

Список баз на сервере:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| kino               |
| kino_test          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

Наши здесь kino и kino_test. Остальные четыре служебные: в mysql сервер хранит пользователей и права, в information_schema, performance_schema и sys лежат сведения о самом сервере. Их не трогают.

Как назвать базу

Имя базы пиши маленькими латинскими буквами, цифрами и подчёркиванием: kino, kino_test, shop_2026. С дефисом или пробелом имя придётся каждый раз брать в обратные кавычки, как в выводе `kino-test`, и об этом легко забыть.

Регистр тоже лучше не смешивать. На Linux сервер хранит каждую базу отдельной папкой на диске, поэтому Kino и kino там две разные базы, а на Windows и macOS одна и та же. Скрипт, который работал у тебя на ноутбуке, может сломаться на сервере. Маленькие буквы снимают вопрос.

И ещё одно отличие от PostgreSQL. Там внутри базы есть промежуточный уровень, схема, и таблица живёт по адресу «база, схема, таблица». В MySQL слова database и schema означают одно и то же, и команда CREATE SCHEMA kino_test создаст обычную базу. Уровней всего два: база и таблица. Как это устроено у соседа, смотри в уроке про базы PostgreSQL.

Войти в кабинет: USE

Чтобы работать с таблицами базы, её выбирают командой USE:

USE kino_test;

Database changed

SELECT DATABASE();

+------------+
| DATABASE() |
+------------+
| kino_test  |
+------------+
1 row in set (0.00 sec)

Теперь все запросы без имени базы идут в kino_test. Таблиц там пока нет:

SHOW TABLES;

Empty set (0.00 sec)

Можно и не переключаться, а писать имя базы перед таблицей через точку: SELECT * FROM kino.films. Выбрать базу можно и при подключении, последним словом в команде: mysql -u root -p kino.

Посмотрим, с какими настройками создалась база:

SHOW CREATE DATABASE kino_test\G

*************************** 1. row ***************************
       Database: kino_test
Create Database: CREATE DATABASE `kino_test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

Важно здесь utf8mb4. Это кодировка, в которой база хранит текст: полный UTF-8, где есть и кириллица, и эмодзи. В MySQL 8.4 она стоит по умолчанию, ничего настраивать не нужно. А utf8mb4_0900_ai_ci это правила сравнения строк, ими займёмся в уроке про строки.

Пользователь: имя и хост

Теперь пропуск. Заведём пользователя для кассы:

CREATE USER 'cashier'@'%' IDENTIFIED BY 'demo-only-pass';

Query OK, 0 rows affected (0.00 sec)

Пароль demo-only-pass нарочно учебный. Настоящие пароли в уроки, код и чаты не пишут.

Главное здесь запись 'cashier'@'%'. Пользователь в MySQL это пара из имени и хоста: кто подключается и откуда. Знак % значит «с любого адреса». Есть и другие варианты:

'cashier'@'localhost' пускает только с той же машины, где работает сервер;
'cashier'@'192.168.1.%' пускает из домашней сети;
'cashier'@'%' пускает отовсюду, нужен ещё правильный пароль.

Почему мы взяли %? В Docker сервер живёт в контейнере, а приложение приходит к нему по сети, с адреса Docker. Для сервера такое подключение не локальное, и пропуск @'localhost' его бы не пустил.

Пара «имя плюс хост» означает, что 'cashier'@'%' и 'cashier'@'localhost' это два разных пользователя. Убедимся: спросим права второго, которого мы не создавали:

SHOW GRANTS FOR 'cashier'@'localhost';

ERROR 1141 (42000): There is no such grant defined for user 'cashier' on host 'localhost'

Создать того же пользователя второй раз нельзя:

CREATE USER 'cashier'@'%' IDENTIFIED BY 'demo-only-pass';

ERROR 1396 (HY000): Operation CREATE USER failed for 'cashier'@'%'

Права: GRANT

Свежий пользователь умеет только подключиться. Это видно в его правах:

SHOW GRANTS FOR 'cashier'@'%';

+-------------------------------------+
| Grants for cashier@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `cashier`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

USAGE значит «никаких прав», а *.* читается как «все базы, все таблицы». Пропуск в здание есть, ключей от кабинетов нет. Выдадим их командой GRANT. Кассе нужно смотреть сеансы и продавать билеты, то есть читать и добавлять строки:

GRANT SELECT, INSERT ON kino.* TO 'cashier'@'%';

Query OK, 0 rows affected (0.00 sec)

kino.* значит «все таблицы базы kino». В kino_test касса не попадёт, и удалить ничего не сможет: права DELETE у неё нет. Это и есть принцип наименьших привилегий.

Права можно выдать и на одну таблицу. Кассе нужно отмечать возврат билета, то есть менять строки в tickets, и больше нигде:

GRANT UPDATE ON kino.tickets TO 'cashier'@'%';

Query OK, 0 rows affected (0.00 sec)

SHOW GRANTS FOR 'cashier'@'%';

+---------------------------------------------------+
| Grants for cashier@%                              |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `cashier`@`%`               |
| GRANT SELECT, INSERT ON `kino`.* TO `cashier`@`%` |
| GRANT UPDATE ON `kino`.`tickets` TO `cashier`@`%` |
+---------------------------------------------------+
3 rows in set (0.00 sec)

Три строки, три уровня: на весь сервер (*.*), на базу (kino.*) и на одну таблицу (kino.tickets). Теперь приложение кассы может подключаться так: mysql -h 127.0.0.1 -u cashier -p kino. Как отбирать права через REVOKE и собирать их в роли, разберём в уроке про пользователей и резервные копии.

Убрать базу: DROP DATABASE

Тестовая база больше не нужна:

DROP DATABASE kino_test;

Query OK, 0 rows affected (0.00 sec)

DROP DATABASE удаляет базу вместе со всеми таблицами и данными, без вопросов «вы уверены?» и без корзины. Вернуть её можно только из резервной копии. Перед этой командой всегда перечитай имя базы.

Удалить второй раз нельзя, а с IF EXISTS команда пройдёт с заметкой:

DROP DATABASE kino_test;

ERROR 1008 (HY000): Can't drop database 'kino_test'; database doesn't exist

DROP DATABASE IF EXISTS kino_test;

Query OK, 0 rows affected, 1 warning (0.00 sec)

Что запомнить

CREATE DATABASE создаёт базу, IF NOT EXISTS превращает ошибку в предупреждение, а SHOW WARNINGS его показывает.
USE выбирает текущую базу, SELECT DATABASE() напоминает, какая выбрана сейчас.
Пользователь в MySQL это пара из имени и хоста: 'cashier'@'%' и 'cashier'@'localhost' разные учётные записи.
Новый пользователь не может ничего, права выдаёт GRANT, а SHOW GRANTS перечисляет их.
DROP DATABASE удаляет базу вместе со всеми таблицами и не спрашивает подтверждения.

Попробуй сам

Выдай кассе ещё INSERT на kino.tickets и посмотри, как изменится SHOW GRANTS. Создай пользователя 'manager'@'localhost' и проверь, что SHOW GRANTS FOR 'manager'@'%' вернёт ошибку. Выполни CREATE DATABASE IF NOT EXISTS kino; и прочитай заметку через SHOW WARNINGS.

Проверь себя

Задание

MY-03-1★★

Создай базу notes для учебного проекта с IF NOT EXISTS и выполни команду дважды, прочитав заметку через SHOW WARNINGS. Заведи пользователя 'notes_app'@'%' с учебным паролем и выдай ему SELECT и INSERT только на эту базу. Покажи SHOW GRANTS для него и объясни, почему он не сможет удалить строку или прочитать таблицы kino.

Дальше

У нас есть база и пользователь. Пора создавать таблицы, но сначала один вопрос, которого нет в PostgreSQL: каким способом MySQL будет хранить таблицу на диске. В следующем уроке разберём движки InnoDB и MyISAM.

домашка

Домашка

Создать базу для учебного проекта и пользователя, который может только читать и добавлять строки в этой базе, и проверить его права через SHOW GRANTSJ