Changing Passwords in MySQL – An Easy Guide

In this tutorial, we will study how to change user passwords in MySQL using different statements. In MySQL, a user can change the password of the account using the following statements – UPDATE, SET PASSWORD, and ALTER USER. But first of all, we need to check for which user account the password needs to be changed and also for which application the password needs to be changed. When the password is changed we also need to grant new privileges so that the changes can be applied.

Changing Password Using The UPDATE statement

The UPDATE statement is used to modify the records of the table or change the password of the user account. Here we will change the password using the UPDATE statement.

When using the UPDATE statement, you need to also use the FLUSH PRIVILEGES statement to reload the grant table so that the changes will have an effect. The code for this is-

USE mysql;

UPDATE user 
SET password = PASSWORD('abcdefg')
WHERE user = 'root' AND 
      host = 'localhost';

FLUSH PRIVILEGES;Code language: SQL (Structured Query Language) (sql)

Here, the user is the root and the host is localhost.

In the 5.7.6 version of MySQL, SET password is written as SET authentication_string. The code for this is-

USE mysql;

UPDATE user 
SET authentication_string = PASSWORD('abcdefg')
WHERE user = 'root' AND 
      host = 'localhost';

FLUSH PRIVILEGES;Code language: SQL (Structured Query Language) (sql)

Changing Password Using SET PASSWORD

The SET PASSWORD statement is used to change the password of the user account. Here, in this FLUSH PRIVILEGES statement is not needed to reload the privileges or grant table because it is done automatically. The code for this is-

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('abcdefg');Code language: SQL (Structured Query Language) (sql)

Here, the user is root and the host is localhost.

In the 5.7.6 version of MySQL, the syntax is different and it is as follows-

SET PASSWORD FOR 'root'@'localhost' = ('abcdefg');Code language: SQL (Structured Query Language) (sql)

Here, we don’t need to use the PASSWORD() statement.

Changing Password Using ALTER USER

The ALTER USER statement is used to modify the user account password and it is used with IDENTIFIED BY statement. The code for this is-

ALTER USER dbuser@localhost IDENTIFIED BY 'abcdefg';Code language: SQL (Structured Query Language) (sql)

Here, the user is dbuser and the host is localhost. The password for dbuser@localhost is changed to ‘abcdefg’. But in the case where the user is root user, then we need to stop and start the MySQL server without using grant validation.

Conclusion

In this tutorial, we studied changing user passwords in MySQL by using some statements like UPDATE, SET PASSWORD, and ALTER USER.

For more information, you can refer to the official documentation of MySQL.