MySQL Change a User Password

by on July 13, 2007 · 9 comments· LAST UPDATED December 6, 2012

in , ,

I would like to change a password for a user called tom using UNIX / Linux command line option. How do I change a user password under MySQL server?

Tutorial details
DifficultyEasy (rss)
Root privilegesYes (mysql admin user)
Requirementsmysql
Estimated completion timeN/A

You need to use mysql (or mysql.exe on MS-Windows based system) under Linux or Unix like operating system. Open a terminal / ssh session. Type the following command at the shell prompt to login as a root user. The syntax is as follows for Unix like operating system:

$mysql -u root -h localhost -p

OR use mysql.exe under MS-Windows host as follows (first change directory where mysql.exe is located [example: "C:\Program Files\mysql\mysql-5.0.77-win32\bin"] ):

mysql.exe -h localhost --user=root -p

You will see mysq> prompt as follows:

mysq>

Syntax: Sql command to change a user password

Switch to mysql database (type command at mysql> prompt, do not include string "mysql>"):
mysql> use mysql;

The syntax is:

 
SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here');
 

You can also use the following sql syntax:

 
UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='user-name-here' AND Host='host-name-here';
 

In this example, change a password for a user called tom:

SET PASSWORD FOR 'tom'@'localhost' = PASSWORD('foobar');

OR

UPDATE mysql.user SET Password=PASSWORD('foobar') WHERE User='tom' AND Host='localhost';

Sample outputs:

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Feel free to replace the values for "tom" (user), "localhost" (hostname), and "foobar" (password) as per your requirements. Finally, type the following command to reload privileges:

FLUSH PRIVILEGES;

Sample outputs:

Query OK, 0 rows affected (0.00 sec)

To exit from mysql> prompt, enter:

quit;

Sample session

Fig.01: Mysql Updating / Changing password (click to enlarge)

Fig.01: Mysql Updating / Changing password (click to enlarge)

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 9 comments… read them below or add one }

1 Michael September 24, 2009 at 11:28 pm

then…

mysql> flush privileges;

Reply

2 Keilaron February 12, 2010 at 3:37 pm
3 mehari January 4, 2011 at 8:08 pm

This is good

Reply

4 amazed January 27, 2011 at 8:10 am

Very helpful information.

Reply

5 Zipfer October 12, 2011 at 6:31 am

You forgot – FLUSH PRIVILEGES; after update tables

Reply

6 stevyn December 1, 2011 at 1:35 pm

You legend. Thank you :)

Reply

7 Re@lBanda January 25, 2012 at 4:00 am

Small modification in 3rd step… according to the mysql documentation, the password should be typed within single quotes instead of double quotes. But double quotes works. Refer the example below…

mysql> UPDATE user SET Password=PASSWORD(‘NEW-PASSWORD-HERE’) WHERE User=’tom’;

Another thing to mention, make sure you change the password for both the local and remote users because if a remote application server (ex-jboss) or in php connecting to mysql server it will still be needed the old password since it is remaining unchanged.

So according to this scenario the proper commands should be…

mysql> UPDATE user SET Password=PASSWORD(‘NEW-PASSWORD-HERE’) WHERE User=’tom’ AND Host=’local’;

mysql> UPDATE user SET Password=PASSWORD(‘NEW-PASSWORD-HERE’) WHERE User=’tom’ AND Host=’%';

Reply

8 Re@lBanda January 25, 2012 at 4:42 am

Forgot to mentioned….

the first single command will do the password change for local and remote which is..

mysql> UPDATE user SET Password=PASSWORD(‘NEW-PASSWORD-HERE’) WHERE User=’tom’;

Reply

9 Silvio March 6, 2012 at 12:26 pm

I did installed a xampp and mysql don’t change the root password in this time.

SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD(‘<the_my_password'); gives Query OK, 0 rows affected.

Reply

Leave a Comment

Tagged as: , , , , ,

Previous Faq:

Next Faq: