Update user password in Mysql 5.7

authentication root mysql-5.7
Tobia picture Tobia · Aug 25, 2015 · Viewed 44.5k times · Source

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.

Answer

mdamia picture mdamia · Sep 7, 2015

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.

Related questions

MySQL root-login with SSH private key?

Suppose Mysql -dbs and an admin with a bad habbit to forget the passwords (or paranoid enough wanting more creative authentication, not just password). Is it possible to access Mysql -root shell for example in a way that the Ssh-agent …

Read More
Git push requires username and password

I cloned a Git repository from my GitHub account to my PC. I want to work with both my PC and laptop, but with one GitHub account. When I try to push to or pull from GitHub using my PC, …

Read More
Git push results in "Authentication Failed"

I have been using Github for a little while and I have been fine with git add, git commit, and git push so far with no problems. Suddenly I am having an error that says: fatal: Authentication Failed In the …

Read More