MySQL Change a User Password Command Tutorial

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?

Tutorial details
Difficulty level Easy
Root privileges Yes
Requirements mysql
Est. reading time 2 mintues
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:
    mysql -u root -h localhost -p
  2. Run ALERT mysql command:
    ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';
  3. Finally type SQL command to reload the grant tables in the mysql database:
    FLUSH PRIVILEGES;

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

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

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

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

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;

Verify the new password settings

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)

Summing up

You learned how to change MySQL or MariaDB user password using the mysql command line on Linux, Unix, macOS, *BSD and Windows operating systems.


🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 14 comments so far... add one


CategoryList of Unix and Linux commands
Disk space analyzersdf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
14 comments… add one
  • Michael Sep 24, 2009 @ 23:28

    then…

    mysql> flush privileges;

  • Keilaron Feb 12, 2010 @ 15:37

    Or just use the SET PASSWORD command:
    http://dev.mysql.com/doc/refman/5.0/en/set-password.html

  • mehari Jan 4, 2011 @ 20:08

    This is good

  • amazed Jan 27, 2011 @ 8:10

    Very helpful information.

  • Zipfer Oct 12, 2011 @ 6:31

    You forgot – FLUSH PRIVILEGES; after update tables

  • stevyn Dec 1, 2011 @ 13:35

    You legend. Thank you :)

  • Re@lBanda Jan 25, 2012 @ 4:00

    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=’%’;

    • Re@lBanda Jan 25, 2012 @ 4:42

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

  • Silvio Mar 6, 2012 @ 12:26

    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.

  • mjt Sep 9, 2014 @ 4:40

    thx alot

  • archana Apr 20, 2016 @ 18:27

    Very quick tips, helpful

  • Alex Nov 29, 2016 @ 7:02

    Thanks, it work for me!

  • Steve Jan 24, 2017 @ 5:51

    In MySQL 5.7 the Password field has been removed and replaced with authentication_string . So, UPDATE mysql.user SET Password=PASSWORD('foobar') WHERE USER='tom' AND Host='localhost'; throws an error;

    ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

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

  • Edmunds Sigfrid Mar 31, 2021 @ 19:32

    Here how to change MySQL user password on Ubuntu 20.04 LTS with MySQL version
    mysql --version
    I am using this:
    mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)
    Now, I login as mysql root user (admin user):
    mysql -u root -p
    My user name is sigfrid and hostnames are locahost and 192.168.1.100:
    UPDATE mysql.user SET authentication_string = PASSWORD('NEWPASSWORD')
    WHERE User = 'sigfrid' AND Host = 'localhost';
    UPDATE mysql.user SET authentication_string = PASSWORD('NEWPASSWORD')
    WHERE User = 'sigfrid' AND Host = '192.168.1.100;

    I typed command twice for two different hosts. Then I did:
    FLUSH PRIVILEGES;
    After that I quit mysql:
    quit
    Now it worked. Thank you.

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum