1. 为什么单独聊聊MySQL权限管理1.1 一次差点出事的教训说件真事。前几年接手一套线上系统某个周五晚上报表服务突然报数据库权限错误排查许久才发现是隔壁组同事用管理员账号登录生产库手动执行了一段批量授权脚本。本来只想给一个新来的报表同事开只读权限结果脚本里写了GRANT ALL PRIVILEGES ON *.*把整个实例的超级权限给交了出去。那个账号后来又被他同事拷到本地连接工具里密码明文躺在聊天记录中。权限管理这种事平时翻文档觉得都会真到出事那天往往就是一条语句的疏忽。MySQL的授权体系并不复杂但它有自己的一套分层逻辑如果理解不到位很容易出现两个极端要么给得太宽生产库形同虚设要么给得太紧业务半夜打电话说连不上。今天这篇就把MySQL权限管理从底层存储、实操语句、场景设计到故障排查完整过一遍希望对刚接手MySQL维护的后端同学以及已经在干活但全靠百度解决问题的人有点帮助。1.2 这篇文章能帮你解决什么我把日常工作中和权限相关的需求大致归了几类这篇文章会逐一展开新环境搭好后怎么给不同业务模块创建账号而不是全部用root。线上出了问题怎么快速确认某个账号到底有没有某个权限谁给的、能给谁。要做数据安全整改时怎么把DBA、开发、报表、只读这些账号的权限边界划清楚。遇到授权了却还是没权限远程连不上这类经典问题怎么一步步定位。内容偏实战涉及的语句我都会把参数讲明白并标注哪些地方容易踩坑。如果你现在正在用MySQL 5.7或8.0都可以直接参考两个版本在权限模型上整体一致个别差异我会提出来。2. MySQL权限体系的核心认知2.1 权限数据到底存在哪里MySQL把权限信息不是散落在配置文件里而是固化在系统库mysql下的几张表里。理解这一点特别重要因为很多操作最终都是在改这些表只是平常通过SQL语句间接修改而已。核心的几张权限表如下表名作用范围典型字段mysql.user全局权限存放账号、密码、Host及所有全局级权限标记Host, User, authentication_string, Select_priv, Insert_priv...mysql.db数据库级权限控制某个账号对特定库的操作Host, User, Db, Select_priv, Insert_priv...mysql.tables_priv表级权限精确到某张表Host, User, Db, Table_name, Table_priv, Column_privmysql.columns_priv列级权限精确到某一列Host, User, Db, Table_name, Column_name, Column_privmysql.procs_priv存储过程和函数权限Host, User, Db, Routine_name, Routine_type, Proc_priv打开mysql库你会看到这些表。平常执行GRANT SELECT ON db1.* TO user1%MySQL内部做的事情就是把数据写入mysql.db表而如果执行GRANT SELECT ON *.* TO user1%则会把权限标记写进mysql.user表。再说细一点GRANT SELECT, INSERT ON db1.user_info TO user2%这个落库的位置是tables_priv表。另外一个容易被忽略的细节是执行账户密码修改比如ALTER USER ... IDENTIFIED BY ...修改的是mysql.user表的authentication_string字段。5.7版本中该字段保存的是加密后的密文千万不要手动去更新这个字段除非你清楚知道加密规则。我曾见过有人想批量改密码直接UPDATE mysql.user SET authentication_string明文密码结果所有账号都无法登陆只能重启实例用特殊模式恢复非常狼狈。了解权限存储的意义在于当你怀疑权限异常时可以直接查这些表来核实总比一遍遍执行SHOW GRANTS更底层的视角。2.2 权限的分类和粒度MySQL的权限按对象的范围从小到大可以分为四个层级列权限、表权限、数据库权限、全局权限。另外还有一种特殊的管理类权限比如CREATE USER、SUPER、RELOAD、SHUTDOWN它们挂在全局层级只出现在mysql.user表里。具体到日常开发比较常见的库表操作权限有这些SELECT查询数据INSERT插入数据UPDATE更新数据DELETE删除数据CREATE创建库或表DROP删除库、表、视图等对象ALTER修改表结构INDEX创建或删除索引REFERENCES外键约束权限CREATE VIEW创建视图SHOW VIEW查看视图定义CREATE ROUTINE / ALTER ROUTINE / EXECUTE管理或执行存储过程、函数还有几个有点特殊的存在权限名说明风险等级GRANT OPTION允许该账号把拥有的权限再授予其他人高SUPER相当于超管权限可执行很多管理操作5.7之前用途很广极高PROCESS允许查看其它线程信息等于能看到别人正在执行的SQL高FILE允许读写服务器上的本地文件极高CREATE USER允许创建、删除、修改其它账号高RELOAD允许执行FLUSH操作中高MySQL 8.0引入了一组动态权限像BACKUP_ADMIN、AUDIT_ADMIN、SYSTEM_USER等这类权限不会像传统的 * _priv字段那样存在mysql.user表中而是保存在mysql.global_grants表里。它们主要用于把原来SUPER这种大权限拆成一个个细粒度权限方便按需分配。这里提醒一点8.0中对ROOT账号授权一个“最小集”时如果嫌麻烦直接用GRANT ALL PRIVILEGES实际拿到的也不是完整的全部像部分动态权限例如SYSTEM_USER不会自动包含在内。2.3 用户连接的认证逻辑一个客户端要连上MySQL要经过两步第一步是“能不能连进来”第二步是“连进来能干哪些事”。很多人只关心第二步结果第一步卡住了半天找不到原因。第一步的认证逻辑和Host字段紧密相关。MySQL在验证一个连接时不是只拿用户名匹配而是把“用户名 客户端来源主机”放在一起匹配。举个实例mysql SELECT User, Host FROM mysql.user; --------------------------- | User | Host | --------------------------- | app_web | 10.10.0.% | | app_web | localhost | | report_ro | 10.10.1.% | ---------------------------当某台IP为10.10.0.88的机器使用app_web登录时MySQL会优先匹配到10.10.0.%这条记录因为该来源主机符合这个规则。如果有一台IP为10.10.2.5的机器用app_web登录但只配置了10.10.0.%和localhost那连接就会被拒绝报错通常为Access denied for user app_web10.10.2.5 (using password: YES)这个报错信息极其常见后面排查部分我会展开讲。现在先建立意识Host不是一个可有可无的摆设它是账号身份的一部分。同一个用户名配合不同Host可以拥有完全不同的权限这是MySQL权限模型的一大特点也是一大坑点。比如你可能给app_web配置了localhost下拥有全部权限同时给10.10.0.%下只配置了SELECT而一个运维同学恰好从10.10.0.66远程用app_web连接测试发现能查不能改误以为权限系统出bug了。第二步的权限验证逻辑简单说就是执行每条SQL前MySQL会依据当前账号已经“叠加”的各级权限综合判断是否允许。这个叠加规则在第5章详细说这里先记住一句话身份决定了你能进哪扇门权限决定了你能动哪些东西。3. 权限管理的高频实操建号、授权、回收3.1 建用户与密码策略创建用户的语法很简单CREATE USER app_web10.10.0.% IDENTIFIED BY StrongPass_2024;这里的Host可以有几种写法%匹配所有主机最宽松生产环境慎用192.168.1.%匹配特定网段localhost只能本机连接app-server01.example.com匹配域名取决于DNS反查空字符串等价于localhost需要说明的是%和空值是有区别的。%不能匹配socket连接对应的匿名用户而localhost可以。实际开发中如果发现localhost都连不上检查一下是不是Host配成了%导致socket连接走了别的匹配路径。密码方面MySQL 5.7及以上默认安装了密码校验插件validate_password。它的作用是强制密码满足复杂度要求。刚装完环境时如果执行CREATE USER提示“ERROR 1819 (HY000): Your password does not satisfy the current policy requirements”说明密码太弱。密码策略相关参数可以查SHOW VARIABLES LIKE validate_password%;通常需要包含大小写字母、数字和特殊字符。很多人讨厌这套机制但我个人看法是除非在内网离线环境且业务确实依赖简单密码否则不要轻易关掉。加强密码策略是最简单、成本最低的安全措施。如果一定要调低强度可以临时修改validate_password.policy为LOW或MEDIUM注意调整后记得评估风险。8.0中密码改成默认使用caching_sha2_password插件认证5.7默认是mysql_native_password。如果5.7客户端去连8.0或者老程序连新库时报Authentication plugin caching_sha2_password cannot be loaded那是因为驱动版本过老。两种解决方法升级驱动或把该用户改回mysql_native_passwordALTER USER app_web10.10.0.% IDENTIFIED WITH mysql_native_password BY StrongPass_2024;注意mysql_native_password在8.0中已经被标记为废弃而且未来版本会移除所以这只是过渡方案尽量还是推动驱动升级。3.2 GRANT授权的五种作用域创建账号之后下一步就是授权。GRANT的基本格式是GRANT 权限列表 ON 权限对象 TO usernamehost [WITH GRANT OPTION];权限对象决定了授权的粒度由小到大有这些写法写法作用层级说明db1.table1 (col1, col2)列级只给某张表的某几列授权db1.table1表级单表授权db1.*库级整个库.全局实例级别所有库所有表db1.routine_name存储过程级对存储过程的EXECUTE等授权实际工作中真正用得最多的是库级和表级。比如给一个业务系统开账号-- 给某个库单独的DML权限 GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO app_order10.10.0.%; -- 给报表系统只读权限 GRANT SELECT ON report_db.* TO report_ro10.10.1.%;授权时最容易被忽略的是WITH GRANT OPTION这个尾巴。它的意思是允许该账号将自己拥有的权限继续授予别人。假如你给一个开发账号加了这个选项他手里拿到的SELECT权限就能再转授给别人权限扩散将难以追踪。因此除非确确实实要做一个“管理员代理账号”否则不要加这个选项。我在第1节提到的那个事故就是脚本里带了ALL PRIVILEGES以及隐含的GRANT OPTION等于给了“万能钥匙”。另外还有一个思路授权也可以按列来。这个功能很多同学没用过但特定场景比较实用。例如有一张用户表user_base里面有id、name、mobile、email业务方只需要姓名和ID不希望他碰手机号和邮箱可以这么做GRANT SELECT (id, name) ON security_db.user_base TO user_ro10.10.1.%;这样该账号只能查询这两个字段。需要注意列级授权与表级授权不冲突。如果之前已经给了该账号在整张表上的SELECT这个列级授权并不会限制他他照样能查所有列。列级授权适合在“未给表级权限”的前提下做精细控制。3.3 REVOKE与DROP USER的正确姿势回收权限用REVOKE语法的格式几乎和GRANT镜像REVOKE SELECT, INSERT ON order_db.* FROM app_order10.10.0.%;这里有个容易忽略的点REVOKE只能撤销精确对应的那一级权限。什么意思呢如果你给账号授权了db1.*又额外授权了db1.table1现在想收回db1.table1上的权限只执行上面的REVOKE是收不干净的因为db1.table1是独立的一条授权记录。你需要显式回收表级的那条REVOKE SELECT ON db1.table1 FROM app_order10.10.0.%;另外回收不等于删除账号。权限收回后账号仍然存在只是某些事情不能做了。要彻底删除账号使用DROP USER usernamehost;在MySQL 5.7及以后版本DROP USER会同时清理该账号在各个权限表中留下的关联记录不用手动去DELETE FROM mysql.user。注意如果有当前会话正使用该账号连接删除后该会话不会立即中断但新连接会被拒绝。如果想要立刻中断会话可以结合KILL操作-- 先找到该账号的会话ID SELECT id, user, host, db FROM information_schema.processlist WHERE user app_order; -- 然后执行KILL KILL thread_id;场景上这适用于账号泄露后紧急处置第一步先DROP USER第二步杀掉残留会话第三步检查binlog中是否有可疑操作。对于GRANT OPTION的回收有两种情况。如果只想收回“授权的权利”而保留原有数据权限需要单独执行REVOKE GRANT OPTION ON *.* FROM admin_backup10.10.2.%;如果你想同时收回GRANT OPTION和数据权限直接一并写REVOKE ALL PRIVILEGES, GRANT OPTION FROM admin_backup10.10.2.%;实际上REVOKE ALL PRIVILEGES并不会把账号删除也不会回收它拥有的角色这一点在8.0中尤其要注意。账号如果被赋予了角色REVOKE ALL PRIVILEGES后它仍然可以行使角色中的权限。3.4 查看账号权限做权限管理的日常操作里查看权限占了很大比例。最常用的是SHOW GRANTS FOR app_web10.10.0.%;输出结果像这样------------------------------------------------------ | Grants for app_web10.10.0.% | ------------------------------------------------------ | GRANT USAGE ON *.* TO app_web10.10.0.% | | GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO app_web10.10.0.% | ------------------------------------------------------注意SHOW GRANTS输出中默认的USAGE表示“无特殊权限只能连接”这是正常的。如果输出里带了WITH GRANT OPTION那你就要留心这个账号能不能再给别人授权。有时候需要在脚本里判断账号是否拥有某权限可以直接查权限表SELECT Select_priv, Insert_priv, Update_priv, Delete_priv FROM mysql.db WHERE User app_web AND Host 10.10.0.% AND Db order_db;这里Y表示有权限N表示无权限。这种查询方式在自动化巡检脚本里比逐条SHOW GRANTS更高效而且方便批量比对。4. 场景化权限设计从最小权限到按角色拆分4.1 按业务角色拆分成四类账号很多团队的权限管理乱乱在“一人一账号”的思维。其实合理的方式是“一类业务一个账号”或者“一类角色一个账号”让账号与角色绑定而不是与个人绑定。这里给你一个最基本的拆法角色权限范围典型示例应用连接账号只拥有某个业务库的DML权限GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO app_order只读报表账号拥有多个库的SELECT权限GRANT SELECT ON report_db.* TO report_roDDL变更账号只在变更窗口使用拥有表结构变更权限GRANT CREATE, ALTER, INDEX, DROP ON order_db.* TO change_owner管理员账号运维或DBA专用权限覆盖全实例GRANT ALL PRIVILEGES ON.TO dba这样做的好处是显而易见的同一类角色的人流动时不需要重建权限只改密码或者收回个人持有的临时凭证即可。此外还可以在账号上打上明确语义的名字比如以app_、report_、dba_做前缀一眼就能看出来这个账号是干什么用的。强调一点root账号在生产环境不应该被业务程序使用。有的团队图省事程序配置里直接写root连接虽然跑起来没问题但一旦程序被注入或配置泄露等于攻击者拿到了整个数据库。root账号只应作为紧急运维通道平时应该锁起来或仅允许本机访问。4.2 只读账号的“不彻底”问题很多人认为只读账号就是只给SELECT权限其实“只读”这两个字在MySQL中还有不少细节。第一层只给SELECT权限不给INSERT、UPDATE、DELETE、DROP等这是最基础的。第二层需要把EXECUTE权限考虑进来。如果库里有存储过程而该存储过程内部有写操作即使账号只有SELECT权限但拥有EXECUTE权限时调用该存储过程可能间接产生写操作。如果确认不需要调用存储过程就不要给EXECUTE。反过来如果再彻底一点可以给存储过程单独授权GRANT EXECUTE ON PROCEDURE order_db.batch_update TO report_ro10.10.1.%;第三层把LOCK TABLES权限也要注意。有些报表场景会用SELECT ... INTO OUTFILE或手动LOCK TABLE READ。如果账号连LOCK TABLES权限都没有相关语句会报权限不足需要在GRANT中显式加入。第四层MySQL 8.0中只读账号如果拥有SHOW VIEW权限可以查看视图的创建语句但不会看到底表的全部数据权限之外的任何东西。而如果该账号对底表没有SELECT权限通过视图就能“越权”看到一部分列。这就是视图授权的基础——下一节展开。如果你要做一个“绝对只读”的账号一个比较严谨的套餐是GRANT SELECT, SHOW VIEW ON report_db.* TO report_ro10.10.1.%; GRANT SELECT ON report_db.v_order_summary TO report_ro10.10.1.%;这样既能让账号看视图、又能限制它不触碰其他对象。4.3 视图授权做数据脱敏在生产环境经常会有这种需求业务方要看订单数据但手机号、身份证号这类敏感字段不能暴露。这种场景适合用“视图 授权”的组合拳。假设底表是user_base(id, name, mobile, email, create_time)我们创建一个不含敏感字段的视图CREATE VIEW security_db.v_user_basic AS SELECT id, name, create_time FROM security_db.user_base;然后只把视图的SELECT权限授出去GRANT SELECT ON security_db.v_user_basic TO analyst10.10.1.%;这个analyst账号连底表的权限都没有也只能通过视图查询到脱敏后的三列手机号和邮箱完全接触不到。这就是最小权限原则的一种典型应用。但是要注意一点视图本身依赖底表如果底表未来增加了字段视图不会自动更新需要手动ALTER VIEW如果底表被删除或改名视图会失效。所以使用视图授权前要和业务方确认数据字典的稳定度并纳入变更管理流程。另外视图创建后要记得SHOW GRANTS查看别把底表权限顺带授出去。4.4 用MySQL角色ROLE管一组权限MySQL 8.0正式支持了角色ROLE5.7没有这个功能。角色可以理解为一组权限的集合把权限打包成“套餐”然后统一分配给账号这个思路非常像操作系统里的用户组。角色操作的基本流程是-- 创建角色 CREATE ROLE read_only_role, app_dml_role; -- 给角色授权 GRANT SELECT ON report_db.* TO read_only_role; GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO app_dml_role; -- 创建账号 CREATE USER zhang_san10.10.1.% IDENTIFIED BY Password123!; -- 把角色授予账号 GRANT read_only_role TO zhang_san10.10.1.%; -- 启用角色默认不激活需要设置 SET DEFAULT ROLE read_only_role TO zhang_san10.10.1.%;这里的SET DEFAULT ROLE很关键。刚把角色授予账号时账号默认并没有激活该角色也就是还没真正拿到权限。必须设置为默认角色或者让用户在登录后手动执行SET ROLE read_only_role。如果发现给账号授权角色后还是没权限先检查是不是漏了这一步。查看角色情况SHOW GRANTS FOR zhang_san10.10.1.%; -- 输出中会看到角色名 SELECT CURRENT_ROLE();如果希望一个账号默认激活所有已授予的角色可以设置全局变量SET GLOBAL activate_all_roles_on_login ON;这个变量在8.0中默认是OFF。开启后简化了角色管理但需要注意如果给角色授予了一个受限权限所有拥有该角色的账号都将被动获得该权限变更影响面较大需要走变更评审流程。我个人的建议是如果团队规模不大、账号数量少直接用账号权限的模型就够如果管理几十个账号、人员流动频繁还是引入角色比较好省去大量重复授权和回收工作。但要注意角色和账号一样也有Host匹配关系不要以为角色可以跨所有Host生效。授权给角色时角色本质上也有一组User/Host属性通常建议把角色Host设置为%或localhost以免受连接来源限制无法正常启用。5. 权限验证的底层原理和“授权不生效”之谜5.1 MySQL怎么匹配用户前面说过登录认证时用户名和Host是绑定的。但Host匹配还有一套优先级规则很多人不知道。MySQL在匹配Host时会先尝试最精确的匹配再尝试宽泛的匹配。精确度从高到低大致是IP地址或完整主机名比如10.10.0.8或app-server01带前缀匹配的比如10.10.0.%或app-server%最宽泛的%举个例子同时存在app_web10.10.0.%和app_web%当客户端从10.10.0.5连接时MySQL会选10.10.0.%这条而不是%。这导致一个常见现象你给%的账号改了密码但从某个IP连接却始终用的旧的权限。原因就是匹配到了更精确的那一条10.10.0.%记录。这里有一个排查口诀出问题时不要只看用户名先看Host匹配到了哪一条。最快捷的方法是SELECT User, Host, authentication_string FROM mysql.user WHERE User app_web;查出当前实例下该用户所有Host记录然后判断客户端来源落在哪一条规则里。如果对IP来源不熟可以先看客户端的IPSELECT SUBSTRING_INDEX(USER(), , -1) AS client_host, CURRENT_USER() AS matched_user;CURRENT_USER()返回的是MySQL实际匹配后的完整身份比如app_web10.10.0.%这一下就能定位到底用的是哪条账号记录。5.2 权限是叠加还是覆盖权限验证时MySQL把全局权限、库级权限、表级权限、列级权限分别做“或”运算也就是合并取最大范围而不是靠某一条记录直接覆盖。举例-- 全局没有任何权限 -- 库级别被授予了 SELECT, INSERT GRANT SELECT, INSERT ON db1.* TO user1%; -- 表级别被授予了 DELETE GRANT DELETE ON db1.t1 TO user1%;最终user1对db1.t1的权限 SELECT INSERT DELETE而对db1.t2只有SELECT INSERT。虽然全局和库权权限层级“小权限”没有覆盖掉“大权限”的说法实际上是叠加的。但有一个例外情况要考虑当mysql.db中某库权限记录全部为N时MySQL会认为该账号对该库有“显式拒绝授权”这会导致它在这个库的所有表上执行任何操作都会被拒绝即使表级授权里已经给了权限。我遇到过一次某账号对db1.*曾经执行过REVOKE ALL ON db1.*导致mysql.db里留下一条全部为N的记录后续再给表级授权怎么也不生效直到从mysql.db里清理掉那条空记录才恢复正常。所以如果出现“明明授了表级权限但无权限”的情况先查mysql.db是否有残留“N记录”。权限叠加的另一个重要场景是GRANT ALL PRIVILEGES ON *.*。它会赋予账号SELECT、INSERT、UPDATE等几乎所有静态权限但不会自动包含GRANT OPTION也不会包含SUPER在8.0中部分动态权限需单独给。这一点在前面角色部分也提到过。5.3 FLUSH PRIVILEGES什么情况才需要网上很多教程都写“改完权限要执行FLUSH PRIVILEGES”这其实被过度传播了。使用GRANT、REVOKE、CREATE USER等语句时MySQL已经在系统权限表中做了即时更新同时内存中的权限缓存也会同步更新不需要额外FLUSH。什么情况才需要FLUSH PRIVILEGES主要是你绕过MySQL权限管理语句直接修改了系统权限表。比如手动执行了UPDATE mysql.user SET Select_privY WHERE Userapp_web AND Host10.10.0.%;这种情况下MySQL内存中的权限缓存还是旧数据需要执行FLUSH PRIVILEGES;来重新加载。另外如果你修改了mysql.user中的密码字段但没有使用ALTER USER也需要FLUSH。这里必须提醒直接UPDATE权限表是一种危险操作数据写错可能导致账号无法登录或者权限异常扩大。除非在恢复场景下没有其他手段否则不建议这么操作。还有一点FLUSH PRIVILEGES本身的执行权限属于RELOAD。如果某个账号没有RELOAD权限执行时会报权限不足。生产环境要给某个自动化脚本发这个命令记得提前授权。5.4 skip-grant-tables最危险的修复手段遇到忘记root密码或者权限表损坏很多人会想到用skip-grant-tables启动实例。这个方法的原理是MySQL启动时跳过所有权限验证任何用户都能无密码登录并且拥有全部权限。说白了就是“大赦天下”。这个东西不是不能用但必须按下面这套姿势来顺序不能乱停止MySQL服务。修改配置文件在[mysqld]段加入skip-grant-tables。启动MySQL此时所有账号免密登录。用mysql -uroot登录然后执行FLUSH PRIVILEGES;。注意这里如果不执行FLUSH即使你登录了也无法使用ALTER USER修改密码因为内存中的权限模块还没加载。执行修改密码操作。退出移除配置中的skip-grant-tables重启MySQL。常见翻车点有两个。第一个是忘掉第4步的FLUSH PRIVILEGES直接执行ALTER USER会报“ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement”。第二个是改完密码后忘记删除配置项实例一直处于无认证状态任何人都能连上来。这个状态如果暴露在公网后果不堪设想。另外8.0中即使加了skip-grant-tables可能还会遇到插件加载问题因为某些认证插件在免密阶段无法正常加载。通常需要在配置里同时加上skip-networking来禁止远程TCP连接只允许本机socket登录把风险降到最低。我个人的看法是skip-grant-tables是救急手段不是常规方案。生产环境更推荐通过备份恢复、或者用另一台同版本实例挂载数据目录来修复把影响面控制到最小。如果必须用skip-grant-tables建议在变更窗口操作并全程限制网络访问。6. 高频故障排查实录6.1 远程登录被拒绝的定位方法这类报错最经典ERROR 1045 (28000): Access denied for user app_web10.10.2.5 (using password: YES)看到这个报错很多人第一反应是“密码错了”。但密码只是一方面更常见的是Host匹配问题。按下面顺序排查第一步确认当前客户端IP。可以在本地执行mysql -h10.10.2.5 -uapp_web -p如果本机也连不上先看看mysql.user里该用户的Host规则是否覆盖了这个来源IPSELECT User, Host FROM mysql.user WHERE User app_web;第二步确认网络层。虽然MySQL返回了认证错误说明TCP连接其实已经建立但如果是“Host 10.10.2.5 is not allowed to connect to this MySQL server”这种报错那就是纯粹的Host匹配失败和密码无关。第三步查看bind-address配置。MySQL默认可能只监听了127.0.0.1如果配置了只能本机访问远程自然连不上报错一般是“Cant connect to MySQL server on x.x.x.x”。解决办法是修改my.cnf中的bind-address为0.0.0.0或内网IP然后重启或执行SET GLOBAL bind_address注意这个变量在MySQL中不支持动态设置需要重启。第四步防火墙。如果报错是连接超时而不是拒绝访问多半是防火墙或安全组拦截了3306端口。我处理过最折腾的一个case是客户端用域名连接MySQL通过反向解析域名匹配失败导致登录超时最后在my.cnf里加skip-name-resolve才解决。加了该参数后MySQL不再反解IP到域名所有匹配只基于IP。但副作用是mysql.user中如果写了主机名形式的授权记录比如userapp-server01将不再生效。这一点调整后要重新审查所有账号的Host配置。6.2 授权后权限不生效的三大原因明明执行了GRANT SELECT但程序查询时还是报“SELECT command denied to user”。先别急着骂MySQL按照这三个原因排查。原因一会话没有重连。MySQL权限检验是每次SQL执行时实时校验的但账号身份在连接建立时就已经固定。如果你是在一个已经打开的会话里改了权限当前会话不会自动刷新。需要重新连接后才会生效。这是最常见的原因尤其是DBA改了权限但业务连接池里还有老连接在跑。原因二Host匹配到了另一条记录。比如你给app_web10.10.2.%授了权限但客户端实际IP是10.10.3.5匹配到的是另外一条app_web%记录而这条记录上是没有SELECT权限的。上一章已经讲过匹配优先级这里不再赘述。原因三权限表的“空记录”或“N记录”副作用。前面提过的mysql.db残留N记录就是典型例子。排查方式SELECT * FROM mysql.db WHERE Userapp_web AND Host10.10.3.%;如果看到一条记录全是N不把它当回事可能后续还会持续干扰。直接删除该记录即可DELETE FROM mysql.db WHERE Userapp_web AND Host10.10.3.% AND Dbdb1; FLUSH PRIVILEGES;还有一个小坑是大小写敏感。数据库名、表名在Linux环境下区分大小写授权时写的库名如果和实际库名大小写不一致会导致权限匹配不到。授权时最好用反引号把库名和表名包住避免歧义。6.3 忘记root密码的恢复方案除了前面提到的skip-grant-tables之外还有另一个相对更安全的方案通过init-file参数执行密码重置。大致步骤如下在服务器上创建一个文本文件例如/tmp/reset_pwd.sql内容为ALTER USER rootlocalhost IDENTIFIED BY NewStrongPass_2024;配置文件中加入[mysqld] init-file/tmp/reset_pwd.sql重启MySQL。启动时会自动执行该文件中的SQL完成密码修改。确认成功后删除该文件和配置项再次重启。这个方案相比skip-grant-tables的优点是MySQL在启动阶段就进行了权限校验并执行操作不会出现免密访问窗口。缺点是你依然需要能够重启实例。另外init-file在MySQL 8.0中仍然可用但执行时同样需要满足密码策略。整个恢复过程中最要紧的一点是凡是临时文件或临时配置用完后必须立刻删掉。我见过有人把reset_pwd.sql留在/tmp目录后来又因为权限配置不当被其他运维看到导致密码泄露。6.4 权限表损坏权限表损坏虽然不常见一旦发生会出各种诡异的权限问题比如所有账号都登录失败、FLUSH PRIVILEGES卡住、授权语句报错。原因是MyISAM表5.7之前权限表默认MyISAM在异常断电或磁盘故障后可能出现索引损坏。在MySQL 5.7中可以用mysqlcheck --repair mysql user db tables_priv columns_priv或者直接使用myisamchk工具修复对应的.frm和.MYI文件。8.0中权限表迁移到了InnoDB损坏概率大幅下降但如果真遇到最稳妥的办法是从备份中恢复系统库或使用mysqld --initialize-insecure重新初始化并导入业务数据。这种场景比较极端普通团队可能一辈子遇不到。但知道有这个方向和修复路径至少遇到时可以少走弯路。7. 权限审计和日常运维7.1 定期做权限清单审计权限管理不能“授完就忘”。我建议至少每个月做一次权限审计把当前所有账号的权限导出来和业务申请记录比对找出可疑的权限外溢。可以执行mysql -uroot -p -e SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv FROM mysql.user ORDER BY User; user_priv_audit_$(date %F).txt也可以把权限明细导出得更细一点逐库查看SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv FROM mysql.db ORDER BY User, Host, Db;审计时需要重点留意几类账号拥有GRANT OPTION的账号拥有SUPER权限的账号Host为%的账号长期不用但仍保留的账号查出这些问题账号后走流程回收或锁定。锁定账号的方式ALTER USER long_no_use% ACCOUNT LOCK;账户锁定后保留账号记录但无法登录适合不确定以后还要不要用的场景。如果确认彻底不用直接DROP USER。7.2 变更留痕和复盘权限变更最好有留痕。在MySQL层面可以开启审计日志插件MySQL Enterprise Audit通过AUDIT_LOG_INSTALL_PLUGIN等语句或在8.0中使用audit_log插件。需要说明的是企业版插件是商业功能社区版可以使用通用日志general_log来做粗粒度审计。一般团队没有企业版也可以利用binlog来追踪权限表变更。因为GRANT、REVOKE、CREATE USER等操作会写入binlog前提是binlog_format为ROW或MIXED排查时可以通过:mysqlbinlog --start-datetime2024-01-01 00:00:00 --stop-datetime2024-02-01 00:00:00 mysql-bin.000123搜索关键字 GRANT 或 REVOKE找到执行时间、执行位置和上下文。不过更简单有效的方法是建立自己的变更记录表每次执行权限变更前先在内部工单系统登记。这样遇到问题时可以快速回溯是谁、在什么时间、基于什么原因做了变更。不要依赖记忆权限变更这种事情时间一长真的会忘。7.3 密码轮换与账号回收密码轮换是权限管理中很容易被忽略的环节。很多系统账号的密码几年不换一旦出现泄露影响范围非常大。MySQL 5.7及以上支持密码过期策略可以设置-- 让某个用户密码立即过期 ALTER USER report_ro10.10.1.% PASSWORD EXPIRE; -- 设置全局密码过期策略默认单位是天 SET GLOBAL default_password_lifetime 90;设置为90天意味着所有新建账号如果未特别指定密码会在90天后过期届时必须修改密码才能继续连接。这个策略对生产环境尤其有用。但要提醒一点如果应用连接的账号开启了自动密码续期要注意不能去掉手动修改步骤否则应用会在密码过期后突然连不上让业务受损害。账号回收方面人员离职或转岗后应立即处理个人相关账号包括-- 查看该人账号关联的权限 SHOW GRANTS FOR zhang_san10.10.1.%; -- 回收账号 DROP USER zhang_san10.10.1.%; -- 或者仅锁定 ALTER USER zhang_san10.10.1.% ACCOUNT LOCK;如果该账号之前被授予了角色也要一并撤销角色授权REVOKE read_only_role FROM zhang_san10.10.1.%;有些团队习惯把离职人员的账号复用给新同事这个做法我持保留态度。新员工应该有新的账号哪怕权限一样也要走一次完整的授权流程。这样审计时才能把每个操作对应到具体的人。另外对于使用MySQL 8.0的团队还可以借助mysql.global_grants表跟踪动态权限分配情况。定期导出这张表和账号清单做对比可以快速发现异常权限项。最后分享一个我自己的小习惯每次权限变更完成后我会把变更内容记录在一个本地Markdown文件里内容包括变更新增账号、权限范围、申请人、审批人、变更时间。虽然很简单但几次帮助我在季度复盘时快速还原当时的授权背景也让我在操作失误时能第一时间知道如何回滚。权限管理这件事做得好的团队往往不是靠某个神奇工具而是靠一套严谨的流程和习惯。这个方向如果继续深入还可以往两个方向扩展一是结合数据库防火墙或SQL审计系统对敏感操作比如TRUNCATE、DROP做二次拦截二是利用ProxySQL等中间件在应用层做更细粒度的读写分离和权限控制。但无论外部工具怎么丰富MySQL本身的权限体系依然是最底层的防线建议每个维护者都把它吃透。