2008年7月29日星期二

mysql中创建用户后无法使用该用户名登录

现象:
本人是数据库初学者。因安装discuz!NT 2.5要用,刚在Windows 2003虚拟机中安装了mysql 5.0.22,打开MySQL Command Line Client命令行界面用root密码登录成功。
1、随即使用以下命令创建了一个数据库,成功:
CREATE DATABASE discuzdb;

2、创建一个用户,成功:
CREATE USER discuz IDENTIFIED BY 'discuzpwd';

3、并赋权,成功:
GRANT ALL ON discuzdb.* TO discuz WITH GRANT OPTION;

4、然后打开cmd.exe,用mysql.exe连接数据库:
mysql -h localhost -u discuz -p discuzdb
输入密码后连接失败,提示:
ERROR 1045 (28000): Access denied for user 'discuz'@'localhost' (using password: YES)

分析:
5、试着用root连接,成功:
mysql -h localhost -u root -p discuzdb
说明不是防火墙等网络问题。然后也排除了密码错误。
查看联机帮助中13.5.1. Account Management Statements的信息,了解到" Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table row for the account. "
即帐户(account)由用户名(User)和主机名(Host)共同组成。
以及"The simple form user_name is a synonym for user_name@'%'.",通配符“%”为特殊Host。(存疑。)

6、因此查看帐户信息:
mysql> SELECT Host, User FROM mysql.user;
+-----------+---------+
| Host | User |
+-----------+---------+
| % | |
| % | discuz |
| % | root |
| localhost | |
| localhost | root |
+-----------+---------+
6 rows in set (0.01 sec)

对照前述ERROR 1045信息可知系统中并无'discuz'@'localhost'帐户,需要重新创建此帐户。

解决:
7、删除原帐户,即'discuz'@'%':
DROP USER discuz;

8、创建'discuz'@'localhost'帐户:
CREATE USER 'discuz'@'localhost' IDENTIFIED BY 'discuzpwd';

9、赋权:
GRANT ALL ON discuzdb.* TO 'discuz'@'localhost' WITH GRANT OPTION;

10、再打开cmd.exe,连接:
mysql -h localhost -u discuz -p discuzdb
输入密码后连接成功。

接下来将试用Discuz!NT连接此DB。

没有评论: