Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.0Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 37.4Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 216.6Kb
Man Pages (Zip) - 329.5Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Assigning Account Passwords

6.3.6 Assigning Account Passwords

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 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 mysql database (INSERT privilege to create new accounts, UPDATE 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 the SUPER privilege.

The discussion here summarizes syntax only for the most common password-assignment statements. For complete details on other possibilities, see Section 13.7.1.2, “CREATE USER Syntax”, Section 13.7.1.1, “ALTER USER Syntax”, Section 13.7.1.4, “GRANT Syntax”, and Section 13.7.1.7, “SET PASSWORD Syntax”.

MySQL uses plugins to perform client authentication; see Section 6.3.9, “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 the mysql.user table. For the statements described here, MySQL automatically hashes the password specified. There are also syntaxes for CREATE USER and ALTER USER that permit 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';

CREATE USER also supports syntax for specifying the account authentication plugin. See Section 13.7.1.2, “CREATE USER Syntax”.

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 mysql.user table row that matches user_name in the User column and the client host from which you connect in the Host column.

If you are using MySQL Replication, be aware that, currently, a password used by a replication slave as part of a CHANGE MASTER TO statement 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 the MySQL Server generally, but rather is an issue specific to MySQL Replication. (For more information, see Bug #43439.)


User Comments
Sign Up Login You must be logged in to post a comment.