Required credentials for clients that connect to the MySQL server can include a password. This section describes how to assign passwords for MySQL accounts.
MySQL stores passwords in the
user table in the
mysql system database. Operations that assign
or modify passwords are permitted only to users with the
CREATE USER privilege, or,
alternatively, privileges for the
INSERT privilege to
create new accounts,
privilege to modify existing accounts). If the
read_only system variable is
enabled, use of account-modification statements such as
CREATE USER or
SET PASSWORD additionally requires
The discussion here summarizes syntax only for the most common password-assignment statements. For complete details on other possibilities, see CREATE USER Syntax, ALTER USER Syntax, GRANT Syntax, and SET PASSWORD Syntax.
MySQL hashes passwords stored in the
table to obfuscate them. For most statements described here, MySQL
automatically hashes the password specified. An exception is
SET PASSWORD ... =
which you use the
function explicitly to hash the password. There are also syntaxes
PASSWORD that permit hashed values to be specified
literally; for details, see the descriptions of those statements.
MySQL uses plugins to perform client authentication; see Section 5.8, “Pluggable Authentication”. The authentication plugin associated with an account determines the algorithm used to hash passwords for that account.
To assign a password when you create a new account, use
CREATE USER and include an
IDENTIFIED BY clause:
mysql> CREATE USER 'jeffrey'@'localhost' -> IDENTIFIED BY 'mypass';
CREATE USER syntax, MySQL
automatically hashes the password before storing it in the
To assign or change a password for an existing account, use one of the following methods:
ALTER USERstatement with an
mysql> ALTER USER 'jeffrey'@'localhost' -> IDENTIFIED BY 'mypass';
If you are not connected as an anonymous user, you can change your own password without naming your own account literally:
mysql> ALTER USER USER() -> IDENTIFIED BY 'mypass';
ALTER USERsyntaxes, MySQL automatically hashes the password before storing it in the
ALTER USERsyntax for changing passwords is available as of MySQL 5.7.6.
mysql> SET PASSWORD FOR -> 'jeffrey'@'localhost' = PASSWORD('mypass');
If you are not connected as an anonymous user, you can change your own password by omitting the
mysql> SET PASSWORD = PASSWORD('mypass');
PASSWORD()function hashes the password using the hashing method determined by the value of the
old_passwordssystem variable value. If
SET PASSWORDrejects the hashed password value returned by
PASSWORD()as not being in the correct format, it may be necessary to change
old_passwordsto change the hashing method. See SET PASSWORD Syntax.
For this syntax, the meaning differs in MySQL 5.7.6 and higher from earlier versions:
As of MySQL 5.7.6,
SET PASSWORDinterprets the string as a cleartext string and hashes it appropriately for the account authentication plugin before storing it in the
mysql> SET PASSWORD FOR -> 'jeffrey'@'localhost' = 'mypass';
Before MySQL 5.7.6,
SET PASSWORDinterprets the string as a hashed password value to be stored directly.
mysql> SET PASSWORD FOR -> 'jeffrey'@'localhost' = '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
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
GRANT USAGEstatement at the global level (
ON *.*) to change an account password without affecting the account's current privileges:
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost' -> IDENTIFIED BY 'mypass';
GRANTsyntax, MySQL automatically hashes the password before storing it in the
To change an account password from the command line, use the mysqladmin command:
shell> mysqladmin -u user_name -h host_name password "new_password"
The account for which this command sets the password is the one with a
mysql.usertable row that matches
Usercolumn and the client host from which you connect in the
For password changes made using mysqladmin, MySQL automatically hashes the password before storing it in the