3 Best Ways to Change MySQL User Password By Examples

Created with Sketch.

3 Best Ways to Change MySQL User Password By Examples

Summary: in this tutorial, you will learn how to change MySQL user password using various statements such as UPDATE, SET PASSWORD,and ALTER USER statements.

How To Change MySQL User Password

Before changing the password of a MySQL user account, you should consider the following important questions:

  • Which user account do you want to change the password?
  • What application is using the user account whose password is being changed? If you change the password without changing the connection string of the application that is using the user account, the application will not be able to connect to the database server.

After having these questions answered. You can start changing the user account’s password.

MySQL provides various statements that you can use to change the password of a user including the UPDATE , SET PASSWORD, and GRANT USAGE statements.

Change MySQL user password using UPDATE statement

The first way to change the password is to use the UPDATE statement to update the user table of the mysql database.

After executing the UPDATE statement, you also need to execute the FLUSH PRIVILEGES  statement to reload privileges from the grant table in the mysql database.

Suppose you want to change the password for the dbadmin  user that connects from the localhost to dolphin, you need to execute the following statements:

USE mysql;UPDATE user
SET password = PASSWORD(‘dolphin’)
WHERE user = ‘dbadmin’ AND
host = ‘localhost’;

FLUSH PRIVILEGES;

Code language: SQL (Structured Query Language) (sql)

Note that from MySQL 5.7.6, the user table uses the authentication_string column only to store the password. In addition, it removed the password column.

Therefore if you use MySQL 5.7.6+, you must use the authentication_string column in the UPDATE statement instead:

USE mysql;UPDATE user
SET authentication_string = PASSWORD(‘dolphin’)
WHERE user = ‘dbadmin’ AND
host = ‘localhost’;

FLUSH PRIVILEGES;

Code language: SQL (Structured Query Language) (sql)

Notice that the PASSWORD() function computes the hash value from a plain text.

Change MySQL user password using the SET PASSWORD statement

The second way to change the password is by using the SET PASSWORD  statement.

You use the user account in user@host format to update the password. If you need to change the password for other accounts, your account needs to have at least UPDATE privilege.

By using the SET PASSOWORD statement, you don’t need to execute the FLUSH PRIVILEGES statement to reload privileges from grant tables.

The following statement changes the password of  dbadmin user account using the SET PASSWORD  statement.

SET PASSWORD FOR 'dbadmin'@'localhost' = PASSWORD('bigshark');

Code language: SQL (Structured Query Language) (sql)

Notice that from version 5.7.6, MySQL depreciated this syntax and may remove it in the future releases. Instead, it uses the plaintext password as follows:

SET PASSWORD FOR 'dbadmin'@'localhost' = bigshark;

Code language: SQL (Structured Query Language) (sql)

Change MySQL user password using ALTER USER statement

The third way to change the password for a user account is to use the ALTER USER statement with the IDENTIFIED BY  clause.

The following ALTER USER statement changes the password of the dbadmin user to littlewhale:

ALTER USER dbadmin@localhost IDENTIFIED BY 'littlewhale';

Code language: SQL (Structured Query Language) (sql)

In case you want to reset the password of the MySQL root account, you need to force the MySQL database server to stop and restart without using grant table validation.

In this tutorial, we have introduced you to some handy statements that allow you to change the password for a user account in MySQL database.

Leave a Reply

Your email address will not be published. Required fields are marked *