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

MySQL 学习笔记第五期用户管理与权限控制本笔记承接第四期进入用户管理与权限控制的核心内容。涵盖用户账号的组成与创建/删除/重命名、密码管理含忘记密码解决方案、权限的分类与授权/回收、远程连接配置。所有代码均已加以整理和注释。一、MySQL 用户账号概述1.1 账号组成MySQL 中的用户账号由用户名和主机两部分组成格式为usernamehost。用户名和主机组合才能唯一标识一个用户。主机限制该账号只能从指定主机或 IP 连接 MySQL 服务器。通配符%表示任意长度的任意字符_表示单个字符。示例qgdlocalhost— 只能从本机连接qgd192.168.108.110— 只能从指定 IP 连接qgd10.0.0.%— 只能从 10.0.0.0/24 网段连接qgd%— 可从任意主机连接不安全1.2 默认用户MySQL 8.0 安装后默认创建以下用户均只能从 localhost 连接HostUser说明localhostroot超级管理员localhostmysql.infoschema系统账户localhostmysql.session系统账户localhostmysql.sys系统账户注意MySQL 8.0 中默认没有可远程登录的用户。二、用户管理操作DDL2.1 创建用户CREATE USER语法sqlCREATE USER usernamehost [IDENTIFIED BY password];范例sql-- 创建只能从特定网段连接的用户 CREATE USER test192.168.108.% IDENTIFIED BY 123456; -- 创建可从任意主机连接的用户不建议 CREATE USER test2% IDENTIFIED BY 123456; -- 创建无需密码的用户仅限测试环境 CREATE USER test3localhost;注意MySQL 8.0 已不支持GRANT语句同时创建用户必须先用CREATE USER。2.2 重命名用户RENAME USERsqlRENAME USER old_userold_host TO new_usernew_host;范例sqlRENAME USER test192.168.108.% TO tester10.0.0.%;2.3 删除用户DROP USERsqlDROP USER usernamehost;范例sqlDROP USER test192.168.108.%; DROP USER test2%; DROP USER IF EXISTS test3localhost;三、密码管理3.1 设置/修改密码MySQL 8.0 方法MySQL 8.0 中密码存储在mysql.user表的authentication_string字段已取消PASSWORD()函数。方法一使用 ALTER USER推荐sqlALTER USER usernamehost IDENTIFIED BY new_password;范例sqlALTER USER rootlocalhost IDENTIFIED BY 123456;方法二使用 SET PASSWORDMySQL 8.0 仍然支持sqlSET PASSWORD FOR usernamehost new_password;方法三使用 mysqladmin 命令行工具bashmysqladmin -u root -pold_password password new_password3.2 忘记 root 密码的解决方案方案一跳过授权表启动保留数据步骤编辑 MySQL 配置文件如/etc/my.cnf.d/mysql-server.cnf在[mysqld]段添加iniskip-grant-tables skip-networking # MySQL 8.0 可能不需要重启 MySQL 服务bashsystemctl restart mysqld无密码登录 MySQLbashmysql清空 root 密码设为空sqlUPDATE mysql.user SET authentication_string WHERE user root AND host localhost; FLUSH PRIVILEGES;退出 MySQL移除配置文件中的skip-grant-tables重启服务。使用空密码登录再设置新密码sqlALTER USER rootlocalhost IDENTIFIED BY new_password;方案二删除数据目录重新初始化会清除所有数据仅限测试bashsystemctl stop mysqld rm -rf /var/lib/mysql/* systemctl start mysqld # 自动重新初始化root 密码为空注意方案二会丢失所有数据库生产环境绝对禁止四、权限管理DCL4.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或ALL4.2 授权GRANT语法sqlGRANT priv_type [(column_list)] ON [object_type] priv_level TO userhost [WITH GRANT OPTION];priv_level格式*— 所有数据库的所有对象*.*— 所有数据库的所有对象db_name.*— 指定数据库的所有对象db_name.tbl_name— 指定数据库的指定表db_name.routine_name— 指定存储过程/函数范例sql-- 授予指定库的所有权限 GRANT ALL ON wordpress.* TO wordpress10.0.0.%; -- 授予所有库的所有权限超级管理员并允许转授 GRANT ALL PRIVILEGES ON *.* TO root10.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 user1192.168.108.%;注意MySQL 8.0 中GRANT不再支持同时创建用户必须先CREATE USER。4.3 查看权限sql-- 查看指定用户的权限 SHOW GRANTS FOR usernamehost; -- 查看当前登录用户的权限 SHOW GRANTS FOR CURRENT_USER();范例sqlSHOW GRANTS FOR rootlocalhost;4.4 撤销权限REVOKE语法sqlREVOKE priv_type ON [object_type] priv_level FROM userhost;范例sql-- 撤销删除权限 REVOKE DELETE ON *.* FROM testuser172.16.0.%; -- 撤销所有权限 REVOKE ALL ON *.* FROM testuser172.16.0.%;4.5 权限生效MySQL 服务进程启动时将授权表加载到内存。执行GRANT或REVOKE后通常会自动生效。如未自动生效可手动刷新sqlFLUSH PRIVILEGES;五、远程连接配置5.1 创建可远程连接的用户sql-- 创建可从特定 IP 连接的 root 用户 CREATE USER root192.168.108.1 IDENTIFIED BY 123456; -- 授予所有权限 GRANT ALL PRIVILEGES ON *.* TO root192.168.108.1 WITH GRANT OPTION;5.2 远程客户端连接bash# 从客户端如 Windows CMD连接 mysql -u root -h 192.168.108.128 -p1234565.3 连接验证与常见错误查看当前连接的用户信息sqlSELECT USER(); -- 显示当前用户 SELECT CURRENT_USER(); -- 显示权限验证使用的用户查看连接线程sqlSHOW 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_user192.168.108.% IDENTIFIED BY StrongPss123; -- 3. 授予该用户对 blog_db 的所有权限 GRANT ALL ON blog_db.* TO blog_user192.168.108.%; -- 4. 刷新权限可选通常自动生效 FLUSH PRIVILEGES; -- 5. 验证权限 SHOW GRANTS FOR blog_user192.168.108.%;远程测试bash# 在 192.168.108.x 网段的客户端执行 mysql -u blog_user -pStrongPss123 -h 192.168.108.128 -e USE blog_db; SHOW TABLES;七、本期知识点归纳一览表类别知识点关键语法/命令账号组成usernamehost主机限制% 通配符创建用户CREATE USERCREATE USER uh IDENTIFIED BY pwd;重命名用户RENAME USERRENAME USER oldh TO newh;删除用户DROP USERDROP USER uh;修改密码ALTER USERALTER USER uh IDENTIFIED BY new;忘记 root 密码跳过授权表skip-grant-tables→ 更新空密码 → 重新设置权限分类管理类、程序类、库表级、数据操作、字段级见上表授权GRANTGRANT priv ON db.* TO uh;查看权限SHOW GRANTSSHOW GRANTS FOR uh;撤销权限REVOKEREVOKE priv ON db.* FROM uh;权限生效FLUSH PRIVILEGES通常自动生效手动刷新远程连接创建带主机 % 或具体 IP 的用户mysql -u user -h server_ip -p下一期预告MySQL 备份与恢复冷备份、逻辑备份、增量备份与二进制日志恢复。