Update user password in Mysql 5.7

Given that 'SET PASSWORD FOR = PASSWORD('')' is deprecated as on mysql 5.7. If not done correctly you could even end up with below error in syslog.

The plugin 'auth_socket' used to authenticate user 'root'@'localhost' is not loaded. Nobody can currently login using this account.

I suggest to use below command.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass';

https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/


I've used this command to reset to empty password in Mysql 5.7.22

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

This is no more password field in the user table as of mysql 5.7. It's now called authentication_string. You can change or set the password like this:

set password for 'jeff'@'localhost' = PASSWORD('mypass'); // this automatically hashes the password

If you you want to use your query , just change password to authentication_string,and it will work.

UPDATE user SET authentication_string='*C563415623144561...' WHERE user='root@localhost';

Hope this help.