MySQL 用户数据库没有密码列 - 在 OSX 上安装 MySQL

MySQL user DB does not have password columns - Installing MySQL on OSX(MySQL 用户数据库没有密码列 - 在 OSX 上安装 MySQL)

问题描述

我正在尝试更改 MySql 根密码.

I am trying to change MySql root password.

我所做的如下.

1. 安装 MySql-5.7.6 ~ .dmg(社区服务器)和工作台.
2. 在 OSX 系统偏好设置中关闭服务器.
3. 使用控制台访问 MySql.命令是 mysqld_safe --skip-grant
4. 执行 update user set password=password('1111') where user='root'; 并收到错误消息 --> ERROR 1054 (42S22): Unknown column 'password'在'字段列表'.

1. Install MySql-5.7.6 ~ .dmg(Community Server) and workbench.
2. Turn off the server on OSX System preferences.
3. Access MySql with console. The command was mysqld_safe --skip-grant
4. Execute update user set password=password('1111') where user='root'; and got an error message --> ERROR 1054 (42S22): Unknown column 'password' in 'field list'.

仅供参考，我确实使用mysql;.所以我在用户表上选择查询，发现密码列实际上不存在.

FYI, I did use mysql;. So I did select query on user table and found password column actually does not exist.

这很奇怪.有没有可能原来的user表没有password列?

It is very weird. Is it possible that original user table does not have password column?

如何更改不存在的密码?

How can I change password, which does not exist?

感谢您的回答:D

推荐答案

在 MySQL 5.7 中，mysql.user 表字段中的密码字段被删除，现在字段名称为 'authentication_string'.

In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is 'authentication_string'.

首先选择数据库:

mysql>use mysql;

然后显示表格:

mysql>show tables;

您将找到用户表，现在让我们看看它的字段:

You will find the user table, now let's see its fields:

mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(16)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

惊喜！没有名为'password'的字段，密码字段名为'authentication_string'.所以，就这样做:

Surprise！There is no field named 'password', the password field is named ' authentication_string'. So, just do this:

update user set authentication_string=password('1111') where user='root';

现在，一切都会好起来的.

Now, everything will be ok.

与 MySQL 5.6 相比，变化相当广泛:什么是MySQL 5.7 中的新功能

Compared to MySQL 5.6, the changes are quite extensive: What’s New in MySQL 5.7

这篇关于MySQL 用户数据库没有密码列 - 在 OSX 上安装 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！