☰ ×

Change or Alter User Password in MySQL

Last Updated: November 25th, 2020 by in Linux

Change or Alter User Password in MySQL

MySQL users contain different information including, username and password, host information and account privileges to access and manage the database. In some cases, you may need to change the password of the MySQL user. There are several ways to change the MySQL user password.

In this tutorial, we will show you how to change the MySQL user password in three different ways.

Requirements

  • A server running Linux operating system.
  • MySQL or MariaDB installed.
  • A root password is configured.

Login to the MySQL

Before starting, you will need to login to the MySQL shell with the root user.

You can login it with the following command:

mysql -u root -p

You should see the following output:


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

After logging into the MySQL shell, you can change the MySQL user password using your desired method.

Change MySQL User Password with ALTER USER Statement

You can use ALTER USER statement with the IDENTIFIED BY flag to change the MySQL user password.

For example, run the following command to change the password of a user testuser with new-password as their new password (you can change this to whatever password you want):

mysql> ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'new-password';
mysql> FLUSH PRIVILEGES;

Change MySQL User Password with UPDATE Statement

You can also use the UPDATE statement to change the MySQL user password.

This statement will update the user table of the MySQL database.

For example, run the following command to change the password of a user testuser with new-password:

For MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:

mysql> USE mysql;
mysql> UPDATE user SET password = PASSWORD('new-password') WHERE user = 'testuser' AND host = 'localhost';
mysql> FLUSH PRIVILEGES;

For MySQL 5.7.6 and higher or MariaDB 10.1.20 and higher:

mysql> USE mysql;
mysql> UPDATE user SET authentication_string = PASSWORD('new-1password') WHERE user = 'testuser' AND host = 'localhost';
mysql> FLUSH PRIVILEGES;

Change MySQL User Password with SET PASSWORD Statement

You can use the SET PASSWORD statement to change the MySQL user password easily.

For example, run the following command to change the password of a user testuser with new-password:

For MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:

mysql> SET PASSWORD FOR 'testuser'@'localhost' = PASSWORD('new-password');

For MySQL 5.7.6 and higher or MariaDB 10.1.20 and higher:

mysql> SET PASSWORD for 'testuser'@'localhost' = 'new-password';

Verify the Password

After changing the MySQL user password, you will need to verify it whether it is working or not.

You can verify it with the following command:

mysql -u testuser -p

You will be asked to provide your new password.

If everything is fine, you will be connected to the MySQL shell as shown below:

Conclusion

In the above guide, we’ve learned how to change the MySQL user password using three different ways. We hope this will help to reset the MySQL password.