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 theuser table in themysql system database. Operations that assign or modify passwords are permitted only to users with theCREATE USER privilege, or, alternatively, privileges for themysql database (INSERT privilege to create new accounts,UPDATE privilege to modify existing accounts). If theread_only system variable is enabled, use of account-modification statements such asCREATE USER orALTER USER additionally requires theCONNECTION_ADMIN privilege (or the deprecatedSUPER privilege).
The discussion here summarizes syntax only for the most common password-assignment statements. For complete details on other possibilities, seeCREATE USER Statement,ALTER USER Statement, andSET PASSWORD Statement.
MySQL uses plugins to perform client authentication; seeSection 4.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 themysql.user system table. For the statements described here, MySQL automatically hashes the password specified. There are also syntax forCREATE USER andALTER 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, useCREATE USER and include anIDENTIFIED BY clause:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';CREATE USER also supports syntax for specifying the account authentication plugin. SeeCREATE USER Statement.
To assign or change a password for an existing account, use theALTER USER statement with anIDENTIFIED 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 themysqladmin command:
mysqladmin -uuser_name -hhost_name password "password" The account for which this command sets the password is the one with a row in themysql.user system table that matchesuser_name in theUser column and the client hostfrom which you connect in theHost column.
Setting a password usingmysqladmin should be consideredinsecure. On some systems, your password becomes visible to system status programs such asps 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 tops. (SystemV Unix systems and perhaps others are subject to this problem.)
If you are using MySQL Replication, be aware that, currently, a password used by a replica as part of aCHANGE REPLICATION SOURCE TO statement (from MySQL 8.0.23) orCHANGE MASTER TO statement (before MySQL 8.0.23) 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.