» MySQL Adminsitration » Changing Password for MySQL Account

MySQL Changing Password for Accounts

Summary: In this tutorial you will learn how to change or reset password for MySQL account in various ways by using the UPDATE, SET PASSWORD and GRANT statements.

MySQL provides several ways to allow you to change or reset password of MySQL account. Before changing password you have to answer the following questions:

  • Which user do you want to change the password?
  • From host which user connects to you want to change the password? 
  • Do you notify the related applications which are using the MySQL account you are planning to change password? Otherwise those applications will lose the connection to the database and stop functioning.

The first way to change the password is by using the UPDATE statement to update the User and Host values for account user table in the database MySQL. And then you need to run the command FLUSH PRIVILEGES to reload privileges from the grant table in the mysql database. Suppose you want to change the password for user ‘mysqltutorial from host mysqltutorial.org to ‘Secret1970, you need to perform the following queries:

USE mysql;

UPDATE user 
SET password = PASSWORD('Secret1970')
WHERE user = 'mysqltutorial' AND 
      host = 'mysqltutorial.org';
      
FLUSH PRIVILEGES;

Note that weve used the PASSWORD() function to encrypt plaintext password for storage in the password column of the grant table. Here is an example of using password function to return a encrypted format of plaintext password.

SELECT PASSWORD('Secret1970') AS encrypt_password

*16A54B5EE15C823362FAE7F64409A8F8C8501DD6

The second way to the password is by using SET PASSWORD statement. You use the MySQL account format as user@host to update the password. If you need to change password for other account, you need to have UPDATE privilege. You dont need to call FLUSH PRVILILEGES to reload privileges from grant table. Here is statement for changing password as above example:

SET PASSWORD FOR 'mysqltutorail'@'mysqltutorial.org' = PASSWORD('Secret1970')

The third way to change the password is by using GRANT USAGE statement with an IDENTIFIED BY clause, in which you specify the plaintext password instead of PASSWORD function. Here is the query to change the password by using GRANT USAGE with IDENTIFIED BY clause:

GRANT USAGE ON *.* TO ‘mysqltutorial@mysqltutorial.org IDENTIFIED BY ‘Secret1970

In the case that you want to reset the roots password without remembering the password, you need to force the server to stop and restart it without grant table validation.

Search