Mysql Password Expiry - How to Resolve and Then Disable

I recently had the issue that the root user for a MySQL installation had their password expire.

Apparently this is a fairly new MySQL feature which is designed to improve security and assist with meeting PCI-DSS requirements. However for this development box, it is not really necessary so I would prefer to disable.

The issue became apparent when trying to log into phpmyadmin and seeing the error message: “#1862 - Your password has expired. To log in you must change it using a client that supports expired passwords.”

On the command line the confusing thing is that you can log in, only when you run a query such as show databases; do you then get the error message:

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

So to fix this, firstly you can just reset your password back to the same one you were already using:

set password = password('mySecretPass');

And then to disable this you can use this query:

alter user 'root'@'localhost' password expire never;

Tags: mysqlpasswordexpirysolution