Syntax:
SET PASSWORD [FOR user] = password_option

password_option: {
    PASSWORD('auth_string')
  | OLD_PASSWORD('auth_string')
  | 'hash_string'
}

The SET PASSWORD statement assigns a password to a MySQL user account,
specified as either a cleartext (unencrypted) or encrypted value:

o 'auth_string' represents a cleartext password.

o 'hash_string' represents an encrypted password.

*Important*:

SET PASSWORD may be recorded in server logs or on the client side in a
history file such as ~/.mysql_history, which means that cleartext
passwords may be read by anyone having read access to that information.
For information about password logging in the server logs, see
http://dev.mysql.com/doc/refman/5.5/en/password-logging.html. For
similar information about client-side logging, see
http://dev.mysql.com/doc/refman/5.5/en/mysql-logging.html.

SET PASSWORD can be used with or without a FOR clause that explicitly
names a user account:

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

SET PASSWORD FOR 'jeffrey'@'localhost' = password_option;

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

SET PASSWORD = password_option;

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

SELECT CURRENT_USER();

Setting the password for a named account (with a FOR clause) requires
the UPDATE privilege for the mysql system database. Setting the
password for yourself (for a nonanonymous account with no FOR clause)
requires no special privileges. When the read_only system variable is
enabled, SET PASSWORD requires the SUPER privilege in addition to any
other required privileges.

If a FOR user clause is given, the account name uses the format
described in http://dev.mysql.com/doc/refman/5.5/en/account-names.html.
For example:

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

The host name part of the account name, if omitted, defaults to '%'.

The password can be specified in these ways:

o Use the PASSWORD() function

  The PASSWORD() argument is the cleartext (unencrypted) password.
  PASSWORD() hashes the password and returns the encrypted password
  string for storage in the mysql.user account row.

  The PASSWORD() function hashes the password using the hashing method
  determined by the value of the old_passwords system variable value.
  It should be set to a value compatible with the hash format required
  by the account authentication plugin. For example, if the account
  uses the mysql_native_password authentication plugin, old_passwords
  should be 0 for PASSWORD() to produce a hash value in the correct
  format. For mysql_old_password, old_passwords should be 1.

  Permitted old_passwords values are described later in this section.

o Use the OLD_PASSWORD() function:

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

o Use 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.

URL: http://dev.mysql.com/doc/refman/5.5/en/set-password.html

