Syntax:
SET PASSWORD syntax for MySQL 5.7.6 and higher:

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.

*Note*: SET PASSWORD ... = PASSWORD('auth_string') syntax is deprecated
as of MySQL 5.7.6 and will be removed in a future MySQL release. SET
PASSWORD ... = 'auth_string' syntax is not deprecated, but ALTER USER
is now the preferred statement for assigning passwords. For example:
ALTER USER user IDENTIFIED BY 'auth_string';

SET PASSWORD can be used with or without an explicitly named 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;

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

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. To see which account the
  server authenticated you as, invoke the CURRENT_USER() function:

SELECT CURRENT_USER();

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.7/en/account-names.html.
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 account's mysql.user table row. The host name part of
the account name, if omitted, defaults to '%'. 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('auth_string');

The password can be specified in these ways:

o Using the PASSWORD() function (deprecated as of MySQL 5.7.6)

  The 'auth_string' function 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.
  If SET PASSWORD rejects the hashed password value returned by
  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, the hashed password value returned by
  PASSWORD() is 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

  Permitted old_passwords values are described later in this section.

o Using the OLD_PASSWORD() function (permitted before MySQL 5.7.5
  only):

  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.

  *Note*: Passwords that use the pre-4.1 hashing method are less secure
  than passwords that use the native password hashing method and should
  be avoided. Pre-4.1 passwords are deprecated and support for them is
  removed in MySQL 5.7.5. Consequently, OLD_PASSWORD() is deprecated
  and is removed in MySQL 5.7.5. For account upgrade instructions, see
  http://dev.mysql.com/doc/refman/5.7/en/account-upgrades.html.

o Using a string without PASSWORD() or OLD_PASSWORD()

  For this syntax, the meaning differs in MySQL 5.7.6 and higher from
  earlier versions:

  o As of MySQL 5.7.6, SET PASSWORD interprets the string as a
    cleartext string and hashes it appropriately for the account
    authentication plugin before storing it in the mysql.user account
    row.

  o Before MySQL 5.7.6, SET PASSWORD interprets the string as a hashed
    password value to be stored directly. The string must be hashed in
    the format required by the account authentication plugin. A string
    not hashed appropriately causes client connections for the account
    to fail with an Access denied error.

For more information about setting passwords, see
http://dev.mysql.com/doc/refman/5.7/en/assigning-passwords.html.

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

