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