In this blog, you will study how to alter MySQL user password using several statements such as UPDATE, SET PASSWORD, and ALTER USER statements.
Before altering the password of a MySQL user account, you should study the following vital questions:
Which user account do you want to modify the password?
What application is using the user account whose password is being changed? If you alter the password without altering the connection string of the application that is using the user account, the application will not be able to connect to the database server.
After taking these questions answered. You can start altering the user account’s password.
MySQL provides several statements that you can use to change the password of a user with the UPDATE, SET PASSWORD, and GRANT USAGE statements.
Alter MySQL user password by means of UPDATE statement
The first way to modification the password is to use the UPDATE statement to update the user table of the mysql database.
After performing the UPDATE statement, you also need to perform the FLUSH PRIVILEGES statement to reload privileges from the grant table in the mysql database.
Presume you want to change the password for the dbadmin user that connects from the localhost to catfish, you need to execute the subsequent statements:
Note that from MySQL 5.7.6, the user table uses the authentication_string column only to store the password. In addition, it detached the password column.
Therefore, if you use MySQL 5.7.6+, you must use the authentication_string column in the UPDATE statement in its place:
Notice that the PASSWORD() function calculates the hash value from a plain text.
Alter MySQL user password by means of the SET PASSWORD statement
The second way to modification the password is by using the SET PASSWORD statement.
You practice the user account in user@host plan to update the password. If you need to change the password for other accounts, your account desires to have at least UPDATE privilege.
By using the SET PASSWORD statement, you don’t need to execute the FLUSH PRIVILEGES statement to reload privileges from grant tables.
The subsequent statement changes the password of dbadmin user account using the SET PASSWORD statement.
Notice that from version 5.7.6, MySQL depreciated this syntax and may eliminate it in the future releases. Instead, it uses the plaintext password as follows:
Change MySQL user password using ALTER USER statement
The third method to alteration the password for a user account is to use the ALTER USER statement with the IDENTIFIED BY clause.
The following ALTER USER statement changes the password of the dbadmin user to littlecat:
In situation you want to reset the password of the MySQL root account, you essential to force the MySQL database server to stop and restart without using grant table authentication.
In this blog, we have presented you to some handy statements that allow you to alteration the password for a user account in MySQL database.