MySQL Change User Password

Previous Page
Next Page

The User password can be changed using the Update, Set password, and Alter user statements. We will look at each of these statements in turn.

Reset password using update statement in MySQL

We can reset a user’s password using the Update statements.

  • Before MySQL version 5.5, MySQL had the column password in the user table. 
  • After version 5.5, the column was removed and changed to authentication_string.
  • Authentication_string column stores a string.
  • We do not need to specify a hostname for a user even if they have one while changing the password.
  • To update the password, we do the following:
#Example of a user with a specific hostname
UPDATE user 
SET authentication_string = 'dolphin'
WHERE user = 'dbadmin';

#Example of a user without a specific hostname.
Update user
set authentication_string = 'newPassword'
where user = 'dev_user_1';
  • We should always do a FLUSH PRIVILEGES after each update, this statement reloads all the privileges in the Grants table for the user.
FLUSH PRIVILEGES;

Set password in MySQL

We can use the SET PASSWORD statement. The syntax for the construct is as follows:

SET PASSWORD FOR <username>@<hostname>   = ‘<new Password>’
  • For a user with the % hostname we can skip the @hostname part.
  • However, for a user who has a specific hostname assigned to it, we need to specify it, otherwise, MYSQL cannot find the record.

Alter user statement in MySQL

We can also use the Alter User statement along with the IDENTIFIED BY Clause to change the password. The Basic Syntax of the statement is as follows:

ALTER USER <username>@<hostname> IDENTIFIED BY '<newpassword>';

Example:

For a User with no hostname specified,

ALTER USER dev_user_4 IDENTIFIED BY 'pass1';

 For a user with a specific hostname,

ALTER USER dev_user_3@localhost IDENTIFIED BY 'pass100';

The Alter User statement will fail if the hostname is not specified.

 

Previous Page
Next Page