Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update user password in Mysql 5.7

I wrote an installation script to change the root password with this SQL command:

UPDATE user SET password='*C563415623144561...' WHERE user='root';

This doesn't work on Mysql 5.7: http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html#mysqld-5-7-6-account-management

My question is: how to change this command with another one compatible with 5.6 and 5.7 version of Mysql? I would like to update the password with a hashed string and not with a clear password.

like image 321
Tobia Avatar asked Aug 25 '15 15:08

Tobia


People also ask

How can I change MySQL root password?

To change the root password, type the following at the MySQL/MariaDB command prompt: ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd'; flush privileges; exit; Store the new password in a secure location.


2 Answers

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/

like image 200
Venkat Kotra Avatar answered Sep 20 '22 16:09

Venkat Kotra


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 '';
like image 21
Carlos Alberto García Guardia Avatar answered Sep 19 '22 16:09

Carlos Alberto García Guardia