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

MySQL Master-Master Replication setup in 5 easy steps

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     in Windows,   or   under Linux).
  • Add the following lines just below the   section:
  • 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:   Don’t forget to change the YourPassword  YourUser and YourHost placeholders with your choosen password, user and user’s host (which is usually   or  ). Note that you can even change the root@localhost account’s password there, in which case you need to specify  .
  • Open the MySQL configuration file again and comment-out the   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   server/process and then launch it again adding the     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   folder. Just type   to access the mysql prompt: no password is necessary because the server was started with the     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!





About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies.

View all posts by Ryan