MySQL 学习笔记（第五期）：用户管理与权限控制

本笔记承接第四期，进入用户管理与权限控制的核心内容。涵盖：用户账号的组成与创建/删除/重命名、密码管理（含忘记密码解决方案）、权限的分类与授权/回收、远程连接配置。所有代码均已加以整理和注释。

---

一、MySQL 用户账号概述

1.1 账号组成

MySQL 中的用户账号由 用户名 和 主机 两部分组成，格式为 'username'@'host'。用户名和主机组合才能唯一标识一个用户。

• 主机：限制该账号只能从指定主机或 IP 连接 MySQL 服务器。
• 通配符：% 表示任意长度的任意字符，_ 表示单个字符。
• 示例：
  • 'qgd'@'localhost' — 只能从本机连接
  • 'qgd'@'192.168.108.110' — 只能从指定 IP 连接
  • 'qgd'@'10.0.0.%' — 只能从 10.0.0.0/24 网段连接
  • 'qgd'@'%' — 可从任意主机连接（不安全）

1.2 默认用户

MySQL 8.0 安装后默认创建以下用户（均只能从 localhost 连接）：

Host	User	说明
localhost	root	超级管理员
localhost	mysql.infoschema	系统账户
localhost	mysql.session	系统账户
localhost	mysql.sys	系统账户

注意：MySQL 8.0 中默认没有可远程登录的用户。

---

二、用户管理操作（DDL）

2.1 创建用户（CREATE USER）

语法：

sql

CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];

范例：

sql

-- 创建只能从特定网段连接的用户
CREATE USER 'test'@'192.168.108.%' IDENTIFIED BY '123456';

-- 创建可从任意主机连接的用户（不建议）
CREATE USER 'test2'@'%' IDENTIFIED BY '123456';

-- 创建无需密码的用户（仅限测试环境）
CREATE USER 'test3'@'localhost';

注意：MySQL 8.0 已不支持 GRANT 语句同时创建用户，必须先用 CREATE USER。

2.2 重命名用户（RENAME USER）

sql

RENAME USER 'old_user'@'old_host' TO 'new_user'@'new_host';

范例：

sql

RENAME USER 'test'@'192.168.108.%' TO 'tester'@'10.0.0.%';

2.3 删除用户（DROP USER）

sql

DROP USER 'username'@'host';

范例：

sql

DROP USER 'test'@'192.168.108.%';
DROP USER 'test2'@'%';
DROP USER IF EXISTS 'test3'@'localhost';

---

三、密码管理

3.1 设置/修改密码（MySQL 8.0 方法）

MySQL 8.0 中密码存储在 mysql.user 表的 authentication_string 字段（已取消 PASSWORD() 函数）。

方法一：使用 ALTER USER（推荐）

sql

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

范例：

sql

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

方法二：使用 SET PASSWORD（MySQL 8.0 仍然支持）

sql

SET PASSWORD FOR 'username'@'host' = 'new_password';

方法三：使用 mysqladmin 命令行工具

bash

mysqladmin -u root -p'old_password' password 'new_password'

3.2 忘记 root 密码的解决方案

方案一：跳过授权表启动（保留数据）

步骤：

1. 编辑 MySQL 配置文件（如 /etc/my.cnf.d/mysql-server.cnf），在 [mysqld] 段添加：

   ini

   skip-grant-tables
   skip-networking      # MySQL 8.0 可能不需要
   

2. 重启 MySQL 服务：

   bash

   systemctl restart mysqld
   

3. 无密码登录 MySQL：

   bash

   mysql
   

4. 清空 root 密码（设为空）：

   sql

   UPDATE mysql.user SET authentication_string = '' WHERE user = 'root' AND host = 'localhost';
   FLUSH PRIVILEGES;
   

5. 退出 MySQL，移除配置文件中的 skip-grant-tables，重启服务。

6. 使用空密码登录，再设置新密码：

   sql

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

方案二：删除数据目录重新初始化（会清除所有数据，仅限测试）

bash

systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld   # 自动重新初始化，root 密码为空

注意：方案二会丢失所有数据库，生产环境绝对禁止！

---

四、权限管理（DCL）

4.1 权限分类

MySQL 权限分为多个级别和类别：

类别	权限示例
管理类	CREATE USER, FILE, SUPER, SHOW DATABASES, RELOAD, SHUTDOWN, REPLICATION SLAVE, LOCK TABLES, PROCESS
程序类（函数/存储过程/触发器）	CREATE, ALTER, DROP, EXECUTE
库/表级别	ALTER, CREATE, CREATE VIEW, DROP INDEX, SHOW VIEW, WITH GRANT OPTION
数据操作	SELECT, INSERT, DELETE, UPDATE
字段级别	SELECT(col1,col2...), UPDATE(col1,col2...), INSERT(col1,col2...)
所有权限	ALL PRIVILEGES 或 ALL

4.2 授权（GRANT）

语法：

sql

GRANT priv_type [(column_list)] ON [object_type] priv_level TO 'user'@'host' [WITH GRANT OPTION];

• priv_level 格式：
  • * — 所有数据库的所有对象
  • *.* — 所有数据库的所有对象
  • db_name.* — 指定数据库的所有对象
  • db_name.tbl_name — 指定数据库的指定表
  • db_name.routine_name — 指定存储过程/函数

范例：

sql

-- 授予指定库的所有权限
GRANT ALL ON wordpress.* TO 'wordpress'@'10.0.0.%';

-- 授予所有库的所有权限（超级管理员），并允许转授
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%' WITH GRANT OPTION;

-- 授予特定列的操作权限
GRANT SELECT (id, name), INSERT (id, name) ON mydb.users TO 'app'@'%';

-- 授予部分权限
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO 'user1'@'192.168.108.%';

注意：MySQL 8.0 中，GRANT 不再支持同时创建用户，必须先 CREATE USER。

4.3 查看权限

sql

-- 查看指定用户的权限
SHOW GRANTS FOR 'username'@'host';

-- 查看当前登录用户的权限
SHOW GRANTS FOR CURRENT_USER();

范例：

sql

SHOW GRANTS FOR 'root'@'localhost';

4.4 撤销权限（REVOKE）

语法：

sql

REVOKE priv_type ON [object_type] priv_level FROM 'user'@'host';

范例：

sql

-- 撤销删除权限
REVOKE DELETE ON *.* FROM 'testuser'@'172.16.0.%';

-- 撤销所有权限
REVOKE ALL ON *.* FROM 'testuser'@'172.16.0.%';

4.5 权限生效

MySQL 服务进程启动时将授权表加载到内存。执行 GRANT 或 REVOKE 后，通常会自动生效。如未自动生效，可手动刷新：

sql

FLUSH PRIVILEGES;

---

五、远程连接配置

5.1 创建可远程连接的用户

sql

-- 创建可从特定 IP 连接的 root 用户
CREATE USER 'root'@'192.168.108.1' IDENTIFIED BY '123456';

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.108.1' WITH GRANT OPTION;

5.2 远程客户端连接

bash

# 从客户端（如 Windows CMD）连接
mysql -u root -h 192.168.108.128 -p123456

5.3 连接验证与常见错误

• 查看当前连接的用户信息：

  sql

  SELECT USER();          -- 显示当前用户
  SELECT CURRENT_USER();  -- 显示权限验证使用的用户
  

• 查看连接线程：

  sql

  SHOW PROCESSLIST;
  

常见错误：ERROR 1130 (HY000): Host 'xxx' is not allowed to connect to this MySQL server
原因：用户未授权从该主机连接。解决：创建或修改用户的主机部分。

---

六、完整权限管理实战流程

场景：为应用 blog_app 创建专用数据库和用户

sql

-- 1. 创建数据库
CREATE DATABASE blog_db CHARACTER SET utf8mb4;

-- 2. 创建用户（允许从 192.168.108.% 网段连接）
CREATE USER 'blog_user'@'192.168.108.%' IDENTIFIED BY 'StrongP@ss123';

-- 3. 授予该用户对 blog_db 的所有权限
GRANT ALL ON blog_db.* TO 'blog_user'@'192.168.108.%';

-- 4. 刷新权限（可选，通常自动生效）
FLUSH PRIVILEGES;

-- 5. 验证权限
SHOW GRANTS FOR 'blog_user'@'192.168.108.%';

远程测试

bash

# 在 192.168.108.x 网段的客户端执行
mysql -u blog_user -pStrongP@ss123 -h 192.168.108.128 -e "USE blog_db; SHOW TABLES;"

---

七、本期知识点归纳一览表

类别	知识点	关键语法/命令
账号组成	'username'@'host'	主机限制，% 通配符
创建用户	CREATE USER	CREATE USER 'u'@'h' IDENTIFIED BY 'pwd';
重命名用户	RENAME USER	RENAME USER 'old'@'h' TO 'new'@'h';
删除用户	DROP USER	DROP USER 'u'@'h';
修改密码	ALTER USER	ALTER USER 'u'@'h' IDENTIFIED BY 'new';
忘记 root 密码	跳过授权表	skip-grant-tables → 更新空密码 → 重新设置
权限分类	管理类、程序类、库表级、数据操作、字段级	见上表
授权	GRANT	GRANT priv ON db.* TO 'u'@'h';
查看权限	SHOW GRANTS	SHOW GRANTS FOR 'u'@'h';
撤销权限	REVOKE	REVOKE priv ON db.* FROM 'u'@'h';
权限生效	FLUSH PRIVILEGES	通常自动生效，手动刷新
远程连接	创建带主机 % 或具体 IP 的用户	mysql -u user -h server_ip -p

---

下一期预告：MySQL 备份与恢复（冷备份、逻辑备份、增量备份与二进制日志恢复）。