Error while trying to change user password is one of the widely encountered issues in MySQL. This article addresses the FAQ on how to change MySQL user password with proper illustrations.
How to change my Password in MySQL?
In MySQL, changing the user password can be done using SET PASSWORD and ALTER USER.
1. SET PASSWORD
When the user is logged in already, the new password can be entered in “clear text” format, which gets stored either as a Hash or clear text in user table based on the authentication plugin in use.
2. ALTER USER
We can also change the user password by altering the “user” table. By using the following query, we can look at a few columns of our interest from the “user” table.
Here, the authentication string column holds the hash of the password in use by that user, which can be modified by:
The USER() function points to the current user, and hence this command will change the password of the current user to ‘new_password’, provided the user is logged in already.
How to change MySQL user password / How to change root password MySQL?
– “root” is the default superuser with all privileges in MySQL and its default password is empty. Since “root” is also a user, the password of it can also be changed using SET PASSWORD like this:
Change Password for user MySQL
In SET PASSWORD, the FOR clause is used to specify the user name for which we need to change the password (the saNoteme can be done using ‘ALTER USER’ command as well).
To execute this command successfully, the executing user should have all the credentials over the mentioned user to change the password.
How to reset MySQL Root password?
When you forget the root password of MySQL, you can go through the following steps to reset it.
- Stop MySQL.
- Restart mysqld with skip-grant-tables option. This option will not load authentication related files with MySQL and hence you will be allowed to access and change the password. In Linux,
- The skip-grant-tables option will also cut down remote access to the user and the authentication commands like ALTER USER and SET PASSWORD will not work either. So, We need to directly write an update statement on top of the user table.
(In MySQL versions prior to 5.7.x, the authentication_string field was represented by password field)
Where should I look for MySQL Password requirements?
This command will select all the environment variables starting with ‘validate_password’, which will give us all the requirements to set a password.
validate_password_dictionary_file – the pathname of the dictionary file, which is used to validate passwords in MySQL. By default, it is empty unless a validate password dictionary is installed.
validate_password_length – the minimal length of a password required.
validate_password_mixed_case_count – the minimum number of lower case and upper case characters required.
validate_password_number_count – the minimum count of numbers required to be part of a valid password.
valid_password_policy – the intensity of checks performed to validate a password. If it is LOW, only the length check is performed. If it MEDIUM, all checks other than dictionary file are performed and if it is STRONG, every check is performed to validate the given password.
validate_password_special_char_count – the minimal number of non-alphanumeric required to be part of a valid password.
How to resolve this error in MySQL: “Your password does not satisfy the current policy requirements”?
There can be two potential problems here.
- The entered password is not meeting the policy requirements. To resolve this, you can look into system variables starting with ‘validate_password’ and change accordingly (please refer question number 4 to look into the system variables).
- The other possible reason is that you might be repeating your historical password within a particular interval. For this, you need to look into password_history and password_reuse_interval system variables. You can also change these for a particular user by using ‘ALTER USER’ command.