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   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:
  • 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!

 

 

 

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. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

One Comment on “MySQL User Password Expired: how to (permanently) fix it”

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.