MySQL Change a User Password

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?

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;