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>