Как управлять базами данных MySQL и пользователями командной строки в Linux

Введение

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

В этом руководстве объясняется, как использовать командную строку для создания и управления базами данных и пользователями MySQL или MariaDB.

Предпосылки

Прежде чем мы начнем с этого руководства, давайте предположим, что в вашей системе уже установлен сервер MySQL или MariaDB. Все команды будут выполняться от имени пользователя root.

Чтобы открыть приглашение MySQL, введите следующую команду и введите пароль пользователя root MySQL при появлении запроса:

mysql -u root -p

Создайте новую базу данных MySQL

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

CREATE DATABASE database_name;

Query OK, 1 row affected (0.00 sec)

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

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

Чтобы избежать ошибок, если база данных с тем же именем, которое вы пытаетесь создать, существует, вы можете использовать следующую команду:

CREATE DATABASE IF NOT EXISTS database_name;

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

В приведенном выше выводе Query OK указывает на то, что запрос был выполнен успешно, а 1 warning указывает на то, что база данных уже существует и что новая база данных не создавалась.

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

Вы можете просмотреть все существующие базы данных на нашем сервере MySQL или MariaDB с помощью следующей команды:

SHOW DATABASES;

Вывод будет выглядеть следующим образом:

+--------------------+
 | Database |
 +--------------------+
 | information_schema |
 | database_name |
 | mysql |
 | performance_schema |
 | sys |
 +--------------------+
 5 rows in set (0.00 sec)

Базы данных information_schema, mysql, performance_schema и sys создаются при установке и являются хранилищем информации обо всех других базах данных, конфигурации системы, пользователях, разрешениях и других важных данных. Эти базы данных необходимы для правильной работы установки MySQL.

Удалить базу данных MySQL

Удалить базу данных MySQL так же просто, как запустить одну команду. Это необратимое действие и должно выполняться с осторожностью. Не удаляйте неправильную базу данных, так как после удаления базу данных нельзя будет восстановить.

Чтобы удалить MySQL или MariaDB, выполните следующую команду:

DROP DATABASE database_name;

Query OK, 0 rows affected (0.00 sec)

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

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

Чтобы избежать этой ошибки, вы можете использовать следующую команду:

DROP DATABASE IF EXISTS database_name;

Создайте новую учетную запись пользователя MySQL

Учетная запись пользователя в MySQL состоит из имени пользователя и части имени хоста.

Чтобы создать новую учетную запись пользователя MySQL, выполните следующую команду, просто замените database_user на имя пользователя, которого хотите создать:

CREATE USER 'database_user'@'localhost' IDENTIFIED BY 'user_password';

В приведенной выше команде мы установили часть имени хоста на localhost, что означает, что этот пользователь сможет подключаться к серверу MySQL только с локального хоста (то есть из системы, в которой работает MySQL Server). Если вы хотите предоставить доступ с другого хоста (узлов), просто измените localhost на IP-адрес удаленного компьютера или используйте подстановочный знак '%' для части хоста, что означает, что учетная запись пользователя сможет подключаться с любого хоста.

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

CREATE USER IF NOT EXISTS 'database_user'@'localhost' IDENTIFIED BY 'user_password';

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

Изменить пароль учетной записи пользователя MySQL

Синтаксис для изменения пароля учетной записи пользователя MySQL или MariaDB зависит от версии сервера, работающего в системе.

Вы можете узнать версию своего сервера с помощью следующей команды:

mysql --version

Если у вас MySQL 5.7.6 и выше или MariaDB 10.1.20 и выше, используйте следующую команду для изменения пароля:

ALTER USER 'database_user'@'localhost' IDENTIFIED BY 'new_password';

Если у вас MySQL 5.7.5 и ранее или MariaDB 10.1.20 и ранее, используйте:

SET PASSWORD FOR 'database_user'@'localhost' = PASSWORD('new_password');

В любом случае вывод должен выглядеть так:

Query OK, 0 rows affected (0.00 sec)

Список всех учетных записей пользователей MySQL

Мы можем получить список всех учетных записей пользователей MySQL или MariaDB, запросив таблицу mysql.users:

SELECT user, host FROM mysql.user;

Вывод должен выглядеть так:

+------------------+-----------+
 | user | host |
 +------------------+-----------+
 | database_user | % |
 | database_user | localhost |
 | debian-sys-maint | localhost |
 | mysql.session | localhost |
 | mysql.sys | localhost |
 | root | localhost |
 +------------------+-----------+
 6 rows in set (0.00 sec)

Удалить учетную запись пользователя MySQL

Чтобы удалить учетную запись пользователя, используйте следующую команду:

DROP USER 'database_user@'localhost';

Если вы попытаетесь удалить несуществующую учетную запись пользователя, произойдет ошибка.

ERROR 1396 (HY000): Operation DROP USER failed for 'database_user'@'localhost'

Как и при работе с базами данных, чтобы избежать ошибки, вы можете использовать:

DROP USER IF EXISTS 'database_user'@'localhost';

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

Предоставление разрешений учетной записи пользователя MySQL

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

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

GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

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

GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';

Чтобы назначить все привилегии учетной записи пользователя в определенной таблице из базы данных, используйте следующую команду:

GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';

Если вы хотите предоставить только определенные привилегии учетной записи пользователя для определенного типа базы данных:

GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'localhost';

Отозвать разрешения от учетной записи пользователя MySQL

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

REVOKE ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

Просмотр привилегий учетной записи пользователя MySQL

Чтобы найти привилегии, предоставленные определенному типу учетной записи пользователя MySQL:

SHOW GRANTS FOR 'database_user'@'localhost';

+---------------------------------------------------------------------------+
 | Grants for database_user@localhost |
 +---------------------------------------------------------------------------+
 | GRANT USAGE ON *.* TO 'database_user'@'localhost' |
 | GRANT ALL PRIVILEGES ON `database_name`.* TO 'database_user'@'localhost' |
 +---------------------------------------------------------------------------+
 2 rows in set (0.00 sec)

Вывод

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