MySQL 5.7 User Password Expiration

Starting with MySQL 5.7.4 and in later versions, user passwords will now have an expiration timer set, by default, for 360 days. If you would like to keep this functionality, you certainly can; however, for some the need to change your user passwords once a year could be a nuisance.

If you suddenly come across the following error, it is likely that your user’s password as expired.

 ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
​
Before you can make any changes one of two actions must happen, otherwise, you could continually meet the same error.

Firstly, you could modify the password, thereby resetting the password expiry flag. You can accomplish this by using the following Query. Replacing ‘user’ with the user whose password is expired and ‘host’ which is the server’s address. Finally,‘MyNewPass’ would be the new password.

 ALTER USER 'user'@'host' IDENTIFIED BY 'MyNewPass';
​
A second option is to set the password expiry flag to never expire and restart the MySQL service.

The setting that is responsible for this behavior is "default_password_lifetime"

There are a few methods for modifying this setting for your MySQL server instances.

Within the MySQL server instance CLI (command line interface) or using a GUI client like PHPMyAdmin or MySQL Workbench, you can execute a query to modify the setting for all users password expiry. Once you have logged into your MySQL instance, run the following query to remove the Password Expiry.

show global variables like 'default_password_lifetime';
​

This query will output the value for the “default_password_lifetime” variable. The output should look similar to the following.

 +---------------------------+-------+
 | Variable_name | Value |
 +---------------------------+-------+
 | default_password_lifetime | 360 |
 +---------------------------+-------+
 1 row in set (0.00 sec)
​

As you can see in the example above the value is set to “360.” That numeric value is representative of days. So this value would be the system default.

To alter this value, run the query below:

 set global default_password_lifetime=0;
​

Note: When running SQL queries remember your semi-colon (";") at the end of the query!

Now that the value has been altered, you can run the same “show” query to see your change.

mysql> show global variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 0 |
+---------------------------+-------+
1 row in set (0.00 sec)
​

If changing the variable globally is a little too broad of a stroke, you can elect to modify this rule for a single user.

Log into your MySQL instance and then run the query below:

 ALTER USER 'user'@'host' PASSWORD EXPIRE NEVER
​

Note: Replace the user and host with the appropriate value.

This will allow the user you have specified in the query to have a password that will not expire; however, this will only affect the user for that particular “host.” You will need to apply the “Alter User” statement to all of the user/host iterations to ensure that the password remains the same for each one.

Note: You can find all of the user/host iterations by running the following query:

 SELECT User, Host FROM mysql.user;
​

This method of modifying the Password Expiration variable is not within the MySQL instance itself but instead, you will be manipulating the value from the configuration file. This method will require that you restart the MySQL instance to implement, where the variable through the query method will be implemented without a restart of the MySQL server.

Note: Make sure that the “default_password_lifetime” is nested, or placed under the [mysqld] line.