MySQL用户管理与权限设置实战：从GRANT到远程连接排查

接手过不少MySQL环境，也帮人排查过很多数据库问题，发现真正让运维和开发头疼的，往往不是SQL写得不好，而是用户管理和权限设置这块没搞清爽。尤其是线上环境，账号多了、权限乱了，要么是开发抱怨连不上库，要么是安全审计发现问题，最后都得回来补课。

这篇文章不绕弯子，就把MySQL用户管理和权限设置从头到尾捋一遍。包括用户是怎么组织的、权限分几层、GRANT到底怎么写、flush privileges用还是不用、远程连不上怎么排查，以及忘记root密码之后的紧急处理。内容覆盖MySQL 5.7和8.0两个主流版本，遇到差异我会单独指出来。适合刚入门的新手，也适合干了几年但没系统整理过这套东西的同行。

1. MySQL用户体系：先搞清楚“用户”到底是什么

1.1 用户信息存在哪里

很多初学者以为MySQL用户是某种独立于数据的配置文件，其实不然。MySQL的所有账号信息、权限信息、密码散列值，都存放在系统自带的mysql数据库里。核心的表有这么几张：

• user：全局账号信息，包括用户名、可登录的主机、认证插件、密码散列、全局权限，以及账号是否锁定、密码过期时间等。
• db：库级别的权限记录，记录了哪个用户对哪个数据库有哪些操作权限。
• tables_priv：表级别权限。
• columns_priv：列级别权限。
• procs_priv：存储过程和函数级别的权限。
• global_grants：8.0新增的动态全局权限管理表。

所以你在命令行里看到的mysql.user表，就是整个用户体系的核心。理解这一点对后续排查很重要：有时候权限看起来不对，直接翻这几张表，比来回试SQL直观得多。

注意：千万别手动去UPDATE或DELETE mysql.user表里的记录，除非你非常清楚原理并且确实需要离线修改。直接改表绕过MySQL的权限管理逻辑，容易出现数据不一致，升级版本时也可能出问题。常规操作用CREATE USER、ALTER USER、DROP USER这类SQL就够了。

1.2 用户由“用户名+主机”组成

这是MySQL用户体系里最容易忽略的概念。MySQL里的一个用户，不是简单的'zhangsan'，而是'zhangsan'@'localhost'这样一个组合。同一时刻，可以存在'zhangsan'@'localhost'和'zhangsan'@'192.168.1.%'，这是两个完全独立的账号，密码、权限都互不影响。

主机部分支持多种写法：

• localhost：只允许本机通过socket连接。
• 127.0.0.1：只允许本机通过TCP回环地址连接。
• %：允许任意主机连接，这是最宽松的写法。
• 192.168.1.%：允许192.168.1网段。
• %example.com：允许example.com域名的机器。

很多“为什么我授权了还是连不上”的问题，根源就在这里。比如你执行了GRANT ALL ON testdb.* TO 'app'@'localhost'，然后从另一台机器用app账号连，那肯定连不上。因为MySQL认为你要连接的用户是app@'192.168.x.x'，而不是app@'localhost'，这个账号根本不存在。

连接时MySQL会按精确度匹配主机部分，规则是：先匹配最精确的，比如localhost、具体IP，再匹配模糊的%。但要注意，一旦存在冲突，执行SELECT等操作时会按匹配到的记录逐条验证权限，所以一般不建议同用户名建多个不同host的账号，容易埋坑。

1.3 认证插件差异：8.0和5.7的坑

MySQL 5.7默认的认证插件是mysql_native_password，MySQL 8.0改成了caching_sha2_password。这个改动本身更安全，但带来了一个常见的兼容性问题：老版本客户端（比如Navicat旧版、某些老旧驱动）不认识新插件，就会出现“Authentication plugin 'caching_sha2_password' cannot be loaded”之类的报错。

我个人的处理原则是：如果是全新项目，直接用MySQL 8.0默认的caching_sha2_password，客户端驱动尽量升级到兼容版本；如果被老客户端卡住，再针对个别账号改成mysql_native_password。改法在创建用户时指定：

CREATE USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPass123!';

或者在已有账号上修改：

ALTER USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPass123!';

注意这种兼容性处理只针对特定账号，不要全局把默认认证插件换回去，否则8.0的安全性提升就白做了。

2. 用户管理实操：创建、修改、删除与密码处理

2.1 创建用户的完整语法

MySQL创建用户的标准语句是CREATE USER，它的好处是账号和密码一步到位，并且不会自动赋予任何权限，安全边界比较清晰。

CREATE USER 'app_user'@'192.168.10.%' IDENTIFIED BY 'App@123456';

这条语句创建了一个允许192.168.10.0/24网段连接、密码为App@123456的账号。在MySQL 8.0里，密码默认按caching_sha2_password处理；在5.7里默认是mysql_native_password。

如果你希望账号初始就是锁定状态，后续确认没问题再解锁，可以这样：

CREATE USER 'temp_user'@'%' IDENTIFIED BY 'Temp@123456' ACCOUNT LOCK;

这样创建的账号无法登录，直到你执行ALTER USER ... ACCOUNT UNLOCK。这个技巧在批量创建账号、走审批流程的场景下很实用，避免“建完就能连”的安全隐患。

创建时可以顺带设置密码过期策略：

CREATE USER 'user1'@'%' IDENTIFIED BY 'Pass@123456' PASSWORD EXPIRE INTERVAL 90 DAY;

表示密码90天后过期，过期后必须修改密码才能继续操作。对需要满足合规要求的系统，给业务账号统一加上周期过期是很常见的做法。

2.2 修改用户信息和删除用户

改用户名用的是RENAME USER：

RENAME USER 'old_name'@'%' TO 'new_name'@'%';

要注意的是，改名操作会影响已授权的权限记录，MySQL会自动把它们迁移到新用户名下。一般不建议频繁改名，容易让权限审计记录变混乱。

删除用户用DROP USER：

DROP USER 'temp_user'@'%';

在MySQL 5.7之前，删除用户有时候需要先执行REVOKE ALL PRIVILEGES再DROP USER，5.7之后一条DROP USER会连带把该用户在所有权限表里的记录清干净。如果你遇到老版本环境，稳妥起见可以先看下mysql.user、mysql.db等表里有没有残留记录。

2.3 修改密码的几种方式

修改密码的姿势比较多，我按推荐的优先级排序：

• 用ALTER USER，这是最标准的方式：

ALTER USER 'app_user'@'192.168.10.%' IDENTIFIED BY 'NewPass@123';

• 用SET PASSWORD，可以只针对当前登录用户：

SET PASSWORD = 'NewPass@123';

也可以指定账号：

SET PASSWORD FOR 'app_user'@'192.168.10.%' = 'NewPass@123';

• 在命令行用mysqladmin改密码，适合脚本化操作：

mysqladmin -u root -p'OldPass' password 'NewPass123'

注意：命令行里直接写密码会把密码留在shell历史记录里，线上环境建议用MYSQL_PWD环境变量配合交互式输入，或者用mysql_config_editor工具。这些细节看起来小，但安全审计时都会被翻出来。

2.4 账号锁定、解锁与密码过期管理

账号被锁定的表现是：无论密码对不对，都会提示Access denied。锁定和解锁的SQL如下：

ALTER USER 'app_user'@'%' ACCOUNT LOCK; ALTER USER 'app_user'@'%' ACCOUNT UNLOCK;

这个功能最常见的应用场景有两个：一是员工离职但暂时不能删账号时先锁住，保证即使密码泄露也无法登录；二是发现某账号有异常连接行为，先锁再排查。

查看账号的锁定状态和密码过期时间，可以查mysql.user表的account_locked和password_expired字段，MySQL 8.0里还有password_last_changed、password_lifetime等字段。日常巡检时拉一下这个列表，能及时发现长期未改密码或异常锁定的账号。

3. 权限体系拆解：层级、授权与回收

3.1 MySQL的权限层级

MySQL的权限不是一把抓的，而是分成了几个层级，理解了这个层级，你才知道GRANT语句里ON后面到底该写什么：

• 全局权限：作用于所有数据库。用ON *.*表示，存在mysql.user表。
• 库级权限：作用于指定数据库下的所有对象。用ON db_name.*表示，存在mysql.db表。
• 表级权限：作用于某张表。用ON db_name.table_name表示，存在mysql.tables_priv表。
• 列级权限：作用于某几个列。用ON db_name.table_name (col1, col2)表示，存在mysql.columns_priv表。
• 存储过程/函数权限：作用于指定的存储过程或函数，存在mysql.procs_priv表。

权限判断的逻辑是“按最小范围累加或合并”：连接时校验全局权限；执行具体操作时，MySQL会先看有没有全局权限，没有就往下找库级，再往下找表级，再是列级。也就是说，某用户对db1有全部权限，但对全局没有任何权限，那他在db1里可以随便操作，到了db2就什么也做不了。

3.2 常用权限清单与最小权限原则

先列一个常用权限速查表，后面授权时直接对着选：

实际操作中，我强烈建议坚持最小权限原则：一个账号只给完成业务最低限度需要的权限。业务应用账号一般给SELECT, INSERT, UPDATE, DELETE就够，除非确实需要建表才加CREATE。报表只读账号就只给SELECT。给过头了，一旦账号被拖库或者SQL注入，破坏范围会成倍放大。

3.3 GRANT与REVOKE的实操写法

授权的基本语法是：

GRANT 权限列表 ON 权限层级 TO '用户'@'主机';

几个常见写法：

-- 给全局所有权限（一般只用于管理员账号） GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%'; -- 给某个库下所有表的查询、插入、更新、删除权限 GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app_user'@'192.168.10.%'; -- 给某张表的只读权限 GRANT SELECT ON myapp_db.orders TO 'report_user'@'%'; -- 给某几个列的查询权限（列级权限比较少见，但对敏感字段隔离很有用） GRANT SELECT (user_name, user_email) ON myapp_db.users TO 'privacy_user'@'%';

回收权限对应的语句是REVOKE：

-- 回收某账号对某库的DELETE权限，保留其他权限 REVOKE DELETE ON myapp_db.* FROM 'app_user'@'192.168.10.%'; -- 回收该账号在全局的所有权限 REVOKE ALL PRIVILEGES ON *.* FROM 'app_user'@'192.168.10.%';

注意REVOKE ALL PRIVILEGES不会删除账号本身，只是清空权限。账号还能不能登录，取决于账号本身是否存在且未锁定。

3.4 flush privileges到底是不是必须的

这个问题几乎每次培训都会有人问。先说结论：如果用户是用CREATE USER、GRANT、REVOKE、ALTER USER这些SQL语句操作的，那么不需要执行FLUSH PRIVILEGES，权限会自动生效。

FLUSH PRIVILEGES的真正用途是：当你直接修改了mysql.user等系统权限表（比如用INSERT、UPDATE操作了表记录），需要让它重新加载权限缓存。换句话说，日常正规操作下，这条命令基本用不上。

但我见过太多人养成了一个习惯：每次授权后都来一句FLUSH PRIVILEGES。问题倒是不大，但它属于“无效且可能带来短暂锁”的操作。而且在主从环境下，多执行一次无意义的FLUSH还会增加不必要的binlog事件。

重要提醒：如果你真的走了“直接改表”这条路，记得不仅要在改完后执行FLUSH PRIVILEGES，还要先备份原表。虽然我不推荐这种操作方式，但紧急恢复场景下确实有人这么干过。

4. 典型场景演练：从最小权限到管理员

4.1 场景一：给业务应用创建账号

这是最常用的场景。假设你的应用部署在192.168.10.0/24网段，需要访问mall数据库，平时只做增删改查，不需要改表结构：

CREATE USER 'mall_app'@'192.168.10.%' IDENTIFIED BY 'Mall@2024App'; GRANT SELECT, INSERT, UPDATE, DELETE ON mall.* TO 'mall_app'@'192.168.10.%';

如果应用后续需要执行DDL（比如自动迁移表结构），再把CREATE, ALTER, INDEX, DROP加上。但请注意，这些权限放开后风险明显上升，建议在测试环境验证好，必要时限制来源IP更严格一些。

如果应用需要批量导入导出，可能还要FILE权限。但FILE权限能让用户读写服务器本机文件，风险极大，非特殊需求尽量不要给。可以用其他方式（如通过中间层）完成数据导入导出。

4.2 场景二：给报表或BI工具创建只读账号

报表系统只需要读数据，那这个账号就只给SELECT：

CREATE USER 'bi_reader'@'%' IDENTIFIED BY 'Bi@ReadOnly2024'; GRANT SELECT ON mall.* TO 'bi_reader'@'%';

如果报表工具还需要看视图定义，加上SHOW VIEW。如果涉及存储过程调用，再加EXECUTE。有极个别报表框架在连接时会检查SHOW DATABASES，在未授权其他库的情况下，它只能看到有权限的库，不需要额外授权。

只读账号的一个隐藏坑是：SELECT权限不能让用户看到binlog和主从状态，这些属于全局权限REPLICATION CLIENT，别混为一谈。

4.3 场景三：给DBA创建管理员账号

管理员账号一般在*.*级别授权，但不建议所有DBA都用root。可以给每个人建独立管理员账号，并限制只能从办公网段登录：

CREATE USER 'dba_zhang'@'10.0.0.%' IDENTIFIED BY 'Dba@Pass2024'; GRANT ALL PRIVILEGES ON *.* TO 'dba_zhang'@'10.0.0.%' WITH GRANT OPTION;

WITH GRANT OPTION允许该用户把自己拥有的权限再授给其他用户，这是管理员账号的关键标志。如果不需要下放权限，就别加这个选项。

MySQL 8.0还引入了更细的动态权限，比如BACKUP_ADMIN、SHUTDOWN、SYSTEM_VARIABLES_ADMIN等。有精细化管理需求时，可以给DBA分批授予这些权限，而不是一把梭的ALL PRIVILEGES。不过说实话，在中小团队里这样做有点过度设计，按需来就行。

4.4 场景四：配置主从复制账号

搭建主从复制时，需要在主库创建一个复制专用账号，切记不要用业务账号或root来复制。标准授权如下：

CREATE USER 'repl'@'192.168.20.%' IDENTIFIED BY 'Repl@2024Sync'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.20.%';

这里REPLICATION SLAVE是必须的，它让从库能用这个账号去主库拉binlog。REPLICATION CLIENT是可选的，给了之后可以执行SHOW MASTER STATUS、SHOW REPLICA STATUS来查看复制状态，方便排查问题。

注意这两个权限都是全局权限，所以ON后面只能写*.*，写成具体库名会报语法错误。这个知识点在面试里也经常出现，值得记牢。

4.5 场景五：忘记root密码的紧急处理

每个人迟早会遇到一次。处理方式大同小异，核心思路是先绕过权限验证启动MySQL，然后登录进去改密码。

先停掉MySQL服务：

systemctl stop mysqld # 或者 service mysql stop

然后以跳过授权表的方式启动：

mysqld_safe --skip-grant-tables --skip-networking &

注意务必加上--skip-networking，否则在跳过权限检查的状态下，任何人都能通过网络连上你的数据库，跟裸奔没区别。

这一步的另一种做法是在/etc/my.cnf的[mysqld]段临时加一行skip-grant-tables，启动完再删掉重启。两种方式效果一样，看个人习惯。

接着无密码登录：

mysql -uroot

登录后先刷新权限，让ALTER USER可以用：

FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewRootPass@123';

改完后把加了--skip-grant-tables的启动方式停掉，用正常方式启动MySQL，再用新密码登录验证。

这里有个我自己踩过的坑：MySQL 5.7.6之后，skip-grant-tables状态下如果不先FLUSH PRIVILEGES就执行ALTER USER，可能会报错。所以顺序一定要对：先FLUSH PRIVILEGES，再改密码。

5. 常见问题与排查技巧实录

5.1 error 2002：Can't connect to local MySQL server through socket

这个几乎每天都有人在群里问。报错一般长这样：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

它的本质是：客户端用socket文件连接本机MySQL，但找不到那个socket文件。主要原因有：

• MySQL服务没启动。
• socket文件路径不对，MySQL生成的socket在/var/lib/mysql/mysql.sock，但客户端默认找/tmp/mysql.sock。
• 磁盘满了，MySQL进程无法写socket文件。

排查顺序是：先确认进程ps -ef | grep mysqld，再确认端口sockstat -l | grep 3306，最后用mysqladmin -u root -p status试试。如果服务正常，通常可以在连接命令里指定socket路径：

mysql -u root -p --socket=/var/lib/mysql/mysql.sock

这个报错和“用户不存在”没有直接关系。但如果你在socket连接时被提示Access denied，那就是另外一回事，往下看第5.3节。

5.2 授权之后不生效，是不是忘了flush？

如果你用的是GRANT、CREATE USER、ALTER USER，授权后不需要FLUSH PRIVILEGES也会立刻生效。但如果连接池里已经存在旧连接，这些连接持有的权限不会因为你的授权变化而动态刷新。也就是说，新权限对“新建立的连接”生效，已存在的连接可能继续按旧权限执行。

所以排查“权限不生效”时，先确认是不是用之前的老连接在测试，试着断开重连一次看看。另外一个容易忽略的点是：如果账号同时存在'user'@'%'和'user'@'具体IP'两个记录，用户实际匹配到的是更精确的那条，它上面的权限才是生效的权限。

5.3 账号存在却连不上？host匹配在捣乱

典型的报错是：

ERROR 1045 (28000): Access denied for user 'app'@'192.168.10.55' (using password: YES)

很多人第一反应是密码错了，但其实还有一种可能：你创建的是'app'@'localhost'，而客户端是从192.168.10.55发起的连接。MySQL匹配不到'app'@'192.168.10.55'这个账号，于是拒绝。

排查时可以执行：

SELECT user, host, plugin, account_locked FROM mysql.user WHERE user = 'app';

看看到底建了哪些账号。如果要允许远程，确认是否有'app'@'%'或'app'@'192.168.%'这样的记录。

5.4 Navicat / Workbench连接报错：认证插件不兼容

Navicat老版本连MySQL 8.0时，经常报：

Authentication plugin 'caching_sha2_password' cannot be loaded

这个问题有两类解法：

• 升级Navicat到支持caching_sha2_password的版本。
• 把账号认证插件改成mysql_native_password：

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'App@123456';

同理，用MySQL Workbench的时候，如果版本偏旧，也可能出现类似问题，优先升级客户端工具而不是迁就旧插件。

5.5 权限表损坏或升级后的残留问题

MySQL升级版本后，偶尔会出现权限相关的诡异问题，比如授权语句报错、账号消失、权限缺失。这时候通常需要执行升级检查：

mysql_upgrade -u root -p

MySQL 8.0.16之后mysql_upgrade已经弃用，启动服务时会自动执行升级检查。如果你还是在旧版本上做升级，跑一次mysql_upgrade可以修复系统表结构不匹配的问题。

如果权限表真的损坏了，修复思路是先用--skip-grant-tables方式启动，把mysql库备份出来，然后重建系统表。这个操作比较冷门，真遇到了建议先咨询有经验的人，不要贸然删表重建。

6. 权限管理的一些实操心得

最后聊几个我在实际运维中沉淀下来的习惯，不一定写成标准规范，但确实帮我省了不少麻烦。

第一，所有账号的密码都必须走密码管理器或配置中心，禁止明文写在代码仓库里。每一次密码变更，都同步到配置中心，避免开发本地配一套、测试环境配一套，最后线上连不上又来找DBA。

第二，账号命名要有规范。我习惯用“用途_环境”的格式，比如mall_app_prod、mall_ro_staging、bi_ro_prod，看名字就知道这个账号干嘛用的、在哪个环境、是读写还是只读。比user1、test这种命名强太多了。

第三，定期巡检mysql.user表。我一般是每季度拉一次全部账号列表，检查有没有长期未使用的账号、host为%的高权限账号、锁定状态异常的账号。MySQL 8.0里可以直接用sys.user_summary视图辅助分析。

第四，用SHOW GRANTS评估账号实际权限，不要只盯着grant语句看。比如：

SHOW GRANTS FOR 'mall_app'@'192.168.10.%';

这条命令会列出该账号实际拥有的所有权限，是排查问题最快的手段。

第五，权限变更走工单或至少走个记录。哪怕在小团队里，也建议在变更记录里写明：谁在什么时间给哪个账号加了什么权限，原因是什么。真要出问题的时候，这点记录能救命。

MySQL的用户管理和权限设置，本质上就是“最小权限”这四个字落实到每一个账号上。多花点时间把账号和权限梳理清楚，比你装好数据库后天天救火要轻松得多。这套东西说难不难，但确实是从新手到能扛事儿的DBA之间必须迈过的一道坎。