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.

Before changing the password, there are some points that you should consider:

• Which user do you want to change the password?

• What host, which the user connects to, you want to change the password?

• Have you been aware of the related applications that are currently using the MySQL account whose password is being changed?If you change the password without proper planning, those applications will lose the connection to the database and stop functioning properly.

MySQL provides several statements that you can use to change or reset password of a MySQL account including UPDATE statement, SET PASSWORD statement and GRANT USAGE statement.

MySQL changing password using UPDATE statement

The first way to change the password is to use the UPDATE statement to update the User and Host column of the user table in the mysql database. After executing the UPDATE statement, you need to execute the FLUSH PRIVILEGES statement to reload privileges from the grant table in the mysql database.

Suppose you want to change the password for OutOfMemory user that connect from the  OutOfMemory.CN host to Secret1970, you need to execute the following statements:

USE mysql; UPDATE user  SET password = PASSWORD('Secret1970') WHERE user = 'OutOfMemory' AND        host = 'OutOfMemory.CN'; FLUSH PRIVILEGES;

It is important to note that we’ve used the PASSWORD() function to encrypt the plain text password. The following example demonstrates how the PASSWORD() function encrypts a plain text password.

SELECT PASSWORD('Secret1970') AS encrypt_password

*16A54B5EE15C823362FAE7F64409A8F8C8501DD6

MySQL changing password using SET PASSWORD statement

The second way to change the password is by using the SET PASSWORD statement. You use the MySQL account in  user@host format to update the password. If you need to change password for other accounts, you need to have at least UPDATE privilege. You don’t need to execute the FLUSH PRVILILEGES statement to reload privileges from grant table.

The following statement change password of OutOfMemory account by using the SET PASSWORD statement.

SET PASSWORD FOR 'mysqltutorail'@'OutOfMemory.CN' = PASSWORD('Secret1970')

MySQL changing password using GRANT USAGE statement

The third way to change the password is by using the GRANT USAGE statement with the IDENTIFIED BY clause. In this way, you specify the password in plain text instead of the encrypted from.

The following statement changes the password of OutOfMemory account by using the GRANT USAGE statement:

GRANT USAGE ON *.* TO [email protected] IDENTIFIED BY Secret1970

In case you want to reset the MySQL root account’s password, you need to force the MySQL database server to stop and restart without using grant table validation.

In this tutorial, we introduced you to some handy MySQL statements that allows you to change MySQL accounts password.

Related Tutorials

• Getting Started with MySQL Access Control System

• MySQL Create User

• How to Use MySQL GRANT to Grant Privileges to Account

原文链接：http://outofmemory.cn/mysql/administration/mysql-changing-password