SET PASSWORD [FOR user] =
    {
        PASSWORD('cleartext password')
      | OLD_PASSWORD('cleartext password')
      | 'encrypted password'
    }

The SET PASSWORD statement assigns a password to an existing MySQL user account. In MySQL 5.0, enabling the read_only system variable does not prevent use of SET PASSWORD.

If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the cleartext (unencrypted) password should be given as the argument to the function, which hashes the password and returns the encrypted password string. If the password is specified without using either function, it should be the already encrypted password value as a literal string. In all cases, the encrypted password string must be in the format required by the authentication method used for the account.

With no FOR user clause, this statement sets the password for the current user. (To see which account the server authenticated you as, invoke the CURRENT_USER() function.) Any client who successfully connects to the server using a nonanonymous account can change the password for that account.

With a FOR user clause, this statement sets the password for the named user. You must have the UPDATE privilege for the mysql database to do this. The user account name uses the format described in Section 6.2.3, “Specifying Account Names”. The user value should be given as 'user_name'@'host_name', where 'user_name' and 'host_name' are exactly as listed in the User and Host columns of the mysql.user table row. (If you specify only a user name, a host name of '%' is used.) For example, to set the password for an account with User and Host column values of 'bob' and '%.example.org', write the statement like this:

SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');

That is equivalent to the following statements:

UPDATE mysql.user SET Password=PASSWORD('cleartext password')
  WHERE User='bob' AND Host='%.example.org';
FLUSH PRIVILEGES;

Another way to set the password is to use GRANT:

GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';

The old_passwords system variable value determines the hashing method used by PASSWORD(). If you specify the password using that function and SET PASSWORD rejects the password as not being in the correct format, it may be necessary to set old_passwords to change the hashing method. For descriptions of the permitted values, see Section 5.1.4, “Server System Variables”.

For more information about setting passwords, see Section 6.3.5, “Assigning Account Passwords”

If you are using MySQL Replication, be aware that, currently, a password used by a replication slave as part of a CHANGE MASTER TO statement is effectively limited to 32 characters in length; the password can be longer, but any excess characters are truncated. This is not due to any limit imposed by the MySQL Server generally, but rather is an issue specific to MySQL Replication. (For more information, see Bug #43439.)