MySQL Change a User Password

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 on MySQL server?

You need to use mysql (or mysql.exe on MS-Windows based system) command on a Linux or Unix like operating system. Open a terminal app or 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.

How to change user password on mysql

Mysql change user password using the following method:

  1. Open the bash shell and connect to the server as root user
  2. mysql -u root -h localhost -p
  3. Run command:
  4. ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';

Please note that use mysql.exe on MS-Windows host as follows (first change directory where mysql.exe is located [example: “C:Program Filesmysqlmysql-5.0.77-win32bin”]. Let us see examples and syntax in details.

mysql sql command to change a user password

Login as root from the shell:
$ mysql -u root -p
Switch to mysql database (type command at mysql> prompt, do not include string “mysql>”):
mysql> use mysql;
The syntax is as follows for mysql database server version 5.7.5 or older:

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

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

For mysql database server version 5.7.6 or newer use the following syntax:

ALTER USER 'user'@'hostname' IDENTIFIED BY 'newPass';

ALTER USER ‘user’@’hostname’ IDENTIFIED BY ‘newPass’;

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';

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');

SET PASSWORD FOR ‘tom’@’localhost’ = PASSWORD(‘foobar’);

OR

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

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;

FLUSH PRIVILEGES;

Sample outputs:

Query OK, 0 rows affected (0.00 sec)

To exit from mysql> prompt, enter:

quit;

quit;

User or you can test new password using the following shell syntax:
mysql -u tom -p
When promoted enter new password you set earlier for tom user.

Sample session

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

Posted by: SXI ADMIN

The author is the creator of SXI LLC and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.