查mysql.user表：一次搞懂用户密码哈希与权限排查

搞MySQL的人早晚会在mysql.user表里栽几个跟头。我印象最深的一次某业务系统报“密码错误”开发同事反复确认密码没输错我上去查了一圈才发现根本不是密码问题而是账户匹配到了错误的 host 组合连密码哈希都还没来得及比对就被拒了。后来我写了个习惯凡是涉及登录、权限、密码的故障第一件事永远是查mysql.user里的user、host、plugin、authentication_string这四列。今天就把这套东西从头到尾讲透包括 MySQL 内置系统表怎么分工、用户密码哈希存在哪、hash 格式长什么样、怎么查、以及线上踩过的坑。这篇文章适合三类人日常被数据库连接问题折磨的后端开发、要管服务器又要管库的运维、还有刚接触 MySQL 权限机制的初学者。读完你不仅能看懂authentication_string里的那串乱码还能照着 SQL 自己排查账户问题。1. MySQL的系统库全家桶先分清职责再动手很多人刚接触时都会困惑连上 MySQLSHOW DATABASES;会看到mysql、information_schema、performance_schema、sys这些库它们到底谁管什么其实这四个库就是 MySQL 的“内部管理系统”各管一摊分清楚了后面查用户 hash 的时候才知道去哪个库找数据。1.1 mysql库账户权限的核心仓库mysql库是整个 MySQL 实例的“管理中枢”里面存放的全是元数据。我们最关注的用户账户、权限信息、密码哈希都存在这个库里。核心表包括user用户账户、host、密码哈希、插件、账户状态、资源限制。这是本文的绝对主角。db用户在某数据库上的权限。tables_priv用户在某个表上的权限。columns_priv用户在某个列上的权限。procs_priv存储过程和函数上的权限。global_grants一些动态全局权限MySQL 8.0 新增。servers、tablespaces、event、time_zone等日常用得少但也是系统表的一部分。需要特别强调的是mysql库里的表绝大多数是MyISAM存储引擎MySQL 8.0 以后部分表改成了 InnoDB它的数据文件就在数据目录下的mysql/子目录里。所以如果备份时只备份业务库漏掉mysql库等于把所有用户密码哈希、权限配置都丢掉了恢复业务库后却发现连不进去这种事故我见过不止一次。1.2 information_schemaMySQL的“词典视图”information_schema严格说不是存储数据的库而是一组“视图”实时从 MySQL 各个模块读取信息。它没有对应的数据目录文件你也没法直接往里面写数据。它是兼容数据库行业标准的东西核心价值在于查询元数据。常用视图有TABLES所有库的表、行数估算、表类型、字符集。做容量评估、巡检脚本时高频使用。COLUMNS所有表的字段信息包括字段类型、是否允许 NULL、默认值、注释。STATISTICS索引信息联合索引字段顺序、唯一性等。USER_PRIVILEGES全局权限的视图能直接看到当前有哪些全局授权。SCHEMA_PRIVILEGES、TABLE_PRIVILEGES、COLUMNS_PRIVILEGES库、表、列权限视图配合权限排查非常顺手。PROCESSLIST当前连接线程相当于SHOW PROCESSLIST的信息来源。INNODB_TABLES、INNODB_INDEXES等InnoDB 引擎内部结构信息。举个例子我想看某个用户在所有库里都有哪些权限一条 SQL 就查出来了SELECT GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE FROM information_schema.SCHEMA_PRIVILEGES WHERE GRANTEE LIKE app_user%;这个库最大的价值是“用 SQL 获取原本要猜的信息”尤其写自动化巡检脚本的时候information_schema几乎是必备数据源。1.3 performance_schema 与 sys监控与辅助performance_schema是 MySQL 5.6 开始引入的“性能监控数据库”它在 MySQL 内部收集 wait、lock、I/O、语句执行等事件。它不是给人直接看报表的而是给 DBA 排查性能瓶颈用的。我查慢查询分布、锁等待来源时经常从events_statements_summary_by_digest里捞数据。但这个库的表巨多新手通常只记得performance_schema这个名字就好具体业务不涉及性能分析时不要乱动它的开关参数。sys库是后期加进来的“便捷工具库”本质是建立在performance_schema之上的一组视图、存储过程和函数。它的表名和视图名非常直白比如sys.user_summary按用户汇总当前连接数、总执行时间查询用户级负载很好用。sys.session当前会话的情况比直接看PROCESSLIST多了一层统计。sys.schema_table_lock_waits表锁等待相关。sys.innodb_lock_waits行锁等待排队信息。所以这四个库的关系可以这样理解mysql库是“账本”记录谁是谁、能干什么information_schema是“字典”描述数据库里有什么performance_schema是“监控仪表盘”记录正在发生什么sys是“快捷菜单”把监控数据整理成好读的报表。查用户 hash 只需要盯着mysql.user表但排查权限问题时常需要配合information_schema各个权限视图一起看。2. mysql.user核心字段密码哈希到底藏在哪mysql.user表是整个权限体系的心脏。一行记录代表一个“账户”账户不是只看用户名而是user host 的组合。这一点特别容易出问题很多“密码正确却连不上”的故障就是 host 组合没匹配上。2.1 从 password 字段到 authentication_string历经多个大版本MySQL 存储密码哈希的字段发生了明显变化MySQL 版本密码字段说明5.6Password经典mysql_native_password40位*开头十六进制哈希5.7Password与authentication_string并存Password字段保留但推荐用authentication_string8.0authentication_string彻底移除Password字段所有插件哈希统一存这里所以如果你在不同版本的库上跑同一条 SQL很容易翻车。比如 8.0 上执行SELECT Password FROM mysql.user;直接报错 Unknown column而 5.6 上SELECT authentication_string FROM mysql.user;可能返回 NULL 因为老库只写了Password字段。这是我第一条经验查用户密码哈希先确认版本再决定字段最省事的写法是直接看authentication_string但老库要做兼容处理。2.2 plugin决定哈希生成方式的开关很多新手只看authentication_string的长度和前缀忽略plugin字段结果连不上也不知道为什么。mysql.user.plugin表示这个账户用什么认证插件它不仅决定密码校验方式还直接决定了authentication_string里的字符串格式。常见值有三个mysql_native_password老牌插件哈希是*加 40 位大写十六进制。兼容性极好老驱动、老客户端都认它。caching_sha2_passwordMySQL 8.0 默认插件哈希以$A$开头。安全性更高但老版本客户端比如 5.x 的 libmysqlclient连不上报错信息里往往有Authentication plugin caching_sha2_password cannot be loaded的字样。sha256_password另一个基于 SHA-256 的插件通常用于 SSL 加密连接场景哈希也是$开头但标志不同。说个我踩过的坑有一次我在 8.0 上新建用户没写任何插件参数默认就是caching_sha2_password结果某台老跳板机上的脚本连不上。当时第一反应是密码写错了折腾半天最后在mysql.user里一看 plugin 才反应过来。后面的解决方案是把那个用户改成mysql_native_password或升级客户端驱动视业务可接受度选择。2.3 容易被忽略的状态字段除了 user、host、plugin、authentication_stringmysql.user里还有几个日常排查时很容易被忽略但是很重要的字段account_locked账户锁定标记。字段是N表示正常Y表示锁定。锁定账户即使密码完全正确也会报 Access denied。password_expired密码是否已过期。N为正常Y表示客户端需要重置密码才能继续操作。遇到“登录成功了却什么 SQL 都执行不了”的情况多半是这个字段。password_lifetime密码有效期单位是天默认 NULL 表示用全局配置。ssl_type、ssl_cipher是否强制 SSL 连接部分安全要求高的环境会用到。max_questions、max_updates、max_connections、max_user_connections资源限制某些用户会因为连接数超限被拒但报错通常不是密码错误而是 Too many connections。我建议写巡检脚本的时候把这几个字段一起带上SELECT user, host, plugin, authentication_string, account_locked, password_expired, password_lifetime FROM mysql.user;一条 SQL 把账户状态看全。很多“灵异”问题其实都是这些字段没查。3. 用户hash查询实操一条SQL顺藤摸瓜了解了字段实操就很简单。但“查 hash”不只是执行一条SELECT后面还要会读、会验证、会延伸排查。3.1 最常用的查询SQL组合先看基本查询SELECT user, host, plugin, authentication_string FROM mysql.user WHERE user app_user;如果查不到结果不要急着认为用户不存在先去掉 where 条件看全表SELECT user, host, plugin, authentication_string FROM mysql.user;因为app_user可能匹配的是app_user%也可能匹配app_userlocalhost它们完全是两行记录。只看 user 不看 host是最常见的误判。查空密码账户这也是安全审计的必修课SELECT user, host, plugin, authentication_string FROM mysql.user WHERE authentication_string OR authentication_string IS NULL;查锁定的账户SELECT user, host, account_locked FROM mysql.user WHERE account_locked Y;查密码过期的账户SELECT user, host, password_expired, password_lifetime FROM mysql.user WHERE password_expired Y;3.2 hash格式解读看到乱码别慌拿到authentication_string里的值不同插件读法完全不同。mysql_native_password 格式类似*6C387FC3893DBAF3D0D1403A7C1E2C9A8B7C6A1E固定 41 个字符以*开头。它其实是SHA1(SHA1(明文密码))的十六进制大写结果。可以自己验证一遍用 Python 就能算import hashlib password your_password.encode() hash1 hashlib.sha1(password).hexdigest().encode() hash2 hashlib.sha1(hash1).hexdigest().upper() print(* hash2)理论上能对上就说明 hash 与明文匹配。不过实际排查中很少需要本地计算因为 MySQL 自己会验证但理解这个双重 SHA1 的过程能帮助你明白一件事MySQL 里存的不是密码明文也不是可直接解密的密文而是不可逆的摘要。所以任何人告诉你他可以从mysql.user里“解密”出原密码基本都是在吹牛。caching_sha2_password 格式形如$A$005$3a1b2c...MySQL 8.0 默认格式。$A$表示算法标识005表示 5000 次迭代后面是加盐后的哈希内容。这种格式比mysql_native_password安全得多加了随机盐因此同样的明文密码每次生成的 hash 都不一样。这也是不能拿mysql_native_password的*格式直接放进authentication_string里冒充caching_sha2_password的原因插件不同验证算法完全不同。sha256_password 格式类似$5$...或$SHA256$...相对少见。日常运维一般碰不到知道有这种格式即可。3.3 顺着用户hash延伸的权限排查密码哈希只是账户信息的一部分排查权限问题时不能只盯着mysql.user。我常用的权限查询路线是这样的。全局权限SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE LIKE app_user%;库级别权限SELECT GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE FROM information_schema.SCHEMA_PRIVILEGES WHERE GRANTEE LIKE app_user%;表级别权限SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE LIKE app_user%;列级别权限SELECT * FROM information_schema.COLUMN_PRIVILEGES WHERE GRANTEE LIKE app_user%;实操中一条 SQL 就能把用户看得七七八八SELECT user, host, plugin, authentication_string, account_locked, password_expired FROM mysql.user WHERE user app_user; SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE LIKE app_user%; SELECT * FROM information_schema.SCHEMA_PRIVILEGES WHERE GRANTEE LIKE app_user%;这三组查询组合起来基本能回答“这个用户是谁、密码用哪种格式存、能不能连、连上来有哪些权限”这几个核心问题。4. 实战排查与常见问题避开这些坑查 hash 是手段解决实际问题才是目的。下面这些坑都是我实际踩过或帮别人排查过的整理成速查思路。4.1 登录失败排查的正确顺序收到“用户连不上数据库”的反馈我现在的排查顺序已经固定了基本不会错先确认账户是否存在userhost是否匹配当前连接来源。app_userlocalhost和app_user%不是一回事MySQL 按 host 的精确度选匹配项规则比较复杂先按“最具体的优先”理解即可。再看account_locked是否Y锁定的账户密码再对也进不来。再看password_expired是否Y这种情况连接可能成功但后续操作受限容易表现成“能连但什么都干不了”。再看plugin是否和客户端驱动兼容8.0 默认插件对老客户端不友好。最后才怀疑密码哈希本身。实际上大部分“密码错误”都不是 hash 错了而是前面几步的匹配问题。举个例子曾经有个程序用app_user10.0.0.%的网段连接而 MySQL 里只有app_userlocalhost和app_user%因为%被匹配到了但权限和预期不一致。这种问题从密码角度永远找不到答案必须回到 host 组合来看。4.2 直接改authentication_string的雷区网上很多“重置密码”的野路子让你直接UPDATE mysql.user SET authentication_stringxxx WHERE useryyy;。这个方法在特殊情况下有效比如忘记 root 密码时可以配合skip-grant-tables来应急但生产环境我强烈不建议把改 hash 当成常规手段。直接改 hash 有几个隐藏问题哈希必须与 plugin 匹配格式错了照样登不上。MySQL 8.0 里直接改authentication_string不会同步更新账户的认证插件相关信息容易造成“hash 对了但登录逻辑还是不对”的诡异状态。绕过权限管理器的行为一旦出错可能把整个权限表搞乱恢复成本远高于正常重置。正确做法永远是ALTER USER app_user% IDENTIFIED BY 新密码;如果你确实需要把一个用户的哈希复制到另一个用户不要拼字符串去 update应该用 MySQL 官方的认证器语法例如CREATE USER new_user% IDENTIFIED WITH mysql_native_password AS *6C387FC3893DBAF3D0D1403A7C1E2C9A8B7C6A1E;这样 hash 和 plugin 是一起设置的不会出现“分离”问题。4.3 匿名用户和host匹配的坑匿名账户localhost是默认安装时潜在风险点它匹配的是没有指定用户名的连接。如果你的业务程序连接字符串里用户名写错了或者解析出了问题可能就被匿名账户接住了表现成“没有一个叫 app_user 的权限但居然能连上且只有极少权限”。这类问题很隐蔽排查时要查一下SELECT user, host, authentication_string FROM mysql.user WHERE user ;查出匿名账户后确认不是故意配置就清理掉。但注意清理前要看有没有业务依赖部分老系统会在本机用无密码 socket 方式连库直接删可能误伤。4.4 其它高频问题速查表症状可能原因排查/解决ERROR 1045 (28000): Access denied for user密码错、host 不匹配、账户锁定按 4.1 顺序逐步查连接报Authentication plugin caching_sha2_password cannot be loaded客户端版本太老不支持默认插件升级驱动或改回mysql_native_password能连上但执行任何 SQL 都提示需要重置密码password_expiredYALTER USER ... IDENTIFIED BY ...;密码正确但提示账号已锁定account_lockedYALTER USER ... ACCOUNT UNLOCK;程序查到用户但权限老是少host 匹配到了另一条记录检查userhost组合不要只看 user误改mysql.user导致无法登录权限表损坏用skip-grant-tables应急通过官方ALTER USER修复慎用直接 update最后再说一个习惯上的建议。我把“用户审计”写进日常巡检脚本定期跑一遍mysql.user重点看是否存在空密码账户、是否存在过期且未重置的密码、是否存在锁定后又被释放的账户、是否存在跨 host 的重复用户。这些看起来基础的检查往往能在故障发生前就暴露问题。少一次半夜被叫醒多睡几个好觉。说实话到现在我每次面对数据库连接类故障第一反应仍然是去查mysql.user。不是因为它万能而是因为它包含了最基础的账户状态和密码哈希信息。你把 user、host、plugin、authentication_string 这四个字段看明白了MySQL 的登录认证机制就懂了一大半。剩下的权限细节再顺着 information_schema 一层层往外查就行了。这套方法从 5.6 排到 8.0都好用。