mysql grant all on . to xxx@'%' 报Access denied for user 'root'@'localhost'

最新推荐文章于 2025-07-03 17:57:59 发布

weixin_33755649

最新推荐文章于 2025-07-03 17:57:59 发布

阅读量878

点赞数

文章标签：数据库

本文记录了一次MySQL服务器登录故障及权限问题的排查过程，详细介绍了从登录失败到发现用户仅能看到information_schema的问题定位，再到通过更新密码和刷新权限来解决问题的具体步骤。

今日，开发反馈某台mysql服务器无法登陆，解决之后，远程登录后发现用户只能看到information_schema，其他均看不到。

故登录服务器执行：

mysql> grant all on *.* to root@'%';

报了ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

查看user_privileges看见权限都是有的，如下：

mysql> select * from user_privileges;
+--------------------+---------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+-------------------------+--------------+
| 'root'@'localhost' | def | SELECT | YES |
| 'root'@'localhost' | def | INSERT | YES |
| 'root'@'localhost' | def | UPDATE | YES |
| 'root'@'localhost' | def | DELETE | YES |
| 'root'@'localhost' | def | CREATE | YES |
| 'root'@'localhost' | def | DROP | YES |
| 'root'@'localhost' | def | RELOAD | YES |
| 'root'@'localhost' | def | SHUTDOWN | YES |
| 'root'@'localhost' | def | PROCESS | YES |
| 'root'@'localhost' | def | FILE | YES |
| 'root'@'localhost' | def | REFERENCES | YES |
| 'root'@'localhost' | def | INDEX | YES |
| 'root'@'localhost' | def | ALTER | YES |
| 'root'@'localhost' | def | SHOW DATABASES | YES |
| 'root'@'localhost' | def | SUPER | YES |
| 'root'@'localhost' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'localhost' | def | LOCK TABLES | YES |
| 'root'@'localhost' | def | EXECUTE | YES |
| 'root'@'localhost' | def | REPLICATION SLAVE | YES |
| 'root'@'localhost' | def | REPLICATION CLIENT | YES |
| 'root'@'localhost' | def | CREATE VIEW | YES |
| 'root'@'localhost' | def | SHOW VIEW | YES |
| 'root'@'localhost' | def | CREATE ROUTINE | YES |
| 'root'@'localhost' | def | ALTER ROUTINE | YES |
| 'root'@'localhost' | def | CREATE USER | YES |