93

I'm trying to update the password for a database user using the command line, and it's not working for me. This is the code I'm using:

mysql> UPDATE user SET password=PASSWORD($w0rdf1sh) WHERE user='tate256';

Could someone tell me what's wrong with this code.

user3310572
  • 1,059
  • 1
  • 7
  • 6
  • Well what steps are you following? Simply putting it in won't work. If I remember correctly its a process and that code. – Idris Apr 01 '14 at 00:37
  • Ah. Do you remember the process of how to do it? – user3310572 Apr 01 '14 at 00:39
  • This is a bit more detailed and should be more easier to understand http://innovativethought.net/2007/05/17/resetting-your-forgotten-mysql-password/ – Idris Apr 01 '14 at 00:41

10 Answers10

121

In your code, try enclosing password inside single quote. Alternatively, as per the documentation of mysql, following should work -

SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('cleartext password');

FLUSH PRIVILEGES;

The last line is important or else your password change won't take effect unfortunately.

EDIT:

I ran a test in my local and it worked -

mysql>  set password for 'test' = PASSWORD('$w0rdf1sh');
Query OK, 0 rows affected (0.00 sec)

Mine is version 5. You can use following command to determine your version -

SHOW VARIABLES LIKE "%version%";
Community
  • 1
  • 1
hellboy
  • 2,222
  • 1
  • 14
  • 19
  • 4
    When I do that, it's returning syntax error near unexpected token (' – user3310572 Apr 01 '14 at 00:42
  • 1
    What version of mysql are you using? I picked up the above syntax from version 5.0 documentation. Did you try the command that I posted? – hellboy Apr 01 '14 at 00:44
  • I believe I'm using a 4.0 version and yes I tried it – user3310572 Apr 01 '14 at 00:47
  • Command works well for me in MySQL Ver 14.14, ensure the password is wrapped in quotes and any quotes in the password are escaped. – Snaver Jun 24 '14 at 18:26
  • ```error near unexpected token ('``` happens if you havent logged in to mysql but try to use the statements in the shell. Always log on mysql first with (f.e. root): ```mysql -u root -p``` – Florian Bauer Dec 29 '15 at 04:24
  • 1
    Strangely, even after flushing, still doesn't work for me :-( I'm using Mysql `5.7.18-0ubuntu0.16.04.1` – coding_idiot Jul 07 '17 at 03:42
43

As of MySQL 5.7.6, use ALTER USER

Example:

ALTER USER 'username' IDENTIFIED BY 'password';

Because:

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

  • SET PASSWORD ... = 'auth_string' syntax is not deprecated, but ALTER USER is now the preferred statement for assigning passwords.

T.Todua
  • 53,146
  • 19
  • 236
  • 237
Govind Rai
  • 14,406
  • 9
  • 72
  • 83
  • 1
    is this true even for interacting with MySQL via PHP? – oldboy Aug 07 '17 at 02:39
  • @Anthony yes iff your MySQL version is >= 5.7.6 – Govind Rai Aug 07 '17 at 14:06
  • i'm new to MySQL. what does `ALTER USER ... IDENTIFIED BY ...` do exactly? how is that different from `UPDATE ... SET ...` or `INSERT INTO ...`? – oldboy Aug 07 '17 at 19:02
  • 1
    `ALTER USER ... IDENTIFIED BY ...` is a SQL statement that configures user permissions for your database. `UPDATE` and `INSERT` have different functionalities, which like the names suggest, update records and insert records into tables in your database. – Govind Rai Aug 07 '17 at 20:58
  • ohhhhhhhh `ALTER...` is for configuring user permission. that makes sense. – oldboy Aug 07 '17 at 21:00
  • the genius who decided this just made virtually all tutorials and resource online out of date, and it is now a PITA to get started in mysql, I'm fuming rn. – v.oddou Mar 10 '18 at 17:55
  • Oh I see, the `ALTER` stuff passes through the password strength test plugin, and of course as usual in strenght checking, nobody heard of that https://www.xkcd.com/936/ and wrongly rejects very long passphrases. double fail, mysql. – v.oddou Mar 10 '18 at 17:59
  • 2
    gives error in mariaDb `ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'root' IDENTIFIED BY` – Pavel Niedoba Apr 16 '19 at 09:49
  • How about when using `--skip-grant-tables` to restore root access? I get this error. `ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded` – Mathieu J. Nov 18 '19 at 09:04
  • For me the syntax is: `ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';` – Kohjah Breese Jan 29 '20 at 21:37
14

Note: you should login as root user

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your password');
General Grievance
  • 4,555
  • 31
  • 31
  • 45
vijay kumar
  • 1,025
  • 13
  • 11
7

this is the updated answer for WAMP v3.0.6

UPDATE mysql.user 
SET authentication_string=PASSWORD('MyNewPass') 
WHERE user='root';

FLUSH PRIVILEGES;
Adiii
  • 54,482
  • 7
  • 145
  • 148
Robert Anthony S. Tribiana
  • 1,012
  • 12
  • 16
3

Before MySQL 5.7.6 this works from the command line:

mysql -e "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('$w0rdf1sh');"

I don't have a mysql install to test on but I think in your case it would be

mysql -e "UPDATE mysql.user SET Password=PASSWORD('$w0rdf1sh') WHERE User='tate256';"
Abdull
  • 26,371
  • 26
  • 130
  • 172
David Silva Smith
  • 11,498
  • 11
  • 67
  • 91
2

In windows 10, just exit out of current login and run this on command line

--> mysqladmin -u root password “newpassword”

where instead of root could be any user.

Napolean
  • 5,303
  • 2
  • 29
  • 35
2

As of MySQL 8.0.18 This works fine for me

mysql> SET PASSWORD FOR 'user'@'localhost' = 'userpassword';
mykoman
  • 1,715
  • 1
  • 19
  • 33
2

This works for me. Got solution from MYSQL webpage

In MySQL run below queries:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'New_Password';
Kirti Nikam
  • 2,166
  • 2
  • 22
  • 43
0

You can use the below query which worked for me.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.01 sec)

After this you need to restart SQL server

sudo service mysql stop
sudo service mysql start

Earlier I was facing the same error,

mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

But, now I can login using command mysql -u root -p and password password.

Sanjit
  • 57
  • 8
-1

Your login root should be /usr/local/directadmin/conf/mysql.conf. Then try following

UPDATE mysql.user SET password=PASSWORD('$w0rdf1sh') WHERE user='tate256' AND Host='10.10.2.30';
FLUSH PRIVILEGES;

Host is your mysql host.

user353gre3
  • 2,747
  • 4
  • 24
  • 27