WD1X.COM - 问答一下,轻松解决,电脑应用解决专家
主板显卡CPU内存显示器
硬盘维修显卡维修显示器维修
注册表系统命令DOS命令Win8
存储光存储鼠标键盘
内存维修打印机维修
WinXPWin7Win11Linux
硬件综合机箱电源散热器手机数码
主板维修CPU维修键盘鼠标维修
Word教程Excel教程PowerPointWPS
网络工具系统工具图像工具
数据库javascript服务器
PHP教程CSS教程XML教程

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this oper

更新时间:2021-05-07 18:16 作者:selectgo点击:
1  用以往的mysql登陆模式登陆
 
[mysql@eanintmydbc002db1 mysqllog]$ mysql 
 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3157186
Server version: 5.5.25a-log MySQL EA Release
 
 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost     |
+----------------+
1 row in set (0.01 sec)
 
 
mysql> 
mysql> 
 
 
 
2 奇怪,再次登陆试试看
[mysql@xxxmydbc002db1 mysqllog]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3157145
Server version: 5.5.25a-log MySQL EA Release
 
 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
mysql> show processlist;
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost     |
+----------------+
1 row in set (0.00 sec)
 
看到这里竟然没有用户名,只有ip地址为localhost,好诡异的状况,看下当前所有的进程
 
 
mysql> show full processlist;
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
mysql> 
 
 
 
3 去用标准的登陆模式mysql -uroot -p -hlocalhost登陆试下,看看问题到底在哪里。
 
[novamysqladminint@xxmydbc002db1 ~]$ mysql -uroot -p -hlocalhost
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3257823
Server version: 5.5.25a-log MySQL EA Release
 
 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
mysql> select user,host from mysql.user;
+--------------+-------------------------------------+
| user         | host                                |
+--------------+-------------------------------------+
| mmm_agent    | %                                   |
| mmm_monitor  | %                                   |
| replication  | %                                   |
| root         | %                                   |
| root         | 127.0.0.1                           |
| root         | ::1                                 |
|              | localhost                           |  有这个用户的,那就查下用户到底是啥权限.
| nuc_monitor  | xx.xx.abn-iad.xx.com |
| nuc_monitor  | xx.xx.abn-iad.xx.com |
+--------------+-------------------------------------+
23 rows in set (0.00 sec)
 
 
mysql> show grants for ''@'localhost';
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |  看到这里,这个用户只赋予了useage权限,可以connect上面,但是做不了任何操作。
+--------------------------------------+
1 row in set (0.00 sec)
 
 
mysql> 
 
解决方案:这个表示你的权限已经被收回了.但是user表里面还有你的记录.你需要做的是,重新赋予这个用户权限或者 drop掉这个无用的帐号换一个标准的帐号登陆。
 
 
 
Why ?
 
在binlog里面去查看了操作记录以及自己平时的工作记录,发现当时一个同事在清理帐号的时候,remove了''@'localhost'的权限,但是没有drop 掉这个用户,所以在默认mysql登陆的时候,优先选择了''@'localhost',而不是用'root'@'localhost',但是问题来了,为什么执行mysql会优先用''@'localhost'帐号登陆,而不用'root'@'localhost'登陆呢?
 
 
 
查询了很多资料以及与网友讨论,结果是执行mysql的时候,自动以系统sa的用户的方式去登陆,如果没有跟sa同名的mysql帐号,就优先以''登陆,如果没有,就以root帐号登陆,比如当前系统帐号是mysql,排序如下,如果sa是mysql::
 
1  ''@'localhost'
 
2  'mysql'@'localhost'
 
3  ''@'localhost'
 
4  'root'@'localhost'
顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
你可能感兴趣的内容