Основные моменты для работы с СУБД MySQL

Доброго дня коллеги , ниже опешу мини оглавление :

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

Основные команды управления MySQL Server

Создание БД.

Удаление БД.

Как посмотреть сколько весят базы в MB

Работа с пользователями (Создание, удаление.

Делегирование прав пользователя.

Создание Таблиц.

Добавление столбцов в таблицу

Отключить Bin_log

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

Из официальных репозиториев (Ubuntu)

sudo apt update
sudo apt install mysql-server mysql-client

Из репозиториев разработчиков

Сначала необходимо добавить этот репозиторий в систему.

Скачаем deb пакет с конфигурацией репозитория с оф. сайта

https://dev.mysql.com/downloads/repo/apt/

Теперь установим скачанный выше deb пакет командой

sudo dpkg -i ~/Загрузки/mysql-apt-config_ВЕРСИЯ.deb

Затем, обновим список пакетов в репозиториях:

sudo apt update

И установите MySQL:

sudo apt install mysql-server mysql-client

Для проверки работоспособности сервера выполним:

sudo systemctl status mysql
или 
sudo systemctl status mysqld

Для остановки работы сервера:

sudo systemctl stop mysql
или 
sudo systemctl stop mysqld

Для запуска сервера:

sudo systemctl start mysql
или 
sudo systemctl start mysqld

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

sudo mysql_secure_installation

Вход СУБД

Если во время установки вы не указывали пароль для root MySQL :

sudo mysql -u root
или вот так если указывали пароль
sudo mysql -u root -p

Создание баз данных

Для создание используется команда CREATE DATABASE :

CREATE DATABASE `zabbix3` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

CHARACTER SET — это кодировка, набор символов. CHARACTER SET может включать в себя различные наборы символов. Различные CHARACTER SET могут включать примерно одинаковые наборы символов но в различном порядке (см. например koi8ru и cp1251). MySQL необходимо знать какой CHARACTER SET будет использован для данных в таблице, чтобы корректно проводить сортировку и индексацию данных.

COLLATION — способ, с помощью которого следует упорядочивать и сравнивать данные в БД. Для одного и того же CHARACTER SET может существовать несколько COLLATION. Например: cp1251_general_ci — сравнение не чувствительное к регистру, cp1251_bin — чувствительное к регистру.

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

Удаление базы производится командой DROP :

DROP DATABASE база_данных;

Как посмотреть сколько весят базы в MB

SELECT table_schema "DB Name",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema;

Создание пользователей MySQL

Для создания пользователя используется команда CREATE USER, её синтаксис такой:

CREATE USER 'имя-пользователя'@'хост или %' IDENTIFIED BY 'пароль';

Кроме имени пользователя, здесь нужно задать хост, с которого может авторизоваться этот пользователь. Здесь может быть доменное имя, IP-адрес, адрес подсети или знак «%«, который означает все возможные хосты. Это очень удобно, потому что вы можете создать пользователя, к которому можно будет подключится только локально или настроить отдельно права для локального или удалённого пользователя.

Чтоб посмотреть наших пользователей :

SELECT User,Host FROM mysql.user;

Права пользователя MySQL

Для того чтобы назначить/дать права пользователю используем команду GRANT :

GRANT привилегии ON база_данных . таблица TO 'имя_пользователя'@'хост';

Здесь можем дать определённые привилегии для опредёленного пользователя к базе данных или таблице. Если нужно дать права для всех баз данных или таблиц, используйте вместо названия звездочку — «*«. Вот основные права пользователя MySQL, которые вы можете использовать:

• ALL PRIVILEGES — все, кроме GRANT;
• USAGE PRIVILEGES — никаких привилегий;
• SELECT — делать выборки из таблиц;
• INSERT — вставлять данные в таблицу;
• UPDATE — обновлять данные в таблице;
• DELETE — удалять данные из таблицы;
• FILE — разрешает читать файлы на сервере;
• CREATE — создавать таблицы или базы данных;
• ALTER — изменять структуру таблиц;
• INDEX — создавать индексы для таблиц;
• DROP — удалять таблицы;
• EVENT — обработка событий;
• TRIGGER — создание триггеров.

Для администрирования баз данных:

• GRANT — изменять права пользователей;
• SUPER — суперпользователь;
• PROCESS — получение информации о состоянии MySQL;
• RELOAD — позволяет перезагружать таблицы привилегий;
• SHUTDOWN — позволяет отключать или перезапускать базу данных;
• SHOW DATABASES — просмотр списка баз данных;
• LOCK TABLES — блокирование таблиц при использовании SELECT;
• REFERENCES — создание внешних ключей для связывания таблиц;
• CREATE USER — создание пользователей;

Чтобы дать права пользователю zabbix@localhost на обновление и добавление записей для базы данных zabbixdb :

GRANT SELECT,UPDATE,INSERT ON zabbixdb . * TO 'zabbix'@'localhost';

Чтобы дать все права(привилегии) пользователю zabbix@localhost :

GRANT ALL PRIVILEGES ON zabbixdb . * TO 'zabbix'@'localhost';

Чтобы посмотреть привилегии пользователя :

SHOW GRANTS FOR 'zabbix'@'localhost';

ВАЖНО!

После изменений прав пользователя необходимо обновить таблицу прав пользователей MySQL  в памяти. Для этого выполните:

FLUSH PRIVILEGES;

Создание таблиц

Для создания таблиц используется команда CREATE TABLE

CREATE TABLE название_таблицы
(название_столбца1 тип_данных атрибуты_столбца1, 
 название_столбца2 тип_данных атрибуты_столбца2,
);

ВАЖНО!

Перед созданием таблицы необходимо выбрать базу где будет новая таблица командой USE

use база_данных;

После команды CREATE TABLE идет название таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно должно быть уникальным. Затем в скобках перечисляются названия столбцов, их типы данных и атрибуты. В самом конце можно определить атрибуты для всей таблицы. Атрибуты столбцов, а также атрибуты таблицы указывать необязательно.

CREATE TABLE Avto
(
id INT,
Marka VARCHAR(15),
Model VARCHAR(15),
Year INT
);

Собственно название таблицы Avto

в ней четыре столбца: id , Marka , Model , Year ,

в каждом столбце есть тип данных

атрибуты столбцов необязательны.

Добавление столбцов в таблицу

Для добавления новых столбцов в таблицу в СУБД MySQL присутствует специальная команда — ALTER TABLE ADD COLUMN:

ALTER TABLE имя_таблицы ADD COLUMN имя_нового_столбца тип_данных [FIRST|AFTER имя_существующего_столбца];

Сначала указывается имя таблицы, в которую будет добавлен новый столбец. Имя идёт сразу после команды ALTER TABLE. Далее после директивы ADD COLUMN необходимо указать имя нового столбца, который будет создан и задать его тип данных. Ключевое слово COLUMN можно опустить, оно не обязательно. Также в качестве дополнительных параметров можно указать положение нового столбца в таблице. Если указать FIRST, то столбец будет создан в самом начале таблице, также можно указать расположение после специфического столбца (при условии, что он присутствует в таблице) для этого необходимо вписать ключевое слово AFTER и указать имя уже существующего столбца в таблице.

Переименование таблиц

Чтобы переименовать таблицу воспользуемся RENAME TABLE:

RENAME TABLE старое_название TO новое_название;

Очистка таблицы

Для полного удаления данных, очистки таблицы применяется команда TRUNCATE TABLE. Например, очистим таблицу Avto:

TRUNCATE TABLE Avto;

Удаление таблиц

Для удаления таблицы из БД применяется команда DROP TABLE:

DROP TABLE Avto;