How to change MySQL 'root' password using MySQL v5.7?

In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is authentication_string.

First choose the database:

mysql> use mysql;

And then show the tables:

mysql> show tables;

You will find the user table, and see its fields:

mysql> describe user;

You will realize there is no field named password, the password field is named authentication_string. So, just do this:

update user set authentication_string=password('XXXX') where user='root';

As suggested by @Rui F Ribeiro, alternatively you can run:

mysql> SET PASSWORD FOR 'root' = PASSWORD('new_password');

The MySQL way of changing password is SET PASSWORD

SET PASSWORD FOR 'root' = PASSWORD('new_password');

see MySQL 5.7 Reference Manual / ... / SET PASSWORD Syntax

The SET PASSWORD statement assigns a password to a MySQL user account, specified as either a cleartext (unencrypted) or encrypted value:

'auth_string' represents a cleartext password.

'hash_string' represents an encrypted password.

The accepted answer from Rahul shows how to update password with DML statement.

update user set authentication_string=password('XXXX') where user='root';

Warning: that's not the official and supported way. It can cause troubles, if you don't know what you are doing. Don't forget FLUSH PRIVILEGES.

For most operations, like creating a user, changing its privileges, or changing its password, you will want to use the high-level statements. Not only they are easier to use and they are compatible with a larger number of MySQL versions, but they will also prevent you from making mistakes (of course, remember to setup the “NO_AUTO_CREATE_USER“ sql mode). They even usually work nicely in a MyISAM-hostile environment like a Galera cluster.

Stop using FLUSH PRIVILEGES

Please use GRANT, REVOKE, SET PASSWORD, or RENAME USER and not direct DML statements.

Update: SET PASSWORD ... = PASSWORD('auth_string') syntax is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.

Tags:

Mysql