MySQL用户管理完全手册：8组核心命令实战指南

MySQL用户管理是数据库安全的第一道防线。本文一次性梳理CREATE USER、GRANT、REVOKE、DROP USER、ALTER USER、ACCOUNT LOCK、KILL会话、权限审计8组核心命令的完整语法，附MySQL 5.7和8.0差异对照、5个真实生产事故复盘与最佳实践清单。

更新 22 分钟阅读 1,824 阅读

保哥从2010年代开始就一直在用MySQL，这些年带过不少新人，发现MySQL用户管理是大家最容易出错也最容易留下安全隐患的一块。表面上就那几条CREATE USER、GRANT、REVOKE、DROP USER命令，但里面的坑细究下来不少：5.7和8.0语法不一样、localhost和%行为完全不同、FLUSH PRIVILEGES什么时候要执行什么时候不需要、REVOKE撤完了还能登录是怎么回事。这篇我把日常运维里高频用到的MySQL用户管理命令完整梳理一遍，配上自己踩过的坑和验证方法，方便需要的朋友照着用。

理解MySQL的用户与权限模型

开始动手之前，先把概念理清楚，后面踩坑会少很多。

MySQL的用户标识是user加上host这一对组合，不是单纯的用户名。也就是说test@localhost、test@192.168.1.10、test@%这三个是完全独立的账号，可以分别设置不同的密码和不同的权限。host字段支持以下几种写法：

  • localhost：仅允许本机通过Unix Socket登录
  • 127.0.0.1：仅允许本机通过TCP回环登录
  • 192.168.1.10：仅允许这一个具体IP
  • 192.168.1.%：允许192.168.1.0/24整个网段
  • %：允许任意IP（生产环境慎用）

保哥的经验是：host越窄越安全。除非真的没办法预知客户端IP，否则不要写%。在阿里云RDS、腾讯云CDB这类托管数据库上，安全组已经做了第一层网络过滤，但MySQL层面的host限定仍然是必要的纵深防御。

关于权限的存储位置，MySQL 5.7及之前是mysql.user、mysql.db、mysql.tables_priv、mysql.columns_priv四张表分别管理全局、库级、表级、列级权限。MySQL 8.0新增了mysql.global_grants用来管理动态权限（如BINLOG_ADMIN、ROLE_ADMIN），传统的SUPER权限被拆分成多个细粒度动态权限。理解这套表结构，遇到权限疑难杂症时直接SELECT这些表就能定位问题。

创建用户的完整命令清单

登入MySQL：

mysql -u root -p

标准创建语法：

-- 仅本机可登录
CREATE USER 'test'@'localhost' IDENTIFIED BY 'Test@2024Pass';

-- 指定 IP 可登录
CREATE USER 'test'@'192.168.7.22' IDENTIFIED BY 'Test@2024Pass';

-- 指定网段可登录
CREATE USER 'test'@'192.168.7.%' IDENTIFIED BY 'Test@2024Pass';

-- 任意 IP 可登录
CREATE USER 'test'@'%' IDENTIFIED BY 'Test@2024Pass';

MySQL 8.0指定认证插件：MySQL 8.0默认用caching_sha2_password，部分老客户端不兼容，可以显式指定：

-- 显式使用兼容性更好的插件
CREATE USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPass!23';

-- 显式使用更安全的插件（默认）
CREATE USER 'modern_app'@'%' IDENTIFIED WITH caching_sha2_password BY 'ModernPass!23';

创建用户时的常见报错。保哥遇到过最坑的一个错：ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value。这个错通常出现在升级过的MySQL 5.6或5.7实例上，mysql.user表结构异常。我的处理流程是：

# 1. 编辑 my.cnf 或 my.ini，找到 sql-mode
# 原来：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# 改成：
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# 2. 重启 MySQL
sudo systemctl restart mysql

如果还不行，那就是mysql.user表本身需要upgrade：

sudo mysql_upgrade -u root -p

授权语法骨架与场景化模板

用户建好之后默认是没有任何库表权限的，连SHOW DATABASES都看不全。需要单独GRANT。

GRANT语法骨架：

GRANT 权限列表 ON 数据库.表 TO '用户'@'host';

常见的权限列表：

-- 全部权限
GRANT ALL PRIVILEGES ON quant.* TO 'test'@'localhost';

-- 业务系统通常只需 CRUD
GRANT SELECT, INSERT, UPDATE, DELETE ON quant.* TO 'test'@'localhost';

-- 只读账号
GRANT SELECT ON quant.* TO 'reader'@'%';

-- 备份账号
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

-- 管理账号（带 GRANT OPTION，可以再向下分授权）
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.0.0.5' WITH GRANT OPTION;

MySQL 5.7 vs 8.0语法差异。MySQL 5.7允许GRANT ... IDENTIFIED BY一行搞定建用户加授权：

-- 5.7 可用，8.0 报错
GRANT ALL ON quant.* TO 'test'@'localhost' IDENTIFIED BY '123';

MySQL 8.0必须先CREATE USER再GRANT：

-- 8.0 推荐分两步
CREATE USER 'test'@'localhost' IDENTIFIED BY 'Test@2024';
GRANT ALL ON quant.* TO 'test'@'localhost';

保哥写脚本时统一按8.0写法来，可以兼容5.7（5.7也支持分两步），反过来不行。

FLUSH PRIVILEGES什么时候要执行。这是新手最容易迷惑的点。规则其实很简单：用CREATE USER、GRANT、REVOKE、DROP USER、SET PASSWORD、ALTER USER这些正规账号管理语句，不需要FLUSH PRIVILEGES，MySQL会自动重载权限。直接用INSERT、UPDATE、DELETE改mysql.user等系统表，必须FLUSH PRIVILEGES才能生效。现在还在生产环境直接UPDATE mysql.user的人不多了，所以多数场景其实可以省掉这条。但加上也无害，写脚本时为了保险我都会加：

FLUSH PRIVILEGES;

撤销权限的全场景对照

REVOKE是GRANT的反操作，语法对称：

-- 标准格式
REVOKE 权限列表 ON 数据库.表 FROM '用户'@'host';

-- 撤销具体权限
REVOKE INSERT, UPDATE, DELETE ON quant.* FROM 'test'@'localhost';

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON quant.* FROM 'test'@'localhost';

-- 撤销 GRANT OPTION（管理权限）
REVOKE GRANT OPTION ON *.* FROM 'dba'@'10.0.0.5';

-- 一次撤销账户的全局所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'test'@'localhost';

一个常见的迷惑。保哥经常被问：我REVOKE了，为什么test用户还能登录？答：REVOKE撤的是数据库或表权限，不是登录权限。MySQL中只要mysql.user表里有这个user加host记录，密码对就能登录，只是登录后什么也看不到、什么也干不了。要彻底禁止登录，得用DROP USER或者ALTER USER ... ACCOUNT LOCK。

-- 锁定账户（保留账号但禁止登录，8.0 支持）
ALTER USER 'test'@'localhost' ACCOUNT LOCK;

-- 解锁
ALTER USER 'test'@'localhost' ACCOUNT UNLOCK;

删除用户与会话强制踢除

确认账号不再使用了，就直接删掉：

-- 删除单个账号
DROP USER 'test'@'localhost';

-- 一次删多个
DROP USER 'test'@'localhost', 'test'@'192.168.7.22', 'test'@'%';

-- 删除前先确认账号存在
SELECT user, host FROM mysql.user WHERE user = 'test';

注意DROP USER不会断开当前已经连上的会话。如果有人正在用这个账号操作数据库，连接会一直保留到他自己断开为止。要立刻踢人，可以：

-- 找出该用户的所有会话 ID
SELECT id, user, host, db, command, state
FROM information_schema.processlist
WHERE user = 'test';

-- 把对应的会话 KILL 掉
KILL 12345;

修改密码与权限的常用操作

顺手把日常会用到的几个补全，这样就是一个完整的运维手册：

-- 修改自己的密码（任何用户都能改自己的）
ALTER USER USER() IDENTIFIED BY 'NewPass@2024';

-- root 改某个用户的密码
ALTER USER 'test'@'localhost' IDENTIFIED BY 'NewPass@2024';

-- 强制用户下次登录改密码
ALTER USER 'test'@'localhost' PASSWORD EXPIRE;

-- 设置密码 90 天过期
ALTER USER 'test'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 重命名用户
RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';

-- 查看某用户的所有授权
SHOW GRANTS FOR 'test'@'localhost';

-- 查看当前登录用户的授权
SHOW GRANTS;

5个真实生产事故复盘

这一节保哥拿过去这几年帮客户处理过的5个真实MySQL用户管理事故出来对照，让你看到「命令会用」和「不出事故」之间的距离。

事故一：root账号被全网爆破。某做跨境电商的客户，MySQL实例直接暴露在公网3306端口，root@%账号开了远程登录，密码强度还可以但抵不住分布式扫描。两周内被尝试登录127万次，最终在某个晚上被撞库成功，整库被加密勒索。事后保哥介入做的第一件事就是DROP root@%，关闭3306公网访问，root只保留root@localhost。

事故二：业务账号被多个微服务复用。某SaaS客户的应用层有8个微服务，全部用同一个app@%账号连MySQL，权限是ALL PRIVILEGES ON saas.*。某次开发同事写错了一段DELETE脚本误删了几千条订单数据，事后想从binlog反推是哪个微服务执行的，但所有连接都是同一账号根本无法区分。解决方案是按微服务拆分账号app_order@%、app_user@%、app_payment@%等，再开慢日志和审计插件。

事故三：DBA离职没回收权限。某游戏公司DBA离职后2个月，他的私人IP仍然能通过dba@public_ip连进生产库做SELECT。被发现的时候他已经SELECT了用户表27次，幸好没有泄漏外部。补救措施是建立离职checklist，HR确认离职当天必须DROP所有相关账号，且每月跑一次SELECT user, host FROM mysql.user对照在职名单审计。

事故四：FLUSH PRIVILEGES漏跑导致权限不生效。某团队用脚本批量改了mysql.user表的plugin字段（从mysql_native_password改成caching_sha2_password），但忘了FLUSH PRIVILEGES，结果新登录的客户端还在用老插件认证，部分应用层报1045错误。教训是绝对不要直接UPDATE系统表，要改用ALTER USER语法。

事故五：GRANT OPTION滥用引发权限蔓延。某客户的运维同事给所有DBA都开了WITH GRANT OPTION，结果一位DBA把app@%账号也加了GRANT OPTION，应用层程序通过SQL注入漏洞拿到了app账号，然后用GRANT OPTION给自己提权到root级别。修复办法是收回所有非DBA管理员的GRANT OPTION，且业务账号绝不允许任何GRANT权限。

权限审计SQL与监控视图

除了知道命令怎么用，更重要的是建立审计机制。保哥分享几条日常巡检会跑的SQL。

列出所有具有DBA级别权限的账号：

SELECT grantee, privilege_type
FROM information_schema.user_privileges
WHERE privilege_type IN ('SUPER','GRANT OPTION','CREATE USER','RELOAD','SHUTDOWN','PROCESS','FILE')
ORDER BY grantee;

找出所有host为%的账号（潜在风险）：

SELECT user, host, authentication_string, account_locked
FROM mysql.user
WHERE host = '%'
ORDER BY user;

统计每个账号的当前会话数（看看哪些账号活跃）：

SELECT user, host, COUNT(*) AS conn_count
FROM information_schema.processlist
GROUP BY user, host
ORDER BY conn_count DESC;

查找30天没登录过的账号（结合general log或慢日志反向定位）：

-- MySQL 8.0 起有 performance_schema 可以辅助
SELECT user, host, last_login
FROM mysql.user
WHERE password_last_changed < DATE_SUB(NOW(), INTERVAL 90 DAY);

保哥建议把这4条SQL封装成存储过程或者定时任务，每周生成一份权限健康度报告，方便审计追溯。配合企业微信或飞书机器人推送，运维同事手机上就能看到当周权限变化。

生产环境用户管理的最佳实践

保哥这几年经手的项目里，用户管理出问题往往不是命令不会用，而是没有规范。结合多次审计经验，给几条建议：

  1. 一个业务一个账号。不要让多个服务共用一个数据库账号，出问题排查不出谁干的。
  2. 最小权限原则。业务账号只给业务库的CRUD，不给DDL；监控账号只给PROCESS、REPLICATION CLIENT；备份账号只给只读加LOCK TABLES。
  3. host精确到IP。即使有K8s这种动态IP场景，也尽量精确到Pod网段，比如'app'@'10.244.%'。
  4. 密码进密钥管理服务。不要把密码写在代码、配置文件、镜像里，用Vault、KMS、阿里云Secrets Manager这类工具管。
  5. 定期审计mysql.user。每个季度跑一遍SELECT user, host FROM mysql.user，把不再使用的账号DROP掉。
  6. root@%永远不要存在。这个是被扫描器爆破的头号目标，发现立刻删。

常见问题解答

CREATE USER之后必须FLUSH PRIVILEGES吗

不必须。CREATE USER、GRANT、REVOKE、DROP USER等账号管理语句会自动重载权限。只有当你直接UPDATE系统表（如mysql.user）时，才需要FLUSH PRIVILEGES。MySQL 5.7和8.0行为完全一致，写脚本时不需要为了兼容性强制加FLUSH。

为什么我创建了test@%还是连不上

先确认MySQL是否监听了远程地址（bind-address），再检查防火墙和云安全组。MySQL用户授权只是认证层，前面还有网络层。另外MySQL在认证时会按host的精确度匹配，如果同时存在test@localhost和test@%，本机连接会命中前者，密码不匹配就连不上。可以用mysql -h 127.0.0.1这种IP方式强制走TCP而不是Unix Socket，绕过localhost的匹配。

REVOKE ALL之后用户为什么还能登录

因为REVOKE撤的是库表权限，不是登录权限。要禁止登录用ALTER USER ... ACCOUNT LOCK；要彻底删除用DROP USER。LOCK的好处是保留账号信息（包括密码、host、过期时间），未来如果要恢复，UNLOCK一下就行；DROP是彻底删除，恢复要重新建。

MySQL 8.0用Navicat 11连不上怎么办

八成是认证插件不兼容。把账号的认证插件改成mysql_native_password：ALTER USER user@host IDENTIFIED WITH mysql_native_password BY pass； 或者升级Navicat到12以上版本。MySQL Workbench 8.0以上、DataGrip 2019.3以上、HeidiSQL 11.0以上都原生支持caching_sha2_password，没必要为了一个老GUI让全公司密码插件降级。

如何批量把所有账号的密码插件从mysql_native_password改成caching_sha2_password

不要直接UPDATE mysql.user表，会出问题。标准做法是用一条SQL生成ALTER USER脚本：SELECT CONCAT(ALTER USER ', user, @, host, ' IDENTIFIED WITH caching_sha2_password BY ', password, '; ') FROM mysql.user WHERE plugin = mysql_native_password。把生成的脚本人工review后逐条执行。注意密码要从应用层重新分发，因为新插件用了新的哈希算法，老密码不会自动迁移。

K8s场景下host字段应该怎么配

K8s的Pod IP是动态的，但Pod所在网段是固定的。最常见的方案是给业务账号配Pod CIDR对应的网段host，比如app@10.244.%。如果使用Calico等CNI且开启了Network Policy，可以进一步限制只允许特定namespace的Pod访问数据库，这是更纵深的防御。也可以用ServiceAccount + Vault动态生成短期数据库账号（每小时轮换），这是云原生最佳实践但落地成本较高。

写在最后

MySQL用户管理不是高深技术，但一个公司数据库的安全防线第一道就建立在这里。保哥的建议是把这套命令写成模板，每次新建账号都从模板生成，host段、权限范围、密码策略全部规范化，用一年以后你会发现安全事件少了一大半。技术不是壁垒，规范才是。最后再强调一句：所有用户管理操作都要在审计日志里留痕，事后可追溯，这比任何防御技术都更重要。

分享到
QQ 微博 X Facebook LinkedIn Telegram Reddit
标签
版权声明

本文标题：《MySQL用户管理完全手册：8组核心命令实战指南》

本文链接：https://zhangwenbao.com/mysql-creates-users-authorizes-revoke-privileges-and-removes-user-commands.html

版权声明：本文原创，转载请注明出处和链接。许可协议： CC BY-NC-SA 4.0

继续阅读

← 上一篇

DedeCMS 列表页无缩略图不显示：arc.listview.class.php 改造、runphp 单双引号陷阱与 CSS 现代化方案

下一篇 →

Notepad++批量删除空白行的三种实战方法与避坑指南
发表评论
分享到微信 或在下方手动填写
支持 Ctrl + Enter 提交