Syntax:
SET PASSWORD [FOR user] = password_option

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

The SET PASSWORD statement assigns a password to a MySQL user account.
'auth_string' represents a cleartext (unencrypted) password.

*Note*:

o SET PASSWORD ... = PASSWORD('auth_string') syntax is deprecated in
  MySQL 5.7 and is removed in MySQL 8.0.

o SET PASSWORD ... = 'auth_string' syntax is not deprecated, but ALTER
  USER is the preferred statement for account alterations, including
  assigning passwords. For example:

ALTER USER user IDENTIFIED BY 'auth_string';

*Important*:

Under some circumstances, 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 the conditions under which
this occurs for the server logs and how to control it, see
https://dev.mysql.com/doc/refman/5.7/en/password-logging.html. For
similar information about client-side logging, see
https://dev.mysql.com/doc/refman/5.7/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' = 'auth_string';

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

SET PASSWORD = 'auth_string';

  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();

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

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

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

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.

The password can be specified in these ways:

o Use a string without PASSWORD()

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

  SET PASSWORD interprets the string as a cleartext string, passes it
  to the authentication plugin associated with the account, and stores
  the result returned by the plugin in the account row in the
  mysql.user system table. (The plugin is given the opportunity to hash
  the value into the encryption format it expects. The plugin may use
  the value as specified, in which case no hashing occurs.)

o Use the PASSWORD() function (deprecated in MySQL 5.7)

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

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

  The PASSWORD() function hashes the password using the hashing method
  determined by the value of the old_passwords system variable value.
  Be sure that old_passwords has the value corresponding to the hashing
  method expected by the authentication plugin associated with the
  account. 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('password');

  If the old_passwords value differs from that required by the
  authentication plugin, the hashed password value returned by
  PASSWORD() cannot be used by the plugin and correct authentication of
  client connections cannot occur.

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

