MySQL User Password Expired: how to (permanently) fix it

If you stumbled upon this post it probably means that your favourite MySQL management client (SQLyog, MySQL Workbench or any other) and/or your MySQL-based website or service is suddently unable to connect to your database server, leaving you with the following error message:

Your password has expired. To log in you must change it using a client that supports expired passwords.

This is a rather normal behaviour from MySQL 5.7.4 and above, where the automatic password expiration policy is enabled by default with a 360 days threshold -  meaning that all passwords will expire once a year. To read more about this, we strongly suggest to read the official MySQL 5.7 documentation by clicking here.

In a standard scenario, fixing such issue can be rather easy: you just have to login with an administrative user - such as the local root user - and change the expired password accordingly: needless to say, if the credentials are stored within a script (or website) configuration file, you will also need to change the password there as well. However, there are at least two scenarios that require further actions to take:

• If the root user password is also expired.
• If we want to prevent such issue from happening again.

The purpose of this post is to give a viable answer for both of them.

How to reset an expired root password

Among a wide number of available workarounds, I strongly suggest using the following (it works on Windows and Linux systems):

• Stop the MySQL service/process.
• Locate and open the MySQL configuration file used by your running MySQL instance (usually   C:\ProgramData\MySQL\MySQL Server 5.7\my.ini  in Windows, /etc/mysql/my.cnf  or /etc/mysql/my.cnf  under Linux).
• Add the following lines just below the [mysqld]  section:
• INI

  # Uncomment the following line to disable password check. # WARNING: this will allow any user to login, even with a wrong or expired password!!! skip-grant-tables

  1 2 3

  # Uncomment the following line to disable password check. # WARNING: this will allow any user to login, even with a wrong or expired password!!! skip-grant-tables

• Restart the MySQL service/process.
• Login to the server using your favourite MySQL GUI client with the root user - or any other administrative user - using any password (or even no passwords at all).
• Execute the following query: UPDATE mysql.user SET authentication_string = PASSWORD('YourPassword'), password_expired = 'N' WHERE User = 'YourUser' AND Host = 'YourHost'; FLUSH PRIVILEGES;  Don't forget to change the YourPassword  YourUser and YourHost placeholders with your choosen password, user and user's host (which is usually *  or localhost ). Note that you can even change the root@localhost account's password there, in which case you need to specify User = 'root' AND Host = 'localhost' .
• Open the MySQL configuration file again and comment-out the skip-grant-tables  command added moments ago.
• Stop and restart the MySQL service/process.

That's it!

If you don't want to alter the configuration file, you can just stop the mysqld  server/process and then launch it again adding the   --skip-grant-tables  option. If you have no MySQL clients available, you can also do the latest 4 steps from the command prompt by using the mysql command, which is available in the /bin/  folder. Just type mysql -u root  to access the mysql prompt: no password is necessary because the server was started with the   --skip-grant-tables  option.

How to disable the automatic password expiration policy

The following query can be used to disable the automatic password expiration for a specific user:

To establish a global policy such that passwords never expire, you can add (or change) this option to your MySQL configuration file:

IMPORTANT: you'll still need to manually set PASSWORD EXPIRE NEVER for every user created before the global policy change.

That's all for now: happy MySQL!

Print Friendly & PDF Download