mysql登录报错ERROR 1045 (28000) 的解决方法



标签： mysql   ERROR 1045 (28000)   Access denied for user 'root'@'localhost' 

 

问题描述：

    mysql -u root -p命令登录MySQL，提示ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

解决办法：

information_schema 库的USER_PRIVILEGES 表中 没有给 'root'@'localhost' 赋权。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7CF1EF598B5B0FC97DAD7C8051A25DC0CE8A9AD9' ;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7CF1EF598B5B0FC97DAD7C8051A25DC0CE8A9AD9' |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

注意：赋权中的 PASSWORD '*7CF1EF598B5B0FC97DAD7C8051A25DC0CE8A9AD9' ;  是 root的密码加密后的字符串。 可以在 mysql库中的 user 表中查到。

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user;

问题分析：

通过 mysql -u root -p -h 127.0.0.1 可以登录，但是 mysql -u root -p 就报错。

查看information_schema 库的 USER_PRIVILEGES; 表，发现'root'@'localhost' 没有权限。 执行

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7CF1EF598B5B0FC97DAD7C8051A25DC0CE8A9AD9' ;  赋权后，增加了 'root'@'localhost'  的一些行：

mysql> select * from USER_PRIVILEGES ;
+----------------------+---------------+-------------------------+--------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+----------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'   | def           | SELECT                  | YES          |
| 'root'@'localhost'   | def           | INSERT                  | YES          |
| 'root'@'localhost'   | def           | UPDATE                  | YES          |
| 'root'@'localhost'   | def           | DELETE                  | YES          |
| 'root'@'localhost'   | def           | CREATE                  | YES          |
| 'root'@'localhost'   | def           | DROP                    | YES          |
| 'root'@'localhost'   | def           | RELOAD                  | YES          |
| 'root'@'localhost'   | def           | SHUTDOWN                | YES          |
| 'root'@'localhost'   | def           | PROCESS                 | YES          |
| 'root'@'localhost'   | def           | FILE                    | YES          |
| 'root'@'localhost'   | def           | REFERENCES              | YES          |
| 'root'@'localhost'   | def           | INDEX                   | YES          |
| 'root'@'localhost'   | def           | ALTER                   | YES          |
| 'root'@'localhost'   | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost'   | def           | SUPER                   | YES          |
| 'root'@'localhost'   | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost'   | def           | LOCK TABLES             | YES          |
| 'root'@'localhost'   | def           | EXECUTE                 | YES          |
| 'root'@'localhost'   | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost'   | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost'   | def           | CREATE VIEW             | YES          |
| 'root'@'localhost'   | def           | SHOW VIEW               | YES          |
| 'root'@'localhost'   | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost'   | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost'   | def           | CREATE USER             | YES          |
| 'root'@'localhost'   | def           | EVENT                   | YES          |
| 'root'@'localhost'   | def           | TRIGGER                 | YES          |
| 'root'@'localhost'   | def           | CREATE TABLESPACE       | YES          |
| 'root'@'127.0.0.1'   | def           | SELECT                  | YES          |
| 'root'@'127.0.0.1'   | def           | INSERT                  | YES          |
| 'root'@'127.0.0.1'   | def           | UPDATE                  | YES          |
| 'root'@'127.0.0.1'   | def           | DELETE                  | YES          |
| 'root'@'127.0.0.1'   | def           | CREATE                  | YES          |
| 'root'@'127.0.0.1'   | def           | DROP                    | YES          |
| 'root'@'127.0.0.1'   | def           | RELOAD                  | YES          |
| 'root'@'127.0.0.1'   | def           | SHUTDOWN                | YES          |
| 'root'@'127.0.0.1'   | def           | PROCESS                 | YES          |
| 'root'@'127.0.0.1'   | def           | FILE                    | YES          |
| 'root'@'127.0.0.1'   | def           | REFERENCES              | YES          |
| 'root'@'127.0.0.1'   | def           | INDEX                   | YES          |
| 'root'@'127.0.0.1'   | def           | ALTER                   | YES          |
| 'root'@'127.0.0.1'   | def           | SHOW DATABASES          | YES          |
| 'root'@'127.0.0.1'   | def           | SUPER                   | YES          |
| 'root'@'127.0.0.1'   | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'127.0.0.1'   | def           | LOCK TABLES             | YES          |
| 'root'@'127.0.0.1'   | def           | EXECUTE                 | YES          |
| 'root'@'127.0.0.1'   | def           | REPLICATION SLAVE       | YES          |
| 'root'@'127.0.0.1'   | def           | REPLICATION CLIENT      | YES          |
| 'root'@'127.0.0.1'   | def           | CREATE VIEW             | YES          |
| 'root'@'127.0.0.1'   | def           | SHOW VIEW               | YES          |
| 'root'@'127.0.0.1'   | def           | CREATE ROUTINE          | YES          |
| 'root'@'127.0.0.1'   | def           | ALTER ROUTINE           | YES          |
| 'root'@'127.0.0.1'   | def           | CREATE USER             | YES          |
| 'root'@'127.0.0.1'   | def           | EVENT                   | YES          |
| 'root'@'127.0.0.1'   | def           | TRIGGER                 | YES          |
| 'root'@'127.0.0.1'   | def           | CREATE TABLESPACE       | YES          |
| 'root'@'::1'         | def           | SELECT                  | YES          |
| 'root'@'::1'         | def           | INSERT                  | YES          |
| 'root'@'::1'         | def           | UPDATE                  | YES          |
| 'root'@'::1'         | def           | DELETE                  | YES          |
| 'root'@'::1'         | def           | CREATE                  | YES          |
| 'root'@'::1'         | def           | DROP                    | YES          |
| 'root'@'::1'         | def           | RELOAD                  | YES          |
| 'root'@'::1'         | def           | SHUTDOWN                | YES          |
| 'root'@'::1'         | def           | PROCESS                 | YES          |
| 'root'@'::1'         | def           | FILE                    | YES          |
| 'root'@'::1'         | def           | REFERENCES              | YES          |
| 'root'@'::1'         | def           | INDEX                   | YES          |
| 'root'@'::1'         | def           | ALTER                   | YES          |
| 'root'@'::1'         | def           | SHOW DATABASES          | YES          |
| 'root'@'::1'         | def           | SUPER                   | YES          |
| 'root'@'::1'         | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'::1'         | def           | LOCK TABLES             | YES          |
| 'root'@'::1'         | def           | EXECUTE                 | YES          |
| 'root'@'::1'         | def           | REPLICATION SLAVE       | YES          |
| 'root'@'::1'         | def           | REPLICATION CLIENT      | YES          |
| 'root'@'::1'         | def           | CREATE VIEW             | YES          |
| 'root'@'::1'         | def           | SHOW VIEW               | YES          |
| 'root'@'::1'         | def           | CREATE ROUTINE          | YES          |
| 'root'@'::1'         | def           | ALTER ROUTINE           | YES          |
| 'root'@'::1'         | def           | CREATE USER             | YES          |
| 'root'@'::1'         | def           | EVENT                   | YES          |
| 'root'@'::1'         | def           | TRIGGER                 | YES          |
| 'root'@'::1'         | def           | CREATE TABLESPACE       | YES          |
| 'tigase'@'localhost' | def           | USAGE                   | NO           |
| 'root'@'%'           | def           | SELECT                  | YES          |
| 'root'@'%'           | def           | INSERT                  | YES          |
| 'root'@'%'           | def           | UPDATE                  | YES          |
| 'root'@'%'           | def           | DELETE                  | YES          |
| 'root'@'%'           | def           | CREATE                  | YES          |
| 'root'@'%'           | def           | DROP                    | YES          |
| 'root'@'%'           | def           | RELOAD                  | YES          |
| 'root'@'%'           | def           | SHUTDOWN                | YES          |
| 'root'@'%'           | def           | PROCESS                 | YES          |
| 'root'@'%'           | def           | FILE                    | YES          |
| 'root'@'%'           | def           | REFERENCES              | YES          |
| 'root'@'%'           | def           | INDEX                   | YES          |
| 'root'@'%'           | def           | ALTER                   | YES          |
| 'root'@'%'           | def           | SHOW DATABASES          | YES          |
| 'root'@'%'           | def           | SUPER                   | YES          |
| 'root'@'%'           | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'%'           | def           | LOCK TABLES             | YES          |
| 'root'@'%'           | def           | EXECUTE                 | YES          |
| 'root'@'%'           | def           | REPLICATION SLAVE       | YES          |
| 'root'@'%'           | def           | REPLICATION CLIENT      | YES          |
| 'root'@'%'           | def           | CREATE VIEW             | YES          |
| 'root'@'%'           | def           | SHOW VIEW               | YES          |
| 'root'@'%'           | def           | CREATE ROUTINE          | YES          |
| 'root'@'%'           | def           | ALTER ROUTINE           | YES          |
| 'root'@'%'           | def           | CREATE USER             | YES          |
| 'root'@'%'           | def           | EVENT                   | YES          |
| 'root'@'%'           | def           | TRIGGER                 | YES          |
| 'root'@'%'           | def           | CREATE TABLESPACE       | YES          |
| 'tigase'@'%'         | def           | USAGE                   | NO           |
| 'jira'@'%'           | def           | USAGE                   | NO           |
+----------------------+---------------+-------------------------+--------------+
115 rows in set (0.00 sec)