尝试授予特权时，拒绝用户'root'@'localhost'的访问。如何授予特权？

Question

我看过许多类似的问题，因此表明我已经检查了基础知识。当然，这并不意味着我没有错过任何显而易见的东西。:-)

我的问题是：为什么我拒绝具有特权的用户访问权限，而我却已经在其中输入密码并被授予访问权限？（为了完整起见，我尝试输入错误的密码只是为了确保MySQL客户端在程序启动时拒绝我访问。）

背景：

通过ssh登录到运行MySQL服务器的计算机的外壳，我以root用户身份登录：

[myname@host ~]$ mysql -u root -p -hlocalhost

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 62396

Server version: 5.5.18-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

太棒了 我对类似问题的答案的阅读表明，我应该确保特权与授予表中的权限保持最新

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> 

接下来，请确保我是我认为的我：

mysql> SELECT user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

...真的真的很确定：

mysql> SELECT current_user();

+----------------+

| current_user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

mysql> 

到现在为止还挺好。现在我有什么特权？

Answer 1

注意输出SHOW GRANTS FOR 'root'@'localhost';没有说“ ALL PRIVILEGES”，而是不得不说出root @ localhost的含义。授予所有特权将失败，因为用户无法授予他/她没有的特权，并且服务器似乎认为这里不存在某些东西...现在，接下来缺少什么呢？在我的系统上，我得到以下信息：mysql> select version();+------------+| version()&nbsp; |+------------+| 5.5.21-log |+------------+1 row in set (0.00 sec)mysql> SHOW GRANTS FOR 'root'@'localhost';+---------------------------------------------------------------------+| Grants for root@localhost&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION&nbsp; &nbsp; &nbsp; &nbsp; |+---------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> SELECT * FROM mysql.user WHERE User='root' and Host='localhost'\G*************************** 1. row ***************************&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Host: localhost&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; User: root&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Password:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Select_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Insert_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Update_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Delete_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Create_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Drop_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Reload_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Shutdown_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Process_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;File_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Grant_priv: Y&nbsp; &nbsp; &nbsp; &nbsp;References_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Index_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Alter_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Show_db_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Super_priv: Y&nbsp;Create_tmp_table_priv: Y&nbsp; &nbsp; &nbsp; Lock_tables_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Execute_priv: Y&nbsp; &nbsp; &nbsp; &nbsp;Repl_slave_priv: Y&nbsp; &nbsp; &nbsp; Repl_client_priv: Y&nbsp; &nbsp; &nbsp; Create_view_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; Show_view_priv: Y&nbsp; &nbsp;Create_routine_priv: Y&nbsp; &nbsp; Alter_routine_priv: Y&nbsp; &nbsp; &nbsp; Create_user_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Event_priv: Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Trigger_priv: YCreate_tablespace_priv: Y <----------------------------- new column in 5.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ssl_type:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ssl_cipher:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;x509_issuer:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x509_subject:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;max_questions: 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;max_updates: 0&nbsp; &nbsp; &nbsp; &nbsp;max_connections: 0&nbsp; max_user_connections: 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; plugin: <------------------------------- new column in 5.5&nbsp;authentication_string: <------------------------------- new column in 5.51 row in set (0.00 sec)5.5中还有一些新表，例如mysql.proxies_user：确保您拥有它们。安装全新的mysql服务器实例时，安装脚本将创建具有适当结构的所有mysql。*表。从旧版本升级时，请确保使用正确的升级过程（mysql_upgrade），它将添加缺少的表/列。这只是一个猜测，但似乎该实例的mysql_upgrade未完成，导致出现了这种现象。

Answer 2

当我尝试安装比发行版随附的MySQL版本更高的MySQL版本时，发生了这种情况。我删除了旧版本，然后安装了新版本（rpm -e ...然后是rpm -i MySQL-server *），但没有意识到/ var / lib / mysql中的文件仍然来自较早版本（区别在于由Marc Alff解释-谢谢！）我本可以做一个mysql_upgrade，但是由于我想从头开始，所以我做了：# su - mysql$ rm -rf /var/lib/mysql/*$ mysql_install_db# /etc/init.d/mysql start然后设置root密码（/ usr / bin / mysqladmin -u root密码），所有命令都可以通过GRANT命令正常工作...