如何高效管理MySQL数据库及其用户？

MySQL数据库和计算_管理数据库和用户（MySQL）

用户管理

1、创建用户：在MySQL中，可以使用CREATE USER语句来创建新用户，语法如下：

“`sql

CREATE USER ‘用户名’@’来源地址’ [IDENTIFIED BY [PASSWORD] ‘密码’];

“`

用户名：指定将创建的用户名。

来源地址：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%。

密码：若使用明文密码，直接输入密码；若使用加密密码，需要先使用SELECT PASSWORD(‘密码’)获取密文，再在语句中添加PASSWORD ‘密文’。

示例：

“`sql

CREATE USER ‘zhangsan’@’localhost’ IDENTIFIED BY ‘123123’;

CREATE USER ‘lisi’@’localhost’ IDENTIFIED BY PASSWORD ‘密文’;

“`

2、查看用户信息：用户创建后，其信息会保存在mysql数据库的user表中，可以通过以下命令查看用户信息：

“`sql

USE mysql;

SELECT User, authentication_string, Host FROM user;

“`

3、重命名用户：可以使用RENAME USER语句将用户改名：

“`sql

RENAME USER ‘zhangsan’@’localhost’ TO ‘wangwu’@’localhost’;

“`

4、删除用户：可以使用DROP USER语句删除用户：

“`sql

DROP USER ‘lisi’@’localhost’;

“`

5、修改当前登录用户的密码：可以使用SET PASSWORD语句修改当前登录用户的密码：

“`sql

SET PASSWORD = PASSWORD(‘123456’);

“`

6、修改其他用户的密码：可以使用ALTER USER语句修改其他用户的密码：

“`sql

ALTER USER ‘nancy’@’localhost’ IDENTIFIED BY ‘abc123’;

“`

7、忘记root用户密码的解决办法：

修改配置文件/etc/my.cnf，不使用密码直接登录到MySQL：

“`shell

vim /etc/my.cnf

[mysqld]

skipgranttables

“`

重启服务：

“`shell

service mysqld restart

“`

登录MySQL并修改root密码：

“`sql

UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD(‘123456’) WHERE User = ‘root’;

FLUSH PRIVILEGES;

quit

“`

删除配置文件中的配置，然后重启服务：

“`shell

vim /etc/my.cnf

# 删除skipgranttables行

service mysqld restart

“`

8、查看当前登录用户：使用以下命令查看当前登录用户和来源地址：

“`sql

SELECT USER();

“`

权限管理

1、授予用户权限：GRANT语句用于设置数据库用户的访问权限，当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时，GRANT语句用于修改用户信息。

“`sql

GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@’来源地址’ [IDENTIFIED BY ‘密码’];

“`

权限列表：列出各种数据库操作，以逗号分隔如"select, insert, update"，使用"all"表示所有权限（实际上部分权限仍无法使用）。

数据库名.表名：指定授权操作的数据库和表的名称，其中可以使用通配符*，使用"mysql.*"表示授权操作的对象为mysql数据库中的所有表。

用户名@来源地址：指定用户名称和允许访问的客户机地址，来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址。

IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串，在新建用户时，若省略"IDENTIFIED BY"部分，则用户的密码将为空。

示例：

“`sql

GRANT select ON school.* TO ‘zhangsan’@’localhost’ IDENTIFIED BY ‘123456’;

“`

2、撤销权限：如果需要撤销先前授予的权限，可以使用REVOKE命令。

“`sql

REVOKE ALL PRIVILEGES ONdatabase_name.* FROM ‘username’@’hostname’;

REVOKE permission_type ONdatabase_name.* FROM ‘username’@’hostname’;

“`

示例：

“`sql

REVOKE ALL PRIVILEGES ONmydb.* FROM ‘myuser’@’%’;

REVOKE SELECT, INSERT ONmydb.* FROM ‘myuser’@’%’;

“`

3、查看权限：要查看用户的权限，可以查询MySQL的系统表，以下是一些常用的查询：

查看特定用户的权限：

“`sql

SHOW GRANTS FOR ‘username’@’hostname’;

“`

查看所有用户的权限：

“`sql

SELECT user, host FROM mysql.user;

“`

常见问题解答（FAQs）

Q1: 如何查看MySQL中所有用户及其权限？

A1: 你可以通过查询mysql库中的user表来查看所有用户的信息，通过SHOW GRANTS命令来查看特定用户的权限，具体命令如下：

“`sql

USE mysql;

SELECT User, Host FROM user;

SHOW GRANTS FOR ‘username’@’hostname’;

“`

Q2: 如果忘记了root用户的密码怎么办？

A2: 可以通过以下步骤重置root用户的密码：

1. 修改配置文件/etc/my.cnf，不使用密码直接登录到MySQL：

“`shell

vim /etc/my.cnf

[mysqld]

skipgranttables

“`

2. 重启服务：

“`shell

service mysqld restart

“`

3. 登录MySQL并修改root密码：

“`sql

UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD(‘new_password’) WHERE User = ‘root’;

FLUSH PRIVILEGES;

quit

“`

4. 删除配置文件中的配置，然后重启服务：

“`shell

vim /etc/my.cnf

# 删除skipgranttables行

service mysqld restart

“`