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

Question

Current Environment :

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.13                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.13                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)

Password Change command user :

mysql> update user set password=PASSWORD("XXXX") where user="root";
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

Am I missing something?

try to start mysql under safe mode. then you can change the root password — AReddy, Jun 22 '16 at 08:02
To anyone reading this thread. MySQL 5.7 has substantial changes under the hood. Please read about them before upgrading. — Rui F Ribeiro, Jun 22 '16 at 08:42

Rahul · Accepted Answer · 2016-06-22T12:24:34.237

25

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');

edited Jun 22 '16 at 12:24

answered Jun 22 '16 at 08:14

Rahul

13,589

1

+1 for the authentication_string reference. I also recently find out about it after upgrading to 5.7 – Rui F Ribeiro Jun 22 '16 at 08:22
5

While it is not directed entirely to this answer, another alternative for 5.7 is SET PASSWORD FOR 'root' = PASSWORD('new_password'); – Rui F Ribeiro Jun 22 '16 at 08:38
1

@RuiFRibeiro thanks, updated my answer – Rahul Jun 22 '16 at 12:24
1

Rahul Initially I edited the thread to later than 5.7, but then changed it to using MySQL 5.7, to keep more to the spirit of the question. e.g. @Shivu did not know it is a change in 5.7. – Rui F Ribeiro Jun 22 '16 at 12:57
@RuiFRibeiro but OP doesn't even care about what's going on here. – Rahul Jun 22 '16 at 12:59
1

It is a way of framing the question. We are not editing questions and answers only for the benefit of the OP, but also for others later on. – Rui F Ribeiro Jun 22 '16 at 13:00
@RuiFRibeiro that's right – Rahul Jun 22 '16 at 13:02
1

way to go mysql! such a brilliant idea to break millions of scripts out there in the wild....who needs stupid things like backwards-compatibility anyway? – cas Jun 23 '16 at 01:33
@cas I didn't get you – Rahul Jun 23 '16 at 05:22
Tried to look at 'user' table, couldn't relate the change.Thanks @Rahul. – Shivu RH Jun 23 '16 at 07:26
1

@ShivuRH did that helped you ? you were able to change root password with this method ? – Rahul Jun 23 '16 at 07:27
@ShivuRH you were not able to change password ? – Rahul Jun 23 '16 at 12:06
@ShivuRH Query OK, 1 row affected that means, it has changed root password successfully. Please try to login in mysql with the password which you set. – Rahul Jun 23 '16 at 12:13
`mysql> update user set authentication_string=password('xxxxxxx') where user='root'; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 1
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

mysql> \q Bye`
– Shivu RH Jun 23 '16 at 12:16
Let us continue this discussion in chat. – Rahul Jun 23 '16 at 12:18
set password for... didn't work for me on OSX but update user set... did work. – nyxee Aug 24 '17 at 04:26
@Rahul I tried what's in your answer several times but I still can't login, any ideas? – Petar Vasilev Dec 04 '18 at 12:31
@PetarVasilev any error in log file ? – Rahul Dec 05 '18 at 06:05
@Rahul in the log file there is only "Access denied" errors – Petar Vasilev Dec 16 '18 at 20:18

score 3 · Answer 2 · edited Oct 19 '19 at 12:24

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.

score 1 · Answer 3 · edited Jun 22 '16 at 08:25

1

mysqladmin -u user-name password -p "oldpassword" "newpass"

if you can login then try this "" wont work try '' single quote

update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE';

edited Jun 22 '16 at 08:25

Rui F Ribeiro

56,709
26
150
232

answered Jun 22 '16 at 08:09

AReddy

3,172
5
36
76

While I do prefer the mysqladmin answer, see Rahul´s answer. Your SQL query is outdated. I corrected the mysqladmin, you were missing the password directive. – Rui F Ribeiro Jun 22 '16 at 08:22
1

Thanks Ribeiro. I missed authentication_string – AReddy Jun 22 '16 at 08:32

score 0 · Answer 4 · edited Jun 07 '18 at 11:29

0

In my case

mysql.server start
$ mysql -uroot
mysql> update user set authentication_string=password('123456') where User='root';
mysql> exit;
mysql.server restart (if you not restart , connection will not work.)

So I think, your update command is right, but you need to restart your mysql server.

edited Jun 07 '18 at 11:29

peterh

9,731

answered Jun 07 '18 at 11:01

Jack LI

1

score -1 · Answer 5 · answered Apr 18 '17 at 02:23

For this problem, I used a simple and rude method, rename the field name to password, the reason for this is that I use the mac navicat premium software in the visual operation error: Unknown column 'password' in 'field List ', the software itself uses password so that I can not easily operate. Therefore, I root into the database command line, run

Use mysql;

And then modify the field name:

ALTER TABLE user CHANGE authentication_string password text;

After all normal.

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

5 Answers5

Linked