MySQL - Change a user password

The MySQL SET PASSWORD statement or ALTER USER statement can be used to change the password of a user in the MySQL database.

Note: Rather than using SET PASSWORD to assign passwords, ALTER USER is the preferred statement for account alterations, including assigning passwords.

Syntax

The syntax of changing password using SET PASSWORD statement in MySQL is given below:

SET PASSWORD [FOR user_name]
  = {'auth_string'| TO RANDOM}

Parameters

`FOR user_name`	`Optional.` Specify the user for which the password need to be changed. If not provided, the password will be changed for the current user (see CURRENT_USER() function).
`auth_string`	First method assigns the account the given literal password.
`TO RANDOM`	Second method assigns the account a password randomly generated by MySQL. The statement also returns the cleartext password in a result set to make it available to the user or application executing the statement.

The syntax of changing password using ALTER USER statement in MySQL is given below:

ALTER USER user_name IDENTIFIED BY 'auth_string';

Parameters

`user_name`	`Required.` Specify the user for which the password need to be changed.
`auth_string`	`Required.` Assigns the given literal password to user_name.

Example: Changing password for current user

In the example below, the SET PASSWORD statement is used to change the password for current user to 'password1'.

SET PASSWORD = 'password1';

Example: Changing password by account name

In the example below, the password of account named 'john'@'localhost' is changed to 'password1'.

SET PASSWORD 
  FOR 'john'@'localhost' = 'password1';

ALTER USER 
  'john'@'localhost' IDENTIFIED BY 'password1';