最近帮一个团队排查线上连接问题绕了一圈发现不是网络也不是配置的锅而是新建的账号压根没拿到权限。类似这种MySQL用户管理的坑几乎每个用MySQL的人都会踩一遍。今天就把这块内容系统地整理一遍从用户创建到权限分配从常用命令到生产环境的实操套路该说的细节都会说到尤其是那些官方文档里不会写明白的坑。这篇文章不是什么从入门到精通的大部头就是把MySQL用户管理这件事讲透让开发、运维、甚至自己搭环境玩的人看完都能直接上手。1. MySQL用户管理的整体设计思路1.1 先搞清楚MySQL的用户到底是谁很多初学者对MySQL用户的理解就是一个名字加一个密码其实不对。MySQL里一个用户的完整身份是用户名主机的组合用户名只是前半部分后面那个主机限制同样关键。举个例子你创建了app%这个账号可以从任何主机连接。但如果你创建的是applocalhost那就只有本机才能用这个账号连上来。很多人排查账号密码明明对为什么连不上的问题八成就是栽在这个主机限定上。另外MySQL的用户认证信息存储在系统库mysql下的user表里密码不是明文而是经过哈希处理的摘要串。你可以查询这张表来确认账号情况SELECT user, host, plugin, account_locked FROM mysql.user;如果哪天你发现用户列表里出现一堆rootlocalhost、root127.0.0.1、root::1不用慌这不是重复而是MySQL把不同来源的root连接都当成了独立条目来看待。1.2 权限体系才是用户管理的核心用户管理如果只是建账号、设密码那就太简单了。真正麻烦的是权限体系——你可以把权限想象成一把把钥匙每一把钥匙能打开不同的门。MySQL的权限是分层级的从大到小排列全局权限对整个MySQL实例生效写在mysql.user表里授权语句里通常写成ON *.*库级权限对某个数据库生效写在mysql.db表里授权语句里形如ON db_name.*表级权限对某张表生效写在mysql.tables_priv表里写法是ON db_name.table_name列级权限对某个表的某几列生效写在mysql.columns_priv表里精确到列名存储过程和函数权限写在mysql.procs_priv表里授权的时候系统会按照这个层级去匹配。一个用户即使没有全局权限只要在某个库上有库级权限就能操作这个库里的表。理解了这个分层结构你才能明白为什么有些账号能看所有库有些账号只能看一个库里的几张表。市面上那些MySQL可视化工具其实也就是在帮你拼GRANT语句底层逻辑还是这一套。1.3 为什么用户管理要提前规划我在实际工作中见过太多先跑起来再说的项目开发环境共用root账号生产环境也共用root账号结果某天一个误操作把整个数据库删了连数据恢复机制都没来得及建立。用户管理最核心的目标是最小权限原则也就是说一个账号只给它完成工作所必需的最小权限。这样做有三个好处降低误操作风险。没给DELETE权限的账号再怎么手滑也删不掉数据。提升安全性。数据库被入侵时攻击者能拿到的权限上限被限制住了。方便排查问题。每个账号对应一个业务模块出了问题能快速定位是谁干的。这套规划最好在数据库初始化阶段就做。如果你接手的是一个已经跑了几年的老库也没关系可以后续慢慢梳理和收敛权限。本文后面会专门写一套生产环境的账号规划模板。2. 用户创建与管理不出错的基础操作2.1 创建用户的完整语法与常见写法创建用户的标准语法在MySQL 5.7和8.0里略有差异但核心逻辑是相同的。最基本的写法是CREATE USER 用户名主机 IDENTIFIED BY 密码;这里有几个关键点需要详细说说主机部分怎么写。localhost限定只能本机连接192.168.1.%限定只能从192.168.1.x网段连接%代表不限制主机任何IP都能连。如果业务服务器和数据库服务器分离部署建议精确到IP或网段而不是偷懒一律用%。密码的存放方式。8.0版本默认使用了caching_sha2_password插件的认证方式而5.7时代默认是mysql_native_password。很多老客户端连不上MySQL 8.0就是因为不支持新的认证插件这时候需要在创建用户时指定CREATE USER app% IDENTIFIED WITH mysql_native_password BY 你的密码;不过这里我建议尽量升级客户端而不是降级认证方式毕竟caching_sha2_password更安全。常见写法整理一下大致如下-- 最简单的创建方式 CREATE USER devlocalhost IDENTIFIED BY Dev123456; -- 指定认证插件兼容老客户端 CREATE USER app192.168.1.% IDENTIFIED WITH mysql_native_password BY App666888; -- 创建用户并同时授权开发环境常用生产环境慎用 GRANT ALL PRIVILEGES ON mydb.* TO devlocalhost IDENTIFIED BY Dev123456;注意MySQL 8.0 中GRANT语句不再支持在授权的同时创建用户需要先CREATE USER再GRANT。如果你用的是8.0用上面第三个语句会直接报语法错误。2.2 查看用户与权限信息创建完用户之后想确认一下创建得对不对有两个查询方向第一看用户的账号信息SELECT user, host, plugin, account_locked, password_expired FROM mysql.user;第二看用户的权限明细-- 查看某个用户有哪些权限 SHOW GRANTS FOR app192.168.1.%; -- 如果你不确定主机怎么写先查一下 SELECT user, host FROM mysql.user WHERE user app;SHOW GRANTS这个命令一定要养成习惯。排查权限问题时它比任何工具都直接。它会把该用户在当前节点上拥有的所有权限一条一条列出来包括角色信息。有一个容易忽略的点SHOW GRANTS FOR abc和SHOW GRANTS FOR abc%结果可能完全不同因为前者会默认补上abc%但实际生效的账号可能是abclocalhost。所以查询的时候最好把主机部分写全了。另外如果你想查看当前会话用户的权限可以直接SHOW GRANTS;还有一点8.0版本引入了角色Role概念后直接看SHOW GRANTS可能只会显示角色名称而不是展开后的具体权限。你需要加一句SHOW GRANTS FOR app% USING read_only_role;或者干脆设置登录时自动激活所有角色这个后面章节细说。2.3 修改密码与密码过期策略修改密码的常用语法在8.0和5.7里基本一致-- 修改当前登录用户的密码 ALTER USER USER() IDENTIFIED BY 新密码; -- 修改指定用户的密码 ALTER USER app% IDENTIFIED BY 新密码;如果是5.7版本还可以用SET PASSWORD FOR app% PASSWORD(新密码);但8.0里已经移除了PASSWORD()函数所以统一推荐ALTER USER写法。这里必须提醒一个老坑你有权限改别人的密码前提是你自己拥有对这个用户的CREATE USER或UPDATE权限否则会收到ERROR 1044 (42000): Access denied的报错。密码过期策略在生产环境很实用。你可以让某个账号每隔一段时间必须改一次密码-- 让密码90天后过期 ALTER USER app% PASSWORD EXPIRE INTERVAL 90 DAY; -- 立刻让密码失效下次登录必须改密码 ALTER USER app% PASSWORD EXPIRE; -- 取消过期限制 ALTER USER app% PASSWORD EXPIRE NEVER;如果你不想让数据库里的账号密码长期不动又怕大家忘记改可以全局设置默认密码过期策略在my.cnf里加default_password_lifetime 902.4 删除与锁定用户删用户很简单DROP USER app%;但有些情况下你只是想临时停用一个账号而不是删除比如离职人员交接期、某个应用正在维护中。这时候用锁定更稳妥-- 锁定账号 ALTER USER app% ACCOUNT LOCK; -- 解锁账号 ALTER USER app% ACCOUNT UNLOCK;这个功能我经常用在离职员工交接但发现还有定时任务在跑的场景。先锁定观察一段时间确认没有任务依赖再删除比直接DROP USER安全得多。另外提醒一下DROP USER一个不存在的用户会报ERROR 1396 (HY000): Operation DROP USER failed。结合前面提到的先查一下mysql.user表里账号的完整 host 信息再删百试百灵。3. 权限授权与回收掌握最小权限原则3.1 常见权限类型与适用场景MySQL的权限类型非常多但日常工作中常用的就那么几种。我整理了一个表格方便快速对照权限名称适用范围说明SELECT库/表/列查询数据只读账号必备INSERT库/表/列插入数据UPDATE库/表/列更新数据DELETE库/表/列删除数据CREATE库/表创建库或表DROP库/表删除库或表高危权限ALTER表修改表结构INDEX表创建或删除索引REFERENCES表/列外键约束的参照权限CREATE VIEW视图创建视图SHOW VIEW视图查看视图定义CREATE ROUTINE存储过程/函数创建存储过程、函数ALTER ROUTINE存储过程/函数修改或删除存储过程、函数EXECUTE存储过程/函数执行存储过程、函数PROCESS全局查看所有线程信息DBA排查慢查询常用RELOAD全局执行 FLUSH 操作REPLICATION SLAVE全局主从复制从库拉取二进制日志需要REPLICATION CLIENT全局查看主从状态SUPER全局超级权限8.0中被拆分精简高权限操作ALL PRIVILEGES全局/库/表除 GRANT OPTION 以外的所有权限需要注意的是GRANT OPTION是单独的权限它的作用是把授权的能力授予用户也就是说允许你把拥有的权限转授给其他账号。默认GRANT ALL并不会授予GRANT OPTION除非你显式指定。3.2 GRANT与REVOKE的语法与常见组合授权和回收权限的基本语法-- 授权 GRANT 权限列表 ON 对象 TO 用户主机; -- 回收权限 REVOKE 权限列表 ON 对象 FROM 用户主机;举个例子要给一个只读账号授权GRANT SELECT ON mydb.* TO readonly%;要给应用账号授权增删改查GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app%;要给某个表单独的权限GRANT SELECT ON mydb.orders TO report%;如果想一次性把所有权限都给某个库开发环境常用GRANT ALL PRIVILEGES ON mydb.* TO devlocalhost;但真不建议动态地把ALL PRIVILEGES开到生产环境去除非这个库就是私人玩具。回收权限要注意一个点如果你之前给了ALL PRIVILEGES想回收部分权限不能只写REVOKE SELECT那样会报REVOKE ALL PRIVILEGES的写法问题。正确做法是把之前授权的内容完整地回收掉再重新授权需要的部分。比如-- 错误的做法可能报错或与预期不符 REVOKE ALL PRIVILEGES ON mydb.* FROM devlocalhost; -- 正确的做法 REVOKE SELECT, INSERT, UPDATE, DELETE ON mydb.* FROM devlocalhost;这里想强调一下MySQL的权限表按层级独立记录授权和回收都是增量叠加。比如你先授权了SELECT ON mydb.*又授权了SELECT ON mydb.orders那么用户对orders表有两层SELECT权限但后面回收mydb.*的SELECT时表级权限依然保留。3.3 WITH GRANT OPTION授权链的风险WITH GRANT OPTION是权限管理里一个挺危险但是又经常被误解的选项。GRANT SELECT ON mydb.* TO user_a% WITH GRANT OPTION;执行完后user_a不仅拥有mydb库的SELECT权限还能把自己拥有的权限转授给其他人。也就是说它可以创建一个新用户并给这个新用户赋mydb.*的SELECT权限。这在创建DBA类账号时可能有用但给普通业务账号加上就非常危险。万一账号泄露攻击者可以自己创建账号你回收了原账号也无济于事因为新的账号还在。所以我的建议很明确业务应用账号一律不要加WITH GRANT OPTION哪怕是管理员账号也建议单独用一个管理账号不要和业务账号混在一起。3.4 用角色Role简化权限管理MySQL 8.0开始支持角色Role这是一个非常实用的功能本质上是把一组权限打包成一个集合然后把集合赋给用户。好比公司里开发是一个角色拥有开发需要的一组权限DBA是另一个角色拥有数据库管理需要的一组权限。创建角色的基础操作-- 创建角色 CREATE ROLE read_only_role; -- 给角色授予权限 GRANT SELECT ON mydb.* TO read_only_role; -- 把角色授予用户 GRANT read_only_role TO app_read%;需要注意的是角色授予用户后默认不会自动激活需要设置-- 设置某用户登录后激活所有角色 SET DEFAULT ROLE ALL TO app_read%; -- 或者全局开启所有用户登录时自动激活所有角色 SET GLOBAL activate_all_roles_on_login ON;推荐后一种方式省心省力。否则你会遇到一个很奇怪的场景SHOW GRANTS里能看到角色但实际查询时权限不生效就是因为角色没有被激活。角色还有一个好处是批量管理方便。比如你新招了一个数据分析师他需要和之前的分析师一样的权限只需要GRANT analyst_role TO new_user%; SET DEFAULT ROLE ALL TO new_user%;不用再一条条地GRANT SELECT/INSERT/UPDATE...省了不少事。4. 生产环境用户管理实操套路4.1 按业务场景建立账号把业务和账账号类型理清楚之后你在生产环境的用户管理才不容易乱。我这边的经验是建一个账号规划表在一个新环境初始化时直接照着执行。拿一个比较典型的Web应用系统举例通常可以规划成账号类型用户名主机限制权限范围说明应用主账号app_web应用服务器IP网段业务库的SELECT/INSERT/UPDATE/DELETE给后端代码连接用只读分析账号readonly_report报表服务器IP业务库的SELECT给BI报表、数据导出用备份账号backup_opslocalhost或备份机IPRELOAD、SELECT、REPLICATION CLIENT等给备份工具专用DBA管理账号dba_admin跳板机IP全局权限GRANT OPTION给DBA日常维护用结构变更账号ddl_deployCICD服务器IPALTER、CREATE、INDEX、DROP风险操作给自动化发版脚本用操作完回收实操时可以把这些CREATE USER和GRANT语句都写到初始化脚本里作为一套标准交付物。新环境建库时直接执行比手动一个个点省心多了。开发环境可以适当放宽权限但也不能反正就是内部用就乱来。我见过太多开发环境的root密码全网公用的案例某天一个测试脚本误连接了生产库后果自己体会。4.2 主机限制与远程访问安全%是很多新手喜欢用的通配符因为省事。但生产环境里主机限制是一道重要的安全防线。举个例子CREATE USER app% IDENTIFIED BY password;这条指令创建了一个可以从任何IP连接的账号。万一密码泄露攻击者可以从任何地方尝试连接风险极大。更合理的方式是绑定IP或网段CREATE USER app192.168.10.% IDENTIFIED BY password;这样只有来自192.168.10.x网段的机器能连。就算是开发环境也建议用类似10.10.%的段位限制不要直接开成%。MySQL还有一个容易忽略的坑如果你只创建了app%但客户端本机用-h localhost连接时可能不会匹配到%这时候需要专门创建一个applocalhost。很多人在Docker里跑MySQL时容器内连接死活认证失败就是因为容器内的连接来源和外部连接来源被系统当成不同的用户匹配了。4.3 密码安全与validate_password插件MySQL 8.0默认启用了密码校验组件validate_password它的作用就是检查你设置的密码是否符合安全策略。默认情况下密码必须包含大小写字母、数字和特殊字符长度也有最低要求。查看和修改密码策略相关的参数SHOW VARIABLES LIKE validate_password%;输出里有几个关键参数validate_password.length密码最小长度validate_password.policy密码强度策略LOW/MEDIUM/STRONGvalidate_password.number_count数字至少出现次数validate_password.mixed_case_count大小写字母至少出现次数validate_password.special_char_count特殊字符至少出现次数对于测试环境如果你觉得这个组件太烦可以调整参数SET GLOBAL validate_password.policy LOW; SET GLOBAL validate_password.length 6;不过生产环境还是建议保持默认的强密码策略或者往上调不建议降级。还有一个经验之谈正式环境不要用一个密码打天下每个账号尽量独立密码定期更换。像Vault、AWS Secrets Manager这类密钥管理工具可以自动化密码轮换如果有条件可以引入没有的话也可以做一个定期提醒的脚本。4.4 Docker MySQL实例中的用户管理注意点现在用Docker跑MySQL的人越来越多了用户管理有一些容器环境特有的坑单独拿出来说一下。用docker run启MySQL时可以通过环境变量来初始化用户docker run -d \ --name mysql \ -e MYSQL_ROOT_PASSWORDroot_password \ -e MYSQL_DATABASEmyapp \ -e MYSQL_USERapp \ -e MYSQL_PASSWORDapp_password \ -p 3306:3306 \ mysql:8.0这里MYSQL_USER和MYSQL_PASSWORD会在数据库初始化时自动创建一个app%账号并且只授予MYSQL_DATABASE这个库的全部权限。注意这个机制只在数据目录首次初始化时生效。如果你挂载了已有的数据卷再改MYSQL_USER是没用的。很多人在Docker里改了环境变量发现账号没变就是这个原因。Docker容器里忘记密码后的操作顺序是停止容器把启动命令改成增加--skip-grant-tables参数有些镜像会默认忽略这个参数需要加--skip-grant-tables1。重启容器此时无需密码就能进MySQL。修改密码然后去掉跳过授权参数重启容器。这个处理逻辑和普通环境一样只是涉及Docker的命令层操作。具体细节在下一章讲密码忘记问题时会再展开。容器和宿主机之间的权限匹配也要注意。如果你从宿主机用Navicat连接MySQL容器连接来源在MySQL看来是宿主机的IP不是容器内的IP所以用户的主机限制要根据实际访问路径来确定不是容器启动参数里写什么就一定唯一。5. 常见问题与排查技巧实录5.1 常见错误速查表日常运维中关于用户管理和权限的错误码大概就那么几个我把常见的整理成一张速查表错误码错误信息常见原因排查方向1045Access denied for user密码错误或主机被限制检查密码、host匹配、认证插件1044Access denied for database没有该库的访问权限查看用户权限是否包含目标库1130Host is not allowed to connect用户没有匹配该来源主机的记录检查mysql.user里的host字段1142command denied to user对某对象的特定操作没有权限检查权限层级1396Operation ... failed用户不存在或已存在确认账号完整身份和状态1862Your password has expired密码已过期重置密码或修改过期策略2002Cant connect to local MySQL serversocket文件或服务未启动检查mysqld进程和socket路径2013Lost connection during query网络中断或超时检查网络、超时参数我曾经被一个ERROR 1045卡住了一下午最后发现是连接串里把%转义成了%25导致主机匹配不上排查了半天。5.2 案例Access denied 的排查流程假设你现在遇到了这个报错ERROR 1045 (28000): Access denied for user app192.168.1.50 (using password: YES)完整的排查思路是第一步确认用户是否存在。在服务器上用root登录执行SELECT user, host, authentication_string FROM mysql.user WHERE user app;如果查询结果为空说明账号没建对或者你连接的数据库不是目标实例。如果查询结果里有app%但没有app192.168.1.%那就要考虑主机匹配问题。第二步确认密码是否正确。用mysql -u app -p在目标来源机上测试或者换一个明确的主机限制试一下。如果换成localhost能连远程连不上那就是主机限制的原因。第三步确认认证插件是否兼容。老客户端连接8.0时经常遇到这个坑ERROR 2059 (HY000): Authentication plugin caching_sha2_password cannot be loaded解决方法有两个升级客户端驱动或把用户的认证插件改为mysql_native_password。第四步确认mysql.user表里是否有多个匹配条目。MySQL在匹配用户时会按精确到模糊的顺序applocalhost和app%不是一回事注意区分。5.3 案例权限明明给了为什么还是没生效这是另一个非常高频的坑。你执行了GRANT SELECT ON mydb.* TO app%;但用app登录后查询还是提示无权限或者SHOW GRANTS里能看到权限但实际执行SQL时被拒绝。第一个要检查的是角色有没有激活。8.0里如果权限是通过角色授予的而角色的默认状态是未激活那实际生效的权限列表就是空的。检查方式SELECT CURRENT_ROLE();如果是空值说明当前会话没有激活任何角色。可以执行SET ROLE ALL;或者直接设置登录时自动激活SET GLOBAL activate_all_roles_on_login ON;第二个要检查的是sql_mode或大小写敏感问题。表名大小写、库名的大小写在某些操作下会影响权限匹配。Linux上MySQL的表名默认区分大小写Windows默认不区分权限匹配时会受这个影响。第三个是权限继承问题。前面说过库级、表级、列级权限是分层记录的某个权限在某一层没有不表示在下一层有。比如有用户对mydb库有SELECT但你对mydb.orders授予了更细的列权限操作时总权限是取交集不是取并集。第四个容易被忽略的是连接池问题。应用连接池里老连接持有的是旧权限快照修改权限后新连接生效但老连接还在用旧权限。遇到改完权限还是不行的反馈先让应用重启连接池试试。5.4 日常维护建议与实践总结写代码技术的人常犯一个毛病只关注业务的实现忽略了数据库账号层面的维护。但实际上用户管理的健壮性直接决定了数据库的安全底线。几点常规建议分享给大家建议一定期审计账号。每季度做一次账号复盘确认哪些账号还在用、哪些账号权限过大了。用一条SQL就能列出所有账号和最近状态SELECT user, host, plugin, account_locked, password_expired FROM mysql.user WHERE user NOT IN (mysql.session, mysql.sys, root);把业务账号清点后对应的负责人确认一遍长期无人认领的直接锁定。建议二统一权限模板。把常用的权限组合做成脚本或SQL模板比如只读模板、业务读写模板、备份模板需要时直接引用减少临时发挥带来的错误。建议三保留变更记录。每次GRANT、REVOKE、CREATE USER操作都记录下来方便事后审计。Git里放一个SQL变更目录是我推荐的做法比在聊天记录里找历史靠谱得多。建议四权限回收要算时间账。给临时账号设一个明确的过期时间到期后自动锁定或删除。写一个简单的定时任务去检查password_expired字段和账号的锁定状态可以避免大量遗留账号堆积。回到开头那个同事遇到的问题最后查明就是GRANT给了错的主机导致程序所在服务器无法匹配到权限。折腾了一上午其实就是创建用户时多写了一个%和少写了一个网段的问题。MySQL用户管理这件事平时看着简单一出事都是火烧眉毛的场面。从账号规划、主机限制、密码策略、权限回收这几个方向做好基本功大部分问题都能在设计阶段提前规避。