Change User Password In MySQL

This article describes how to use UPDATE, SET PASSWORD, and ALTER USER statements to change user password in MySQL.

Changing the password of a MySQL user account is a common operation, but before changing the password of a MySQL user account, you should consider the following important issues:

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

After answering these questions, you can start changing the password of your user account.

In MySQL, you can use the UPDATE, SET PASSWORD and ALTER USER statements to change a user’s password.

Change MySQL User Password Using UPDATE Statement

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

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

Assuming that you want to change the password of the user sqliz connecting from the host localhost to Db123654, you need to execute the following statements step by step:

  1. Select the mysql database using MySQL USE command:

    USE mysql;

  2. Update the user’s password using this statement:

    UPDATE user
SET password = PASSWORD('Db123654')
WHERE user = 'sqliz' AND
      host = 'localhost';

  3. Reload privileges:

    FLUSH PRIVILEGES;

Note that as of MySQL 5.7.6, the user table uses a column named authentication_string to store passwords, not the column named password.

Therefore, if you are using MySQL 5.7.6+, you must modify the authentication_string column in the UPDATE statement:

UPDATE user
SET authentication_string = PASSWORD('Db123654')
WHERE user = 'sqliz' AND
      host = 'localhost';

Note that the PASSWORD() function computes the hash from plain text.

Use the SET PASSWORD statement to change MySQL user password

The second way to change the password is to use the SET PASSWORDstatement.

You can use a user account in the format user@host to update the password. If you need to change the password of another account, your account needs at least UPDATE privilege.

By using this SET PASSWORD statement, you can reload permissions from the grant table without executing the statement FLUSH PRIVILEGES.

The following statement uses the SET PASSWORD statement to change the password of the user sqliz:

SET PASSWORD FOR 'sqliz'@'localhost' = PASSWORD('Db123654');

Note that starting from version 5.7.6, you do not need to use the PASSWORD() function in the SET PASSWORD statement, use the clear text password directly, as follows:

SET PASSWORD FOR 'sqliz'@'localhost' = 'Db123654';

Change MySQL User Password Using ALTER USER Statement

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

The following ALTER USER statement changes the password of sqliz to Db123654:

ALTER USER sqliz@localhost IDENTIFIED BY 'Db123654';

Conclusion

This article introduces the three ways of modifying user passwords through examples:

  1. Modify the user table in the mysql database through the UPDATE statement.
  2. Use SET PASSWORDstatement.
  3. Use ALTER USERstatement.