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 credentials in the
user table in
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
ALTER USER additionally requires
CONNECTION_ADMIN privilege (or
The discussion here summarizes syntax only for the most common password-assignment statements. For complete details on other possibilities, see Section 220.127.116.11, “CREATE USER Statement”, Section 18.104.22.168, “ALTER USER Statement”, and Section 22.214.171.124, “SET PASSWORD Statement”.
MySQL uses plugins to perform client authentication; see
Section 8.2.17, “Pluggable Authentication”. In password-assigning
statements, the authentication plugin associated with an account
performs any hashing required of a cleartext password specified.
This enables MySQL to obfuscate passwords prior to storing them in
mysql.user system table. For the statements
described here, MySQL automatically hashes the password specified.
There are also syntax for
ALTER USER that
permits hashed values to be specified literally. For details, see
the descriptions of those statements.
To assign a password when you create a new account, use
CREATE USER and include an
IDENTIFIED BY clause:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
To assign or change a password for an existing account, use the
ALTER USER statement with an
IDENTIFIED BY clause:
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
If you are not connected as an anonymous user, you can change your own password without naming your own account literally:
ALTER USER USER() IDENTIFIED BY 'password';
To change an account password from the command line, use the mysqladmin command:
mysqladmin -u user_name -h host_name password "password"
The account for which this command sets the password is the one
with a row in the
mysql.user system table that
user_name in the
User column and the client host from
which you connect in the
Setting a password using mysqladmin should be considered insecure. On some systems, your password becomes visible to system status programs such as ps that may be invoked by other users to display command lines. MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible. Also, on some systems this overwriting strategy is ineffective and the password remains visible to ps. (SystemV Unix systems and perhaps others are subject to this problem.)
If you are using MySQL Replication, be aware that a password used
by a replica as part of
SOURCE TO is effectively limited to 32 characters in
length; if the password is longer, any excess characters are
truncated. This is not due to any limit imposed by MySQL Server
generally, but rather is an issue specific to MySQL Replication.