搞懂 MySQL 的用户创建与授权能省下你后半夜的运维电话。我见过太多人栽在权限细节上开发提了个需求说“给我开个测试库账号”你一条 GRANT ALL 甩过去等哪天误删了生产表背锅的就是你。今天这篇就围绕 MySQL 创建用户并授权这条主线从最小权限原则到实际场景的命令写法再到 8.0 和 5.7 之间的坑掰开揉碎讲清楚。不论你是在 Linux 虚拟机里自己搭环境练手还是维护公司线上实例照着做都能少踩几个坑后面也会附排查思路和速查表有需要的建议直接收藏。1. 用户与权限的核心设计思路1.1 为什么要单独建用户而不是一直用 root先说个实际案例。我接手过一套系统之前的人图省事所有应用连数据库都用的 root 账号密码还写在代码仓库里。后来有一次前端配置出错批量更新语句跑错了环境几百条数据直接被覆盖连审计日志都查不出是谁干的因为所有人都是 root。这就是用 root 跑业务的最典型风险权限太大出错没办法追溯安全上更是裸奔。所以 MySQL 建用户这件事不该当成“给个账号能用就行”而要当成一次权限设计来做。合理的做法是每个应用、每个开发者、每个运维脚本都单独建用户只给它们完成工作所必需的最小权限。比如只读报表账号就只给 SELECT某张表的维护账号就只给该表的 INSERT、UPDATE、DELETEDBA 管理账号才需要全局权限。这样做的直接好处有三个。第一责任能追溯每个操作都能定位到具体账号第二出错影响面可控就算某条命令写错了它也只能在授权范围内产生破坏第三降低泄露风险即使某个应用账号密码泄露攻击者拿到的也只是一个受限权限不至于直接拖库。1.2 MySQL 权限模型的基本概念MySQL 的权限验证分两个阶段连接验证和操作验证。连接阶段服务器根据你的主机名或 IP和用户名确认是否有资格连进来操作阶段再根据你发出的每个具体请求去匹配对应权限。权限的存储是分层级的。最顶层是全局层存在mysql.user表里对应GRANT ALL ON *.*这种写法影响所有库表再往下是数据库层存在mysql.db表对应GRANT ALL ON dbname.*只管指定库然后是表层存在mysql.tables_priv对应GRANT SELECT ON dbname.tablename最细还能到列层和存储过程层。这里需要理解一个关键点授权范围越小匹配优先级越高。如果你对某个数据库没有显式授权服务器会继续往上层找看全局权限里有没有如果全局层和库层都没有就拒绝访问。这套模型理解了后面所有授权命令其实都能自己推导出来不用死记硬背。2. 建库、建用户、设密码基础操作全流程2.1 先从建库说起很多初学者会把建库和建用户混在一起其实它们是两件事。建库决定“有没有这个仓库”建用户决定“谁能进仓库、能搬什么东西”。实际操作用一条CREATE DATABASE就能搞定CREATE DATABASE IF NOT EXISTS app_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;字符集和排序规则建议在建库时就定好别等表都建完了再换。utf8mb4是目前的主流选择能存 emoji 和生僻字兼容性远好于老旧的utf8MySQL 8.0 的默认字符集也已经是它了。排序规则里utf8mb4_general_ci性能好一点utf8mb4_0900_ai_ci是 8.0 的默认值大小写不敏感按官方推荐走也不会错。如果你不确定库里有没有数据建议加IF NOT EXISTS前缀避免脚本重复执行时报错。这属于写初始化脚本的基本素养幂等性要考虑进去。2.2 CREATE USER创建用户的完整姿势MySQL 创建用户的标准命令是CREATE USER语法结构如下CREATE USER usernamehost IDENTIFIED BY password;username就是要创建的登录名。host指定允许从哪台机器连接常见取值有localhost仅本机、192.168.1.%某网段、%所有地址。这里强烈不建议一上来就写%尤其是开发者本机调试用的账号能限定 IP 就限定 IP多少安全事件都是从“所有地址可访问”开始的。MySQL 8.0 里CREATE USER后账号默认是密码永不过期和用户不能更改密码两件事的默认关闭状态即默认密码会过期、用户可以自己改密码。如果你要建一个服务账号希望它不要求定期改密可以加CREATE USER app_user192.168.1.% IDENTIFIED BY StrongPss2024 PASSWORD EXPIRE NEVER ACCOUNT LOCK;ACCOUNT LOCK是我个人习惯先锁住账号等授权配置完毕后再ALTER USER ... ACCOUNT UNLOCK解锁。这样能避免出现“账号刚建好、密码就被别人猜到”的窗口期。2.3 密码相关属性和安全建议关于密码策略MySQL 有validate_password组件8.0 默认启用会强制要求密码长度和复杂度。如果你想查当前密码策略执行SHOW VARIABLES LIKE validate_password%;如果只是本地测试环境不想搞太复杂可以临时调低策略但生产环境请保持默认甚至加强。实际开发中我见过太多人在建用户时把密码写在命令行历史里这是大忌。建议用mysql_config_editor或者环境变量注入方式至少别让密码直接暴露在~/.mysql_history里。另外改密码的正确姿势在 8.0 里是:ALTER USER app_user192.168.1.% IDENTIFIED BY NewPass2024;老版本流行的SET PASSWORD FOR xxxxxx PASSWORD(xxx)在 8.0 已经不能用了后面排查问题时要注意版本差异。3. GRANT 授权给用户分配任务的正确方式3.1 先搞清楚有哪些权限类型很多教程上来就教GRANT ALL PRIVILEGES ON *.* TO ...这本质上是造了另一个 root。真要理解授权先认权限类型权限名作用范围说明SELECT表/视图/列查询数据报表账号最常见INSERT表插入数据UPDATE表/列更新数据DELETE表删除数据CREATE库/表创建新库或新表DROP库/表/视图删除结构危险权限ALTER表修改表结构INDEX表创建或删除索引REFERENCES表创建外键CREATE VIEW / SHOW VIEW视图视图相关EXECUTE存储过程/函数执行存储过程TRIGGER表管理触发器GRANT OPTION全局允许该用户给别人授权慎给看起来很多但核心记住一句GRANT具体到什么层级就给什么层级。能用表级解决就不要放宽到库级能用库级解决就不要放宽到全局。3.2 几个典型场景的授权写法场景一给开发同学一个测试库的全部权限但不允许他动其他库GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON app_db.* TO dev_user192.168.1.%;这样他能在app_db里建表、改表、删表但碰不到别的库。注意DROP要不要给取决于团队约定如果希望他连表结构都能自己折腾就给否则别给测试库删表很容易误删。场景二数据分析师只读权限但要能看多个库GRANT SELECT ON app_db.* TO analystlocalhost; GRANT SELECT ON report_db.* TO analystlocalhost;这样一条条授权最后他SHOW DATABASES时只能看到有权限的库看不到别的。场景三应用账号只需要某几张表的增删改查其他表一概不给GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.users TO app_service192.168.1.%; GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.orders TO app_service192.168.1.%;表级授权适合那种一个库里有敏感表、普通表混在一起的情况。比如users表里有手机号和地址报表账号只给orders表的读权限没给users表那么他想查手机号也查不了。3.3 授权后一定要刷新吗这是老生常谈的问题。传统说法是执行FLUSH PRIVILEGES让权限立刻生效但在 MySQL 5.7 和 8.0 中使用CREATE USER、GRANT、REVOKE这类语句修改权限表后权限缓存是自动更新的不需要手动FLUSH。什么情况下才需要FLUSH PRIVILEGES一种是直接操作了mysql.user等系统表修改权限另一种是遇到某些诡异的不生效问题可以试着重载。网上很多老教程还在教“授权后必须 FLUSH”其实是历史遗留习惯。顺手执行一下不算错但要清楚它到底解决什么问题。另外撤销权限是REVOKEREVOKE INSERT ON app_db.* FROM dev_user192.168.1.%;撤销和授权一样都是按层级匹配的。你授权给了app_db.*撤销的时候也要对应给app_db.*不能只撤某张表的。理解这个逻辑能减少很多无效操作。4. 版本差异MySQL 8.0 和 5.7 的几个关键不同4.1 8.0 不再默认使用 mysql_native_password这是个容易被忽略的点。MySQL 8.0 默认认证插件是caching_sha2_password而 5.7 是mysql_native_password。如果你的应用连接工具尤其是老版本 JDBC 驱动、老版本 Navicat不支持新插件就会报“Authentication plugin caching_sha2_password cannot be loaded”这类错误。解决办法有两种。一种是升级客户端驱动这是最推荐的做法另一种是兼容性调整在创建用户时指定老插件CREATE USER legacy_userlocalhost IDENTIFIED WITH mysql_native_password BY Password123;但要注意mysql_native_password在 8.0 中标记为废弃未来版本可能移除。如果只是临时兼容没问题如果长期使用建议推动应用侧升级驱动。4.2 GRANT 和 CREATE USER 的合并与拆分MySQL 5.7 及更早版本你可以用一条GRANT ALL ON *.* TO userhost IDENTIFIED BY password顺手把用户建了、密码也设了。但 8.0 里这种写法不行了IDENTIFIED BY子句已经从GRANT语法里移除必须先用CREATE USER建用户再用GRANT授权。别小看这个变化。很多从 5.7 升到 8.0 的兄弟直接拿旧脚本跑报错You have an error in your SQL syntax一脸懵。正确姿势就是两步走-- 8.0 里先建用户 CREATE USER new_userlocalhost IDENTIFIED BY Password123; -- 再授权 GRANT SELECT ON app_db.* TO new_userlocalhost;4.3 查看用户权限和元数据的差异查看一个用户有哪些权限各版本通用写法是SHOW GRANTS FOR usernamehost;如果是查看当前登录用户的权限直接用SHOW GRANTS FOR CURRENT_USER();。8.0 里mysql.user表结构也有调整例如password_last_changed、account_locked这些字段比较好用查账号状态时直接看这张表SELECT user, host, account_locked, password_expired FROM mysql.user WHERE user app_user;MySQL 8.0 还引入了角色role概念可以把一组权限打包成一个角色再赋给多个用户。这在团队账号管理时很实用比如建一个readonly_role授予只读权限然后GRANT readonly_role TO user1localhost比挨个给权限好维护得多。5. 远程访问配置让用户从别的主机连进来5.1 建一个能被远程连接的用户默认情况下MySQL 的账号基本都限制在localhost因为多数人的数据库和应用部署在同一台机器。但开发环境、测试环境经常要远程连过去这时候就要在用户的主机范围上做文章。如果你要创建一个能从任意 IP 连接的用户可以这样CREATE USER remote_app% IDENTIFIED BY RemotePass123; GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO remote_app%; FLUSH PRIVILEGES;注意%是通配符代表所有主机但它匹配不到localhost。也就是说如果你本机想用mysql -uremote_app -p去连会发现连不上因为localhost被视为单独入口。解决办法就是额外建一个remote_applocalhost账号或者本机使用时指定-h 127.0.0.1。这个细节踩坑率极高。5.2 远程连接不上的排查思路远程连接失败90% 是三类问题账号没授权对应主机、MySQL 没监听外部地址、防火墙拦截。第一步看 MySQL 监听地址。bind-address如果设置成127.0.0.1外部自然连不上。Linux 下可以检查配置文件或者在 MySQL 里执行SHOW VARIABLES LIKE bind_address;。如果值是127.0.0.1改成0.0.0.0再重启服务。第二步确认用户主机匹配。SELECT user, host FROM mysql.user;看看有没有包含你来源 IP 的条目。比如你从192.168.31.88去连账号是remote_app%这是匹配的如果账号是remote_app192.168.1.%那你的来源不在范围内必然连不上。第三步检查操作系统防火墙。Linux 上如果你开了 firewalld需要放行 3306 端口firewall-cmd --permanent --add-port3306/tcp firewall-cmd --reload不过这一步有个前提你真的需要对外网暴露 MySQL 吗如果只是内网访问建议把 bind-address 和账号 host 都限制在内网网段别图省事全开%和0.0.0.0。5.3 常见工具的连接测试命令行测试连接最直接mysql -h 192.168.1.100 -u remote_app -p -P 3306如果提示Access denied优先查账号密码和主机匹配如果卡住不动优先查防火墙和网络连通性用telnet 192.168.1.100 3306看端口通不通。用 Navicat 或 MySQL Workbench 连不上时也是同一套排查逻辑工具只是调用了同样的协议问题根源大多在服务端。6. 常见问题与排错经验6.1 高频问题速查表现象可能原因解决办法授权成功后新连接仍无权限授权后未重载权限缓存执行FLUSH PRIVILEGESAccess denied for user xy账号、主机、密码三者不匹配检查mysql.user表对应记录远程连不上bind-address 绑定 127.0.0.1修改配置并重启 MySQL远程连不上但本地能连防火墙未放行 3306放行端口或改走 SSH 隧道8.0 连接报认证插件错误客户端驱动太老升级驱动或临时指定 mysql_native_passwordUSING PASSWORD()报错8.0 不支持旧式改密码语法改用ALTER USER6.2 踩过的坑和实战经验先讲一个localhost和%的坑。有次同事反馈说新创建的用户本机登录没问题程序远程连却报权限不足。我查了一圈发现他只建了userlocalhost程序那台机器的 IP 不在授权范围内。后来补了user192.168.1.%才解决。这种事特别容易发生在建账号时——大多数人下意识觉得自己只在服务器上连就写localhost回头应用部署在其他机器上就翻车。再讲一个最小权限的坑。有团队为了省事把所有应用账号都赋予了全局的CREATE和DROP权限结果有个人误执行了DROP DATABASE。我当时给的建议是把应用账号的DROP全部收回只保留DROP给 DBA 专用账号。很多人觉得少一个权限就多一次麻烦但实际生产环境里省掉这次麻烦的代价可能是一整年的数据。还有个细节是GRANT OPTION。这个权限的意思是“允许这个用户把他拥有的权限再转授给别人”。一旦你把它赋给某个账号该账号操作者就可以给其他人开权限。DBA 团队之外的人一律不要给这个权限。我见过有人为了省事GRANT ALL PRIVILEGES ON *.* TO app% WITH GRANT OPTION结果应用账号密码一泄露攻击者直接给自己开了个超级用户。这种场景属于安全事故的高级版本只要权限设计合理基本可以避免。6.3 权限回收之后的事REVOKE撤权限简单但要说一个容易忽略的点撤销权限后已建立的连接不会立刻断掉它们保持原来的会话权限直到连接关闭。如果你要彻底踢掉某个用户的连接需要配合-- 查出该用户的进程 SHOW PROCESSLIST; -- 逐个 kill 对应 id KILL id;这在应对已泄露账号时特别重要。光改密码还不够已连接会话还在跑要把进程也断掉才安全。平时做权限变更演练时这套流程也是 DBA 必会操作。7. 我对这套权限管理的经验之谈用 MySQL 这么多年我最大的感受是权限管理不难难的是你愿不愿意在建账号时多想一步。每次申请账号先问三个问题谁要用从哪里连要干什么把这三个问题的答案对应到用户、host、权限上一套合理的授权方案基本就出来了。我自己习惯的做法是写一个初始化脚本模板固定好字符集、用户命名规范、密码复杂度策略、权限清单然后按环境复制修改。比如开发环境可能宽松一点给到库级权限测试环境严格一点只给必要表生产环境所有变更走工单流程脚本里绝不出现GRANT ALL ON *.*。最后再分享一个小技巧建账号前先查一下用户是否已存在避免重复执行脚本报错。SELECT user, host FROM mysql.user WHERE user app_user;返回空再执行CREATE USER。这个动作虽然简单但在自动化部署场景里能省不少排错时间。说实话把基础动作做扎实比掌握各种花哨技巧更能在关键时刻救命。