接手过不少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_grants8.0新增的动态全局权限管理表。所以你在命令行里看到的mysql.user表就是整个用户体系的核心。理解这一点对后续排查很重要有时候权限看起来不对直接翻这几张表比来回试SQL直观得多。注意千万别手动去UPDATE或DELETE mysql.user表里的记录除非你非常清楚原理并且确实需要离线修改。直接改表绕过MySQL的权限管理逻辑容易出现数据不一致升级版本时也可能出问题。常规操作用CREATE USER、ALTER USER、DROP USER这类SQL就够了。1.2 用户由“用户名主机”组成这是MySQL用户体系里最容易忽略的概念。MySQL里的一个用户不是简单的zhangsan而是zhangsanlocalhost这样一个组合。同一时刻可以存在zhangsanlocalhost和zhangsan192.168.1.%这是两个完全独立的账号密码、权限都互不影响。主机部分支持多种写法localhost只允许本机通过socket连接。127.0.0.1只允许本机通过TCP回环地址连接。%允许任意主机连接这是最宽松的写法。192.168.1.%允许192.168.1网段。%example.com允许example.com域名的机器。很多“为什么我授权了还是连不上”的问题根源就在这里。比如你执行了GRANT ALL ON testdb.* TO applocalhost然后从另一台机器用app账号连那肯定连不上。因为MySQL认为你要连接的用户是app192.168.x.x而不是applocalhost这个账号根本不存在。连接时MySQL会按精确度匹配主机部分规则是先匹配最精确的比如localhost、具体IP再匹配模糊的%。但要注意一旦存在冲突执行SELECT等操作时会按匹配到的记录逐条验证权限所以一般不建议同用户名建多个不同host的账号容易埋坑。1.3 认证插件差异8.0和5.7的坑MySQL 5.7默认的认证插件是mysql_native_passwordMySQL 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_user192.168.10.% IDENTIFIED BY App123456;这条语句创建了一个允许192.168.10.0/24网段连接、密码为App123456的账号。在MySQL 8.0里密码默认按caching_sha2_password处理在5.7里默认是mysql_native_password。如果你希望账号初始就是锁定状态后续确认没问题再解锁可以这样CREATE USER temp_user% IDENTIFIED BY Temp123456 ACCOUNT LOCK;这样创建的账号无法登录直到你执行ALTER USER ... ACCOUNT UNLOCK。这个技巧在批量创建账号、走审批流程的场景下很实用避免“建完就能连”的安全隐患。创建时可以顺带设置密码过期策略CREATE USER user1% IDENTIFIED BY Pass123456 PASSWORD EXPIRE INTERVAL 90 DAY;表示密码90天后过期过期后必须修改密码才能继续操作。对需要满足合规要求的系统给业务账号统一加上周期过期是很常见的做法。2.2 修改用户信息和删除用户改用户名用的是RENAME USERRENAME USER old_name% TO new_name%;要注意的是改名操作会影响已授权的权限记录MySQL会自动把它们迁移到新用户名下。一般不建议频繁改名容易让权限审计记录变混乱。删除用户用DROP USERDROP USER temp_user%;在MySQL 5.7之前删除用户有时候需要先执行REVOKE ALL PRIVILEGES再DROP USER5.7之后一条DROP USER会连带把该用户在所有权限表里的记录清干净。如果你遇到老版本环境稳妥起见可以先看下mysql.user、mysql.db等表里有没有残留记录。2.3 修改密码的几种方式修改密码的姿势比较多我按推荐的优先级排序用ALTER USER这是最标准的方式ALTER USER app_user192.168.10.% IDENTIFIED BY NewPass123;用SET PASSWORD可以只针对当前登录用户SET PASSWORD NewPass123;也可以指定账号SET PASSWORD FOR app_user192.168.10.% NewPass123;在命令行用mysqladmin改密码适合脚本化操作mysqladmin -u root -pOldPass 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库/表/索引创建库、表、索引DROP库/表/视图删除库、表、视图等ALTER表修改表结构INDEX表创建和删除索引REFERENCES表创建外键CREATE VIEW视图创建视图SHOW VIEW视图查看视图定义CREATE ROUTINE存储过程/函数创建存储过程和函数ALTER ROUTINE存储过程/函数修改和删除存储过程、函数EXECUTE存储过程/函数执行存储过程和函数TRIGGER表管理触发器LOCK TABLES表显式锁表需要有SELECT权限RELOAD全局执行FLUSH操作SHUTDOWN全局关闭MySQL服务PROCESS全局查看所有线程执行SHOW PROCESSLISTSUPER全局超级权限8.0后拆分为多个动态权限REPLICATION SLAVE全局主从复制中从库连接主库所需权限REPLICATION CLIENT全局查看主从状态SHOW MASTER STATUS等实际操作中我强烈建议坚持最小权限原则一个账号只给完成业务最低限度需要的权限。业务应用账号一般给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_user192.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_user192.168.10.%; -- 回收该账号在全局的所有权限 REVOKE ALL PRIVILEGES ON *.* FROM app_user192.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_app192.168.10.% IDENTIFIED BY Mall2024App; GRANT SELECT, INSERT, UPDATE, DELETE ON mall.* TO mall_app192.168.10.%;如果应用后续需要执行DDL比如自动迁移表结构再把CREATE, ALTER, INDEX, DROP加上。但请注意这些权限放开后风险明显上升建议在测试环境验证好必要时限制来源IP更严格一些。如果应用需要批量导入导出可能还要FILE权限。但FILE权限能让用户读写服务器本机文件风险极大非特殊需求尽量不要给。可以用其他方式如通过中间层完成数据导入导出。4.2 场景二给报表或BI工具创建只读账号报表系统只需要读数据那这个账号就只给SELECTCREATE USER bi_reader% IDENTIFIED BY BiReadOnly2024; GRANT SELECT ON mall.* TO bi_reader%;如果报表工具还需要看视图定义加上SHOW VIEW。如果涉及存储过程调用再加EXECUTE。有极个别报表框架在连接时会检查SHOW DATABASES在未授权其他库的情况下它只能看到有权限的库不需要额外授权。只读账号的一个隐藏坑是SELECT权限不能让用户看到binlog和主从状态这些属于全局权限REPLICATION CLIENT别混为一谈。4.3 场景三给DBA创建管理员账号管理员账号一般在*.*级别授权但不建议所有DBA都用root。可以给每个人建独立管理员账号并限制只能从办公网段登录CREATE USER dba_zhang10.0.0.% IDENTIFIED BY DbaPass2024; GRANT ALL PRIVILEGES ON *.* TO dba_zhang10.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 repl192.168.20.% IDENTIFIED BY Repl2024Sync; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.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 rootlocalhost IDENTIFIED BY NewRootPass123;改完后把加了--skip-grant-tables的启动方式停掉用正常方式启动MySQL再用新密码登录验证。这里有个我自己踩过的坑MySQL 5.7.6之后skip-grant-tables状态下如果不先FLUSH PRIVILEGES就执行ALTER USER可能会报错。所以顺序一定要对先FLUSH PRIVILEGES再改密码。5. 常见问题与排查技巧实录5.1 error 2002Cant connect to local MySQL server through socket这个几乎每天都有人在群里问。报错一般长这样ERROR 2002 (HY000): Cant 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 app192.168.10.55 (using password: YES)很多人第一反应是密码错了但其实还有一种可能你创建的是applocalhost而客户端是从192.168.10.55发起的连接。MySQL匹配不到app192.168.10.55这个账号于是拒绝。排查时可以执行SELECT user, host, plugin, account_locked FROM mysql.user WHERE user app;看看到底建了哪些账号。如果要允许远程确认是否有app%或app192.168.%这样的记录。5.4 Navicat / Workbench连接报错认证插件不兼容Navicat老版本连MySQL 8.0时经常报Authentication plugin caching_sha2_password cannot be loaded这个问题有两类解法升级Navicat到支持caching_sha2_password的版本。把账号认证插件改成mysql_native_passwordALTER USER app_user% IDENTIFIED WITH mysql_native_password BY App123456;同理用MySQL Workbench的时候如果版本偏旧也可能出现类似问题优先升级客户端工具而不是迁就旧插件。5.5 权限表损坏或升级后的残留问题MySQL升级版本后偶尔会出现权限相关的诡异问题比如授权语句报错、账号消失、权限缺失。这时候通常需要执行升级检查mysql_upgrade -u root -pMySQL 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_app192.168.10.%;这条命令会列出该账号实际拥有的所有权限是排查问题最快的手段。第五权限变更走工单或至少走个记录。哪怕在小团队里也建议在变更记录里写明谁在什么时间给哪个账号加了什么权限原因是什么。真要出问题的时候这点记录能救命。MySQL的用户管理和权限设置本质上就是“最小权限”这四个字落实到每一个账号上。多花点时间把账号和权限梳理清楚比你装好数据库后天天救火要轻松得多。这套东西说难不难但确实是从新手到能扛事儿的DBA之间必须迈过的一道坎。