MySQL in a Nutshell, 2nd Edition

Name

SET PASSWORD

Synopsis

SET PASSWORD [FOR 'user'@'host'] = PASSWORD('password')

Use this statement to change the password for a user account. The username and host must be given. The change of password will apply only to the given combination of username and host. It won’t apply to other hosts for the same user in the grant tables.

To get a list of user accounts on your server, enter the following SQL statement:

SELECT User, Host FROM mysql.user;

If the FOR clause is not given with the SET PASSWORD statement, the current user account is assumed. The PASSWORD() function will encrypt the password given.

This statement does not need to be followed by a FLUSH PRIVILEGES statement. It will automatically update the privileges cache for the new password. If you updated your server from a version before 4.1 to a new version, you may have problems changing a user account’s password and cause the user account’s password to become invalid. You may need to run the mysql_fix_privilege_tables utility to change the Password column in the user table in the mysql database. See Chapter 16 for more information on this utility.

Here is an example of changing a user account’s password:

SET PASSWORD FOR 'kenneth'@'localhost' = PASSWORD('his_password');