84

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
  • 969
  • 1
  • 6
  • 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

9 Answers9

116

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,116
  • 1
  • 11
  • 18
  • 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
38

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
  • 44,747
  • 17
  • 195
  • 185
Govind Rai
  • 10,062
  • 7
  • 54
  • 74
  • 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: u should login as root user 

 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your password');
vijay kumar
  • 761
  • 10
  • 8
6

this is the updated answer for WAMP v3.0.6

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

FLUSH PRIVILEGES;
Adiii
  • 35,809
  • 6
  • 84
  • 87
Robert Anthony S. Tribiana
  • 944
  • 10
  • 15
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
  • 23,005
  • 22
  • 116
  • 159
David Silva Smith
  • 10,265
  • 10
  • 62
  • 87
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
  • 3,988
  • 2
  • 24
  • 31
1

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,026
  • 2
  • 18
  • 43
0

As of MySQL 8.0.18 This works fine for me

mysql> SET PASSWORD FOR 'user'@'localhost' = 'userpassword';
mykoman
  • 787
  • 1
  • 7
  • 20
-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,636
  • 4
  • 22
  • 26