1. 先搞清楚为什么不能直接拿 root 一把梭每次看到有人建库建表都用 root 账号一把梭我都替他的数据库捏把汗。MySQL 默认安装完成后root 是超级管理员拥有全部权限任何表都能查能改任何库都能删能建。这个账号一旦泄露、被爆破或者被误操作整个数据库就是裸奔状态。而实际业务里我们需要的往往只是一个“能从应用服务器连上 MySQL能读写某个库但是不能碰其他库”的账号这类需求用 root 来做既不安全也容易在事后排查时分不清是谁在操作。这个标题“Mysql 创建用户并授权”看起来简单但背后实际上涉及三件事账号本身怎么建、权限怎么给、给完之后怎么验证和维护。这几步如果只记住几条 SQL遇到生产环境会栽很多跟头。本文面向的是那些刚入门 MySQL 管理、或者准备把数据库账号从 root 收敛成专用账号的开发者我会把创建、授权、远程连接、SSL 报错、权限回收这些环节全部走一遍并把实际踩过的坑也放进来。MySQL 的账号体系其实和 Linux 的用户体系很像先有人账号再给他角色权限并且要告诉他能在哪台机器上登录host 限定。只不过 MySQL 的账号是由 user 和 host 两部分组成的同一个用户名在不同来源下可能是两个独立的账号这一点很多新手会忽略。理解了这套基础模型后面的所有操作逻辑都会变得顺理成章。2. 从零创建一个专用 MySQL 账号2.1 基础命令CREATE USER 与 IDENTIFIED BY最简单的方式是在 MySQL 命令行里执行下面这段CREATE USER appuserlocalhost IDENTIFIED BY App123456;这条语句的含义是创建一个名为 appuser 的账号只允许从本机localhost发起数据库连接密码是 App123456。注意 MySQL 8.0 里面必须先执行 CREATE USER后面才能执行 GRANT 授权两者是分开的。但在 MySQL 5.7 及更早版本里可以直接用 GRANT 语句顺手创建用户比如GRANT SELECT ON mydb.* TO appuserlocalhost IDENTIFIED BY App123456;也就是说老版本里 GRANT 自带“建人”功能。8.0 把这个行为废掉了强制拆成两步目的是让账号创建和权限授予边界更清楚。很多人在 8.0 环境下习惯性地写GRANT ALL ON mydb.* TO ...却报错就是因为没先建用户。另一个容易忽略的细节是用户名后面那个localhost千万别省。如果不写主机部分默认是appuser%意思是不限制来源任何机器都能拿这套账号密码尝试连接。虽然最终能不能登录还取决于 bind-address 和防火墙但权限面确实被放大了。密码那一项我建议不要用过于简单的纯数字或纯字母组合。MySQL 8.0 默认安装了 validate_password 密码校验组件强度不够会直接拒绝执行比如强制要求大小写字母、数字和特殊字符同时出现。5.7 如果装了对应插件也会有同样约束没装的话则只受全局参数validate_password_policy控制。生产环境里“密码要强”不是口号而是实打实的防爆破底线。2.2 把授权粒度做到刚好够用账号建完之后第二步是授权。比如要给 appuser 分配“只读某个业务库”的权限GRANT SELECT ON mydb.* TO appuserlocalhost;如果你想让他能做增删改但不允许他改动表结构就要分开列GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO appuserlocalhost;这里mydb.*表示 mydb 库下面所有表。MySQL 的授权粒度其实是非常细的可以从“所有库”一路细到“某张表”甚至“某个字段”-- 整个实例的所有库 GRANT SELECT ON *.* TO appuserlocalhost; -- 某个库的所有表 GRANT SELECT ON mydb.* TO appuserlocalhost; -- 某张表 GRANT SELECT ON mydb.orders TO appuserlocalhost; -- 某张表的某些列比如只能看订单编号和金额不能看手机号 GRANT SELECT (order_id, amount) ON mydb.orders TO appuserlocalhost;绝大多数业务环境把粒度控制到“库”这一级就够了表和字段级授权用得少因为管理成本和误操作风险都会上升。但有一个场景是例外报表账号。给第三方出数、给 BI 工具拉数据时经常遇到“一个账号要连几个库、但又不能碰某些敏感列”的诉求。字段级授权虽然烦但比另建一张脱敏视图省事。关于 ALL PRIVILEGES 要单独提醒一句GRANT ALL ON mydb.*只是把“该库内”的所有权限都给了它并不包含 GRANT OPTION。也就是说这个账号仍然没有资格把权限转授给别人。如果你想让他当该库的管理员可以额外加WITH GRANT OPTION但一般情况下不建议因为一旦账号沦陷影响范围会被放大。给到 ALL 并且带 GRANT OPTION某种程度上已经等同于这个库的 root 了。2.3 授权之后怎么查、怎么收授权完之后很多人随手敲一个FLUSH PRIVILEGES;这个动作其实不是必需的。用 GRANT 或 REVOKE 修改权限时MySQL 会自动重新加载权限表不需要手动刷新。FLUSH PRIVILEGES 只有在直接用 INSERT、UPDATE、DELETE 操作了mysql.user、mysql.db这些底层权限表时才是必要的。查一个账号当前有什么权限用SHOW GRANTS FOR appuserlocalhost;输出结果里能看到以GRANT ... TO形式展示的权限列表。注意这里必须带上 host 部分和创建账号时的写法保持一致否则可能查出来的是另一个同名账号。想收回权限用 REVOKEREVOKE UPDATE ON mydb.* FROM appuserlocalhost;想彻底删除账号DROP USER appuserlocalhost;这两个操作在生产环境执行前我强烈建议先看一遍SHOW GRANTS和相关库的连接情况尤其是“这个账号是不是还在被生产应用使用”。我踩过一次很蠢的坑某个后台管理系统用的账号第一个人离职前清理“无用账号”直接 DROP 了结果第二天线上系统连不上库全家老小一起排查才发现是账号没了。任何账号删除前先查information_schema.processlist里有没有活跃连接这个习惯能救命。3. 授权之后真正难缠的地方远程连接与 SSL3.1 从应用服务器连接要怎么限定来源场景一升级事情就变复杂了。开发环境只在本机操作appuserlocalhost够用但线上应用一般部署在单独的机器上应用服务器要通过网络连 MySQL这时候账号就得改成允许指定主机访问。通常是这么写CREATE USER appuser192.168.1.100 IDENTIFIED BY App123456; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO appuser192.168.1.100;这里的 IP 就是应用服务器的内网地址。只允许这一个来源比写成%稳妥得多。%是通配符表示“任意主机都能试这个账号密码”配合强密码也许还能扛一阵但只要密码泄露或爆破成功攻击者想从哪连都能连。按最小化原则主机限定也应该收窄。如果应用服务器的 IP 经常变比如动态分配的云主机、容器调度环境可以把来源写成网段CREATE USER appuser192.168.1.% IDENTIFIED BY App123456;这个%只能匹配 IP 段里的最后一部分意思是“192.168.1.x 这个网段的机器都能连”。MySQL 的主机匹配并不是直接按字符串相等来做的而是按精确度从高到低排序带具体 IP 的最优先其次是主机名然后是网段最后才是%。这意味着如果同时存在appuser192.168.1.100和appuser%那个具体 IP 永远会命中前者两个账号密码可以分别设置互不影响。这也是排查账号问题时要特别注意的点你改的可能是%这条记录但应用服务器实际命中的却是具体 IP 那条。3.2 bind-address 和 skip-name-resolve 的坑不少人在配置远程连接的过程中明明账号、授权、防火墙都弄好了客户端还是报Cant connect to MySQL server on x.x.x.x。这时候十有八九是服务端配置文件的问题。默认安装的 MySQLbind-address可能被设置为127.0.0.1意思是只监听本机回环地址外部请求根本到不了 MySQL 进程。改法是在my.cnf或my.ini的[mysqld]段里设置bind-address 0.0.0.00.0.0.0表示监听所有网卡生产环境建议直接写内网 IP比如bind-address 192.168.1.10这样外网网卡不监听少暴露一层。还有skip-name-resolve这个参数它默认在部分发行版的配置里是关闭的。如果打开MySQL 就不会反查客户端 IP 对应的主机名。此时在授权语句里写appuserlocalhost可能没问题但写appuserwebserver.hostname就会失效因为服务端根本不做主机名反解了。反过来如果服务端开启了反解而 DNS 又有问题客户端连接时会出现很长时间的延迟最后报Access denied或解析超时。遇到这类奇怪问题先看一眼my.cnf里有没有skip-name-resolve再决定账号 host 是写 IP 还是主机名。最省心的做法是账号一律用 IP 或网段服务端保持 skip-name-resolve 开启状态不依赖 DNS。3.3 caching_sha2_password 与 SSL 连接报错MySQL 8.0 默认的认证插件是caching_sha2_password这比 5.7 时代的mysql_native_password安全很多。但它有个连锁反应一些老版本的客户端驱动、旧版 JDBC 驱动、部分第三方工具比如很老版本的 Navicat 或 PHP 的 mysql 扩展不认识这个插件连接时会直接报错Authentication plugin caching_sha2_password cannot be loaded还有一类报错更隐蔽初次连接时提示需要 SSL 或 RSA 公钥交换比如Public Key Retrieval is not allowed这是因为我这边的客户端驱动在做 caching_sha2_password 认证时需要向服务端获取 RSA 公钥来加密密码传输。新版 MySQL 客户端默认允许这种行为但一些 JDBC 连接串并没有默认开这个开关。常见处理办法是在 JDBC URL 末尾加allowPublicKeyRetrievaltrueuseSSLfalse不过关闭 SSL 会降低传输安全性。更合理的做法是升级驱动或者给这类老客户端单独创建一个使用旧认证插件的账号CREATE USER legacy_app192.168.1.% IDENTIFIED WITH mysql_native_password BY Legacy123456;说真的如果业务允许我更倾向升级驱动而不是开mysql_native_password因为 caching_sha2_password 本身对密码传输有强保护MySQL 8.x 后续版本对旧插件的支持也只会越来越边缘化。创建用户时如果看到认证插件相关的报错先分清是驱动太老还是服务端配置太激进再决定改哪一头。3.4 Docker 部署 MySQL 时的授权前置步骤现在很多人用 Docker 跑 MySQL这也会影响“创建用户和授权”的流程。一个典型的启动命令会把数据目录和配置文件都挂载出去docker run -d \ --name mysql8 \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDRoot123456 \ -e MYSQL_DATABASEmydb \ -e MYSQL_USERappuser \ -e MYSQL_PASSWORDApp123456 \ -v /opt/mysql/data:/var/lib/mysql \ mysql:8.0注意这里的环境变量MYSQL_USER只会在数据目录是“首次初始化”时生效它创建的账号默认是appuser%而且会授予这个账号MYSQL_DATABASE指定库的全部权限。如果你已经挂载了一个旧的数据目录或者镜像已经初始化过这些环境变量就不会再生效了。很多人犯的错是项目里写了MYSQL_USER环境变量但容器用的数据卷是之前的老数据里面根本不存在这个账号启动后应用自然连接失败。这时候需要进入容器手动补授权或者用docker exec执行 SQLdocker exec -it mysql8 mysql -uroot -p进入后执行常规的CREATE USER和GRANT即可。还要记住容器内 MySQL 的默认配置通常监听3306端口但容器外能否连接取决于-p端口映射和宿主机防火墙这三层里任何一层没通远程连接都会失败。4. 常见问题排查与避坑清单4.1 Access denied 到底是在哪一层被拦的Access denied for user appuserx.x.x.x (using password: YES)是 DBA 们最熟悉也最烦的报错之一。这个报错出现在认证阶段与网络连通性无关。看到它第一反应不是去折腾防火墙而是先猜这么几种可能账号不存在尤其要注意 host 部分是不是匹配不上。你建的可能是appuser192.168.1.100应用却从192.168.1.101连过来匹配不上就报拒绝。密码错了。这个最简单也最容易在事态混乱时被忽略。账号和密码都没问题但skip_name_resolve开着客户端 IP 反解失败导致 host 匹配不上。插件不兼容。比如服务端默认 caching_sha2_password老客户端不支持。排查这类问题我一般从两个信息入手第一SELECT user, host, plugin FROM mysql.user;看账号信息全貌第二看 MySQL 错误日志。日志里通常会记录具体是哪个账号、从哪个 IP 被拒绝能直接定位到“账号不存在”还是“密码错误”。还有一个小技巧在应用服务器上用命令先测一遍排除客户端驱动层面的干扰mysql -uappuser -p -h 192.168.1.10 mydb这一步能连上就说明服务和账号都正常问题出在应用配置或驱动上连不上再按上面的顺序逐步缩小范围。4.2 为什么改了权限还是没生效很多人跑了 GRANT 之后客户端依然报没有权限。最常见的原因不是没刷新而是你改错了账号。因为 MySQL 账号由 user host 双重决定如果程序连接时命中的是appuser192.168.1.%你却在改appuserlocalhost的权限那当然不生效。排查思路很直接在应用服务器上用CURRENT_USER()看一下实际登录身份SELECT CURRENT_USER();返回的结果就会告诉你这次的会话到底是以哪个 userhost 身份进来的。比如返回appuser192.168.1.101那就去改appuser192.168.1.%的权限。这一步能省掉大量瞎猜时间。另一个可能的原因是账号在连接时用的库不属于授权范围内。比如你只授权了mydb.*但客户端连接串里默认的数据库写的是information_schema或者空库某些查询用到未授权的库时自然报SELECT command denied。这不是授权没生效而是连接参数的库名和授权范围不匹配。4.3 localhost 和 % 的微妙关系前面提到过 host 匹配的优先级问题这里单独展开讲。MySQL 在匹配账号来源时会先按 host 的精确度排序而不是按插入顺序。匹配优先级从高到低大致是具体 IP 主机名 网段比如 192.168.1.% 空字符串表示本机匿名%。举例说明。执行下面两条语句CREATE USER test% IDENTIFIED BY pass1; CREATE USER testlocalhost IDENTIFIED BY pass2;在 MySQL 服务器本机执行mysql -utest -p时命中testlocalhost。因为在优先级排序里localhost精确度最高不会落到%。也就是说你用pass2才能在本机登录用pass1反而连不上。这种情况经常让人以为是自己密码打错了其实是 MySQL 暗地里帮你做了账号分流。所以当你发现“同一个用户名不同地方登录行为不一致”的时候一定要去mysql.user表里把所有同名账号列出来看看 host 字段各是什么再判断是不是 host 匹配在捣乱。4.4 忘了 root 密码怎么把权限找回来说句实话再谨慎的人也有过忘记 root 密码的时刻。这个问题的应急方案是在配置文件中临时加一条skip-grant-tables然后重启 MySQL这样无需密码就能进入数据库再自行修改密码。具体流程是编辑my.cnf或my.ini在[mysqld]段下加skip-grant-tables重启 MySQL 服务service mysql restart然后直接无密码进入mysql -uroot进入后先执行FLUSH PRIVILEGES;让权限系统重新加载再改密码ALTER USER rootlocalhost IDENTIFIED BY NewRoot123456;改完后一定要把配置文件里的skip-grant-tables注释掉再重启一次服务。因为这条参数会让 MySQL 跳过所有权限检查任何知道端口的人都能连进来读写数据这是极其危险的临时状态。生产环境操作这个流程时我建议选在流量低峰期并且把防火墙临时收紧到只允许本地管理 IP 访问不然等于大门敞开。4.5 权限回收与账号生命周期管理授权的另一边是回收。一个项目的账号不可能永远不变人离职、系统下线、权限收敛都会涉及“把权限拿回来”这件事。我建议平时就建立一套简单的账号管理清单至少包含三个维度账号对应的应用、最后一次活跃时间、授权范围。定期清点mysql.user表找到那些“看起来还在但已经三个月没有连接记录”的账号。查询活跃连接可以用SELECT user, host, db, command, time, state FROM information_schema.processlist;这个视图会显示当前所有会话。如果一个账号不在任何 processlist 里同时又没有被任何配置引用那它就应该被仔细审查。删除前先SHOW GRANTS留底再用 DROP USER 清理避免像我在前面提到的那样误删核心账号。权限回收还有一个容易被忽略的操作当你调整权限时已建立的连接不会立即失效会话会继续保留原来的权限直到断开。所以线上压权限时除了执行 REVOKE有时候还要配合KILL掉对应会话才能真正生效。这与“授权后不需要 FLUSH PRIVILEGES”并不冲突因为刷新权限缓存和断开已有会话是两个不同维度的问题。5. 一些实战中沉淀的小心得CREATE USER和GRANT这套 SQL 本身不难难点在于“了解这个动作会带来什么影响”。我经历了从开发到运维再到自己管理线上库的转变最大的体会是权限永远要按最小化去给能只读就别给写能只限具体 IP 就别用%能不用 root 就别用 root。密码策略再繁琐也比被拖库之后处理事故轻松百倍。还有一个和标题无关但常常一起出现的小建议尽量把所有账号的创建和授权语句放进一个 SQL 版本管理目录里和代码一起走评审。很多故障不是语法不会而是“忘了当时给这个账号配了什么权限”。把授权语句当成代码一样管理至少事后能查账而不是对着mysql.user表发呆。如果正在看这篇文章的你还处于刚接触 MySQL 的阶段建议这会儿就打开终端把文中的 CREATE USER、GRANT、SHOW GRANTS、REVOKE、DROP USER 全部操作一遍。建错了没关系权限给大了也没关系开发环境本来就是用来试错的。真正重要的是你要把整套流程的操作手感练出来知道每敲一条命令会产生什么后果并且养成授权之后立刻查SHOW GRANTS的习惯。相信我这个习惯会给未来的线上事故排查省下大把时间。