用户管理MySQL这件事很多人觉得简单——不就是CREATE USER加GRANT两三句 SQL 的事吗但我在一线折腾数据库这几年发现线上大多数“连不上”“没权限”“账号被锁”的故障最后都能追到用户管理上。不是密码写错就是 host 匹配错了再就是权限给宽了或者给窄了。更麻烦的是MySQL 8.0 之后认证插件改了、密码策略有了、角色能用了老一套经验经常直接失效。这篇我就把自己实际用过、踩过坑的 MySQL 用户管理完整流程整理出来包括权限模型、创建用户、授权回收、密码重置、远程连接排查以及连接池、主从复制、容器部署里的账号配置细节。不管你是刚入门还是已经在维护生产库照着这套思路走能省掉一大半的冤枉路。1. 用户管理到底管的是什么先从权限模型说起1.1 MySQL 账号不是“用户名密码”这么简单MySQL 的账号体系和其他数据库有个特别大的差异一个账号由user和host两个字段共同决定。你可以把host理解成“这个账号允许从哪里登录”。比如app_userlocalhost只能从本机登录app_user192.168.1.100只能从固定 IP 登录app_user192.168.1.%只能从 192.168.1 网段登录app_user%可以从任意主机登录这意味着app_userlocalhost和app_user%是两个完全独立的账号密码可以不同权限也可以不同。很多初学者只创建了一个app_user%然后用mysql -u app_user -p不带-h去连本机结果报密码错误或者权限不足。原因就是本机连接默认匹配的是app_userlocalhost而这个账号根本不存在或者存在但是密码不一样。想看清库里的账号可以查mysql.user表SELECT user, host, authentication_string, plugin, password_expired, account_locked FROM mysql.user;这里的authentication_string就是密码的哈希值不要尝试解读它plugin表示认证插件MySQL 8.0 默认是caching_sha2_passwordpassword_expired和account_locked则是密码是否过期、账号是否锁定的标识。1.2 权限层级从全局到行列MySQL 的权限是分层的常用层级从大到小分别是全局权限*.*影响所有库表例如SUPER、PROCESS、REPLICATION SLAVE数据库权限db_name.*影响某个库里的所有对象表权限db_name.table_name只能操作某一张表列权限甚至可以细到只能操作某几列存储过程/函数权限单独控制能否执行实际工作中用得最多的是数据库权限和表权限。用一条GRANT可以同时授权多个操作比如GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO app_user192.168.1.%;这里特别注意ON app_db.*表示 app_db 库下所有表但不包含CREATE DATABASE等库级管理操作。ON *.*是全局权限给应用账号时非常危险。生产环境我见过有人为了省事给应用账号执行了GRANT ALL PRIVILEGES ON *.*结果应用被注入后整个实例的数据任人摆布。最小权限原则不是口号是保命的。下面列一下常用权限的典型用途方便对照规划权限典型用途建议授予对象SELECT只读查询报表、BI、只读从库账号INSERT/UPDATE/DELETE数据写入应用读写账号CREATE/DROP/ALTER修改表结构DBA、迁移账号CREATE TEMPORARY TABLES临时表复杂查询、ETL账号RELOAD执行 FLUSH备份账号REPLICATION SLAVE / CLIENT主从复制复制专用账号SUPER停止/启动线程、修改全局变量仅限 DBAGRANT OPTION允许再授权给别的账号仅限管理账号1.3 为什么我推荐用 GRANT而不是直接 UPDATE mysql.user网上有些老教程会教你直接改mysql.user表比如UPDATE mysql.user SET authentication_string ... WHERE user root; FLUSH PRIVILEGES;这种做法在 MySQL 5.6 时代还能勉强用但在 MySQL 8.0 里非常坑。第一authentication_string的格式和认证插件强绑定你手写出来的哈希很大概率不对第二直接改表会绕过 MySQL 的权限校验机制少了mysql.db、mysql.tables_priv等关联表的同步容易出现“权限表一致性问题”第三FLUSH PRIVILEGES只是让 MySQL 重新读取授权表它不会帮你自动修复你写坏的字段。标准做法永远是先CREATE USER再GRANT。如果需要调整用ALTER USER。如果需要迁移账号用RENAME USER。这些都是 SQL 标准里的授权路径会自动处理好所有关联数据。2. 创建用户的正确姿势从规划到授权的完整流程2.1 动手之前先想清楚三件事我见过太多人在生产库上一边敲 SQL 一边想“这个账号要什么权限”这是很危险的。创建用户前至少要把三件事定下来这个账号给谁用应用连接池、人工运维、只读报表、还是备份工具不同用途的权限天差地别。允许从哪来本机、固定 IP、内网网段、还是所有来源能用具体 IP 就尽量不用%。需要哪些权限最小集合是什么要写数据就只给读写权限别顺带ALTER、DROP。清晰的规划表大概长这样账号名来源权限范围用途app_prod10.10.0.%app_db 的增删改查后端应用report_ro10.10.0.%app_db 的 SELECT报表查询backup_dbalocalhost全库备份相关权限定时备份admin_ops办公网IP全局管理权限DBA运维2.2 用 CREATE USER 创建账号MySQL 8.0 里创建账号的基本语法CREATE USER app_prod10.10.0.% IDENTIFIED BY StrongPssw0rd;这一步只创建账号不赋予任何权限。此时这个账号能连上数据库但做不了任何事连SHOW DATABASES都只能看到空的列表。这里有个细节IDENTIFIED BY后面跟的密码会经过 MySQL 的validate_password组件校验。如果你的实例启用了密码策略生产环境应该启用那么“123456”“password”这类弱密码根本创建不成功。报错大概是ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。想查看当前密码策略SHOW VARIABLES LIKE validate_password%;另外MySQL 8.0 默认的认证插件是caching_sha2_password。如果你的客户端是经过 JDBC 连接用的新版驱动8.0没问题但如果你还在用非常老的 PHP、Navicat、Python 旧库就可能碰到“Authentication plugin caching_sha2_password cannot be loaded”的报错。这时候有两个选择要么升级客户端驱动要么在创建用户时显式指定老插件CREATE USER app_prod10.10.0.% IDENTIFIED WITH mysql_native_password BY StrongPssw0rd;我的建议是能升级客户端就升级不要为了老客户端把整个实例的默认认证插件改成mysql_native_password。毕竟caching_sha2_password在传输安全和密码存储上都更好。如果实在改不了那至少只在特定账号上指定老插件不要动全局默认值。2.3 GRANT 赋权最常用的授权写法创建完账号后赋权以刚才的app_prod为例GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON app_db.* TO app_prod10.10.0.%;这一条授权覆盖了大部分后端业务的日常需求。注意我特意加上了CREATE TEMPORARY TABLES很多框架如 MyBatis Plus 或某些复杂报表查询会用到临时表没有这个权限SQL 执行到一半可能报“Permission denied to create temporary tables”。很多运维漏掉这一项导致应用的某个特定接口时好时坏。如果是一个只读报表账号CREATE USER report_ro10.10.0.% IDENTIFIED BY ReportOnly2024; GRANT SELECT ON app_db.* TO report_ro10.10.0.%;如果是对单表授权GRANT SELECT ON app_db.orders TO report_ro10.10.0.%;这里我特别提醒一点不要在GRANT后面随便加WITH GRANT OPTION。它的意思是允许这个账号把自己拥有的权限再转授给其他账号。对应用账号来说这是典型的越权风险。只有 DBA 管理账号才需要这个能力。2.4 常见的生产账号授权组合根据自己的维护经验我整理了三个最常用的账号组合直接抄作业就行组合一后端应用读写账号CREATE USER app10.10.0.% IDENTIFIED BY App_Str0ng!Pass; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON app_db.* TO app10.10.0.%;组合二报表只读账号CREATE USER bi10.20.0.% IDENTIFIED BY Bi_Read0nly!; GRANT SELECT ON app_db.* TO bi10.20.0.%;组合三备份专用账号CREATE USER backuplocalhost IDENTIFIED BY Backup_DBA#2024; GRANT SELECT, RELOAD, PROCESS, REPLICATION CLIENT, SHOW VIEW, EVENT ON *.* TO backuplocalhost;备份账号要全局权限是因为mysqldump需要读取所有库的表定义和数据还需要RELOAD来执行FLUSH TABLES WITH READ LOCK对应 binlog 位置点的锁定REPLICATION CLIENT是为了查看主从状态和位点。3. 权限查看、回收和账号清理管理员每天都在做的事3.1 查看用户到底有什么权限创建完账号后第一件事应该是查看授权是否正确。标准命令SHOW GRANTS FOR app_prod10.10.0.%;返回结果会以GRANT SELECT, INSERT, ... ON ... TO ...的形式展示可以直观看到这个账号在哪些对象上有什么权限。如果想看账号的基本属性比如密码是否过期、是否被锁、用了什么插件还是回到mysql.userSELECT user, host, plugin, password_expired, password_last_changed, account_locked FROM mysql.user WHERE user app_prod;有时候也需要看某个数据库被授权给了哪些用户可以查information_schema.SCHEMA_PRIVILEGESSELECT GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE FROM information_schema.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA app_db;3.2 REVOKE 回收权限和 DROP USER 删账号权限给错了或者员工离职、应用下线要回收权限。语法和GRANT正好相反REVOKE INSERT ON app_db.* FROM app_prod10.10.0.%;回收后可以再SHOW GRANTS确认。删除账号用DROP USER app_prod10.10.0.%;注意这里有个常见的坑DROP USER必须带host否则可能删不到目标。如果提示ERROR 1396 (HY000): Operation DROP USER failed大概率就是 host 写错了。另外一个隐蔽问题是已经连接到数据库的会话在你删掉账号之后并不会立刻被踢掉。MySQL 允许这个会话继续使用直到查询结束或连接断开。所以生产环境要回收某个用户时除了DROP USER最好还要KILL掉它的活跃连接具体可以查processlist里对应的user和host字段。改 host 也不要直接改mysql.user表优先使用RENAME USERRENAME USER app_prod10.10.0.% TO app_prod10.10.0.5;3.3 复制权限从一个账号迁移到另一个账号运维中最常见的一个需求主从或者新旧环境切换需要把 A 账号的权限原样复制到 B 账号。MySQL 没有自带的COPY USER命令我常用的办法是查看源账号的授权SHOW GRANTS FOR source_user%;手动把GRANT ... TO source_user%里的目标账号替换成新账号然后执行。如果账号很多可以用官方工具pt-show-grants它能直接把你实例里所有账号的授权语句导出成一个 SQL 文件在另一个实例执行就能完整重建用户体系。这个工具我基本每次迁移 MySQL 都会用比手写几十条GRANT稳得多。4. 密码过期、认证插件和忘记 root 密码的现场急救4.1 ALTER USER 修改密码和账号状态修改密码的标准语法ALTER USER app_prod10.10.0.% IDENTIFIED BY New_Str0ng!Pass;需要注意执行完之后使用该账号的旧连接不会被强制断开但下一次需要重新认证的操作比如 reconnection就会要求新密码。所以生产环境改密码一定要和应用配置同步否则连接池出问题。设置密码立即过期ALTER USER app_prod10.10.0.% PASSWORD EXPIRE;这个指令会让账号在下一次连接时必须修改密码。对于 DBA 账号挺有用比如给临时协作者创建一次账号用完直接过期。账号锁定和解锁ALTER USER app_prod10.10.0.% ACCOUNT LOCK; ALTER USER app_prod10.10.0.% ACCOUNT UNLOCK;连接池场景下如果账号被锁或者密码过期表现是应用突然大批量报Access denied而且连接池会持续重试把数据库的 error log 刷满。处理时会发现mysql.user里account_lockedY或password_expiredY解开后要记得让应用重新建立连接。4.2 忘记 root 密码的救援流程这是每个 DBA 早晚都会遇到的事。流程可以分成四步第一步停掉 MySQL 服务。用 systemd 管理的发行版sudo systemctl stop mysqld第二步跳过授权表启动。为了安全一定要同时禁用网络避免其他机器趁这个窗口连进来sudo mysqld_safe --skip-grant-tables --skip-networking 第三步用 root 无密码登录mysql -u root进入 MySQL 后先执行一下FLUSH PRIVILEGES;让授权表在内存里生效否则后续ALTER USER可能报“Table mysql.user doesnt exist”或者权限不一致。然后FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY New_Root#Pass2024;第四步重启 MySQL 服务用新密码登录验证。不要少了第四步否则下次还是无密码跳过授权表状态等于裸奔。这里再强调一次现在是 MySQL 8.0 时代不要再用网上的老教程去UPDATE mysql.user SET authentication_string...那个老写法在 8.0 里会把自己坑死。FLUSH PRIVILEGES也不是万能药在不该用的时候用了也不会修复权限数据。4.3 认证插件引发的连不上问题MySQL 8.0 默认的caching_sha2_password与老客户端不兼容最常见的报错是ERROR 1524 (HY000): Plugin caching_sha2_password is not loaded或者Authentication plugin caching_sha2_password cannot be loaded解决方案我前面提过。这里补充一个细节caching_sha2_password在首次认证时需要额外的 RSA 公钥交换来传输密码字节。如果客户端所在的网络环境不允许或者 JDBC 配置里没指定allowPublicKeyRetrievaltrue也可能报错。所以用 Java 连接 MySQL 8 时如果在localhost之外连且没有走 SSLJDBC URL 里经常要加上jdbc:mysql://host:3306/app_db?allowPublicKeyRetrievaltrueuseSSLfalse不过这个配置只适合内网开发环境生产建议用useSSLtrue并配置证书。5. 远程连接“连不上”host 匹配和排查链路5.1 host 的匹配顺序比你想象的更严格我遇到过大量“创建了user%为什么还是Access denied”的案例。答案往往就藏在 host 匹配顺序里。MySQL 匹配账号不是随便挑一个能匹配的而是有顺序的精确 IP 优先于网段网段优先于%。举个例子如果库里同时存在这三个账号app_userlocalhost app_user192.168.1.50 app_user%当客户端来自192.168.1.50时MySQL 会优先匹配app_user192.168.1.50而不是app_user%。当客户端从本机通过 socket 连接时它实际的来源 host 是localhost会匹配第一个即使你给第三个账号设置了不同密码。这就能解释为什么你反复确认密码正确却仍然登不进你连的本机账号根本不是你以为的那个账号。排查的时候首先要分清“客户端看到的自己的来源 IP”和“MySQL 识别到的来源 host”。假如应用服务器是10.0.0.8它连接 MySQL 服务器时报错里写的Access denied for user app10.0.0.8中的10.0.0.8是应用服务器的 IP不是 MySQL 服务器 IP。你需要在mysql.user里找userapp且host能覆盖10.0.0.8的账号。5.2 允许远程连接的正确配置远程连接不上不一定是账号问题还可能是 MySQL 本身只监听了本机。MySQL 8.0 的默认配置里bind-address127.0.0.1这表示服务只允许本机访问。要允许远程访问需要修改配置文件比如/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf[mysqld] bind-address 0.0.0.0改完重启 MySQL。注意监听 0.0.0.0 意味着所有网卡都能访问如果服务器还有公网 IP一定要靠防火墙或者安全组严格限制 3306 端口的来源。接下来还要看防火墙。以 firewalld 为例sudo firewall-cmd --permanent --add-rich-rulerule familyipv4 source address10.0.0.0/24 port protocoltcp port3306 accept sudo firewall-cmd --reload这一步很关键即使 MySQL 配置、账号都对防火墙没放行表现还是超时或者Connection refused。另外还有一个skip-name-resolve参数。如果开启了MySQL 不会做反向 DNS 解析所有客户端来源都显示为 IP。这时候你在host字段写主机名比如app_server.example.com是匹配不到的必须写 IP 或者网段。建议生产环境直接skip-name-resolveON能减少 DNS 延迟和解析失败的风险。5.3 Access Denied 的完整排查链路为了让你能复现我的排查思路我把一次真实的Access denied排查过程完整记下来比如应用日志报ERROR 1045 (28000): Access denied for user app_prod10.12.3.8 (using password: YES)我的排查顺序是先不急着改权限。看是否有匹配来源的账号。执行SELECT user, host, plugin, authentication_string, password_expired FROM mysql.user WHERE user app_prod;假设结果只有一行app_prodlocalhost。那问题已经清楚了来源10.12.3.8的客户端在mysql.user里根本找不到匹配的 host所以被拒绝。如果确实有多个 host比如有app_prod10.12.3.%和app_prod%就要检查哪个匹配了10.12.3.8然后确认这个匹配账号的密码是否和应用配置一致。可以用mysql -u app_prod -p -h 你的IP -P 3306手动试连。检查是不是密码策略把账号锁了SELECT user, host, account_locked, password_expired FROM mysql.user WHERE userapp_prod;检查processlist和 error log。如果已经有不少失败连接general_log会记录真实来源。临时打开通用日志SET GLOBAL general_log ON; SET GLOBAL general_log_file /tmp/mysql_general.log;观察一段时间后记得关掉生产环境别一直开着。整个链路的关键是先查mysql.user有没有匹配来源的账号再查密码和插件再查服务器监听和防火墙。不要一上来就FLUSH PRIVILEGES也不要不分青红皂白把 host 改成%那等于扩大了风险面。5.4 顺带说一句 RabbitMQ 管理界面的权限问题你可能看到网上有人问“RabbitMQ 管理界面能打开但 admin 用户不能创建虚拟主机”“用 rabbitmqctl 能创建用户但镜像自带 web 管理界面显示不能连到服务器”。这类问题本质上也是“连接成功但授权不足”的典型案例。RabbitMQ 的虚拟主机创建除了要求账号有administrator标签还要求用户有对应 virtual host 的权限。你通过rabbitmqctl创建了用户但没配置 tag 和 vhost 权限界面当然会提示操作失败。这和 MySQL 中“账号存在但GRANT没给够就报权限不足”是一个逻辑。做用户管理永远要区分三件事能不能连上来、连上来之后是什么角色、这个角色能操作哪些对象三者缺一不可。6. 真实场景中的用户管理连接池、主从复制和容器部署6.1 连接池里账号配置容易被忽略的两个点Java 应用基本都用 HikariCP、Druid 或 Tomcat JDBC Pool。连接池启动时会用配置的数据库账号建立若干连接。这个场景下用户管理最容易出两个问题。第一个问题是账号权限只覆盖应用自己的库但连接池做SELECT 1或SELECT session.tx_isolation时没权限。别看只是心跳检测如果账号完全没有SELECT权限连接池可能频繁报错。虽然我建议最小权限但连接池自身的心跳检查需要少量权限至少要能在当前库执行SELECT 1。所以应用账号一般要有SELECT哪怕只读心跳。第二个问题是密码变更后连接池不感知。数据库里改了密码连接池里已经存在的旧连接不会马上失效下一次新建连接时才会发现密码不对。这会导致诡异的现象应用刚开始运行正常跑一会儿突然大量报Access denied重启应用又好了。其实是因为连接池里旧连接还在用新建连接失败。遇到这种情况查一下是不是刚好有人改了数据库密码而不是急着给账号加权限。还有一点连接池里的账号务必做到“一个应用一个账号”。多个应用共用同一个账号出了问题你根本不知道是哪个应用在大量查询、哪个应用在误删数据。账号隔离是成本最低的治理手段。6.2 主从复制账号只要复制不要其他权限MySQL 主从复制需要一个专门账号。很多人直接拿应用账号来复制甚至拿 root 账号这是非常糟糕的习惯。标准做法是CREATE USER repl10.10.0.% IDENTIFIED BY Repl_Str0ng!Pass; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl10.10.0.%;REPLICATION SLAVE是让从库可以向主库请求 binlog 的权限属于全局权限必须用*.*。REPLICATION CLIENT允许查主从状态比如SHOW MASTER STATUS、SHOW REPLICA STATUS。这两个权限足够用了千万别给ALL PRIVILEGES。从库执行CHANGE MASTER TO时MASTER_USER和MASTER_PASSWORD要对应这个账号。如果从库是 MySQL 8.0主库也是 8.0认证插件保持默认没问题如果是从库版本比较老可能需要主库为复制账号指定mysql_native_password否则从库 I/O 线程无法认证。这类问题在升级 MySQL 后经常出现重启复制无效看SHOW REPLICA STATUS\G里Last_IO_Error会提示认证插件不兼容。6.3 Docker 和 Kubesphere 部署时的初始化用户用 Docker 官方镜像跑 MySQL可以在首次启动时通过环境变量初始化用户docker run -d \ --name mysql8 \ -e MYSQL_ROOT_PASSWORDRoot_Str0ng!Pass \ -e MYSQL_DATABASEapp_db \ -e MYSQL_USERapp \ -e MYSQL_PASSWORDApp_Str0ng!Pass \ -p 3306:3306 \ mysql:8.0官方镜像的入口脚本会在数据卷为空时执行这些变量自动创建app用户并授予app_db库全部权限同时还会创建app_db数据库。需要注意的是这个初始化只在第一次启动且数据卷为空时执行。如果你改了密码想重新初始化单纯docker rmdocker run是不行的必须同时删除数据卷否则环境变量不生效。Kubesphere 里部署 MySQL 也类似账号密码通常放在 Secret 里Deployment 的环境变量引用 Secret 的值。改 Secret 之后如果 Pod 不重建或者 PVC 里的数据还在环境变量里的新用户不会自动补上。换句话说容器平台的 MySQL 用户管理核心还是在 SQL 层操作初始化变量只是“第一套餐具”后面加菜必须自己动手写 SQL。6.4 库存里的其他常见运维场景再补充几个我日常会做的操作不算复杂但很实用每季度检查一次mysql.user把三个月内没用过的账号列出来和负责人确认后DROP USER。给所有 DBA 账号设置定期密码过期策略比如 90 天一变避免长期不换密码的风险。用pt-show-grants把授权备份成 SQL 文件存到配置仓库里。这样即使整个实例挂了还能快速把用户体系恢复到新库。7. 那些“用户管理背锅”的经典报错和避免方法7.1 ERROR 2002这不是账号问题但总有人混淆ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock这个报错经常在刚装完 MySQL 或者稍微改了配置之后出现。表面意思是“通过 socket 文件连不上”本质是客户端和服务端对 socket 文件路径的认知不一致。MySQL 客户端默认去找/tmp/mysql.sock而服务端可能把 socket 放在了/var/run/mysqld/mysqld.sock或者服务根本没启动。排查时先看服务状态systemctl status mysqld再查实际 socket 文件位置mysqladmin var | grep socket或者看配置文件里的socket参数。临时想绕开 socket 走 TCP 的话可以指定-h127.0.0.1而非-hlocalhost因为localhost会触发客户端走 socket 连接。但这个手段解决的是暂时连接需求根因还是要统一 socket 路径或者在客户端配置文件~/.my.cnf里写对socket。7.2 ERROR 1045先分辨“密码错”还是“账号不存在”Access denied是最常见、也最让人暴躁的报错。我处理时第一步永远是看报错里的完整格式ERROR 1045 (28000): Access denied for user foohost (using password: YES/NO)这里有几个关键判断using password: YES表示客户端确实发了密码。此时如果密码错基本就是账号密码不匹配如果密码正确但 host 不匹配也会报同样错误。using password: NO表示客户端没发密码。通常是命令行里没放在-p后接密码或者应用配置里没配密码。账号完全不存在也会报 1045而不是 1449。密码过期也会表现为Access denied需要查password_expired字段。生产上还见过一种情况错误日志里是Access denied for user rootlocalhost但mysql.user里确实有rootlocalhost而且密码没错。最后发现是mysql.user里存在多个root条目其中一条的认证插件或哈希字段损坏MySQL 优先匹配到了那个坏条目。这种时候用DROP USER重建rootlocalhost再授权即可。7.3 匿名用户、空密码用户和 root‘%’ 的隐藏风险MySQL 在新装之后可能存在匿名账号即user字段为空的账号例如localhost。如果你不清理任何本机用户都可能不用密码或以任意用户名匹配到这个匿名账号造成“我怎么没设密码也能登录”的错觉。安装完成后建议运行安全加固脚本mysql_secure_installation它会引导你设置 root 密码、删除匿名账号、禁止 root 远程登录、删除 test 库。这些操作都是用户管理的一部分别跳过。还有一条铁律root%应该只在完全隔离的内网环境中使用并且密码要足够复杂。生产环境我强烈建议只保留rootlocalhost。对外的管理操作走独立 DBA 账号来源限制到办公网固定 IP。否则一旦有人爆破数据库的 3306 端口拿到的就是超级权限。7.4 MySQL 8.0 的密码策略和账号锁定机制现在创建用户时密码太短、太简单都会被直接拒绝。在validate_password组件开启时默认要求密码至少 8 位包含大小写和数字。查看规则SELECT * FROM mysql.component; SHOW VARIABLES LIKE validate_password%;账号连续登录失败也可能触发锁定策略这在 MySQL 8.0.19 之后的版本里可以用登录失败跟踪ALTER USER app_prod10.10.0.% FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2;这条命令表示连续 5 次登录失败后锁定 2 天。对公网暴露的 MySQL 实例非常有用。锁住之后管理员可以手动解ALTER USER app_prod10.10.0.% ACCOUNT UNLOCK;8. 关于用户管理我最后想说的几句话MySQL 用户管理看起来是几个 SQL 命令实际上是一整套“身份来源权限生命周期”的治理工作。我刚接手维护工作的时候也犯过错图省事直接UPDATE mysql.user结果把权限搞坏为了调试方便给应用账号配了一个%host后来审计发现这个账号可以被任意机器访问还有一次给报表账号漏了CREATE TEMPORARY TABLES被业务连续问了一下午为什么凌晨报表偶尔失败。现在我的习惯是每次新建账号先写清楚用途、来源、权限清单三个字段每次授权执行完必查SHOW GRANTS每个月跑一遍SELECT user, host, password_expired, account_locked FROM mysql.user过目。再配一个pt-show-grants的定时导出把权限配置纳入备份体系里。最后送你一个小技巧如果你想把一个账号的权限完整迁移到另一个账号可以先SHOW GRANTS复制输出把TO后面的账号名换掉就行。这个办法在 MySQL 5.7 和 8.0 都适用比手写授权快得多也安全得多。用户管理没有太多黑科技把基础流程走稳把每个账号的边界划清楚生产环境一大半连接问题都能提前避免。
MySQL用户管理实战：权限模型、授权回收与远程连接排障
用户管理MySQL这件事很多人觉得简单——不就是CREATE USER加GRANT两三句 SQL 的事吗但我在一线折腾数据库这几年发现线上大多数“连不上”“没权限”“账号被锁”的故障最后都能追到用户管理上。不是密码写错就是 host 匹配错了再就是权限给宽了或者给窄了。更麻烦的是MySQL 8.0 之后认证插件改了、密码策略有了、角色能用了老一套经验经常直接失效。这篇我就把自己实际用过、踩过坑的 MySQL 用户管理完整流程整理出来包括权限模型、创建用户、授权回收、密码重置、远程连接排查以及连接池、主从复制、容器部署里的账号配置细节。不管你是刚入门还是已经在维护生产库照着这套思路走能省掉一大半的冤枉路。1. 用户管理到底管的是什么先从权限模型说起1.1 MySQL 账号不是“用户名密码”这么简单MySQL 的账号体系和其他数据库有个特别大的差异一个账号由user和host两个字段共同决定。你可以把host理解成“这个账号允许从哪里登录”。比如app_userlocalhost只能从本机登录app_user192.168.1.100只能从固定 IP 登录app_user192.168.1.%只能从 192.168.1 网段登录app_user%可以从任意主机登录这意味着app_userlocalhost和app_user%是两个完全独立的账号密码可以不同权限也可以不同。很多初学者只创建了一个app_user%然后用mysql -u app_user -p不带-h去连本机结果报密码错误或者权限不足。原因就是本机连接默认匹配的是app_userlocalhost而这个账号根本不存在或者存在但是密码不一样。想看清库里的账号可以查mysql.user表SELECT user, host, authentication_string, plugin, password_expired, account_locked FROM mysql.user;这里的authentication_string就是密码的哈希值不要尝试解读它plugin表示认证插件MySQL 8.0 默认是caching_sha2_passwordpassword_expired和account_locked则是密码是否过期、账号是否锁定的标识。1.2 权限层级从全局到行列MySQL 的权限是分层的常用层级从大到小分别是全局权限*.*影响所有库表例如SUPER、PROCESS、REPLICATION SLAVE数据库权限db_name.*影响某个库里的所有对象表权限db_name.table_name只能操作某一张表列权限甚至可以细到只能操作某几列存储过程/函数权限单独控制能否执行实际工作中用得最多的是数据库权限和表权限。用一条GRANT可以同时授权多个操作比如GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO app_user192.168.1.%;这里特别注意ON app_db.*表示 app_db 库下所有表但不包含CREATE DATABASE等库级管理操作。ON *.*是全局权限给应用账号时非常危险。生产环境我见过有人为了省事给应用账号执行了GRANT ALL PRIVILEGES ON *.*结果应用被注入后整个实例的数据任人摆布。最小权限原则不是口号是保命的。下面列一下常用权限的典型用途方便对照规划权限典型用途建议授予对象SELECT只读查询报表、BI、只读从库账号INSERT/UPDATE/DELETE数据写入应用读写账号CREATE/DROP/ALTER修改表结构DBA、迁移账号CREATE TEMPORARY TABLES临时表复杂查询、ETL账号RELOAD执行 FLUSH备份账号REPLICATION SLAVE / CLIENT主从复制复制专用账号SUPER停止/启动线程、修改全局变量仅限 DBAGRANT OPTION允许再授权给别的账号仅限管理账号1.3 为什么我推荐用 GRANT而不是直接 UPDATE mysql.user网上有些老教程会教你直接改mysql.user表比如UPDATE mysql.user SET authentication_string ... WHERE user root; FLUSH PRIVILEGES;这种做法在 MySQL 5.6 时代还能勉强用但在 MySQL 8.0 里非常坑。第一authentication_string的格式和认证插件强绑定你手写出来的哈希很大概率不对第二直接改表会绕过 MySQL 的权限校验机制少了mysql.db、mysql.tables_priv等关联表的同步容易出现“权限表一致性问题”第三FLUSH PRIVILEGES只是让 MySQL 重新读取授权表它不会帮你自动修复你写坏的字段。标准做法永远是先CREATE USER再GRANT。如果需要调整用ALTER USER。如果需要迁移账号用RENAME USER。这些都是 SQL 标准里的授权路径会自动处理好所有关联数据。2. 创建用户的正确姿势从规划到授权的完整流程2.1 动手之前先想清楚三件事我见过太多人在生产库上一边敲 SQL 一边想“这个账号要什么权限”这是很危险的。创建用户前至少要把三件事定下来这个账号给谁用应用连接池、人工运维、只读报表、还是备份工具不同用途的权限天差地别。允许从哪来本机、固定 IP、内网网段、还是所有来源能用具体 IP 就尽量不用%。需要哪些权限最小集合是什么要写数据就只给读写权限别顺带ALTER、DROP。清晰的规划表大概长这样账号名来源权限范围用途app_prod10.10.0.%app_db 的增删改查后端应用report_ro10.10.0.%app_db 的 SELECT报表查询backup_dbalocalhost全库备份相关权限定时备份admin_ops办公网IP全局管理权限DBA运维2.2 用 CREATE USER 创建账号MySQL 8.0 里创建账号的基本语法CREATE USER app_prod10.10.0.% IDENTIFIED BY StrongPssw0rd;这一步只创建账号不赋予任何权限。此时这个账号能连上数据库但做不了任何事连SHOW DATABASES都只能看到空的列表。这里有个细节IDENTIFIED BY后面跟的密码会经过 MySQL 的validate_password组件校验。如果你的实例启用了密码策略生产环境应该启用那么“123456”“password”这类弱密码根本创建不成功。报错大概是ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。想查看当前密码策略SHOW VARIABLES LIKE validate_password%;另外MySQL 8.0 默认的认证插件是caching_sha2_password。如果你的客户端是经过 JDBC 连接用的新版驱动8.0没问题但如果你还在用非常老的 PHP、Navicat、Python 旧库就可能碰到“Authentication plugin caching_sha2_password cannot be loaded”的报错。这时候有两个选择要么升级客户端驱动要么在创建用户时显式指定老插件CREATE USER app_prod10.10.0.% IDENTIFIED WITH mysql_native_password BY StrongPssw0rd;我的建议是能升级客户端就升级不要为了老客户端把整个实例的默认认证插件改成mysql_native_password。毕竟caching_sha2_password在传输安全和密码存储上都更好。如果实在改不了那至少只在特定账号上指定老插件不要动全局默认值。2.3 GRANT 赋权最常用的授权写法创建完账号后赋权以刚才的app_prod为例GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON app_db.* TO app_prod10.10.0.%;这一条授权覆盖了大部分后端业务的日常需求。注意我特意加上了CREATE TEMPORARY TABLES很多框架如 MyBatis Plus 或某些复杂报表查询会用到临时表没有这个权限SQL 执行到一半可能报“Permission denied to create temporary tables”。很多运维漏掉这一项导致应用的某个特定接口时好时坏。如果是一个只读报表账号CREATE USER report_ro10.10.0.% IDENTIFIED BY ReportOnly2024; GRANT SELECT ON app_db.* TO report_ro10.10.0.%;如果是对单表授权GRANT SELECT ON app_db.orders TO report_ro10.10.0.%;这里我特别提醒一点不要在GRANT后面随便加WITH GRANT OPTION。它的意思是允许这个账号把自己拥有的权限再转授给其他账号。对应用账号来说这是典型的越权风险。只有 DBA 管理账号才需要这个能力。2.4 常见的生产账号授权组合根据自己的维护经验我整理了三个最常用的账号组合直接抄作业就行组合一后端应用读写账号CREATE USER app10.10.0.% IDENTIFIED BY App_Str0ng!Pass; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON app_db.* TO app10.10.0.%;组合二报表只读账号CREATE USER bi10.20.0.% IDENTIFIED BY Bi_Read0nly!; GRANT SELECT ON app_db.* TO bi10.20.0.%;组合三备份专用账号CREATE USER backuplocalhost IDENTIFIED BY Backup_DBA#2024; GRANT SELECT, RELOAD, PROCESS, REPLICATION CLIENT, SHOW VIEW, EVENT ON *.* TO backuplocalhost;备份账号要全局权限是因为mysqldump需要读取所有库的表定义和数据还需要RELOAD来执行FLUSH TABLES WITH READ LOCK对应 binlog 位置点的锁定REPLICATION CLIENT是为了查看主从状态和位点。3. 权限查看、回收和账号清理管理员每天都在做的事3.1 查看用户到底有什么权限创建完账号后第一件事应该是查看授权是否正确。标准命令SHOW GRANTS FOR app_prod10.10.0.%;返回结果会以GRANT SELECT, INSERT, ... ON ... TO ...的形式展示可以直观看到这个账号在哪些对象上有什么权限。如果想看账号的基本属性比如密码是否过期、是否被锁、用了什么插件还是回到mysql.userSELECT user, host, plugin, password_expired, password_last_changed, account_locked FROM mysql.user WHERE user app_prod;有时候也需要看某个数据库被授权给了哪些用户可以查information_schema.SCHEMA_PRIVILEGESSELECT GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE FROM information_schema.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA app_db;3.2 REVOKE 回收权限和 DROP USER 删账号权限给错了或者员工离职、应用下线要回收权限。语法和GRANT正好相反REVOKE INSERT ON app_db.* FROM app_prod10.10.0.%;回收后可以再SHOW GRANTS确认。删除账号用DROP USER app_prod10.10.0.%;注意这里有个常见的坑DROP USER必须带host否则可能删不到目标。如果提示ERROR 1396 (HY000): Operation DROP USER failed大概率就是 host 写错了。另外一个隐蔽问题是已经连接到数据库的会话在你删掉账号之后并不会立刻被踢掉。MySQL 允许这个会话继续使用直到查询结束或连接断开。所以生产环境要回收某个用户时除了DROP USER最好还要KILL掉它的活跃连接具体可以查processlist里对应的user和host字段。改 host 也不要直接改mysql.user表优先使用RENAME USERRENAME USER app_prod10.10.0.% TO app_prod10.10.0.5;3.3 复制权限从一个账号迁移到另一个账号运维中最常见的一个需求主从或者新旧环境切换需要把 A 账号的权限原样复制到 B 账号。MySQL 没有自带的COPY USER命令我常用的办法是查看源账号的授权SHOW GRANTS FOR source_user%;手动把GRANT ... TO source_user%里的目标账号替换成新账号然后执行。如果账号很多可以用官方工具pt-show-grants它能直接把你实例里所有账号的授权语句导出成一个 SQL 文件在另一个实例执行就能完整重建用户体系。这个工具我基本每次迁移 MySQL 都会用比手写几十条GRANT稳得多。4. 密码过期、认证插件和忘记 root 密码的现场急救4.1 ALTER USER 修改密码和账号状态修改密码的标准语法ALTER USER app_prod10.10.0.% IDENTIFIED BY New_Str0ng!Pass;需要注意执行完之后使用该账号的旧连接不会被强制断开但下一次需要重新认证的操作比如 reconnection就会要求新密码。所以生产环境改密码一定要和应用配置同步否则连接池出问题。设置密码立即过期ALTER USER app_prod10.10.0.% PASSWORD EXPIRE;这个指令会让账号在下一次连接时必须修改密码。对于 DBA 账号挺有用比如给临时协作者创建一次账号用完直接过期。账号锁定和解锁ALTER USER app_prod10.10.0.% ACCOUNT LOCK; ALTER USER app_prod10.10.0.% ACCOUNT UNLOCK;连接池场景下如果账号被锁或者密码过期表现是应用突然大批量报Access denied而且连接池会持续重试把数据库的 error log 刷满。处理时会发现mysql.user里account_lockedY或password_expiredY解开后要记得让应用重新建立连接。4.2 忘记 root 密码的救援流程这是每个 DBA 早晚都会遇到的事。流程可以分成四步第一步停掉 MySQL 服务。用 systemd 管理的发行版sudo systemctl stop mysqld第二步跳过授权表启动。为了安全一定要同时禁用网络避免其他机器趁这个窗口连进来sudo mysqld_safe --skip-grant-tables --skip-networking 第三步用 root 无密码登录mysql -u root进入 MySQL 后先执行一下FLUSH PRIVILEGES;让授权表在内存里生效否则后续ALTER USER可能报“Table mysql.user doesnt exist”或者权限不一致。然后FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY New_Root#Pass2024;第四步重启 MySQL 服务用新密码登录验证。不要少了第四步否则下次还是无密码跳过授权表状态等于裸奔。这里再强调一次现在是 MySQL 8.0 时代不要再用网上的老教程去UPDATE mysql.user SET authentication_string...那个老写法在 8.0 里会把自己坑死。FLUSH PRIVILEGES也不是万能药在不该用的时候用了也不会修复权限数据。4.3 认证插件引发的连不上问题MySQL 8.0 默认的caching_sha2_password与老客户端不兼容最常见的报错是ERROR 1524 (HY000): Plugin caching_sha2_password is not loaded或者Authentication plugin caching_sha2_password cannot be loaded解决方案我前面提过。这里补充一个细节caching_sha2_password在首次认证时需要额外的 RSA 公钥交换来传输密码字节。如果客户端所在的网络环境不允许或者 JDBC 配置里没指定allowPublicKeyRetrievaltrue也可能报错。所以用 Java 连接 MySQL 8 时如果在localhost之外连且没有走 SSLJDBC URL 里经常要加上jdbc:mysql://host:3306/app_db?allowPublicKeyRetrievaltrueuseSSLfalse不过这个配置只适合内网开发环境生产建议用useSSLtrue并配置证书。5. 远程连接“连不上”host 匹配和排查链路5.1 host 的匹配顺序比你想象的更严格我遇到过大量“创建了user%为什么还是Access denied”的案例。答案往往就藏在 host 匹配顺序里。MySQL 匹配账号不是随便挑一个能匹配的而是有顺序的精确 IP 优先于网段网段优先于%。举个例子如果库里同时存在这三个账号app_userlocalhost app_user192.168.1.50 app_user%当客户端来自192.168.1.50时MySQL 会优先匹配app_user192.168.1.50而不是app_user%。当客户端从本机通过 socket 连接时它实际的来源 host 是localhost会匹配第一个即使你给第三个账号设置了不同密码。这就能解释为什么你反复确认密码正确却仍然登不进你连的本机账号根本不是你以为的那个账号。排查的时候首先要分清“客户端看到的自己的来源 IP”和“MySQL 识别到的来源 host”。假如应用服务器是10.0.0.8它连接 MySQL 服务器时报错里写的Access denied for user app10.0.0.8中的10.0.0.8是应用服务器的 IP不是 MySQL 服务器 IP。你需要在mysql.user里找userapp且host能覆盖10.0.0.8的账号。5.2 允许远程连接的正确配置远程连接不上不一定是账号问题还可能是 MySQL 本身只监听了本机。MySQL 8.0 的默认配置里bind-address127.0.0.1这表示服务只允许本机访问。要允许远程访问需要修改配置文件比如/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf[mysqld] bind-address 0.0.0.0改完重启 MySQL。注意监听 0.0.0.0 意味着所有网卡都能访问如果服务器还有公网 IP一定要靠防火墙或者安全组严格限制 3306 端口的来源。接下来还要看防火墙。以 firewalld 为例sudo firewall-cmd --permanent --add-rich-rulerule familyipv4 source address10.0.0.0/24 port protocoltcp port3306 accept sudo firewall-cmd --reload这一步很关键即使 MySQL 配置、账号都对防火墙没放行表现还是超时或者Connection refused。另外还有一个skip-name-resolve参数。如果开启了MySQL 不会做反向 DNS 解析所有客户端来源都显示为 IP。这时候你在host字段写主机名比如app_server.example.com是匹配不到的必须写 IP 或者网段。建议生产环境直接skip-name-resolveON能减少 DNS 延迟和解析失败的风险。5.3 Access Denied 的完整排查链路为了让你能复现我的排查思路我把一次真实的Access denied排查过程完整记下来比如应用日志报ERROR 1045 (28000): Access denied for user app_prod10.12.3.8 (using password: YES)我的排查顺序是先不急着改权限。看是否有匹配来源的账号。执行SELECT user, host, plugin, authentication_string, password_expired FROM mysql.user WHERE user app_prod;假设结果只有一行app_prodlocalhost。那问题已经清楚了来源10.12.3.8的客户端在mysql.user里根本找不到匹配的 host所以被拒绝。如果确实有多个 host比如有app_prod10.12.3.%和app_prod%就要检查哪个匹配了10.12.3.8然后确认这个匹配账号的密码是否和应用配置一致。可以用mysql -u app_prod -p -h 你的IP -P 3306手动试连。检查是不是密码策略把账号锁了SELECT user, host, account_locked, password_expired FROM mysql.user WHERE userapp_prod;检查processlist和 error log。如果已经有不少失败连接general_log会记录真实来源。临时打开通用日志SET GLOBAL general_log ON; SET GLOBAL general_log_file /tmp/mysql_general.log;观察一段时间后记得关掉生产环境别一直开着。整个链路的关键是先查mysql.user有没有匹配来源的账号再查密码和插件再查服务器监听和防火墙。不要一上来就FLUSH PRIVILEGES也不要不分青红皂白把 host 改成%那等于扩大了风险面。5.4 顺带说一句 RabbitMQ 管理界面的权限问题你可能看到网上有人问“RabbitMQ 管理界面能打开但 admin 用户不能创建虚拟主机”“用 rabbitmqctl 能创建用户但镜像自带 web 管理界面显示不能连到服务器”。这类问题本质上也是“连接成功但授权不足”的典型案例。RabbitMQ 的虚拟主机创建除了要求账号有administrator标签还要求用户有对应 virtual host 的权限。你通过rabbitmqctl创建了用户但没配置 tag 和 vhost 权限界面当然会提示操作失败。这和 MySQL 中“账号存在但GRANT没给够就报权限不足”是一个逻辑。做用户管理永远要区分三件事能不能连上来、连上来之后是什么角色、这个角色能操作哪些对象三者缺一不可。6. 真实场景中的用户管理连接池、主从复制和容器部署6.1 连接池里账号配置容易被忽略的两个点Java 应用基本都用 HikariCP、Druid 或 Tomcat JDBC Pool。连接池启动时会用配置的数据库账号建立若干连接。这个场景下用户管理最容易出两个问题。第一个问题是账号权限只覆盖应用自己的库但连接池做SELECT 1或SELECT session.tx_isolation时没权限。别看只是心跳检测如果账号完全没有SELECT权限连接池可能频繁报错。虽然我建议最小权限但连接池自身的心跳检查需要少量权限至少要能在当前库执行SELECT 1。所以应用账号一般要有SELECT哪怕只读心跳。第二个问题是密码变更后连接池不感知。数据库里改了密码连接池里已经存在的旧连接不会马上失效下一次新建连接时才会发现密码不对。这会导致诡异的现象应用刚开始运行正常跑一会儿突然大量报Access denied重启应用又好了。其实是因为连接池里旧连接还在用新建连接失败。遇到这种情况查一下是不是刚好有人改了数据库密码而不是急着给账号加权限。还有一点连接池里的账号务必做到“一个应用一个账号”。多个应用共用同一个账号出了问题你根本不知道是哪个应用在大量查询、哪个应用在误删数据。账号隔离是成本最低的治理手段。6.2 主从复制账号只要复制不要其他权限MySQL 主从复制需要一个专门账号。很多人直接拿应用账号来复制甚至拿 root 账号这是非常糟糕的习惯。标准做法是CREATE USER repl10.10.0.% IDENTIFIED BY Repl_Str0ng!Pass; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl10.10.0.%;REPLICATION SLAVE是让从库可以向主库请求 binlog 的权限属于全局权限必须用*.*。REPLICATION CLIENT允许查主从状态比如SHOW MASTER STATUS、SHOW REPLICA STATUS。这两个权限足够用了千万别给ALL PRIVILEGES。从库执行CHANGE MASTER TO时MASTER_USER和MASTER_PASSWORD要对应这个账号。如果从库是 MySQL 8.0主库也是 8.0认证插件保持默认没问题如果是从库版本比较老可能需要主库为复制账号指定mysql_native_password否则从库 I/O 线程无法认证。这类问题在升级 MySQL 后经常出现重启复制无效看SHOW REPLICA STATUS\G里Last_IO_Error会提示认证插件不兼容。6.3 Docker 和 Kubesphere 部署时的初始化用户用 Docker 官方镜像跑 MySQL可以在首次启动时通过环境变量初始化用户docker run -d \ --name mysql8 \ -e MYSQL_ROOT_PASSWORDRoot_Str0ng!Pass \ -e MYSQL_DATABASEapp_db \ -e MYSQL_USERapp \ -e MYSQL_PASSWORDApp_Str0ng!Pass \ -p 3306:3306 \ mysql:8.0官方镜像的入口脚本会在数据卷为空时执行这些变量自动创建app用户并授予app_db库全部权限同时还会创建app_db数据库。需要注意的是这个初始化只在第一次启动且数据卷为空时执行。如果你改了密码想重新初始化单纯docker rmdocker run是不行的必须同时删除数据卷否则环境变量不生效。Kubesphere 里部署 MySQL 也类似账号密码通常放在 Secret 里Deployment 的环境变量引用 Secret 的值。改 Secret 之后如果 Pod 不重建或者 PVC 里的数据还在环境变量里的新用户不会自动补上。换句话说容器平台的 MySQL 用户管理核心还是在 SQL 层操作初始化变量只是“第一套餐具”后面加菜必须自己动手写 SQL。6.4 库存里的其他常见运维场景再补充几个我日常会做的操作不算复杂但很实用每季度检查一次mysql.user把三个月内没用过的账号列出来和负责人确认后DROP USER。给所有 DBA 账号设置定期密码过期策略比如 90 天一变避免长期不换密码的风险。用pt-show-grants把授权备份成 SQL 文件存到配置仓库里。这样即使整个实例挂了还能快速把用户体系恢复到新库。7. 那些“用户管理背锅”的经典报错和避免方法7.1 ERROR 2002这不是账号问题但总有人混淆ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock这个报错经常在刚装完 MySQL 或者稍微改了配置之后出现。表面意思是“通过 socket 文件连不上”本质是客户端和服务端对 socket 文件路径的认知不一致。MySQL 客户端默认去找/tmp/mysql.sock而服务端可能把 socket 放在了/var/run/mysqld/mysqld.sock或者服务根本没启动。排查时先看服务状态systemctl status mysqld再查实际 socket 文件位置mysqladmin var | grep socket或者看配置文件里的socket参数。临时想绕开 socket 走 TCP 的话可以指定-h127.0.0.1而非-hlocalhost因为localhost会触发客户端走 socket 连接。但这个手段解决的是暂时连接需求根因还是要统一 socket 路径或者在客户端配置文件~/.my.cnf里写对socket。7.2 ERROR 1045先分辨“密码错”还是“账号不存在”Access denied是最常见、也最让人暴躁的报错。我处理时第一步永远是看报错里的完整格式ERROR 1045 (28000): Access denied for user foohost (using password: YES/NO)这里有几个关键判断using password: YES表示客户端确实发了密码。此时如果密码错基本就是账号密码不匹配如果密码正确但 host 不匹配也会报同样错误。using password: NO表示客户端没发密码。通常是命令行里没放在-p后接密码或者应用配置里没配密码。账号完全不存在也会报 1045而不是 1449。密码过期也会表现为Access denied需要查password_expired字段。生产上还见过一种情况错误日志里是Access denied for user rootlocalhost但mysql.user里确实有rootlocalhost而且密码没错。最后发现是mysql.user里存在多个root条目其中一条的认证插件或哈希字段损坏MySQL 优先匹配到了那个坏条目。这种时候用DROP USER重建rootlocalhost再授权即可。7.3 匿名用户、空密码用户和 root‘%’ 的隐藏风险MySQL 在新装之后可能存在匿名账号即user字段为空的账号例如localhost。如果你不清理任何本机用户都可能不用密码或以任意用户名匹配到这个匿名账号造成“我怎么没设密码也能登录”的错觉。安装完成后建议运行安全加固脚本mysql_secure_installation它会引导你设置 root 密码、删除匿名账号、禁止 root 远程登录、删除 test 库。这些操作都是用户管理的一部分别跳过。还有一条铁律root%应该只在完全隔离的内网环境中使用并且密码要足够复杂。生产环境我强烈建议只保留rootlocalhost。对外的管理操作走独立 DBA 账号来源限制到办公网固定 IP。否则一旦有人爆破数据库的 3306 端口拿到的就是超级权限。7.4 MySQL 8.0 的密码策略和账号锁定机制现在创建用户时密码太短、太简单都会被直接拒绝。在validate_password组件开启时默认要求密码至少 8 位包含大小写和数字。查看规则SELECT * FROM mysql.component; SHOW VARIABLES LIKE validate_password%;账号连续登录失败也可能触发锁定策略这在 MySQL 8.0.19 之后的版本里可以用登录失败跟踪ALTER USER app_prod10.10.0.% FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2;这条命令表示连续 5 次登录失败后锁定 2 天。对公网暴露的 MySQL 实例非常有用。锁住之后管理员可以手动解ALTER USER app_prod10.10.0.% ACCOUNT UNLOCK;8. 关于用户管理我最后想说的几句话MySQL 用户管理看起来是几个 SQL 命令实际上是一整套“身份来源权限生命周期”的治理工作。我刚接手维护工作的时候也犯过错图省事直接UPDATE mysql.user结果把权限搞坏为了调试方便给应用账号配了一个%host后来审计发现这个账号可以被任意机器访问还有一次给报表账号漏了CREATE TEMPORARY TABLES被业务连续问了一下午为什么凌晨报表偶尔失败。现在我的习惯是每次新建账号先写清楚用途、来源、权限清单三个字段每次授权执行完必查SHOW GRANTS每个月跑一遍SELECT user, host, password_expired, account_locked FROM mysql.user过目。再配一个pt-show-grants的定时导出把权限配置纳入备份体系里。最后送你一个小技巧如果你想把一个账号的权限完整迁移到另一个账号可以先SHOW GRANTS复制输出把TO后面的账号名换掉就行。这个办法在 MySQL 5.7 和 8.0 都适用比手写授权快得多也安全得多。用户管理没有太多黑科技把基础流程走稳把每个账号的边界划清楚生产环境一大半连接问题都能提前避免。
