Основы управления MariaDB/MySQL

• Консольный клиент
• Процессы
• Переменные
• Пользователи
• Привилегии
• Базы данных
• Таблицы

Периодически работаю в терминале с MariaDB, иногда бывают редкие команды, которые сразу и не вспомнишь, а есть прям очень частые. Здесь я собрал базовые команды по работе с сервером, с которыми уже несколько раз столкнулся за последний год (на момент написания статьи). Это структурированный базис в начале администрирования MariaDB.

Статья будет пополняться по мере возникновения новых шаблонных запросов.

#Консольный клиент

Установка:

$ sudo apt install mysql-client

Подключение к серверу по сети:

mysql --host=192.168.0.110 --port=3308 -u USER -p

# или

mysql --host=192.168.0.110 --port=3308 -u USER -pPASSWORD

Без указания хоста и порта подключение будет осуществляться к файлу сокету по пути /var/run/mysqld/mysqld.sock.

В консольном клиенте поддерживается вертикальный вывод, а не табличный, что удобно для консоли, при помощи завершающей последовательности \G:

SHOW MASTER STATUS \G;

Полный список команд внутри консольного клиента в документации.

Версия сервера:

SELECT VERSION();

Уровень изоляции запросов:

SHOW VARIABLES LIKE 'tx_isolation';

#Процессы

Список всех процессов:

SHOW PROCESSLIST;

Но этот список может быть неудобен тем что на высоконагруженном сервере может быть множество соединений с базой данных и список будет длинным. Скорее всего нас интересует конкретная группа процессов, например только активные процессы отсортированные по времени выполнения:

SELECT *
FROM `INFORMATION_SCHEMA`.`PROCESSLIST`
WHERE
    state != "" AND
    info is not NULL
ORDER BY `Time` DESC;

Общие состояния процессов можно узнать в документации.

Уничтожить процесс по его ID:

KILL ID

#Переменные

Переменные бывают глобальные GLOBAL (на всю систему или на новые подключения) и сессионные SESSION (на текущее подключение), по умолчанию SESSION.

Показать все глобальные переменные, в которых есть фраза timeout:

SHOW GLOBAL VARIABLES LIKE '%timeout%';

Установить значение сессионной переменной:

SET SESSION max_error_count=128;

Список всех переменных в документации.

#Пользователи

Информация о пользователе username:

SELECT * FROM mysql.user WHERE user='username';

Все пользователи без паролей:

SELECT
    user, host, password, authentication_string
FROM mysql.user
WHERE
    password = '' AND authentication_string = '';

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

CREATE USER 'myuser'@'172.16.250.6' IDENTIFIED BY 'password';

Имя пользователя состоит из двух компонентов имя_пользователя@хост, где в качестве хоста может указываться домен, IP-адрес или подстановочные символы типа % (означает все хосты). Для каждого хоста нужно создавать отдельного пользователя и выдавать ему права.

Может случиться такая ситуация, когда у нас есть только хэш пароля и нам нужно его установить для пользователя:

CREATE USER 'myuser'@'172.16.250.6' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19';

Создать пользователя и указать конкретный плагин аутентификации, в данном случае mysql_native_password:

CREATE USER 'myuser'@'172.16.250.6' IDENTIFIED WITH 'mysql_native_password' BY 'password';

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

ALTER USER 'myuser'@'host' IDENTIFIED BY 'new_password';

Заблокировать пользователя (начиная с MariaDB 10.4.2):

ALTER USER 'myuser'@'%' ACCOUNT LOCK;

Удалить пользователя (разрешения тоже удалятся):

DROP USER myuser;

#Привилегии

Документация по привилегиям.

Список разрешений для пользователя:

SHOW GRANTS FOR 'username'@'host';

Выдать все привилегии на все таблицы * для базы данных mydb пользователя root, который заходит с той же машины где установлена СУБД:

GRANT ALL PRIVILEGES ON mydb.* TO 'root'@'127.0.0.1';

Отозвать разрешения у пользователя:

REVOKE SELECT ON mydatabase.mytable FROM 'user'@'localhost';

После GRANT и REVOKE изменения сразу вступают в силу, но если менять таблицу привилегий напрямую через INSERT/UPDATE/DELETE то необходимо сбросить внутренние кэши разрешений и загрузить новые данные из таблицы:

FLUSH PRIVILEGES;

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

SELECT User, Host, Db, Table_name, Table_priv
FROM mysql.tables_priv
WHERE Db = 'mydb';

#Базы данных

Список доступных баз данных:

SHOW DATABASES;

Можно использовать оператор LIKE с паттернами, например выбрать все базы данных, в которых есть слово product:

SHOW DATABASES LIKE '%product%'

Установить базу данных по умолчанию для текущего соединения (все остальные запросы пойдут к этой базе данных):

USE mydb;

Список всех баз данных:

SELECT * FROM information_schema.tables GROUP BY table_schema;

Вывести список баз данных с размерами в мегабайт:

SELECT
    table_schema AS `Database`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size (MB)`
FROM information_schema.tables
GROUP BY table_schema;

#Таблицы

Список таблиц в текущей базе данных:

SHOW TABLES;

Если не установить базу данных по умолчанию, то можно использовать оператор WHERE:

SHOW TABLES WHERE mydb;

Здесь также можно использовать оператор LIKE с паттернами, выбрать все базы данных, в которых есть слово user:

SHOW TABLES LIKE '%user%';

Список всех таблиц (почти как список баз данных только без группировки):

SELECT * FROM information_schema.tables;

Список таблиц в базе данных mydb с размерами в мегабайт:

SELECT
    table_name AS `Table`,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Size (MB)`
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY `Size (MB)` DESC;

Статус таблицы:

SHOW TABLE STATUS FROM `mydb` LIKE 'mytable';

Выше ссылка на таблицу, а так выглядит вывод статуса в dbeaver: