How to Change MySQL User Password

Posted 

3 min read

In this tutorial, we will show you how to change MySQL user password. The instructions should work with any modern Linux distribution such as Ubuntu 18.04 and CentOS 7.

Prerequisites

Depending on the MySQL or MariaDB server version you are running on your system, you will need to use different commands to change the user password.

You can find your database server version by issuing the following command:

mysql --version

If you have MySQL installed in your system the output will look something like this:

mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

Or output like this for MariaDB:

mysql  Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Be sure to make note of which version of MySQL or MariaDB you’re running. If you want to get a list of all MySQL user accounts please check this guide.

How to Change MySQL User Password

Perform the steps below to change the MySQL user password:

1. Login to the MySQL shell as root

Access the MySQL shell by typing the following command and enter your MySQL root user password when prompted:

mysql -u root -p

If you haven’t set a password for the MySQL root user you can log in with sudo mysql.

2. Set the MySQL user password

  • Type the following commands if you have MySQL 5.7.6 and later or MariaDB 10.1.20 and later:

    ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
    FLUSH PRIVILEGES;

    If ALTER USER statement doesn’t work for you, you can modify the user table directly:

    UPDATE mysql.user SET authentication_string = PASSWORD('NEW_USER_PASSWORD')
    WHERE User = 'user-name' AND Host = 'localhost';
    FLUSH PRIVILEGES;
  • Type the following commands if you have MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:

    SET PASSWORD FOR 'user-name'@'localhost' = PASSWORD('NEW_USER_PASSWORD');
    FLUSH PRIVILEGES;

Make sure you change user-name with the name of the user you want to change the password to. If the user is connecting to the MySQL server from another host, change localhost with the remote hostname or IP Address.

In both cases if all goes well, you will see the following output:

Query OK, 0 rows affected (0.00 sec)

Log out from the MySQL prompt by executing:

quit;

3. Verify the new password

To verify that the new password has been applied correctly type:

mysql -u user-name -h localhost -p

You will be prompted to enter the new user password. Enter it, and you will be logged into your MySQL server.

Conclusion

In this tutorial, you have learned how to change MySQL or MariaDB user password. Make sure the new password is strong and secure and keep it in a safe place.

To learn more about how to use the MySQL shell to perform various administration operations you can check our guide about how to manage MySQL user accounts and databases.

Feel free to leave a comment if you have any questions.