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

MySQL user DB does not have password columns - Installing MySQL on OSX

提问by Juneyoung Oh

I am trying to change MySql root password.

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

What I have done is below.

我所做的如下。

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'.

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' in 'field list'。

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

仅供参考，我做到了use mysql;。所以我在用户表上选择查询，发现密码列实际上不存在。

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?

如何更改不存在的密码？

Thanks for your answer :D

谢谢你的回答:D

回答by nodejh

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

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

First choose the database:

首先选择数据库：

mysql>use mysql;

And then show the tables:

然后显示表格：

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)

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

惊喜！没有名为'password'的字段，密码字段名为'authentication_string'。所以，只需这样做：

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

Now, everything will be ok.

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

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

与 MySQL 5.6 相比，变化相当广泛：MySQL 5.7 的新增功能

回答by bl79

This error happens if you did not set the password on install, in this case the mysql using unix-socket plugin.

如果您没有在安装时设置密码，则会发生此错误，在这种情况下，mysql 使用 unix-socket 插件。

But if delete the plugin link from settings (table mysql.user) will other problem. This does not fix the problem and creates another problem. To fix the deleted link and set password ("PWD") do:

但是如果从设置（表mysql.user）中删除插件链接会出现其他问题。这并不能解决问题，而是会产生另一个问题。要修复已删除的链接并设置密码（“PWD”），请执行以下操作：

1) Run with --skip-grant-tablesas said above.

1)--skip-grant-tables如上所述运行。

If it doesnt works then add the string skip-grant-tablesin section [mysqld]of /etc/mysql/mysql.conf.d/mysqld.cnf. Then do sudo service mysql restart.

如果它不工作，然后添加字符串skip-grant-tables中的部分[mysqld]的 /etc/mysql/mysql.conf.d/mysqld.cnf。然后做 sudo service mysql restart。

2) Run mysql -u root -p, then (change "PWD"):

2）运行mysql -u root -p，然后（更改“PWD”）：

update mysql.user 
    set authentication_string=PASSWORD("PWD"), plugin="mysql_native_password" 
    where User='root' and Host='localhost';    
flush privileges;

quit

then sudo service mysql restart. Check: mysql -u root -p.

然后sudo service mysql restart。检查：mysql -u root -p。

Before restartremove that string from file mysqld.cnf, if you set it there.

在restart从文件 mysqld.cnf 中删除该字符串之前，如果您在那里设置了它。

回答by John C

One pitfall I fell into is there is no password fieldnow, it has been renamed so:

我陷入的一个陷阱是现在没有密码字段，它已被重命名为：

update user set password=PASSWORD("YOURPASSWORDHERE") where user='root';

update user set password=PASSWORD("YOURPASSWORDHERE") where user='root';

Should now be:

现在应该是：

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

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

回答by O. Jones

Use the ALTER USERcommand rather than trying to update a USERrow. Keep in mind that there may be more than one 'root' user, because user entities are qualified also by the machine from which they connect

使用ALTER USER命令而不是尝试更新USER行。请记住，可能有多个“root”用户，因为用户实体也由它们连接的机器限定

https://dev.mysql.com/doc/refman/5.7/en/alter-user.html

https://dev.mysql.com/doc/refman/5.7/en/alter-user.html

For example.

例如。

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password' 
ALTER USER 'root'@'*' IDENTIFIED BY 'new-password' 

回答by Amgad

It only worked with me when I "flushed" after the commands mentioned here. Here's the full list of commands I used:

它只在我在这里提到的命令之后“冲洗”时才对我有用。这是我使用的命令的完整列表：

Previous answers might not work for later mysql versions. Try these steps if previous answers did not work for you:

以前的答案可能不适用于更高版本的 mysql 版本。如果以前的答案不适合您，请尝试以下步骤：

1- Click on the wamp icon > mysql > mysql console

1- 单击 wamp 图标 > mysql > mysql 控制台

2- write following commands, one by one

2- 一一写出以下命令

use mysql;
update user set authentication_string=password('your_password') where user='root';
FLUSH PRIVILEGES;
quit

回答by eli

Thank you for your help. Just in case if people are still having problems, try this.

感谢您的帮助。以防万一，如果人们仍然遇到问题，请尝试此操作。

For MySQL version 5.6 and under

对于 MySQL 5.6 及以下版本

Have you forgotten your Mac OS X 'ROOT' password ?and need to reset it? ?Follow these 4 simple steps:

您是否忘记了 Mac OS X 的“ROOT”密码？需要重新设置吗？?遵循以下 4 个简单步骤：

1. ?Stop the mysqld server. ?Typically this can be done by from 'System Prefrences' > MySQL > 'Stop MySQL Server'
2. ?Start the server in safe mode with privilege bypass ? ? ?From a terminal: ? ? ?sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3. ?In a new terminal window: ? ? ?sudo /usr/local/mysql/bin/mysql -u root ? ? ?UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root'; ? ? ?FLUSH PRIVILEGES; ? ? ?\q
4. ?Stop the mysqld server again and restart it in normal mode.

1. ?停止mysqld服务器。? 通常，这可以通过“系统偏好”> MySQL >“停止 MySQL 服务器”来完成
2. ? 以安全模式启动服务器并绕过特权？? ?从终端：? ?sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3. ?在一个新的终端窗口中： ? ? ?sudo /usr/local/mysql/bin/mysql -u root ? ? ?UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root'; ? ? ?FLUSH PRIVILEGES; ? ? ?\q
4. ? 再次停止 mysqld 服务器并以正常模式重新启动它。

For MySQL version 5.7 and up

对于 MySQL 5.7 及更高版本

1. ?Stop the mysqld server. ?Typically this can be done by from 'System Prefrences' > MySQL > 'Stop MySQL Server'
2. ?Start the server in safe mode with privilege bypass ? ? ?From a terminal: ? ? ?sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3. ?In a new terminal window: ? ? ?? ? ?sudo /usr/local/mysql/bin/mysql -u root ? ? ?UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword') WHERE User='root'; ? ? ?FLUSH PRIVILEGES; ? ? ?\q ? ? ?
4. ?Stop the mysqld server again and restart it in normal mode.

1. ?停止mysqld服务器。?通常这可以通过 from 来完成 'System Prefrences' > MySQL > 'Stop MySQL Server'
2. ? 以安全模式启动服务器并绕过特权？? ?从终端：? ?sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3. ?在一个新的终端窗口中：? ?? ? ?sudo /usr/local/mysql/bin/mysql -u root ? ? ?UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword') WHERE User='root'; ? ? ?FLUSH PRIVILEGES; ? ? ?\q ? ? ?
4. ? 再次停止 mysqld 服务器并以正常模式重新启动它。

回答by luyishisi

For this problem, I used a simple and rude method, rename the field name to password, the reason for this is that I use the mac navicat premium software in the visual operation error: Unknown column 'password' in 'field List ', the software itself uses password so that I can not easily operate. Therefore, I root into the database command line, run

对于这个问题，我用了一个简单粗暴的方法，将字段名重命名为password，原因是我用mac navicat premium软件在可视化操作报错：Unknown column 'password' in 'field List', the软件本身使用密码使我不能轻易操作。因此，我 root 进入数据库命令行，运行

Use mysql;

And then modify the field name:

然后修改字段名：

ALTER TABLE user CHANGE authentication_string password text;

After all normal.

毕竟正常。

回答by Jameskchau

Root Cause: root has no password, and your python connect statement should reflect that.

根本原因：root 没有密码，您的 python 连接语句应该反映这一点。

To solve error 1698, change your python connect password to ''.

要解决错误 1698，请将您的 python 连接密码更改为 ''。

note: manually updating the user's password will not solve the problem, you will still get error 1698

注意：手动更新用户密码并不能解决问题，您仍然会收到错误 1698

回答by Pravin

remember password needs to be set further even after restarting mysql as below

记住密码即使在重新启动mysql后也需要进一步设置，如下所示

SET PASSWORD = PASSWORD('root');