MySQL start

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

Теория

Виды баз данных

Базы данных бывают двух видов:

• Реляционные — (SQL) — база, где данные хранятся в формате таблиц, они строго структурированы и связаны друг с другом.

В них, обычно, планируются несерьезные, неглобальные изменения. Они могут использовать сложные запросы и набор отношений.

• Нереляционные — (NoSQL) — такие базы хранят данные без четких связей друг с другом и четкой структуры.

Они могут как иметь, так и не иметь отношения, не имеют четкой структуры и меняются в процессе.

Структура

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

БД (база данных)
└─СУБД (система управления базами данных)
  └─пользователь

Между базой данных и пользователем используется СУБД - система управления базами данных - специальное ПО обеспечивающее взаимодействие пользователя с базой данных.

Реляционная БД

MySQL является реляционной базой данных. Она состоит из следующих частей:

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

Примечание: В поле необходимо всегда указывать тип записи. При этом нельзя указывать одинаковые записи - т.к. это идентификатор.

Структура базы данных:

БД
├───таблица
│   ├───поле
│   │   └запись
│   ├───поле
│   │   └запись
│   └───поле
│       └запись
├───таблица
│   ├───поле
│   │   └запись
│   ├───поле
│   │   └запись
│   └───поле
│       └запись
└───таблица
    ├───поле
    │   └запись
    ├───поле
    │   └запись
    └───поле
        └запись

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

БД
├───автор
│   ├───библиография
│   ├───биография
│   ├───дата рождения
│   └───имя
├───книги
├───абонементы
└───сотрудник

В данном примере:

• БД - база данных;
  • автор, книги, абонементы, сотрудник - таблицы;
    • библиография, биография, дата рождения, имя - поля.

Типы данных

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

строковые

• char - строка фиксированной длины
  • пример char(20)
    • максимум 20 символов
    • при меньшем количестве переменная заполняется пробелами
    • больше 20 заполнить нельзя
• varchar - строка переменной длины
  • пример varchar(20)
  • будет записано 10 символов
  • в памяти будет хранится 10 символов и 1 говорящий о кол-ве используемых символов
• tiny - текст до 255 байт
• text - текст до 65 кб

числовые

• целые

tinyint     | 1 byte   -128 to 127                                  0 to 255
smallint    | 2 bytes  -32768 to 32767                              0 to 65535
mediumint   | 3 bytes  -8388608 to 8388607                          0 to 16777215
int/integer | 4 bytes  -2147483648 to 2147483647                    0 to 4294967295
bigint      | 8 bytes  -9223372036854775808 to 9223372036854775807  0 to 18446744073709551615

• дробные (указывается в скобках (всего, точность))
  • decimal(3,2) => (xxx,yy) => -999,99 - 999,99
  • float(a,b)
  • double(a,b)

Практика

Установка MySQL

Согласно странице MySQL установим сервер, добавим его в автозапуск и запустим:

$ su -
# apt-get install MySQL-server
# systemctl enable --now mysqld

Создаем пароль для суперпользователя:

$ mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'you_own_pass';

Примечание: Обратите внимание, мы только изучаем MySQL. В продакшине использование root для БД небезопасно и доступ настраивается на обычного пользователя. Но нам как изучающим сойдет и так.

Shell СУБД

Запуск

$ su -
# mysql -u root -p
mysql>

Создание/удаление

• Вывести список БД:

show databases;

• Создать БД:

create database name;
create database if not exists name;

• Удалить БД:

drop database name;
drop database  if exists name;

Работа с сущностями

База данных
├подключиться к базе данных: use ''name'';
├отобразить список баз данных: show databases;
├создать базу данных: create database ''name''; или create database if not exists ''name'';
├удалить базу данных: drop database ''name''; или drop database if exists ''name'';
└── Сущность
    ├ вывести список сущностей (таблиц) (из-под подключенной БД): show tables;
    ├ создать сущность (таблицу) (из-под подключенной БД): create table ''name''(''запись'' ''тип'', ''запись'' ''тип'');
    ├ удалить сущность (таблицу) (из-под подключенной БД): drop table ''name'';
    └── Записи
        ├ ввести данные соответственно типам: insert into ''имя таблицы'' values (''значение записи в соответствии с типом'', ''значение записи в соответствии с типом'');
        ├ вывести записи таблицы: describe ''имя таблицы'';
        ├ вывести все значения таблицы: select * from ''имя таблицы'';
        ├ вывести определенные значения таблицы: select ''имя столбца'', ''имя столбца'' from ''имя таблицы'' where '''(условие)''';
        ├ изменить все значения записи: update ''имя таблицы'' set ''запись'' = ''значение'';
        ├ изменить определенные значения записи: update ''имя таблицы'' set ''запись'' = ''значение'' where '''(условие)''';
        ├ удаление всех записей: delete from  ''имя таблицы'' 
        └ удаление определенной записи: delete from  ''имя таблицы'' where '''(условие)''';

Создание БД

• смотрим, что у нас нет никаких БД
• создаем БД test
• подключаемся к ней.

>show databases;
>create database test;
>use test;

• Создаем таблицу "авторы"

>create table authors (id int, name varchar(30), lastname varchar(30), middlename varchar(30), books text, rating tinytext);

ИЛИ:

mysql> create table authors(
    -> id int,
    -> name varchar(30),
    -> lastname varchar(30),
    -> middlename varchar(30),
    -> books text,
    -> rating tinytext);

• смотрим список таблиц и выводим содержимое таблицы

>show tables;
>describe authors;

Ввод данных

Смотрим содержимое таблицы:

mysql> describe authors;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | YES  |     | NULL    |       |
| name       | varchar(30) | YES  |     | NULL    |       |
| lastname   | varchar(30) | YES  |     | NULL    |       |
| middlename | varchar(30) | YES  |     | NULL    |       |
| books      | text        | YES  |     | NULL    |       |
| rating     | tinytext    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0,00 sec)

По типам данных вводим данные:

> insert into authors values (1, 'Лев', 'Толстой', 'Николаевич', 'Война и мир', '5');
> insert into authors values (2, 'Федор', 'Достоевский', 'Михаилович', 'Преступление и наказание', '4');

Так же, если не нужно заполнять все данные, можно это сделать выборочно:

> insert into authors(name, lastname, rating) values('noname','noname',3);

и множественно:

> insert into authors(name, lastname, rating) values('noname','noname',3), ('noname2', 'noname2',2);

Вывести новые значения таблицы:

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    1 | Лев        | Толстой                | Николаевич           | Война и мир                                    | 5      |
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       | 3      |
| NULL | noname     | noname                 | NULL                 | NULL                                           | 3      |
| NULL | noname     | noname                 | NULL                 | NULL                                           | 3      |
| NULL | noname2    | noname2                | NULL                 | NULL                                           | 2      |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
5 rows in set (0,00 sec)

Обновление данных

Все

Изменить все значения записи:

update authors set rating = 1;

Вывести новые значения таблицы:

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    1 | Лев        | Толстой                | Николаевич           | Война и мир                                    |      1 |
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       |      1 |
|    0 | noname     | noname                 | NULL                 | NULL                                           |      1 |
|    0 | noname     | noname                 | NULL                 | NULL                                           |      1 |
|    0 | noname2    | noname2                | NULL                 | NULL                                           |      1 |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
5 rows in set (0,00 sec)

Определенные

Изменить определенные значения записи:

update authors set rating = 1 where (условие);

Например:

update authors set rating = 5 where lastname='Толстой';

или

update authors set rating = 4 where rating<5;

или

update authors set rating = 3, middlename='nomiddlename' where lastname='noname';

Вывести новые значения таблицы:

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    1 | Лев        | Толстой                | Николаевич           | Война и мир                                    |      5 |
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       |      4 |
|    0 | noname     | noname                 | nomiddlename         | NULL                                           |      3 |
|    0 | noname     | noname                 | nomiddlename         | NULL                                           |      3 |
|    0 | noname2    | noname2                | NULL                 | NULL                                           |      4 |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
5 rows in set (0,00 sec)

Удаление данных

Удаление определенной записи:

delete from authors where name='Лев';

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       |      4 |
|    0 | noname     | noname                 | nomiddlename         | NULL                                           |      3 |
|    0 | noname     | noname                 | nomiddlename         | NULL                                           |      3 |
|    0 | noname2    | noname2                | NULL                 | NULL                                           |      4 |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
4 rows in set (0,00 sec)

Удаление определенной записи с логическим условием:

delete from authors where rating=3 AND name='noname';

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       |      4 |
|    0 | noname2    | noname2                | NULL                 | NULL                                           |      4 |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
2 rows in set (0,00 sec)

Удаление всей таблицы:

delete from authors;

mysql> select * from authors;
Empty set (0,00 sec)

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

select 'имя столбца', 'имя столбца' from 'имя таблицы' where '(условие)';

mysql> select name, books from authors where rating=3;
+------------+------------------------------------------------+
| name       | books                                          |
+------------+------------------------------------------------+
| Федор      | Преступление и наказание                       |
| noname     | NULL                                           |
| noname     | NULL                                           |
+------------+------------------------------------------------+
3 rows in set (0,00 sec)

Условия

• =(условие)
• !=(условие)
• >, <, >=, <=
• (условие)AND(условие), (условие)OR(условие)
• between (значение) AND (значение);
• in (значение)
• not (значение)

Параметры

(пишутся в конце)

• limit n - где вместо n кол-во выводимых строк, вместо 1000 по-умолчанию;
• order by столбец

MySQL Workbench

Еще одним удобным СУБД для MySQL является программа MySQL Workbench.

Для ее установки нужно поставить следующий пакет:

# apt-get install mysql-workbench-community

А чтобы подключиться с локального компьютера к локальной БД нужно выполнить следующую настройку MySQL.

В файле /etc/my.cnf.d/server.cnf нужно закомментировать строку skip-networking.

# mcedit /etc/my.cnf.d/server.cnf

Привести строчку к следующему виду:

#skip-networking

И перезапустить службу:

# service mysqld restart

Коды

Вместо того, чтобы писать все построчно в консоли, в Workbench можно ввести целый скрипт и запустить его. Например:

-- создаем БД, если она не существует и подключаемся к ней
drop database if exists test2;
show databases;
create database if not exists test2;
use test2;
-- создаем таблицу для пользователей
create table if not exists users
(
id int,
username varchar(30),
email varchar (40),
password_hash varchar(200),
age tinyint,
index (username, email)
);

-- вводим значения
insert into users values
(0, 'Petr', 'user@mail.ru', 'f0dlws', '34'),
(1, 'Sergey', 'user2@mail.ru', 'hdjrbk', '22');
insert into users(username, email) values
('Grigory','grig@mail.ru'),
('Vladimir','vlad@mail.ru'),
('Mikhail','miha@mail.ru');

-- обновляем и удаляем значения
update users set id = 0 where username='Vladimir';

update users set username = 'Alexander' where username= 'Petr';

delete from users where username='Vladimir';

-- выводим значения
select * from users;

Работа с пользователями

Просмотр всех пользователей

mysql> select user from mysql.user;

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

mysql> drop user имя@localhost;

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

mysql> ALTER USER 'имя'@'localhost' IDENTIFIED BY 'новый_пароль';

Создание ограниченного пользователя MySQL

Для "продакшена" лучше создать ограниченного пользователя.

Это делается следующим способом:

1. Создается БД

mysql> create database `users`;

2. Создается пользователь

mysql> CREATE USER 'имя'@localhost IDENTIFIED BY 'пароль'

Здесь нужно заменить имя на имя пользователя, а пароль — на пароль для этого пользователя.

3. Выдаются привилегии пользователю

mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'name'@'localhost';

Данная команда применит привилегии, позволяющие пользователю работать с БД без администрирования.

4. Применяются привилегии

mysql> flush privileges;

Привилегии

Источники: [1][2]

Привилегия	Контекст	Назначение
ALL_PRIVILEGES	администрирование сервера	Позволяет совершать любые действия с пользовательскими данными в базе, а также использовать оператор SHOW SLAVE STATUS.
ALL	администрирование сервера	Синоним для привилегии ALL_PRIVILEGES, используемый при управлении привилегиями через CLI.
ALTER	таблицы	Позволяет использовать оператор ALTER TABLE для изменения структуры любых пользовательских таблиц в базе данных. Требует наличия привилегий CREATE и INSERT.
DELETE	таблицы	Позволяет удалять записи из любых пользовательских таблиц в базе данных.
INDEX	таблицы	Позволяет создавать и удалять индексы у существующих в базе данных таблиц.
INSERT	таблицы	Позволяет вставлять записи в пользовательские таблицы в базе данных.
SELECT	таблицы	Позволяет читать данные из таблиц в базе данных.
UPDATE	таблицы	Позволяет обновлять записи в таблицах в базе данных.
CREATE	базы данных, таблицы или индексы	Позволяет использовать оператор CREATE для создания пользовательских таблиц в базе данных.
DROP	базы данных или таблицы	Позволяет удалять таблицы и представления.
GRANT	базы данных или таблицы	Позволят предоставлять или отзывать привилегии у пользователей
CREATE TEMPORARY TABLES	администрирование сервера	Позволяет создавать временные таблицы с помощью CREATE TEMPORARY TABLE
EXECUTE	администрирование сервера	Позволяет использовать операторы, выполняющие хранимые подпрограммы. (хранимые процедуры и функции).
FILE	доступ к файлам на сервере	Глобальная привилегия для чтения и записи файлов на локальном сервере
LOCK TABLES	администрирование сервера	Позволяет блокировать таблицы от указанных потоков
PROCESS	администрирование сервера	Данная привилегия контролирует доступ к информации о выполняемых потоках внутри сервера
PROXY		Позволяет одному пользователю выдавать себя за другого или становиться известным пользователем
RELOAD	администрирование сервера	Позволяет открывать и закрывать файлы журналов, а также перечитывать таблицы привилегий пользователей.
REPLICATION CLIENT	администрирование сервера	Позволяет использовать SHOW MASTER STATUS, SHOW REPLICA STATUS, и SHOW BINARY LOGS
REPLICATION SLAVE	администрирование сервера	Позволяет учетной записи запрашивать обновления, которые были сделаны к базам данных на исходном сервере репликации, используя SHOW REPLICAS
SHOW DATABASES	администрирование сервера	Привилегия, позволяющая выполнить команду "show databases", отображающую список БД
SHUTDOWN	администрирование сервера	Привилегия, позволяющая выполнить команду "shutdown", отключающая работу сервера.

Ошибки и решения

Если служба mysqld перестала запускаться, выполнение

# systemctl start mysqld

ничего не дает, а команда

# systemctl status mysqld

выдает сообщение типа:

Z 0 [ERROR] [MY-010259] [Server] Another process with pid 3849 is using unix socket file.
Z 0 [ERROR] [MY-010268] [Server] Unable to setup unix socket lock file.
Z 0 [ERROR] [MY-010119] [Server] Aborting
Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-alt1.1)  (ALT p10).

Обратите внимание на Unable to setup unix socket lock file!

Проблема в файле mysql.sock.lock. Указанный файл при завершении службы должен удаляться автоматически, но если была аварийная перезагрузка, то он остается и блокирует запуск службы. Его нужно удалить. Сделать это можно следующей командой[i]:

# find /var/lib/mysql -name "mysql.sock.lock" -delete

После этого служба запустится.