MySQL修改密码时报错

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

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

update user set authentication_string=PASSWORD("123456") 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 '("123456") where user="root"' at line 1

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

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

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

flush privileges;

如果设置了skip-grant-tables那就刷新一下再重复上述操作

mysql> grant all on *.* to 'root'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> create user 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

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

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

完美解决。