ERROR 1045 (28000): Access denied for user...错误的解决

刚刚安装好了mysql数据库，root用户登录没有问题，但是在创建了新的数据库后却无法登录，报“ERROR 1045 (28000): Access denied for user”这样的错误，下面对这个错误进行重演,首先以root用户登录：

[root@zabbix ~]# mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.16-log MySQL Community Server (GPL)

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> 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

然后创建数据库wgldjc：

mysql> create database wgldjc;
Query OK, 1 row affected (0.00 sec)

• 1
• 2

授予权限：

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

mysql> 
mysql> 
mysql> exit

• 1
• 2
• 3
• 4
• 5
• 6

登录数据库wgldjc时报错：

[root@zabbix ~]# mysql -uwgldjc -pwgldjcpassword
ERROR 1045 (28000): Access denied for user 'wgldjc'@'localhost' (using password: YES)

• 1
• 2

刷新一下权限，发现还是不行：

[root@zabbix ~]# mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.16-log MySQL Community Server (GPL)

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> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@zabbix ~]# mysql -uwgldjc -pwgldjcpassword
ERROR 1045 (28000): Access denied for user 'wgldjc'@'localhost' (using password: YES)
[root@zabbix ~]# 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21

在网上找到了一种方法，登录数据库mysql，删除user表中user列为空格的行：

[root@zabbix ~]# mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.16-log MySQL Community Server (GPL)

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> use mysql
Database changed
mysql> select host,user,password from user;
+-----------+--------+-------------------------------------------+
| host      | user   | password                                  |
+-----------+--------+-------------------------------------------+
| localhost | root   | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| zabbix    | root   |                                           |
| 127.0.0.1 | root   |                                           |
| ::1       | root   |                                           |
| localhost |        |                                           |
| zabbix    |        |                                           |
| %         | wgldjc | *70E31422FB5C781D112D6C944FAB09312088255B |
+-----------+--------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> delete from user where user=' ';
Query OK, 2 rows affected (0.00 sec)

mysql> exit
Bye

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34

下面再进行尝试登录数据库wgldjc，依然报错：

[root@zabbix ~]# mysql -uwgldjc -pwgldjcpassword
ERROR 1045 (28000): Access denied for user 'wgldjc'@'localhost' (using password: YES)

• 1
• 2

刷新权限

[root@zabbix ~]# mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.16-log MySQL Community Server (GPL)

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> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18

成功登录：

[root@zabbix ~]# mysql -uwgldjc -pwgldjcpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.16-log MySQL Community Server (GPL)

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> exit
Bye
[root@zabbix ~]# 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

再次创建数据库后登录不再报此错：

[root@zabbix ~]# mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.16-log MySQL Community Server (GPL)

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> create database tjp;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON tjp.* TO tjp@'%'  IDENTIFIED BY 'tjp';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@zabbix ~]# mysql -utjp -ptjp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.5.16-log MySQL Community Server (GPL)

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>