Mysql 5.5 源码安装后创建用户报错"ERROR 1045 (28000): Access denied for user"

女爷i 2024-04-18 01:47 82阅读 0赞

安装MySQL后，root用户可设置密码登录，其他新建的用户不能通过密码登录，只能不输入密码登录，而且对新建用户的授权均无效。

\--创建一个库和用户

mysql> create database fire;

Query OK, 1 row affected (0.00 sec)

mysql> create user neo identified by 'Mysql\#2015';

Query OK, 0 rows affected (0.00 sec)

mysql> grant select,create,update,delete on fire.\* to neo;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

**--使用新用户登录报错**  
  
\[root@localhost data\]\# ./bin/mysql -u neo -p"Mysql\#2015"  
**ERROR 1045 (28000): Access denied for user 'neo'@'localhost' (using password: YES)**

**--但是不输入密码却可以登录**  
  
\[root@localhost software\]\# ./bin/mysql -uneo  
Welcome to the MySQL monitor.  Commands end with ; or \\g.  
Your MySQL connection id is 11  
Server version: 5.5.48-log production environment  
  
Copyright (c) 2000, 2016, 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 grants;  
\+--------------------------------------+  
| Grants for @localhost                |  
\+--------------------------------------+  
| GRANT USAGE ON \*.\* TO ''@'localhost' |  
\+--------------------------------------+  
1 row in set (0.00 sec)

**报错原因：** **Mysql使用源码安装后，数据库中存在匿名用户，可以** **通过任意用户名** **不输入密码即可** **登录数据库。**  
  
mysql> select host,user,password  from mysql.user where (user) not in ('root');  
\+-----------------------+------+-------------------------------------------+  
| host                  | user | password                                  |  
\+-----------------------+------+-------------------------------------------+  
| localhost             |      |                                           |  
| localhost.localdomain |      |                                           |  
| %                     | neo  | \*CC4C777F1511C297751B78287D6E05345D227819 |  
| %                     | test | \*443EF031E558E317B19BAD70BDF4E25FA73A89A7 |  
\+-----------------------+------+-------------------------------------------+  
4 rows in set (0.00 sec)  
  
mysql> select host,user,password  from mysql.user where (user) not in ('root','neo','test');  
\+-----------------------+------+----------+  
| host                  | user | password |  
\+-----------------------+------+----------+  
| localhost             |      |          |  
| localhost.localdomain |      |          |  
\+-----------------------+------+----------+  
2 rows in set (0.00 sec)  
  
**解决方法：** **删除匿名用户** **。**  
  
mysql> delete  from mysql.user where (user) not in ('root','neo','test');  
Query OK, 2 rows affected (0.00 sec)  
  
mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

之后新创建的用户即可正常登录到数据库  
  
\[root@localhost software\]\# ./bin/mysql -uneo -p'Mysql\#2015'  
Welcome to the MySQL monitor.  Commands end with ; or \\g.  
Your MySQL connection id is 1  
Server version: 5.5.48-log production environment  
  
Copyright (c) 2000, 2016, 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 grants;  
\+-----------------------------------------------------------------+  
| Grants for neo@%                                                |  
\+-----------------------------------------------------------------+  
| GRANT USAGE ON \*.\* TO 'neo'@'%' IDENTIFIED BY PASSWORD <secret> |  
| GRANT SELECT, INSERT, DELETE, CREATE ON \`fire\`.\* TO 'neo'@'%'   |  
\+-----------------------------------------------------------------+  
2 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2079518/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2079518/