Table of Contents
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:
-
123# 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:
|
1 |
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; |
To establish a global policy such that passwords never expire, you can add (or change) this option to your MySQL configuration file:
|
1 2 |
[mysqld] default_password_lifetime=0 |
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!
