如何从命令行管理 MySQL 数据库和用户

MySQL 是最流行的开源关系数据库管理系统。 MySQL 服务器允许我们创建多个用户和多个数据库并可以授予用户适当的权限,以便用户可以访问和管理数据库。

本教程介绍如何使用命令行创建和管理 MySQL 或 MariaDB 数据库和用户。

开始之前

在开始本教程之前,我们假设您已经在系统上安装了 MySQL 或 MariaDB 服务器。所有命令都将在 MySQL Shell 内使用 root 用户执行。如果没有安装过 Mysql,请参考如下教程:

请键入以下命令,要打开 MySQL Shell,并在出现提示时输入 MySQL root 用户密码:

mysql -u root -p

创建一个新的 MySQL 数据库

要创建新的 MySQL 或 MariaDB 数据库,请运行以下命令,只需把 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_schemasys 是在安装时创建的,并且它们存储有关所有其他数据库,系统配置,用户,许可等重要数据信息。这些数据库是 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;
Query OK, 0 rows affected, 1 warning (0.00 sec)

在上面的输出中,您可以看到 Query OK 表示查询成功, 1 warning 表示数据库不存在。

创建一个新的 MySQL 用户帐户

MySQL 中的用户帐户由用户名和主机名部分组成。

要创建新的 MySQL 或 MariaDB 用户帐户,请运行以下命令,只需将 database_user 替换为您要创建的用户的名称:

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

在上面的命令中,我们设置了主机名部分, localhost 这意味着该用户只能从 localhost (即运行 MySQL Server 的系统)连接到 MySQL 服务器。如果要授予其他主机的访问权限,只需更改 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.users 表列出所有 MySQL 或 MariaDB 用户帐户:

SELECT user, host FROM mysql.user;

输出应类似于下面的内容。此输出列出了运行在 Ubuntu 的机器的 MySQL 5.7 服务器中的默认用户和我们先前添加的两个额外的用户账户 ‘database_user‘@’%’ 和 ‘database_user‘@’localhost’ 。

+------------------+-----------+
| 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 '[email protected]'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 用户帐户权限

您可以为用户帐户授予多种类型的权限。您可以在此处找到 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 [email protected]'localhost';

撤消 MySQL 用户帐户的权限

如果您需要从用户帐户撤消一个或多个权限或所有权限,则语法几乎与授予权限相同。例如,如果要撤消用户在特定数据库上的所有权限,请使用以下命令:

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

显示 MySQL 用户帐户权限

要查找指定的 MySQL 用户帐户的权限,请执行以下操作:

SHOW GRANTS FOR 'database_user'@'localhost';
+---------------------------------------------------------------------------+
| Grants for [email protected] |
+---------------------------------------------------------------------------+
| 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 root 密码的教程,以防您忘记密码。