How to change user password on mysql

Changing User Password in MySQL

Changing a user’s password in MySQL can be done using the ALTER USER or SET PASSWORD command, depending on your MySQL version and configuration. It's important to update passwords regularly for security reasons and to maintain control over your database environment.

1. Change User Password Using ALTER USER (MySQL 5.7.6 and Later)

The ALTER USER command is the recommended method for changing a user's password in MySQL versions 5.7.6 and later.

Syntax:

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

• username: The name of the MySQL user whose password you want to change.
• host: The host from which the user connects. Typically this is 'localhost', but it may be an IP address or a domain name.
• new_password: The new password for the user.

Example:

ALTER USER 'john'@'localhost' IDENTIFIED BY 'new_secure_password';

This command changes the password for the user john who connects from localhost to 'new_secure_password'.

Note: If you encounter an error like ERROR 1396 (HY000): Operation ALTER USER failed for 'username'@'host', it could be because the user does not exist or the privileges were not properly set.

2. Change User Password Using SET PASSWORD (Older Versions of MySQL)

If you're using an older version of MySQL (before 5.7.6), you can change the password using the SET PASSWORD statement.

Syntax:

SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

• username: The MySQL user whose password is being changed.
• host: The host from which the user connects (e.g., 'localhost').
• new_password: The new password is to be assigned to the user.

Example:

SET PASSWORD FOR 'john'@'localhost' = PASSWORD('new_secure_password');

This command changes the password for the user john who connects from localhost.

3. Change Password Using mysqladmin (Command-Line Tool)

Another method to change a MySQL user password is using the mysqladmin command-line tool, which can be executed from your terminal or command prompt.

Syntax:

mysqladmin -u username -p'old_password' password 'new_password'

• username: The MySQL user whose password is being changed.
• old_password: The current password of the user.
• new_password: The new password for the user.

Example:

mysqladmin -u john -p'old_password' password 'new_secure_password'

After executing the above command, the password for the user john will be changed to 'new_secure_password'.

4. Changing Root Password

If you're changing the root password, you can use the same methods described above. If you're changing the root password when logged in as root, use:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_root_password';

Or, for older versions:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_root_password');

Alternatively, using mysqladmin for root:

mysqladmin -u root -p'old_password' password 'new_root_password'

5. Refreshing Privileges

After changing a password, it’s a good idea to run the following command to ensure that MySQL’s privilege tables are reloaded:

FLUSH PRIVILEGES;

This ensures that the changes to user accounts and privileges are applied immediately.

6. Troubleshooting

• User Doesn’t Exist Error: If you receive an error stating that the user doesn't exist (ERROR 1133 (42000): Can't find any matching row in the user table), make sure that the user exists by running:

  SELECT user, host FROM mysql.user;
  

  This will list all users and their associated hosts in MySQL.

• Password Complexity: Ensure that your new password meets the required complexity rules (e.g., length, use of special characters, etc.), depending on your MySQL server's configuration.

• Access Denied Errors: If you receive Access denied errors after changing the password, verify that the user is correctly assigned the required privileges to access the MySQL server.

Conclusion

Changing MySQL user passwords is a straightforward task that can be done through the ALTER USER, SET PASSWORD, or mysqladmin methods. It is recommended to use ALTER USER for MySQL versions 5.7.6 and later, as it is the most secure and flexible method. Regularly updating passwords helps keep your database environment secure, and ensuring that privileges are properly managed is crucial for preventing unauthorized access.