Change mysql user password using command line

mysql command-line putty
user3310572 picture user3310572 · Apr 1, 2014 · Viewed 131.7k times · Source

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.

Answer

hellboy picture hellboy · Apr 1, 2014

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%";

Related questions

How to best display in Terminal a MySQL SELECT returning too many fields?

I'm using PuTTY to run: mysql> SELECT * FROM sometable; sometable has many fields and this results in many columns trying to be displayed in the terminal. The fields wrap onto the next line so it is very hard to …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How to get a list of user accounts using the command line in MySQL?

I'm using the MySQL command line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this? I'm using MySQL version 5.4.1.

Read More