/ERROR 1045 (28000): Access denied for user 'wwn'@'localhost' (using password: YES)

//ERROR 1045 (28000): Access denied for user 'wwn'@'localhost' (using password: YES) 问题


在本次迁移过程中，发现一个问题。用root用户授all privileges 权限时报错：
ERROR 1045 (28000): Access denied for user 'wwn'@'localhost' (using password: YES)

授单个权限时，则可以执行。

mysql> grant all on *.* to 'wlt'@'%' identified by 'root@centos';
ERROR 1045 (28000): Access denied for user 'wwn'@'localhost' (using password: YES)


mysql> grant select  on *.* to 'wlt'@'192.168.%' identified by 'root@centos';
Query OK, 0 rows affected (0.00 sec)


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


明显感觉root权限可能做了某些设置，或者没有这个权限。
查看root权限时，所有权限都有：
mysql> show grants;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    46
Current database: *** NONE ***


+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for wwn@localhost                                                                                                                                                                                                                                                                                                                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'wwn'@'localhost' IDENTIFIED BY PASSWORD '*DE7E9B597ABF3157DAED8A30394B6F713286A204' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'wwn'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


我自己又安装一台mysql，查看权限：


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


果真如此，报错的原因是因为线上服务器root本身就没有all privilegs 权限,所以分配别人all的时候就会报错。