MySQL修改密码时报错ERROR 1064 (42000)

最新推荐文章于 2024-07-03 16:39:32 发布

渣渣珲一枚

最新推荐文章于 2024-07-03 16:39:32 发布

阅读量947

点赞数 6

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Huihuizaizi/article/details/136823455

版权

文章讲述了在Linux环境下，MySQL8.0及以上版本中修改root用户密码时遇到的ERROR1064和ERROR1290错误，介绍了正确的操作步骤，包括创建新用户、授权、更改密码认证方式和刷新权限，以及处理skip-grant-tables的情况。

摘要由CSDN通过智能技术生成

Linux下MySQL报错ERROR 1064 (42000)、ERROR 1290 (HY000)

本人此次错误出现在修改MySQL密码时，使用语句为

update mysql.user set authentication_string=password('Zhonghui123') where user="root";

报错信息为

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 '('Zhonghui123') where user="root"' at line 1

Mysql 8.0以后，都不能这样输入，不适用于。应该按照以下方法来操作：

create user 'root'@'%' identified by 'Zhonghui@123';
grant all on *.* to 'root'@'%';
alter user 'root'@'%' identified with mysql_native_password by 'Zhonghui@123';

最后刷新下权限，就可以重新发起连接，不需要重启服务器

flush privileges;

如果设置了skip-grant-tables那就刷新一下再重复上述操作。然后进入/etc/my.cnf文件，找到【mysqld】模块，注释掉skip-grant-tables，然后重启mysql。最后使用新密码成功进入数据库

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'root'@'%' identified by 'Zhonghui@123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'root'@'%' identified with mysql_native_password by 'Zhonghui@123';
Query OK, 0 rows affected (0.01 sec)

[root@harbor ~]# vim /etc/my.cnf
[root@harbor ~]# systemctl restart mysqld
[root@harbor ~]# mysql -uroot -p'Xielei@123'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>