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