问题描述:
mysql -u root -p命令登录MySQL,提示ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
解决办法:
information_schema 库的USER_PRIVILEGES 表中 没有给 赋权。
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 namesYou can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from user;问题分析:
通过 mysql -u root -p -h 127.0.0.1 可以登录,但是 mysql -u root -p 就报错。
查看information_schema 库的 USER_PRIVILEGES; 表,发现 没有权限。 执行
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7CF1EF598B5B0FC97DAD7C8051A25DC0CE8A9AD9' ; 赋权后,
mysql> select * from USER_PRIVILEGES ;
+--------------------------------+---------------+-------------------------+--------------+| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |+--------------------------------+---------------+-------------------------+--------------+| 'root'@'localhost.localdomain' | def | SELECT | YES || 'root'@'localhost.localdomain' | def | INSERT | YES || 'root'@'localhost.localdomain' | def | UPDATE | YES || 'root'@'localhost.localdomain' | def | DELETE | YES || 'root'@'localhost.localdomain' | def | CREATE | YES || 'root'@'localhost.localdomain' | def | DROP | YES || 'root'@'localhost.localdomain' | def | RELOAD | YES || 'root'@'localhost.localdomain' | def | SHUTDOWN | YES || 'root'@'localhost.localdomain' | def | PROCESS | YES || 'root'@'localhost.localdomain' | def | FILE | YES || 'root'@'localhost.localdomain' | def | REFERENCES | YES || 'root'@'localhost.localdomain' | def | INDEX | YES || 'root'@'localhost.localdomain' | def | ALTER | YES || 'root'@'localhost.localdomain' | def | SHOW DATABASES | YES || 'root'@'localhost.localdomain' | def | SUPER | YES || 'root'@'localhost.localdomain' | def | CREATE TEMPORARY TABLES | YES || 'root'@'localhost.localdomain' | def | LOCK TABLES | YES || 'root'@'localhost.localdomain' | def | EXECUTE | YES || 'root'@'localhost.localdomain' | def | REPLICATION SLAVE | YES || 'root'@'localhost.localdomain' | def | REPLICATION CLIENT | YES || 'root'@'localhost.localdomain' | def | CREATE VIEW | YES || 'root'@'localhost.localdomain' | def | SHOW VIEW | YES || 'root'@'localhost.localdomain' | def | CREATE ROUTINE | YES || 'root'@'localhost.localdomain' | def | ALTER ROUTINE | YES || 'root'@'localhost.localdomain' | def | CREATE USER | YES || 'root'@'localhost.localdomain' | def | EVENT | YES || 'root'@'localhost.localdomain' | def | TRIGGER | YES || 'root'@'localhost.localdomain' | 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 || 'root'@'localhost' | def | SELECT | NO || 'root'@'localhost' | def | INSERT | NO || 'root'@'localhost' | def | UPDATE | NO || 'root'@'localhost' | def | DELETE | NO || 'root'@'localhost' | def | CREATE | NO || 'root'@'localhost' | def | DROP | NO || 'root'@'localhost' | def | RELOAD | NO || 'root'@'localhost' | def | SHUTDOWN | NO || 'root'@'localhost' | def | PROCESS | NO || 'root'@'localhost' | def | FILE | NO || 'root'@'localhost' | def | REFERENCES | NO || 'root'@'localhost' | def | INDEX | NO || 'root'@'localhost' | def | ALTER | NO || 'root'@'localhost' | def | SHOW DATABASES | NO || 'root'@'localhost' | def | SUPER | NO || 'root'@'localhost' | def | CREATE TEMPORARY TABLES | NO || 'root'@'localhost' | def | LOCK TABLES | NO || 'root'@'localhost' | def | EXECUTE | NO || 'root'@'localhost' | def | REPLICATION SLAVE | NO || 'root'@'localhost' | def | REPLICATION CLIENT | NO || 'root'@'localhost' | def | CREATE VIEW | NO || 'root'@'localhost' | def | SHOW VIEW | NO || 'root'@'localhost' | def | CREATE ROUTINE | NO || 'root'@'localhost' | def | ALTER ROUTINE | NO || 'root'@'localhost' | def | CREATE USER | NO || 'root'@'localhost' | def | EVENT | NO || 'root'@'localhost' | def | TRIGGER | NO || 'root'@'localhost' | def | CREATE TABLESPACE | NO || ''@'localhost.localdomain' | 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 |+--------------------------------+---------------+-------------------------+--------------+141 rows in set (0.00 sec)mysql>
'root'@'localhost' 有了权限,就可以正常登录了。