接手过一套历史遗留MySQL环境的人大概都懂那种感觉打开数据库一看业务连接用的是root线上库的账号权限五花八门离职同事的账号还稳稳挂在mysql.user里密码是多少没人记得也没人敢动。MySQL用户管理这个事表面上就是建号、授权、改密、删除几个动作但真正到了生产环境牵扯出来的问题一个比一个麻烦。这篇文章我把这几年在用户管理上踩过的坑、总结出的套路以及背后的权限模型逻辑一次讲清楚适合刚入门MySQL的开发者也适合正在被账号体系折腾的运维和DBA。1. 用户权限体系一张user表背后的完整权限模型很多人以为MySQL的权限管理就是mysql.user表里的一条记录其实这只是入口。理解完整的权限模型才是后面所有操作的根基。1.1 权限不是平铺的而是逐层收紧的五级模型MySQL的授权数据分散在多张系统表中按粒度从粗到细依次是mysql.user全局权限作用于所有数据库mysql.db数据库级权限作用于指定库mysql.tables_priv表级权限作用于指定表mysql.columns_priv列级权限作用于指定表的指定列mysql.routines_priv存储过程/函数级权限一个连接进来时MySQL会从全局权限开始查再看库级、表级、列级逐层往下匹配。权限的生效方式是叠加而不是覆盖只要任何一层给了权限这个权限就生效。比如你在mysql.db里给了某个库的SELECT在mysql.tables_priv里没给某张表的任何权限这张表依然可以查——因为库级权限已经覆盖到了。但有个例外要注意管理类权限SUPER、RELOAD、PROCESS、SHUTDOWN等只存在于全局层不能在db表或者tables_priv里授权。所以如果你看到某个用户需要PROCESS权限比如监控数据库线程状态那只能在全局层给没有折中方案。这也是为什么监控类账号往往需要SELECT ON *.*或者PROCESS ON *.*这种看起来挺宽的授权。1.2 host字段的匹配规则localhost、%与网段到底怎么选MySQL的账号是由user host两部分组成的同一用户名在不同host下可以拥有完全不同的权限。比如CREATE USER applocalhost IDENTIFIED BY pass_strong_1; CREATE USER app192.168.1.% IDENTIFIED BY pass_strong_2;这是两个完全独立的账号互不影响。host的取值从小到大依次是localhost、具体IP如192.168.1.10、网段如192.168.1.%、任意主机%。匹配优先级不是范围越小越优先那么简单MySQL会先精确匹配没精确匹配时按host的具体程度排序。实际排障时你只需要记住一个场景如果同时存在applocalhost和app%本机通过socket登录会命中applocalhost远程连接会命中app%。你改了远程账号的密码本机登录不受影响反之一半的排障时间都容易卡在这里。还有个大坑如果只创建了app%没有任何localhost账号那么在本机上执行mysql -uapp -p连接反而可能失败——因为默认的socket连接匹配的是localhost或空host匹配不到就会去找匿名用户。有些MySQL安装包自带匿名用户User字段为空字符串一旦匹配到匿名用户登录后的身份就不是你了权限也被限制得莫名其妙。建议一安装完就清掉匿名用户DELETE FROM mysql.user WHERE User; FLUSH PRIVILEGES;1.3 认证插件演进5.7老账号连接8.0报2059的根因MySQL 8.0把默认认证插件从mysql_native_password换成了caching_sha2_password这是很多人升级后遇到的第一个坎。连接时报错ERROR 2059 (HY000): Authentication plugin caching_sha2_password cannot be loaded原因是老客户端比如旧版Navicat、老版本PHP的mysqlnd扩展、某些旧语言驱动只实现了mysql_native_password的握手流程没有caching_sha2_password的实现服务端发起该插件认证时客户端直接傻眼。解决办法有三条路升级你的客户端驱动这是治本的办法单个老账号降级认证插件ALTER USER app% IDENTIFIED WITH mysql_native_password BY 新密码;全局改回老插件不推荐但迁移期确实有人这么干# my.cnf [mysqld] default_authentication_pluginmysql_native_password修改后需要重启MySQL。新创建的账号会用回老插件但已经创建的账号不受影响。我自己在升级到8.0时是这么处理的先把所有存量账号批量改成mysql_native_password保证业务方有充足时间升级驱动随后在客户端版本全部达标后再用一个月时间逐个把账号改成caching_sha2_password。整个切换过程隔了差不多两个发布周期稳得很。生产环境不要追求一步到位的切换兼容性过渡比安全理想主义更重要。2. 建号授权的标准动作从CREATE USER到最小权限落地这一节说流程但不说死流程重点是让你明白每一步为什么这么做。2.1 8.0与5.7在创建用户上的语法分水岭MySQL 5.7及以前一句GRANT可以同时完成建号和授权-- 5.7写法 GRANT SELECT ON mydb.* TO app% IDENTIFIED BY password123;这句SQL在8.0里直接报语法错误。8.0把创建账号和授予权限彻底分开了必须分两步CREATE USER app% IDENTIFIED BY password123; GRANT SELECT ON mydb.* TO app%;我第一次从5.7升到8.0时一堆老脚本全挂在这上面。升级前如果有一批自动化建号脚本建议先检查有没有依赖GRANT自动建号的逻辑否则会看到满屏ERROR 1064 (42000)。创建用户时还有个小细节值得注意密码里的特殊字符。单引号、双引号、反斜杠在密码串里必须正确处理。一个比较稳的做法是尽量避开这些字符只用大小写字母、数字和#%^*_这类相对安全的符号。如果非要存带单引号的密码SQL里要写成CREATE USER app% IDENTIFIED BY it\s_pass;这种写法在脚本里特别容易出问题调试起来也难受能避免就避免。2.2 不同角色的授权组合只读、读写、DDL、备份最小权限原则不是一句口号落实到具体账号上要按用途拆分。我常用的角色授权模板如下账号用途授权命令说明只读分析账号GRANT SELECT ON report_db.* TO report192.168.10.%只能查询不能写入应用运行时账号GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO app%应用正常读写不给DDL权限8.0中默认自动创建临时表权限不定必要时加CREATE TEMPORARY TABLES变更发布账号GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, INDEX, DROP, REFERENCES ON app_db.* TO deploy%用于执行数据库变更脚本备份账号GRANT SELECT, SHOW VIEW, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO backup%逻辑备份所需物理备份还可能需要BACKUP_ADMIN8.0监控账号GRANT PROCESS, REPLICATION CLIENT ON *.* TO monitor%查看连接状态、主从状态DBA管理账号GRANT ALL PRIVILEGES ON *.* TO dba% WITH GRANT OPTION仅供DBA使用不共享给业务方注意几点业务账号不要给DDL权限。应用上线后唯一能改表结构的入口应该走变更发布账号这样DBA可以审计。备份账号需要RELOAD是因为mysqldump默认加锁需要它缺了会报Access denied; you need the RELOAD privilege。只读账号如果涉及导出数据到本地比如SELECT INTO OUTFILE还要额外授权FILE但生产环境尽量不要开。2.3 FLUSH PRIVILEGES到底什么时候要用这是网上吵得最多的问题之一其实原理很简单。MySQL启动时把权限表读入内存之后所有权限判断直接看内存。CREATE USER、GRANT、ALTER USER这类账号管理语句在操作时已经同步修改了内存中的权限缓存所以不需要再执行FLUSH PRIVILEGES。但如果你直接操作了系统表比如UPDATE mysql.user SET Host192.168.1.% WHERE Userapp; DELETE FROM mysql.user WHERE Userold_user AND Hostlocalhost;这些语句只改了磁盘上的数据内存里的权限缓存没有变必须执行FLUSH PRIVILEGES才能让它们生效。所以结论很简单用官方账号管理语句不用flush手改grant表必须flush。生产环境里我从来不直接UPDATE mysql.user宁可多敲几句ALTER USER省得哪次忘了flush还把权限弄得神不知鬼不觉。2.4 用SHOW GRANTS验证账号授权是否精准授权完成后不要急着交付先验证SHOW GRANTS FOR app%;输出会列出账号下的所有全局、库级、表级权限。重点检查有没有权限明显超出用途比如只读账号带了UPDATE、有没有多账号共享密码、GRANT OPTION有没有漏关。更严格的验证是实际用这个账号登录执行一条有权限的SQL和一条无权限的SQL确认反馈符合预期。这一步能拦截大多数授权表看起来对实际连不上的问题。注意MySQL自带mysql.sys、mysql.session等系统保留账号千万别去删除或者修改它们的host否则可能导致系统组件异常。动了这些账号的密码有时候比丢root还麻烦。3. 用户生命周期管理改密、锁定、回收与定期审计账号不是建完就完事了从改密到封禁再到定期复查每一步都有讲究。3.1 改密码的几个姿势和它们之间的差异改密码的方法很多我用下来最推荐的是ALTER USERALTER USER app% IDENTIFIED BY New_Pass_456!;这条在5.7和8.0都可用改完立刻生效不需要flush。注意8.0的语法不要把PASSWORD()函数包在里面8.0已经移除了这个函数写了会报错。等价的老式写法是SET PASSWORD FOR app% New_Pass_456!;命令行下还有个mysqladmin的方式适合脚本里批量改mysqladmin -u root -p旧密码 password 新密码但这种方式在shell的历史记录里会留下明文密码生产环境谨慎使用。更安全的方式是交互式登录后用SQL改。还有一个在5.7时代很常见但8.0已经废掉的写法SET PASSWORD PASSWORD(xxx)。如果你在网上搜到这种老语法到8.0环境直接不能用了别奇怪。经验之谈给业务方改密码前先看有没有当前活跃连接。可以用SHOW PROCESSLIST查一下该账号的会话如果有正在跑的长事务直接改密码虽然不会影响已经建立的连接但会导致业务方的连接池里的旧连接在下次校验时全部失败取决于连接池配置。最好在低峰期操作或者配合业务方一起切换。3.2 账号锁定、解锁、删除与重命名部分临时关闭某个账号不需要删锁起来就行ALTER USER temp_user% ACCOUNT LOCK;恢复时ALTER USER temp_user% ACCOUNT UNLOCK;删除账号则用DROP USER temp_user%;删除前建议先确认没有活跃连接SELECT id, user, host, command, time, state FROM information_schema.processlist WHERE user temp_user;如果确认不再需要账号直接DROP USER。注意DROP USER会同时清理该账号在mysql.db、mysql.tables_priv里的授权记录手改表清理反倒可能漏。账号重命名这块也有坑。RENAME USER old TO new只能改用户名host部分保持不变。想同时改host必须写全两个部分RENAME USER old192.168.1.% TO new10.0.0.%;凡是涉及host变更的干脆删除重建比RENAME更清晰尤其是权限结构复杂的账号。3.3 密码策略组件与过期强制轮换8.0的密码校验组件叫validate_password它是一个可加载组件。安装方式INSTALL COMPONENT file://component_validate_password;安装后新创建用户的密码会经过强度校验太弱的直接拒绝。可以通过参数调整策略SET GLOBAL validate_password.policy MEDIUM; SET GLOBAL validate_password.length 12; SET GLOBAL validate_password.mixed_case_count 1; SET GLOBAL validate_password.number_count 1; SET GLOBAL validate_password.special_char_count 1;参数名字在5.7里是validate_password_policy这种下划线风格在8.0里改成了点分风格老参数在8.0中已废弃。密码过期机制也很实用。针对单个用户ALTER USER app% PASSWORD EXPIRE INTERVAL 90 DAY;强制立即过期ALTER USER app% PASSWORD EXPIRE;全局默认[mysqld] default_password_lifetime90密码过期后用户下次登录会被要求先改密码才能执行其他语句这对业务账号来说容易造成断连生产上要跟业务方提前约好。3.4 一份可以直接抄的账号审计SQL模板定期审计账号是DBA的好习惯。我最常用的一组SQL-- 列出所有非系统账号及其状态 SELECT user, host, account_locked, password_expired, plugin, IF(authentication_string , EMPTY_PASS, HAS_PASS) AS pwd_status FROM mysql.user WHERE user NOT IN (mysql.sys, mysql.session, debian-sys-maint); -- 查看某个账号的全局权限 SELECT * FROM mysql.user WHERE user app AND host %\G; -- 查看某个账号的库级权限 SELECT * FROM mysql.db WHERE user app\G; -- 查看某个账号的表级权限 SELECT * FROM mysql.tables_priv WHERE user app\G;审计时我重点关注四类问题有没有plugin为空或authentication_string为空的账号空密码或者异常导入导致的有没有权限明显大于岗位需求的管理类账号比如开发人员的账号带SUPER有没有长期不用的老账号结合processlist和登录日志判断有没有host是%却挂着高权限的账号这类风险极大。建议把审计SQL存成脚本每月跑一次输出到表格里供复查。审计这件事跑一次不难难的是每次跑完都有人跟进结果的整改闭环。4. 用户管理实战中最常见的四个坑这一节把我在实战中遇到的典型问题和完整排查链路写出来你在自己环境里大概率会撞上一两个。4.1 远程授权成功却连不上按这个顺序排查现象GRANT都给了SHOW GRANTS也显示有权限但业务方反馈连不上。诊断要按链路一层一层排除从外到内步骤检查项命令/方法1网络是否通ping 数据库IP2MySQL端口是否可达telnet 数据库IP 3306新的系统可能没有telnet用nc -vz IP 33063端口是否真的在监听ss -lntp | grep 33064服务是否绑定了所有网卡查看my.cnf中bind-address如果是127.0.0.1则远程永远连不上需改为0.0.0.0或具体网卡IP5是否启用了skip-networkingmy.cnf里如果配置了skip-networkingTCP连接全部被拒只能本地socket6账号host是否匹配SELECT user, host FROM mysql.user WHERE userapp;7DNS反解是否拖慢连接开启skip_name_resolveON时连接更快但host字段必须写IP形式写域名会匹配不上有一年我一个线上库频繁出现应用超时的报警查到最后发现是skip_name_resolve没有开每次新连接MySQL都在做DNS反解遇到一次DNS抖动直接连接超时。开了skip_name_resolveON之后这类问题彻底消失。4.2 caching_sha2_password引发的客户端兼容性事故这类事故我在8.0迁移排障时见过太多。现象是密码明明正确客户端就是报Authentication plugin caching_sha2_password cannot be loaded或者握手失败。根因在前面1.3节已经说过这里补充一个排障链路先确认当前用的是哪个插件SELECT user, host, plugin FROM mysql.user WHERE userapp;如果看到caching_sha2_password而你的客户端驱动版本比较老解决方案按优先级排列升级客户端驱动到支持8.0的版本Java的Connector/J 8.0、PHP的mysqlnd 7.2.4、Navicat 12临时把该账号降级为mysql_native_passwordALTER USER app% IDENTIFIED WITH mysql_native_password BY 同一个密码;全局改回老插件最差方案不推荐。这里有个容易忽略的知识点8.0里插件是服务端决定的客户端只是配合。客户端不能自己说我要用老插件所以哪怕客户端支持两种插件只要服务端账号指定了caching_sha2_password客户端就必须支持它。降级插件方案治标不治本该升级客户端还是得升级。4.3 授权明明给了却不生效权限验证的优先级场景用户在mysql.db表里授权了某个库的SELECT但业务端查询时报权限拒绝。第一个要查的是SELECT CURRENT_USER();。因为MySQL判断权限用的是当前会话匹配到的账号而不是你印象里的那个账号。如果授权时匹配到的是app%而实际连接命中了app192.168.1.%host更精确优先匹配两个账号权限不一样就会出现授权给了却不生效的假象。先确认自己是以哪个身份登录的SELECT CURRENT_USER(); SHOW GRANTS FOR CURRENT_USER();第二个常见原因是授权写错了对象。GRANT SELECT ON mydb.*和GRANT SELECT ON mydb.t1不是一个概念两者分别存在mysql.db和mysql.tables_priv我在审计时见过授权到了mydb.t1.*这种完全无效的写法MySQL不会报错但也不会按你的预期生效。第三个原因是授权后已建立的连接不会自动刷新权限。权限是在连接建立时快照的连接建立之后的权限变更不会影响已存在的会话。所以改完权限一定要让相关业务重连。这也是生产上通知业务方重连一下连接池这个操作的理论来源。4.4 忘记root密码的急救流程这可能是每一个MySQL使用者最恐惧的时刻。完整流程如下停止MySQL服务systemctl stop mysqld以跳过权限验证的方式启动mysqld_safe --skip-grant-tables 免密登录mysql -u root清空root密码FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY 新密码; FLUSH PRIVILEGES;如果ALTER USER因为插件问题执行不了用下面这条应急UPDATE mysql.user SET authentication_string WHERE Userroot; FLUSH PRIVILEGES;然后重启MySQLmysqladmin -u root shutdown systemctl start mysqld此时root密码为空登录后再用ALTER USER设置正式密码。这里有几个关键点--skip-grant-tables模式下MySQL默认会跳过权限判断这个状态绝对不能留到业务侧操作完必须立刻恢复正常模式启动。这是救命流程也是高危状态。在8.0里单靠清空authentication_string后重启root依然可能无法正常ALTER所以我的习惯是清空后马上FLUSH再马上ALTER少了一步都会绕回死胡同。如果是主从环境或者多个MySQL实例操作前确保没有正在写入的关键事务避免在恢复root密码的过程中丢了binlog位置或触发从库误判。最后再说点我的体会MySQL用户管理做得顺不顺往往决定了一个团队在数据库面前从容还是狼狈。我现在接手任何一套新环境的MySQL第一周必做三件事清点所有账号、收缩权限边界、确认密码策略和过期周期。这三件事做完后面很多离奇故障都能提前消掉。一个小习惯分享给大家把账号信息登记成一张表列明账号名、host、用途、负责人、最近一次密码轮换时间和授权范围每季度跟着审计脚本一起过一遍。这张表比任何人的记忆力都靠谱出了事它就是排查的第一份依据。数据库账号这东西账面越干净出问题的概率越低。给账号尽量少的权限让每一颗权限的螺丝钉都有出处——这句话值得写进每一个团队的数据库规范里。