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


    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'登陆呢?

  • 相关阅读:
    在django中,执行原始sql语句
    Django的select_related 和 prefetch_related 函数优化查询
    Django -- settings 详解
    Django-models的字段类型
    ORM
    django-response对象
    spark集群进入 bin 下面目录./spark-shell 出现Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
    Hbase启动的时候出现:[RpcServer.handler=28,port=60000] ipc.RpcServer: RpcServer.handler=28,port=60000: exiting,master.HMasterCommandLine: Master exiting
    c++实现写一个函数,求2个整数的和,要求在函数体内不得使用+,-* /
    nutch二次开发环境搭建
  • 原文地址:https://www.cnblogs.com/suncoolcat/p/3290065.html
Copyright © 2020-2023  润新知