[MySQL障害]ERROR 1045(28000):Access denied for user'mmm_agent'@'exxx-tx.com' (using password: YES)

1 errorログインエラー

[uxxx@exxx-0702 ~]$ mysql -hexxx-tx.com --user='mmm_agent' --password='#tx$'
ERROR 1045 (28000): Access denied for user 'mmm_agent'@'exxx-tx.com' (using password: YES)

2-hパラメータを外し、スムーズにログインできる

[uxxx@exxx-0702 ~]$ mysql --user='mmm_agent' --password='#tx$'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 491
Server version: 5.x.xa-log MySQL tx Reltxse


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 cltxr the current input statement.


mysql>

3-hパラメータに制限があるのか?ユーザーを変えてみます

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mg'@'%' IDENTIFIED BY '#tx$';
Query OK, 0 rows affected (0.01 sec)


mysql> exit
[uxxx@exxx-0702 ~]$ mysql -hexxx-tx.com --user='mg' --password='#tx$'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 965
Server version: 5.x.xa-log MySQL tx Reltxse


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 cltxr the current input statement.


mysql> 

-hパラメータの制限問題ではないようですが、別の故障のはずで、パスワードはOKです.
4 mmm_を見に行くAgentユーザーですね.

mysql> select user,host from mysql.user where user='mmm_agent';
+-----------+------+
| user      | host |
+-----------+------+
| mmm_agent | %    |
| mmm_agent | 10.% |
+-----------+------+
2 rows in set (0.00 sec)


mysql> show grants for mmm_agent@'%';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for mmm_agent@%                                                                                            |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mmm_agent'@'%' IDENTIFIED BY PASSWORD '*77074D8AC9603904375ED54F1D2E14CDACB7842F' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show grants for mmm_agent@'10.%';
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mmm_agent@10.%                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'mmm_agent'@'10.%' IDENTIFIED BY PASSWORD '*6745119376CF6BD5E0F0A50484A91AE7BD172612' |
| GRANT EXECUTE ON `audit`.* TO 'mmm_agent'@'10.%'                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


mysql> 

--ここに2つのアカウントがあり、同じユーザー名でhostが異なり、パスワードも異なるので、hostを10%にします.のmmm_Agentユーザーのパスワードをhostと%のmmm_に変更Agentユーザーと同じパスワードで、試してみてください.

GRANT RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'mmm_agent'@'10.%' IDENTIFIED BY '#tx$';
GRANT EXECUTE ON `audit`.* TO 'mmm_agent'@'10.%' 


mysql> GRANT RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'mmm_agent'@'10.%' IDENTIFIED BY '#tx$';
Query OK, 0 rows affected (0.01 sec)


mysql> GRANT EXECUTE ON `audit`.* TO 'mmm_agent'@'10.%' 
    -> ;
Query OK, 0 rows affected (0.00 sec)


mysql> exit
Bye
[uxxx@exxx-0702 ~]$ mysql -hexxx-tx.com --user='mmm_agent' --password='#tx$'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1191
Server version: 5.x.xa-log MySQL tx Reltxse


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 cltxr the current input statement.


mysql> 

--  -h    mysql   。

[まとめ]:
ええ、いいですよ.[uxxx@exxx-0702 ~]$ mysql -hexxx-tx.com --user='mmm_Agent'--password='#tx$'のログインに成功しました.
mysql-hパラメータを呼び出す場合、ユーザー名が同じであれば、hostの選択の上で、まず範囲の小さいユーザーをマッチングします.この例では、%が10%よりもの範囲はずっと広いので、-hexxx-tx.com-ummm_を使います.Agentアクセス時の直接マッチング範囲の小さい10%ユーザーは、ちょうどmmm_agent@'10.%'ユーザーのパスワードとmmm_Agent@'%'ユーザーのパスワードが同じではない場合、次のパスワードエラーのプロンプトが表示されます.
ERROR 1045 (28000): Access denied for user 'mmm_agent'@'%' (using password: YES)