MySQL 用户 DB 没有密码列 - 在 OSX 上安装 MySQL

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

问题描述

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

I am trying to change MySql root password.

我所做的如下.

1. 安装 MySql-5.7.6 ~ .dmg(Community Server) 和工作台.
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.

这很奇怪.原用户表可能没有密码列吗?

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 相比，变化相当广泛:What'sMySQL 5.7 中的新功能

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

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