With no FOR user clause, this statement sets the password for the current user:

SET PASSWORD = PASSWORD('cleartext password');

Any client who connects to the server using a nonanonymous account can change the password for that account. To see which account the server authenticated you for, invoke the CURRENT_USER() function:

SELECT CURRENT_USER();

With a FOR user clause, this statement sets the password for the named account, which must exist:

SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('cleartext password');

In this case, you must have the UPDATE privilege for the mysql database.

Using the PASSWORD() function

The function argument is the cleartext (unencrypted) password. PASSWORD() hashes the password and returns the encrypted password string.

The old_passwords system variable value determines the hashing method used by PASSWORD(). If SET PASSWORD rejects the password as not being in the correct format, it may be necessary to change old_passwords to change the hashing method. For example, if the account uses the mysql_native_password plugin, the old_passwords value must be 0:

SET old_passwords = 0;
SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');

If the old_passwords value differs from that required by the authentication plugin, hashed password values returned by PASSWORD() are not acceptable for that plugin and attempts to set the password produce an error. For example:

mysql> SET old_passwords = 1;
mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

Using the OLD_PASSWORD() function:

The function argument is the cleartext (unencrypted) password. OLD_PASSWORD() hashes the password using pre-4.1 hashing and returns the encrypted password string. This hashing method is appropriate only for accounts that use the mysql_old_password authentication plugin.

Using an already encrypted password string

The password is specified as a string literal. It must represent the already encrypted password value, in the hash format required by the authentication method used for the account.