mysql登陆案例_实例分析mysql用户登录。

今天，在学习mysql授权认证时，遇到了一个问题，看下，我是如何分析的：

我在数据库内添加了一个帐号：

create databases firstdb;

grant all on firstdb.* to ‘firstdb’@’’ identified by ‘xxxxx’;

flush privileges;

(原计划用firstdb

帐号登录能看到firstdb数据库，没想到发生了下面的故事，继续看，你也会成长的。)

我这样登录，mysql –ufirstdb –p输入密码，可提示：

[root@wikiob ~]# mysql -ufirstdb -p

Enter password:

ERROR 1045 (28000): Access denied for user 'firstdb'@'localhost' (using password: YES)

我的密码，肯定没有问题，通过提示分析，我现在用的登录是localhost+firstdb

，但我定义的是任意主机，感觉没有匹配我想要的情况。

分析：

看下mysql.user表的情况

(root@badboy:)[(none)]>select host,user,password from mysql.user;

+---------------------+---------+-------------------------------------------+

| host| user| password|

+---------------------+---------+-------------------------------------------+

| localhost| root| D8BF0760B25D47A3EBF34F |

| wikiob.badboy.com | root| 0760B25D47A3EBF34F |

| 127.0.0.1| root| 760B25D47A3EBF34F |

| localhost|||

| wikiob.badboy.com |||

| localhost| mantis| 36D0D144BDC21263CCFF |

| localhost| dvbbs|D1C26E56446E9DE2F52813 |

| 192.168.1.162| root| 4D8BF0760B25D47A3EBF34F |

| 192.168.2.215| root| 4D8BF0760B25D47A3EBF34F |

|| firstdb | 18BB99005ADCA2EC9D1E19 |

| localhost| test_db | 2A1F959FD02F964C7AF4CFC29 |

+---------------------+---------+-------------------------------------------+

11 rows in set (0.00 sec)

我们根据mysql在加载授权表时，要排序，最终排序结果：

+---------------------+---------+-------------------------------------------+

| host| user| password|

+---------------------+---------+-------------------------------------------+

| localhost| root| D8BF0760B25D47A3EBF34F |

| localhost| mantis| 36D0D144BDC21263CCFF |

| localhost| dvbbs|D1C26E56446E9DE2F52813 |

| localhost| test_db | 2A1F959FD02F964C7AF4CFC29 |

| localhost|||

| wikiob.badboy.com | root| 0760B25D47A3EBF34F |

| wikiob.badboy.com |||

| 127.0.0.1| root| 760B25D47A3EBF34F |

| 192.168.1.162| root| 4D8BF0760B25D47A3EBF34F |

| 192.168.2.215| root| 4D8BF0760B25D47A3EBF34F |

|| firstdb | 18BB99005ADCA2EC9D1E19 |

+---------------------+---------+-------------------------------------------+

这样的话，我刚刚输入的mysql –ufirstdb –p就匹配了第5行，也就是说，客户端是localhost，帐号是任意，密码为空。

根据前面的判断，我不输入密码试下；

[root@wikiob ~]# mysql -ufirstdb -p

Enter password:

Welcome to the MySQL monitor.Commands end with ; or \g.

Your MySQL connection id is 18

Server version: 5.1.30-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

(wiki@badboy:)[(none)]>

好，可以进去了。我现在来看看，我的登录帐号信息：

(firstdb@badboy:)[(none)]>select CURRENT_USER();

+----------------+

| CURRENT_USER() |

+----------------+

| @localhost|

+----------------+

1 row in set (0.00 sec)

看到没，是匿名帐号，和我前面判断的没错,那看下这个帐号下的数据库有哪些….

(firstdb@badboy:)[(none)]>show databases;

+--------------------+

| Database|

+--------------------+

| information_schema |

| test|

| test_db|

+--------------------+

3 rows in set (0.00 sec)

这三个数据库是怎么在匿名帐户下呢？继续分析…

看下mysql.db

(root@badboy:)[(none)]>select host,user,db from mysql.db;

+-----------+---------+---------+

| host| user| db|

+-----------+---------+---------+

|| firstdb | firstdb |

| %|| test|

| %|| test\_% |

| localhost | dvbbs| discuz|

| localhost | mantis| mantis|

| localhost | test_db | test_db |

+-----------+---------+---------+

6 rows in set (0.00 sec)

再排序一次：

(root@badboy:)[(none)]>select host,user,db from mysql.db;

+-----------+---------+---------+

| host| user| db|

+-----------+---------+---------+

| localhost | dvbbs| discuz|

| localhost | mantis| mantis|

| localhost | test_db | test_db |

|| firstdb | firstdb |

| %|| test|

| %|| test\_% |

+-----------+---------+---------+

6 rows in set (0.00 sec)

根据前面登录的是匿名用户，那么只能是最后两行是匹配我的show databases;

通过这个实例，大家一定学会了，在grant一个帐号后，用此帐号登录后发现不是自己想要的结果,如何排除问题喽，加油！~