企业官网建设流程全解析

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 连接）：

注意：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 权限分为多个级别和类别：

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;"

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

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