1. 先聊聊我为什么要写这篇权限管理手册很多人刚接触 MySQL 时觉得用户创建和权限管理是个再简单不过的事CREATE USER一下GRANT一下完事。直到你遇到线上数据库被某个业务账号误删、root 密码在紧急时刻怎么都想不起来、或者同事把GRANT ALL ON *.*当成万能膏药到处贴的时候你才会意识到权限管理这一块的水远比想象中深。这篇文章的核心关键词就三个MySQL 用户创建、权限管理、SUPER 权限外加一个所有人都绕不开的 root 密码实战场景。我会把从创建用户到授权、从 SUPER 权限的真正边界到 root 密码遗忘后的完整救援流程全部拆开揉碎了讲。不管你是刚入门的运维、写着写着突然要管数据库的后端开发还是被临时拉去救火的“兼职 DBA”这篇文章都能让你少走几趟弯路。先说一个我自己的真实经历。有次接手一个老项目发现开发同学给应用账号授了个GRANT ALL PRIVILEGES ON *.* TO app%问就是“方便调试”。结果某次误操作把整个业务库的表删了一批虽然最终通过备份恢复但那个下午的紧张程度我至今记得。这就是典型的权限设计没跟上业务发展的后果——MySQL 的权限系统本身设计得很精细可惜大多数人只用了它的冰山一角。2. MySQL 权限体系的基本盘用户到底是什么2.1 用户名 主机名才是完整的用户身份这是新手最容易忽略的第一课。MySQL 里一个用户不是你理解的zhangsan这么简单而是zhangsanlocalhost和zhangsan%这样由“用户名 客户端主机”共同构成的完整身份。为什么要这样设计因为同一个用户名来自不同机器的连接需要的权限可能完全不一样。比如-- 本机管理用的账号权限可以给大一点 CREATE USER adminlocalhost IDENTIFIED BY StrongPass_2024; -- 应用服务器过来的账号权限必须收紧 CREATE USER app10.0.0.% IDENTIFIED BY AppPass_2024;注意10.0.0.%这种写法它表示来自10.0.0网段的连接。MySQL 通配符有两种%匹配任意主机192.168.1.%匹配指定网段。这里有个坑如果你同时存在app%和app10.0.0.%两个用户MySQL 匹配规则是越精确的匹配优先级越高而不是后创建的先匹配。我在实际项目中遇到过一个问题本地调试时用-h 127.0.0.1连接报Access denied但用-h localhost就能连上。排查了半天才发现是rootlocalhost和root127.0.0.1被当成两个不同用户而密码不一致。所以记住一条排查连接问题先确认你命中的是哪个“用户名主机”组合。2.2 权限范围从全局到单列一共分几个层级MySQL 的权限体系是分层的从大到小分别是权限层级作用范围典型授权方式全局层级所有数据库的所有对象GRANT ... ON *.*数据库层级指定库的所有对象GRANT ... ON db_name.*表层级指定表的所有操作GRANT ... ON db_name.tbl_name列层级指定表的某些列GRANT SELECT(col1, col2) ON db_name.tbl_name存储过程/函数层级指定的例程GRANT EXECUTE ON PROCEDURE db_name.proc_name权限层级越靠近底层控制粒度越细但管理成本也越高。实际生产环境里大多数场景做到“库级权限”就够了只有少数敏感表比如用户表、支付表才需要做到列级限制。顺带说一下授权时的WITH GRANT OPTION要谨慎使用。这个选项意味着被授权者可以把 TA 拥有的权限再转授给别人。如果你给应用账号加了这一句等于是允许应用账号自己去创建其他账号一旦应用被注入或者账号泄露后果不堪设想。我的建议是除非是专职 DBA 账号否则一律不加WITH GRANT OPTION。3. 用户创建实战从最简到最安全3.1 基础版先建用户再授权MySQL 8.0 里必须先CREATE USER再GRANT不能像 5.7 及更早版本那样在GRANT语句里直接隐式建用户。这是一个行为变化很多升级到 8.0 的人在这里踩过坑。-- 第一步创建用户并设置密码 CREATE USER data_reader192.168.1.% IDENTIFIED BY ReadOnly2024; -- 第二步只给查询权限 GRANT SELECT ON business_db.* TO data_reader192.168.1.%; -- 第三步让权限立即生效 FLUSH PRIVILEGES;关于FLUSH PRIVILEGES要不要每次执行这里多说一句如果你用的是CREATE USER和GRANT语句操作权限那 MySQL 会自动更新权限缓存不需要额外执行FLUSH。什么时候才需要当你直接修改了mysql.user、mysql.db这些系统表或者是在一些特殊迁移场景下操作了授权表后才需要执行FLUSH PRIVILEGES来强制重新加载。很多人形成了一看到权限操作就刷FLUSH的习惯这本身无害但如果你的脚本里是通过UPDATE mysql.user绕过标准接口改权限那FLUSH就必不可少。我见过有人改了mysql.user里 root 的 plugin 字段后没刷权限重启 MySQL 直接连不上的事故后面会细讲。3.2 进阶版按业务场景设计权限模板我自己在实践中总结了一套分场景的权限模板直接贴出来给大家参考只读账号给报表、数据分析用CREATE USER report% IDENTIFIED BY ReportPass_2024; GRANT SELECT ON bi_db.* TO report%; -- 如果要看视图定义还需要 SHOW VIEW GRANT SHOW VIEW ON bi_db.* TO report%;注意只读账号也要区分场景。有些报表工具只需要读当前数据有些 BI 工具需要创建临时表那就得额外给CREATE TEMPORARY TABLES权限不然每次跑大查询都会报错。读写账号给业务应用用CREATE USER app_write10.0.0.% IDENTIFIED BY AppWrite2024; GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO app_write10.0.0.%; -- 如果应用需要事务其实上面就够了不要给 DDL 权限这里特别强调一点业务账号永远不要给 DDL 权限CREATE、ALTER、DROP、TRUNCATE。应用代码里出现的建表语句应该在发布流程中由 DBA 或 CI/CD 管道执行而不是让线上应用账号随时都能改表结构。我给一个客户做过一次审计发现他们应用账号居然有DROP权限问开发为什么答曰“导出数据的时候方便清空表”。这就是拿安全换便利的典型反面教材。管理账号CREATE USER dba_opslocalhost IDENTIFIED BY DbaOps_2024; GRANT ALL PRIVILEGES ON *.* TO dba_opslocalhost WITH GRANT OPTION;管理账号可以给大权限但一定要限制来源主机。生产环境的 DBA 账号应该只允许从跳板机或堡垒机登录绝对不要开%全网段。3.3 密码策略MySQL 8.0 的默认规则和自定义方式MySQL 8.0 默认安装了validate_password组件对密码强度有默认要求至少需要包含大小写字母、数字和特殊字符长度不低于 8 位。如果你在CREATE USER时用了弱密码会直接报ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。有些测试环境想临时关掉这种限制可以这样操作-- 查看当前密码策略 SHOW VARIABLES LIKE validate_password%; -- 调低策略等级 SET GLOBAL validate_password.policy LOW; SET GLOBAL validate_password.length 6;不过这只建议在开发环境用。生产环境的密码策略我建议保持默认甚至调更高毕竟数据库密码是最后一道防线。另外IDENTIFIED BY直接写在命令行里的话密码会出现在 shell history 里有安全洁癖的同学可以改用mysql -uroot -p -e CREATE USER app% IDENTIFIED BY $(read -s -p Password: pwd; echo $pwd)这样密码不会直接出现在命令历史中。4. SUPER 权限看着全能实际是个“危险品”4.1 SUPER 权限到底能干什么SUPER 权限是 MySQL 里最高级别的管理权限之一它允许用户执行一系列普通用户不能做的操作包括但不限于修改全局系统变量SET GLOBAL ...使用CHANGE MASTER TO/CHANGE REPLICATION SOURCE TO配置复制关系使用START SLAVE/STOP SLAVE控制复制线程KILL其他用户的线程在read_only1的实例上执行写操作使用SET SESSION修改一部分被限制的变量开启和关闭二进制日志即使达到max_connections限制也能建立连接保留一个连接给 SUPER 用户从这些能力能看出来SUPER 权限本质上是“DBA 专用权限”。它不是为了让你日常执行GRANT用的而是为了在紧急场景下进行运维干预用的。4.2 哪些场景其实用不到 SUPER 权限这里有个常见的认知偏差很多同学遇到“需要修改某个全局参数”就琢磨着要续上 SUPER 权限其实很多操作在 MySQL 8.0 里已经被拆分了更细的权限。操作需求MySQL 8.0 推荐权限为什么不需要 SUPER杀掉某个卡死的会话CONNECTION_ADMIN管理连接专用的细粒度权限修改系统变量SYSTEM_VARIABLES_ADMIN只用于变量管理范围更小配置复制REPLICATION_SLAVE_ADMIN只管理复制线程执行SET USER切换会话用户SET_USER_ID防止提权攻击备份时获取一致性位点BACKUP_ADMIN给备份工具专用如果你在 MySQL 8.0 环境里给业务账号授了SUPER审计工具通常直接标红。原因很简单它权限范围太宽一旦账号被攻破攻击者几乎可以操作实例的一切。替代方案是“权限最小化拆解”你需要哪个能力就给哪个权限别图省事一把梭。4.3 一个让我痛定思痛的线上案例去年帮朋友排查过一次故障。他们的业务账号有 SUPER 权限应用代码里有一段“自动检测慢查询并 kill”的逻辑进度落后时就用业务链接去KILL其他会话。听起来很智能对不对但在某次大促场景下这段逻辑误判把正在跑关键批量任务的几个会话全杀了导致大批数据没处理完上游任务连锁失败。后续复盘时发现的根因是业务系统压根就不该有KILL任何会话的能力。正确的做法是让监控系统用独立的巡检账号去发现问题再通过 DBA 操作入口处理异常会话业务应用只需要在自身连接层面做超时控制即可。我的建议是**生产环境里能开到的最小权限就是最合适的权限。**如果确实有跨团队管理需求也应该走审批流程单独授权到人而不是给一个共享业务账号挂 SUPER。4.4 如何审计哪些用户有高权限想看看谁被授予了 SUPER 权限可以查mysql.user表SELECT User, Host, Super_priv FROM mysql.user WHERE Super_priv Y;同样的思路把所有Grant_priv、Create_user_priv等管理权限都查一遍建立一个“高权限账号台账”每个季度过一遍能有效减少“不知道谁有权限”的混沌状态。5. root 密码忘记最完整的救援实操记录5.1 场景一能登录 MySQL 但不知道能不能改 root 密码有人会说“能登录还用得着救援吗”但真实情况是你通过某个管理账号登进去了却不知道 root 密码而某些工具非要用 root 连接这时就需要重置 root 密码。ALTER USER rootlocalhost IDENTIFIED BY NewRoot2024;如果rootlocalhost用的是auth_socket或caching_sha2_password之类的插件想改成密码登录可以这样-- 指定使用 MySQL 原生密码认证方式8.0 默认 caching_sha2_password ALTER USER rootlocalhost IDENTIFIED WITH caching_sha2_password BY NewRoot2024;注意 MySQL 8.0 默认的caching_sha2_password在某些老客户端比如 PHP 7.1 之前的 PDO、一些旧版 Navicat上连不上会报Authentication plugin caching_sha2_password cannot be loaded。解决办法有两个要么升级客户端要么把账号改成mysql_native_password。我的建议是尽量升级客户端毕竟mysql_native_password在新版本里已经标记为废弃。5.2 场景二完全进不去 MySQL 怎么办这才是真正意义上的“救火”。流程分两种常用方案我都实际操作过二维码全部贴出来。方案一skip-grant-tables大法也就是咱们平时说的“免密进入”。步骤如下第一步停止 MySQL 服务。不同系统的命令略有差异# systemd 系CentOS 7/Ubuntu 16 sudo systemctl stop mysqld # 或者 Ubuntu 上可能是 sudo systemctl stop mysql第二步以跳过授权表的方式启动sudo mysqld_safe --skip-grant-tables --skip-networking 这里加--skip-networking是引起安全考虑让 MySQL 不监听 TCP 端口只允许本机 socket 连接避免“免密模式”下被外部连上来趁火打劫。第三步无密码登录mysql -uroot第四步重置密码FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY NewRoot2024;这里有个细节非常多skip-grant-tables模式下ALTER USER可能会报错或者不生效所以很多人建议先执行FLUSH PRIVILEGES;再改密码。我实操下来先刷权限再改是最稳的路径。第五步退出并重启 MySQL 正常服务mysqladmin -uroot -p shutdown sudo systemctl start mysqld方案二init_file初始化文件法这个方案的好处是不用临时停掉网络监听更接近“优雅”的重置第一步创建一个初始化文件/tmp/mysql-reset-pwd.sqlALTER USER rootlocalhost IDENTIFIED BY NewRoot2024;第二步停止 MySQL用--init-file参数启动sudo systemctl stop mysqld sudo mysqld --init-file/tmp/mysql-reset-pwd.sql 启动完成后密码就改好了然后正常关闭服务再启动即可。说实话这两个方案我比较推荐第一个因为skip-grant-tables的适用范围更广而且排查连接问题时更直观。但方案二适合那种“必须保持网络权限模型不加载变化”的稳妥场景操作痕迹更小。5.3 忘记 root 密码后最容易踩的三个坑坑一skip-grant-tables时不带--skip-networking。这等于把数据库裸奔在网络里任何能连到你 3306 端口的人都能免密进入。我曾经见过测试服务器因为这样被扫描工具直接连上去改了配置整个实例几乎报废。切记记住免密模式只允许本机操作加--skip-networking是底线。坑二改完密码后忘记清理临时文件。用init-file方案时那个包含明文密码的 SQL 文件如果在操作系统上留着等于主动送密码。改完密码后必须立刻删除sudo rm -f /tmp/mysql-reset-pwd.sql坑三直接改mysql.user表导致登录链路更崩。我看到很多教程教人在skip-grant-tables模式下去UPDATE mysql.user SET authentication_string...这本身没错但在 MySQL 8.0 里authentication_string的格式和 5.7 不一样如果直接复制 5.7 的 hash 填充后续可能连环报错。而用ALTER USER是官方支持的接口会自动处理新格式省心无数。5.4 第一件事root 密码重置后必须立即做的检查密码改完之后别急着收工。我建议按这个清单过一遍确认本地 socket 登录正常mysql -uroot -p确认 TCP 远程登录正常mysql -h 实例IP -uroot -p检查慢查询日志或错误日志里是否有异常时间段的无密码连接记录检查mysql.user表里有没有多出陌生的高权限账号如果之前是 root 密码过期导致锁定顺手执行ALTER USER rootlocalhost PASSWORD EXPIRE NEVER;避免周期性锁死6. 授权容易回收难权限变更与撤销的正确姿势6.1 REVOKE 语法别记成 GRANT 反过来撤销权限的语法结构是REVOKE SELECT, INSERT ON business_db.* FROM app10.0.0.%;注意和GRANT的ON位置不同很多人记混了。如果要把某个账号的所有权限全部收回REVOKE ALL PRIVILEGES, GRANT OPTION FROM app10.0.0.%;收起全部权限不等于删掉用户这个用户还能登录只不过任何操作都会报权限不足。如果要彻底禁用ALTER USER app10.0.0.% ACCOUNT LOCK;或者干脆删除DROP USER app10.0.0.%;6.2 一个权限回收的经典翻车现场有次我在测试环境做权限精简先REVOKE了某个账号的DELETE权限然后顺手DROP USER结果发现有张定时任务表用的就是那个账号访问另一个库任务瞬间全部失败。翻车原因就一条没有先梳理“这个账号到底被哪些连接、哪些脚本在用”就动了权限。所以生产环境做权限变更前我强烈建议先执行-- 查这个账号当前的权限快照 SHOW GRANTS FOR app10.0.0.%; -- 查系统里连接来源有哪些需要在 information_schema 里看 SELECT USER, HOST, DB, COMMAND FROM information_schema.PROCESSLIST WHERE USER app;先搞清楚现状再动手能省掉一半的救火时间。6.3 权限生效机制为什么有时候改完要“发一会儿呆”MySQL 权限缓存的生效机制有个细节新连接会立即使用最新权限但已存在的连接不受影响。所以如果你 revoke 一个正在跑的连接的权限那个连接上的后续操作反而不会立刻受限。这在某些场景下是个安全漏洞——比如你发现某账号被滥用revoke 了权限但攻击者已经建立的长连接还能继续操作一段时间。应对办法是紧急情况下直接KILL对应连接-- 先找到可疑连接 SELECT ID, USER, HOST, DB, COMMAND, TIME FROM information_schema.PROCESSLIST WHERE USER app; -- 杀掉连接 KILL 12345;另外还需要知道FLUSH PRIVILEGES只会重新加载权限表不会断开现有连接。这是不少人误会的一点。7. 一文看懂 MySQL 8.0 与 5.7 权限管理的核心差异升到 8.0 之后权限这块有几个影响日常操作的变化我用一张表总结事项MySQL 5.7MySQL 8.0GRANT隐式建用户支持不支持必须先建用户默认认证插件mysql_native_passwordcaching_sha2_passwordSUPER权限包含多种管理能力拆分为多个细粒度权限密码过期策略可设置默认不过期可设置默认不过期但有更强策略组件PASSWORD()函数可用已移除必须用ALTER USERmysql.user表字段authentication_string保留但有格式变化不要手动改如果你是从 5.7 升级到 8.0 的最常遇到的问题就是客户端连接时报caching_sha2_password插件不支持。解决思路是升级驱动或者可以在 8.0 里临时把个别账号切回旧插件ALTER USER old_app% IDENTIFIED WITH mysql_native_password BY OldAppPass;但注意这只是一个过渡手段MySQL 官方已经在后续版本把mysql_native_password标记为废弃长期来看还是得升级客户端。8. 最后再分享几个我常年挂在工单上的自查项这篇文章主要篇幅在讲用户创建和权限分配但实际运维里最值钱的往往是“怎么知道哪里出了问题”。我总结几个自查命令建议收藏备用。查看某用户的所有权限SHOW GRANTS FOR userhost;查看所有用户和认证方式SELECT User, Host, plugin, account_locked FROM mysql.user;查看哪些账号拥有 SUPER 这类高危权限SELECT User, Host, Super_priv, Grant_priv, Create_user_priv FROM mysql.user WHERE Super_priv Y OR Grant_priv Y OR Create_user_priv Y;查当前正在执行的连接来自哪些账号SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE FROM information_schema.PROCESSLIST;这几个命令组合起来基本能覆盖 80% 的权限排查场景。我把它们存成了一个 SQL 脚本每次接手新环境第一件事先跑一遍对全局心里有数再动手改东西。个人经验是权限管理的核心不是“怎么授出去”而是“授出去之后怎么定期收拾”。建议每季度做一轮权限盘点清理掉长期不用的账号、收回超出业务范围的权限、重置可疑账号的密码。这个过程里最忌讳的就是“睁一只眼闭一只眼”。做 DBA 和运维这几年我最大的感受是MySQL 权限管理就像房间的门锁系统——你不需要给每个房间都装金库门但必须清楚每把钥匙能开哪扇门、被谁拿着、什么时候该换锁芯。希望这篇内容能让你在面对用户创建、权限分配、SUPER 权限和 root 密码救援这些场景时少一点手忙脚乱多一点从容。