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.