无法使用 UPDATE 在 MySQL 上更改用户密码 [英] Can't change user password on MySQL using UPDATE
问题描述
在 MySql 5.7.17 下,上面提到的指令不起作用,我总是没有收到反馈或以下错误消息:
Under MySql 5.7.17 the mentioned instruction do not work and always I get no feedback or the following error message:
ERROR 1064 (42000):您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,了解在 'UPDATE mysql.user SET password=password("elephant7") where user="root"' at line ...
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE mysql.user SET password=password("elephant7") where user="root"' at line ...
我在命令行上尝试了以下 UPDATE
:
I tried the following UPDATE
on the command line:
UPDATE mysql.user SET Password = PASSWORD('elephant7') WHERE User='root';
我真的看不到我的错误了.我也试过不用 ;
.
I really don't see anymore my mistake. I also tried without ;
.
推荐答案
不建议直接在 mysql.user
表上使用 UPDATE
这种方式修改密码.您应该使用 SET PASSWORD
代替:
It's not recommended to change the password in this way using UPDATE
directly on the mysql.user
table. You should use SET PASSWORD
instead:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('elephant7');
您的 UPDATE
命令可能不起作用,因为 password
列被替换为 authentication_string
在 MySQL 5.7.6 上.
Your UPDATE
command perhaps doesn't work because the password
column get replaced by authentication_string
on MySQL 5.7.6.
mysql.user
表中的 authentication_string
列现在存储所有帐户的凭据信息.password
列,以前用于存储通过 mysql_native_password
和 mysql_old_password
插件验证的帐户的密码哈希值,已删除.
The
authentication_string
column in themysql.user
table now stores credential information for all accounts. Thepassword
column, previously used to store password hash values for accounts authenticated with themysql_native_password
andmysql_old_password
plugins, is removed.
如果您直接更改授权表,您还必须使用 FLUSH PRIVILEGES
语句重新加载表:
In case you directly change the grant tables you also have to reload the tables by using the FLUSH PRIVILEGES
statement:
如果您直接使用INSERT
、UPDATE
或DELETE
等语句修改授权表(不推荐),更改对权限检查没有影响,直到您告诉服务器重新加载表或重新启动它.因此,如果您直接更改授权表但忘记重新加载它们,那么在您重新启动服务器之前,这些更改将无效.这可能会让您想知道为什么您的更改似乎没有任何影响!
If you modify the grant tables directly using statements such as
INSERT
,UPDATE
, orDELETE
(which is not recommended), the changes have no effect on privilege checking until you either tell the server to reload the tables or restart it. Thus, if you change the grant tables directly but forget to reload them, the changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!
要告诉服务器重新加载授权表,请执行刷新权限操作.这可以通过发出 FLUSH PRIVILEGES
语句来完成.
To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES
statement.
来源:当权限更改生效时
所以您的 UPDATE
命令直接更改授权表上的密码必须如下所示,使用正确的列和 FLUSH PRIVILEGES
声明:
So your UPDATE
command to directly change the password on the grant tables have to look like this, using the correct column and the FLUSH PRIVILEGES
statement:
UPDATE mysql.user SET authentication_string = PASSWORD('elephant7') WHERE User = 'root';
FLUSH PRIVILEGES;
这篇关于无法使用 UPDATE 在 MySQL 上更改用户密码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!